Skip to main content
This guide covers how to work with the database layer using Drizzle ORM, from defining schemas to running migrations.

​
Database Setup

The starter uses Drizzle ORM with SQLite for local development and Turso for production deployments.

​
Configuration

Database configuration is defined in drizzle.config.ts:
drizzle.config.ts
import { defineConfig } from "drizzle-kit";
import env from "@/env";

export default defineConfig({
  schema: "./src/db/schema.ts",
  out: "./src/db/migrations",
  dialect: "turso",
  casing: "snake_case",
  dbCredentials: {
    url: env.DATABASE_URL,
    authToken: env.DATABASE_AUTH_TOKEN,
  },
});
The casing: "snake_case" option automatically converts your camelCase column names to snake_case in the database.

​
Connection

The database client is initialized in src/db/index.ts:
src/db/index.ts
import { drizzle } from "drizzle-orm/libsql";
import env from "@/env";
import * as schema from "./schema";

const db = drizzle({
  connection: {
    url: env.DATABASE_URL,
    authToken: env.DATABASE_AUTH_TOKEN,
  },
  casing: "snake_case",
  schema,
});

export default db;

​
Environment Variables

Set up your database connection in .env:
.env
DATABASE_URL=file:dev.db
DATABASE_AUTH_TOKEN=  # Optional, only needed for Turso

DATABASE_URL=file:dev.db

​
Defining Schemas

Schemas are defined in src/db/schema.ts using Drizzle’s schema builder.

​
Basic Table Definition

src/db/schema.ts
import { integer, sqliteTable, text } from "drizzle-orm/sqlite-core";
import { createInsertSchema, createSelectSchema } from "drizzle-zod";

export const tasks = sqliteTable("tasks", {
  id: integer({ mode: "number" })
    .primaryKey({ autoIncrement: true }),
  name: text().notNull(),
  done: integer({ mode: "boolean" })
    .notNull()
    .default(false),
  createdAt: integer({ mode: "timestamp" })
    .$defaultFn(() => new Date()),
  updatedAt: integer({ mode: "timestamp" })
    .$defaultFn(() => new Date())
    .$onUpdate(() => new Date()),
});

​
Column Types

// Number mode (default)
id: integer({ mode: "number" })

// Boolean mode
done: integer({ mode: "boolean" })

// Timestamp mode
createdAt: integer({ mode: "timestamp" })

// Text column
name: text().notNull()

// Text with length limit
description: text({ length: 500 })

// Real (floating point)
price: real()

// Primary key with auto-increment
id: integer().primaryKey({ autoIncrement: true })

// Not null
name: text().notNull()

// Default value
status: text().default("pending")

// Unique
email: text().unique()

​
Automatic Timestamps

createdAt: integer({ mode: "timestamp" })
  .$defaultFn(() => new Date()),
updatedAt: integer({ mode: "timestamp" })
  .$defaultFn(() => new Date())
  .$onUpdate(() => new Date()),
  • $defaultFn() - Sets value on insert
  • $onUpdate() - Updates value on every update

​
Zod Integration

Drizzle schemas automatically generate Zod schemas for validation.

​
Generate Validation Schemas

src/db/schema.ts
import { createInsertSchema, createSelectSchema } from "drizzle-zod";
import { toZodV4SchemaTyped } from "@/lib/zod-utils";

// Schema for selecting data (includes generated fields)
export const selectTasksSchema = toZodV4SchemaTyped(
  createSelectSchema(tasks)
);

// Schema for inserting data (with custom validation)
export const insertTasksSchema = toZodV4SchemaTyped(
  createInsertSchema(tasks, {
    name: field => field.min(1).max(500),
  })
  .required({ done: true })
  .omit({
    id: true,
    createdAt: true,
    updatedAt: true,
  })
);

// Schema for partial updates
export const patchTasksSchema = insertTasksSchema.partial();
The toZodV4SchemaTyped utility ensures compatibility with Zod v4 while maintaining type safety.

​
Schema Customization

// Add custom validation rules
createInsertSchema(tasks, {
  name: field => field.min(1).max(500),
  email: field => field.email(),
})

// Make fields required
.required({ done: true })

