SQL Database

layers/cms/server/db/schema.ts
layers/products/server/db/schema/products.ts

import { defineNuxtModule, createResolver } from '@nuxt/kit'

export default defineNuxtModule({
  setup(options, nuxt) {
    const { resolvePath } = createResolver(import.meta.url)
    nuxt.hook('hub:db:schema:extend', async ({ dialect, paths }) => {
      // Add your module drizzle schema files for the given dialect
      // e.g. ./schema/pages.postgresql.ts if hub.db is 'postgresql'
      paths.push(await resolvePath(`./schema/pages.${dialect}`))
    })
  }
})

import { users, posts } from 'hub:db:schema'

// Select types (for reading data)
export type User = typeof users.$inferSelect
export type Post = typeof posts.$inferSelect

// Insert types (for creating data)
export type NewUser = typeof users.$inferInsert
export type NewPost = typeof posts.$inferInsert

import { db, schema } from 'hub:db'

export default eventHandler(async (event) => {
  return await db.query.users.findMany()
  // or
  return await db.select().from(schema.users)
})

import { db, schema } from 'hub:db'

export default eventHandler(async (event) => {
  const { name, email } = await readBody(event)

  return await db
    .insert(schema.users)
    .values({
      name,
      email,
      createdAt: new Date()
    })
    .returning()
})

import { db, schema } from 'hub:db'

export default eventHandler(async (event) => {
  const { id } = getRouterParams(event)
  const { name } = await readBody(event)

  return await db
    .update(schema.users)
    .set({ name })
    .where(eq(tables.users.id, Number(id)))
    .returning()
})

import { db, schema } from 'hub:db'

export default eventHandler(async (event) => {
  const { id } = getRouterParams(event)

  const deletedUser = await db
    .delete(schema.users)
    .where(eq(schema.users.id, Number(id)))
    .returning()

  if (!deletedUser) {
    throw createError({
      statusCode: 404,
      message: 'User not found'
    })
  }

  return { deleted: true }
})

export default defineNuxtConfig({
  hub: {
    db: {
      dialect: 'postgresql',
      migrationsDirs: [
        'server/db/custom-migrations/'
      ]
    }
  }
})

npx nuxt db generate

npx nuxt db migrate

-PRAGMA foreign_keys = OFF;
+PRAGMA defer_foreign_keys = on;

ALTER TABLE ...

-PRAGMA foreign_keys = ON;
+PRAGMA defer_foreign_keys = off;

USAGE db generate [OPTIONS] 

OPTIONS
          --cwd    The directory to run the command in.
  -v, --verbose    Show verbose output.

USAGE db migrate [OPTIONS] 

OPTIONS

          --cwd    The directory to run the command in.                               
       --dotenv    Point to another .env file to load.
  -v, --verbose    Show verbose output.

USAGE db mark-as-migrated [OPTIONS] [NAME]

ARGUMENTS
  NAME    The name of the migration to mark as applied.    

OPTIONS
          --cwd    The directory to run the command in.                               
       --dotenv    Point to another .env file to load.
  -v, --verbose    Show verbose output.

USAGE db drop [OPTIONS] <TABLE>

ARGUMENTS
  TABLE    The name of the table to drop.    

OPTIONS
          --cwd    The directory to run the command in.                               
       --dotenv    Point to another .env file to load.
  -v, --verbose    Show verbose output.

USAGE db sql [OPTIONS] [QUERY]

ARGUMENTS
  QUERY    The SQL query to execute. If not provided, reads from stdin.    

OPTIONS
          --cwd    The directory to run the command in.                               
       --dotenv    Point to another .env file to load, relative to the root directory.
  -v, --verbose    Show verbose output.

npx nuxt db sql "SELECT * FROM users"
# or
npx nuxt db sql < dump.sql

export default defineNuxtConfig({
  hub: {
    db: {
      dialect: 'postgresql',
      driver: 'postgres-js', // Optional: explicitly choose driver
      connection: {
        connectionString: process.env.DATABASE_URL
      }
    }
  }
})

export default defineNuxtConfig({
  hub: {
    db: {
      dialect: 'sqlite',
      driver: 'd1-http'
    }
  }
})

export default defineNuxtConfig({
  hub: {
-    database: true
+    db: 'sqlite'
  }
})