Recipes

Drizzle ORM

Learn how to setup Drizzle ORM with NuxtHub.

Learn more about Drizzle ORM.

Setup

To enhance your Developer Experience with the database, we can create a useDrizzle() server composable with few steps.

Install Drizzle

  1. Install the drizzle-orm package to your project:
pnpm add drizzle-orm
  1. Install drizzle-kit development dependency to your project:
pnpm add -D drizzle-kit

drizzle.config.ts

Add a drizzle.config.ts file to your project:

drizzle.config.ts
import { defineConfig } from 'drizzle-kit'

export default defineConfig({
  schema: './server/database/schema.ts',
  out: './server/database/migrations'
})

Database Schema

server/database/schema.ts
import { sqliteTable, text, integer } from 'drizzle-orm/sqlite-core'

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

npm run db:generate

Let's add a db:generate script to the package.json:

package.json
{
  "scripts": {
    "db:generate": "drizzle-kit generate:sqlite"
  }
}

When running the npm run db:generate command, drizzle-kit will generate the migrations based on server/database/schema.ts and save them in the server/database/migrations directory.

Migrations

We are planning to update this section to leverage Nitro Tasks instead of a server plugin very soon.

We can create a server plugin to run the migrations in development automatically:

server/plugins/migrations.ts
import { consola } from 'consola'

export default defineNitroPlugin(async () => {
  if (!import.meta.dev) return

  onHubReady(async () => {
    const migrationsStorage = useStorage('root/server/database/migrations')
    let migrationFiles = await migrationsStorage.getKeys()
    migrationFiles = migrationFiles.filter(key => key.endsWith('.sql'))

    const database = hubDatabase()

    // Make sure to create the _hub_migrations table if it doesn't exist
    await database.prepare('CREATE TABLE IF NOT EXISTS _hub_migrations (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, created_at INTEGER NOT NULL)').run()

    // Get applied migrations from database
    const hubMigrations = await database.prepare('SELECT * FROM _hub_migrations').all()
    const appliedMigrations = (hubMigrations.results || []).map((row) => row.name)
    const missingMigrations = migrationFiles.filter(file => !appliedMigrations.includes(file))
    if (!missingMigrations.length) {
      consola.success('Database up-to-date and ready')
      return
    }

    // Apply missing migrations
    const appliedMigrationsStmts = []
    for (const file of missingMigrations) {
      consola.info(`Applying database migrations from ${file}...`)
      const migration = (await migrationsStorage.getItem<string>(file)) || ''
      const statements = migration.split('--> statement-breakpoint')
      for (let stmt of statements) {
        await database.prepare(stmt.trim()).run()
      }
      appliedMigrationsStmts.push(database.prepare('INSERT INTO _hub_migrations (name, created_at) VALUES (?, ?)').bind(file, Date.now()))
    }
    await database.batch(appliedMigrationsStmts)
    consola.success('Database migrations done')
  })
})
This solution is will create a _hub_migrations table in your database to keep track of the applied migrations. It will also run the migrations automatically in development mode.

useDrizzle()

Lastly, we can create a useDrizzle() server composable to interact with the database:

server/utils/drizzle.ts
import { drizzle } from 'drizzle-orm/d1'
export { sql, eq, and, or } from 'drizzle-orm'

import * as schema from '../database/schema'

export const tables = schema

export function useDrizzle() {
  return drizzle(hubDatabase(), { schema })
}

export type User = typeof schema.users.$inferSelect

We are exporting the tables object and the useDrizzle function to be used in our API handlers without having to import them (Nuxt does it for us as long as it's exported from a server/utils/ file).

This allows you to conveniently reference your tables and interact directly with the Drizzle API.

Note that we are also exporting the User type, which is inferred from the users table. This is useful for type-checking the results of your queries.
We also export the sql, eq, and, and or functions from drizzle-orm to be used in our queries.

Usage

Select

server/api/todos/index.get.ts
export default eventHandler(async () => {
  const todos = await useDrizzle().select().from(tables.todos).all()

  return todos
})

Insert

server/api/todos/index.post.ts
export default eventHandler(async (event) => {
  const { title } = await readBody(event)

  const todo = await useDrizzle().insert(tables.todos).values({
    title,
    createdAt: new Date()
  }).returning().get()

  return todo
})

Update

server/api/todos/[id
export default eventHandler(async (event) => {
  const { id } = getRouterParams(event)
  const { completed } = await readBody(event)

  const todo = await useDrizzle().update(tables.todos).set({
    completed
  }).where(eq(tables.todos.id, Number(id))).returning().get()

  return todo
})

Delete

server/api/todos/[id
export default eventHandler(async (event) => {
  const { id } = getRouterParams(event)

  const deletedTodo = await useDrizzle().delete(tables.todos).where(and(
    eq(tables.todos.id, Number(id))
  )).returning().get()

  if (!deletedTodo) {
    throw createError({
      statusCode: 404,
      message: 'Todo not found'
    })
  }
  return deletedTodo
})

Hooks

Use lifecycle hooks to stay synced with NuxtHub.