// Omit fields
.omit({ id: true, createdAt: true })

// Pick specific fields
.pick({ name: true, done: true })

​
Database Operations

​
Query Data

import db from "@/db";
import { tasks } from "@/db/schema";

// Find all
const allTasks = await db.query.tasks.findMany();

// Find first match
const task = await db.query.tasks.findFirst({
  where(fields, operators) {
    return operators.eq(fields.id, id);
  },
});

// With conditions
const completedTasks = await db.query.tasks.findMany({
  where(fields, operators) {
    return operators.eq(fields.done, true);
  },
});

​
Insert Data

import { eq } from "drizzle-orm";

// Insert single record
const [inserted] = await db
  .insert(tasks)
  .values({ name: "New task", done: false })
  .returning();

// Insert multiple records
const inserted = await db
  .insert(tasks)
  .values([
    { name: "Task 1", done: false },
    { name: "Task 2", done: false },
  ])
  .returning();

​
Update Data

import { eq } from "drizzle-orm";

const [updated] = await db
  .update(tasks)
  .set({ done: true })
  .where(eq(tasks.id, id))
  .returning();

​
Delete Data

import { eq } from "drizzle-orm";

const result = await db
  .delete(tasks)
  .where(eq(tasks.id, id));

if (result.rowsAffected === 0) {
  // Record not found
}

​
Complex Queries

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

// Multiple conditions
const tasks = await db.query.tasks.findMany({
  where(fields, operators) {
    return and(
      eq(fields.done, false),
      like(fields.name, "%urgent%")
    );
  },
});

// OR conditions
const tasks = await db.query.tasks.findMany({
  where(fields, operators) {
    return or(
      eq(fields.priority, "high"),
      eq(fields.status, "urgent")
    );
  },
});

​
Migrations

1
Generate Migration
2
After modifying your schema, generate a migration:
3
pnpm drizzle-kit generate
4
This creates a SQL migration file in src/db/migrations/.
5
Push to Database
6
For development, you can push schema changes directly:
7
pnpm drizzle-kit push
8
drizzle-kit push directly modifies your database without creating migration files. Use generate + migrate for production.
9
Apply Migrations
10
For production deployments:
11
import { migrate } from "drizzle-orm/libsql/migrator";
import db from "@/db";

await migrate(db, { migrationsFolder: "./src/db/migrations" });
12
View Schema
13
Open Drizzle Studio to view and edit your data:
14
pnpm drizzle-kit studio

​
Relationships

Define relations between tables: 
import { relations } from "drizzle-orm";

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

export const tasks = sqliteTable("tasks", {
  id: integer().primaryKey({ autoIncrement: true }),
  name: text().notNull(),
  userId: integer().notNull().references(() => users.id),
});

export const usersRelations = relations(users, ({ many }) => ({
  tasks: many(tasks),
}));

export const tasksRelations = relations(tasks, ({ one }) => ({
  user: one(users, {
    fields: [tasks.userId],
    references: [users.id],
  }),
}));

​
Query with Relations

const userWithTasks = await db.query.users.findFirst({
  where: eq(users.id, userId),
  with: {
    tasks: true,
  },
});

​
Best Practices

Always use the Zod schemas generated from your Drizzle schemas. This ensures your API validation matches your database schema.
// Good
import { insertTasksSchema } from "@/db/schema";

// Avoid
const taskSchema = z.object({ ... });
Always check if records exist before performing operations.
const task = await db.query.tasks.findFirst(...);

if (!task) {
  return c.json(
    { message: "Not Found" },
    HttpStatusCodes.NOT_FOUND
  );
}
When performing multiple related database operations, use transactions.
await db.transaction(async (tx) => {
  await tx.insert(users).values(user);
  await tx.insert(tasks).values(task);
});
Add indexes to columns used in WHERE clauses.
export const tasks = sqliteTable("tasks", {
  userId: integer().notNull(),
}, (table) => ({
  userIdIdx: index("user_id_idx").on(table.userId),
}));

​
Next Steps

Routes

Use your schemas in API routes

Validation

Learn about request/response validation

Drizzle Docs

Official Drizzle ORM documentation

Build docs developers (and LLMs) love

Get started for freeTalk to us