Drizzle ORM

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

Install the drizzle-orm package to your project:

pnpm add drizzle-orm

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({
  dialect: 'sqlite',
  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"
  }
}

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 can create a server plugin to run the migrations in development automatically:

server/plugins/migrations.ts

import { consola } from 'consola'
import { migrate } from 'drizzle-orm/d1/migrator'

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

  onHubReady(async () => {
    await migrate(useDrizzle(), { migrationsFolder: 'server/database/migrations' })
      .then(() => {
        consola.success('Database migrations done')
      })
      .catch((err) => {
        consola.error('Database migrations failed', err)
      })
  })
})

Drizzle will create a __drizzle_migrations table in your database to keep track of the applied migrations. It will also run the migrations automatically in development mode.

To apply the migrations in staging or production, you can run the server using npx nuxi dev --remote command to connect your local server to the remote database, learn more about remote storage.

We are planning to update this section to leverage Nitro Tasks instead of a server plugin in the future.

`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.

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].patch.ts

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].delete.ts

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