Drizzle ORM

Type-safe SQL with schema definitions, migrations, and zero runtime overhead. Drizzle generates queries at build time and gives you full TypeScript inference from schema to route.

Install

bun add drizzle-orm
bun add -d drizzle-kit

Define Your Schema

Create a schema file that defines your tables. Drizzle uses TypeScript functions to declare columns, constraints, and defaults.

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

export const tasks = sqliteTable('tasks', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  title: text('title').notNull(),
  done: integer('done', { mode: 'boolean' }).notNull().default(false),
  createdAt: text('created_at').notNull().default('datetime("now")'),
});

export const users = sqliteTable('users', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  email: text('email').notNull().unique(),
  name: text('name').notNull(),
});

Setup in server.ts

Create the Drizzle client with your SQLite database and share it via context. The schema type flows through to every query.

// server.ts
import { createRouter } from '@cmj/juice/runtime';
import { createContextKey, setContext } from '@cmj/juice/runtime';
import { Database } from 'bun:sqlite';
import { drizzle, BunSQLiteDatabase } from 'drizzle-orm/bun-sqlite';
import * as schema from './db/schema.js';

const sqlite = new Database('app.db');
const db = drizzle(sqlite, { schema });

export type DB = BunSQLiteDatabase<typeof schema>;
export const dbKey = createContextKey<DB>('db');

const router = createRouter({
  onBeforeRequest: async (req) => {
    setContext(req, dbKey, db);
  },
  // ... routes
});

export default {
  fetch: router.fetch,
};

Type-Safe Queries in Routes

Every query is fully typed. Column names, return types, and where conditions are all inferred from the schema.

// app/routes/home.tsx
import React from 'react';
import { getContext } from '@cmj/juice/runtime';
import { dbKey } from '../../server.js';
import { tasks } from '../../db/schema.js';
import { desc, eq } from 'drizzle-orm';

export default async function Home({ request }: { request: Request }) {
  const db = getContext(request, dbKey);

  // SELECT * FROM tasks ORDER BY created_at DESC
  const allTasks = await db.select().from(tasks).orderBy(desc(tasks.createdAt));

  return (
    <div>
      <h2>Tasks</h2>
      <ul>
        {allTasks.map(task => (
          <li key={task.id}>
            {task.done ? '\u2713' : '\u25CB'} {task.title}
          </li>
        ))}
      </ul>
    </div>
  );
}

// app/routes/add.tsx
import React from 'react';
import { getContext, redirect } from '@cmj/juice/runtime';
import { dbKey } from '../../server.js';
import { tasks } from '../../db/schema.js';

async function addTask(formData: FormData) {
  'use server';
  const title = formData.get('title') as string;

  if (!title?.trim()) {
    return { error: 'Title is required' };
  }

  const db = getContext(this.request, dbKey);

  // INSERT INTO tasks (title) VALUES (?)
  await db.insert(tasks).values({ title: title.trim() });

  redirect('/', 303);
}

export default function AddTask() {
  return (
    <form action={addTask} method="POST">
      <input name="title" placeholder="Task title" required />
      <button type="submit">Add</button>
    </form>
  );
}

Filtering and Conditions

import { eq, and, like } from 'drizzle-orm';

// Find incomplete tasks
const pending = await db
  .select()
  .from(tasks)
  .where(eq(tasks.done, false));

// Search by title
const results = await db
  .select()
  .from(tasks)
  .where(and(
    like(tasks.title, `%${search}%`),
    eq(tasks.done, false)
  ));

// Update a task
await db
  .update(tasks)
  .set({ done: true })
  .where(eq(tasks.id, taskId));

// Delete a task
await db
  .delete(tasks)
  .where(eq(tasks.id, taskId));

Migrations with drizzle-kit

Create a drizzle.config.ts file and use drizzle-kit to generate and run migrations when your schema changes.

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

export default defineConfig({
  schema: './db/schema.ts',
  out: './db/migrations',
  dialect: 'sqlite',
  dbCredentials: {
    url: './app.db',
  },
});

# Generate a migration after changing schema
bunx drizzle-kit generate

# Apply pending migrations
bunx drizzle-kit migrate

# Open Drizzle Studio to browse your data
bunx drizzle-kit studio

Other Runtimes

import { drizzle } from 'drizzle-orm/d1';

// In server.ts (Workers entry)
export default {
  async fetch(req: Request, env: { DB: D1Database }) {
    const db = drizzle(env.DB, { schema });
    setContext(req, dbKey, db);
    return handler(req);
  },
};

import { drizzle } from 'drizzle-orm/better-sqlite3';
import Database from 'npm:better-sqlite3';
const db = drizzle(new Database('app.db'), { schema });

import { drizzle } from 'drizzle-orm/postgres-js';
import postgres from 'postgres';
const client = postgres(process.env.DATABASE_URL!);
const db = drizzle(client, { schema });