SQL Database

NuxtHub Database provides a type-safe SQL database powered by Drizzle ORM, supporting PostgreSQL, MySQL, and SQLite with smart detection and automatic migrations at build time.

Getting started

pnpm add drizzle-orm drizzle-kit postgres @electric-sql/pglite

yarn add drizzle-orm drizzle-kit postgres @electric-sql/pglite

npm install drizzle-orm drizzle-kit postgres @electric-sql/pglite

bun add drizzle-orm drizzle-kit postgres @electric-sql/pglite

deno add npm:drizzle-orm drizzle-kit postgres @electric-sql/pglite

npx nypm add drizzle-orm drizzle-kit postgres @electric-sql/pglite

pnpm add drizzle-orm drizzle-kit mysql2

yarn add drizzle-orm drizzle-kit mysql2

npm install drizzle-orm drizzle-kit mysql2

bun add drizzle-orm drizzle-kit mysql2

deno add npm:drizzle-orm drizzle-kit mysql2

npx nypm add drizzle-orm drizzle-kit mysql2

pnpm add drizzle-orm drizzle-kit @libsql/client

yarn add drizzle-orm drizzle-kit @libsql/client

npm install drizzle-orm drizzle-kit @libsql/client

bun add drizzle-orm drizzle-kit @libsql/client

deno add npm:drizzle-orm drizzle-kit @libsql/client

npx nypm add drizzle-orm drizzle-kit @libsql/client

export default defineNuxtConfig({
  hub: {
    database: 'postgresql'
  }
})

export default defineNuxtConfig({
  hub: {
    database: 'mysql'
  }
})

export default defineNuxtConfig({
  hub: {
    database: 'sqlite'
  }
})

import { pgTable, text, serial, timestamp } from 'drizzle-orm/pg-core'

export const users = pgTable('users', {
  id: serial().primaryKey(),
  name: text().notNull(),
  email: text().notNull().unique(),
  password: text().notNull(),
  avatar: text().notNull(),
  createdAt: timestamp().notNull().defaultNow(),
})

import { mysqlTable, text, serial, timestamp } from 'drizzle-orm/mysql-core'

export const users = mysqlTable('users', {
  id: serial().primaryKey(),
  name: text().notNull(),
  email: text().notNull().unique(),
  password: text().notNull(),
  avatar: text().notNull(),
  createdAt: timestamp().notNull().defaultNow(),
})

import { sqliteTable, text, integer } from 'drizzle-orm/sqlite-core'

export const users = sqliteTable('users', {
  id: integer().primaryKey({ autoIncrement: true }),
  name: text().notNull(),
  email: text().notNull().unique(),
  password: text().notNull(),
  avatar: text().notNull(),
  createdAt: integer({ mode: 'timestamp' }).notNull(),
})

import * as schema from 'hub:database:schema'
// or
import { schema } from 'hub:database'

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

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

npx nuxthub database generate

Query database

Now that you have your database schema and migrations set up, you can start querying your database.

SQL Select

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

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

SQL Insert

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

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

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

SQL Update

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

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()
})

SQL Delete

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

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 }
})

Database migrations

Database migrations provide version control for your database schema. NuxtHub supports SQL migration files (.sql) and automatically applies them during development and deployment. Making them fully compatible with Drizzle Kit generated migrations.

Migrations Directories

NuxtHub scans server/database/migrations for migrations in each Nuxt layer.

To scan additional directories, specify them in your config:

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

For more control (e.g., in Nuxt modules), use the hub:database:migrations:dirs hook:

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

export default defineNuxtModule({
  meta: {
    name: 'my-auth-module'
  },
  setup(options, nuxt) {
    const { resolve } = createResolver(import.meta.url)

    nuxt.hook('hub:database:migrations:dirs', (dirs) => {
      dirs.push(resolve('./db-migrations'))
    })
  }
})

CREATE TABLE IF NOT EXISTS users (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT NOT NULL
);

Automatic migrations

Migrations are automatically applied when you:

• Start the development server (npx nuxi dev)
• Build the application (npx nuxi build)

Applied migrations are tracked in the _hub_migrations database table.

Generating migrations

Once you have updates your database schema, you can generate new migrations using the following command:

npx nuxthub database generate

This will generate new migrations files in server/database/migrations/{dialect}/ which are automatically applied during development and deployment.

Applying migrations

Once you have generated new migrations, you can apply them using the following command:

npx nuxthub database migrate

This will apply the new migrations to your database.

Post-migration queries

Use the hub:database:queries:paths hook to run additional queries after migrations:

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

export default defineNuxtModule({
  meta: {
    name: 'my-auth-module'
  },
  setup(options, nuxt) {
    const { resolve } = createResolver(import.meta.url)

    nuxt.hook('hub:database:queries:paths', (paths, dialect) => {
      paths.push(resolve(`./db-queries/seed-admin.${dialect}.sql`))
    })
  }
})

INSERT OR IGNORE INTO admin_users (id, email, password) VALUES (1, 'admin@nuxt.com', 'admin');

Foreign-key constraints

For Cloudflare D1 with Drizzle ORM migrations, replace:

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

ALTER TABLE ...

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

Database seed

You can populate your database with initial data using Nitro Tasks:

export default defineNuxtConfig({
  nitro: {
    experimental: {
      tasks: true
    }
  }
})

export default defineTask({
  meta: {
    name: 'db:seed',
    description: 'Seed database with initial data'
  },
  async run() {
    console.log('Seeding database...')

    const users = [
      {
        name: 'John Doe',
        email: 'john@example.com',
        password: 'hashed_password',
        avatar: 'https://i.pravatar.cc/150?img=1',
        createdAt: new Date()
      },
      {
        name: 'Jane Doe',
        email: 'jane@example.com',
        password: 'hashed_password',
        avatar: 'https://i.pravatar.cc/150?img=2',
        createdAt: new Date()
      }
    ]

    await useDrizzle().insert(tables.users).values(users)

    return { result: 'Database seeded successfully' }
  }
})

Local development

During local development, view and edit your database from Nuxt DevTools using the Drizzle Studio:

Advanced configuration

For advanced use cases, you can explicitly configure the database connection:

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

Migration guide

Configuration changes

The database option now accepts a SQL dialect instead of a boolean to enable it.

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

Valid dialects are sqlite, postgresql and mysql.

API changes

The old hubDatabase() function has been removed. You must now use Drizzle ORM.

Before:

const db = hubDatabase()
const result = await db.prepare('SELECT * FROM users').all()

After:

const db = useDrizzle()
const result = await db.select().from(tables.users)

Migration files

Your existing SQL migration files continue to work! No changes needed.