Nuxt 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: {
    db: 'postgresql'
  }
})

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

export default defineNuxtConfig({
  hub: {
    db: '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(),
})

npx nuxt db generate

Local development

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

Build-time Hooks

export default defineNuxtConfig({
  hooks: {
    'hub:db:migrations:dirs': (dirs) => {
      dirs.push('my-module/db/migrations')
    }
  }
})

export default defineNuxtConfig({
  hooks: {
    'hub:db:queries:paths': (queries, dialect) => {
      queries.push('my-module/db/queries')
    }
  }
})

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

export default defineNuxtModule({
  setup(options, nuxt) {
    const { resolve } = createResolver(import.meta.url)

    nuxt.hook('hub:db:schema:extend', ({ paths, dialect }) => {
      paths.push(resolve(`./db/schema/pages.${dialect}.ts`))
    })
  }
})

Advanced configuration

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

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

Column casing

Database model names often use snake_case conventions, while in TypeScript, it is common to use camelCase for naming models. To address this, you can use the casing option to automatically map camelCase JavaScript keys to snake_case in the database:

export default defineNuxtConfig({
  hub: {
    db: {
      dialect: 'postgresql',
      casing: 'snake_case'
    }
  }
})

This allows you to use camelCase in your TypeScript schema while Drizzle automatically maps them to snake_case in the database:

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

export const users = pgTable('users', {
  id: serial().primaryKey(),
  firstName: text().notNull(), // Maps to first_name in the database
  lastName: text().notNull(),  // Maps to last_name in the database
  createdAt: timestamp().notNull().defaultNow() // Maps to created_at
})

D1 over HTTP

Use the d1-http driver to access a Cloudflare D1 database over HTTP. This is useful when you want to query your D1 database when hosting on other platforms.

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

This driver requires the following environment variables:

Neon Serverless

Use the neon-http driver to connect to Neon serverless PostgreSQL. This driver uses HTTP protocol optimized for serverless environments.

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

Install the required dependency:

pnpm add @neondatabase/serverless

This driver requires the following environment variable:

AI Agents

If you work with an IDE that supports AI agents, you can add the following text in your Agents.md or .cursor/rules file:

# Agent Instructions

/** ... your agent instructions ... */

## Database

- **Database Dialect**: The database dialect is set in the `nuxt.config.ts` file, within the `hub.db` option or `hub.db.dialect` property.
- **Drizzle Config**: Don't generate the `drizzle.config.ts` file manually, it is generated automatically by NuxtHub.
- **Generate Migrations**: Use `npx nuxt db generate` to automatically generate database migrations from schema changes
- **Never Write Manual Migrations**: Do not manually create SQL migration files in the `server/db/migrations/` directory
- **Workflow**:
  1. Create or modify the database schema in `server/db/schema.ts` or any other schema file in the `server/db/schema/` directory
  2. Run `npx nuxt db generate` to generate the migration
  3. Run `npx nuxt db migrate` to apply the migration to the database, or run `npx nuxt dev` to apply the migration during development
- **Access the database**: Use the `db` instance from `hub:db` to query the database, it is a Drizzle ORM instance.

Migrating from v0.9

Configuration changes

The database option has been renamed to db and now accepts a SQL dialect instead of a boolean.

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

Valid dialects are sqlite, postgresql and mysql.

Directory changes

The database directory has been renamed from server/database/ to server/db/:

- server/database/schema.ts
+ server/db/schema.ts

- server/database/migrations/
+ server/db/migrations/

Make sure to move your schema and migration files to the new location.

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 result = await db.select().from(tables.users)

Migration files

Your existing SQL migration files continue to work! Just move them to server/db/migrations/.