BackView as markdown

Setting up Drizzle

Database setup using Drizzle ORM with Bun's native SQLite driver, wired into the project's env validation and src/lib conventions.

Prerequisites

  • Bun project (uses drizzle-orm/bun-sqlite, the Bun-native driver)
  • src/lib/env.server.ts env validation convention already in place

Drizzle 1.0 is currently at release candidate rc5 — install drizzle-orm@rc5 and drizzle-kit@rc5 exactly as shown below. Do not run bun add drizzle-orm without @rc5.

Steps

  1. Install

    bun add drizzle-orm@rc5
bun add -d drizzle-kit@rc5

    drizzle-kit is the CLI for schema pushes, migrations and Drizzle Studio — dev dependency only.

  2. Add scripts to package.json

    • The bun --bun prefix is required so drizzle-kit resolves the SQLite driver through Bun's runtime rather than a Node.js shim
    {
  "scripts": {
    "db:push": "bun --bun drizzle-kit push",
    "db:studio": "bun --bun drizzle-kit studio",
  },
}

  3. Configure environment

    • Add DATABASE_URL to the server environment schema in src/lib/env.server.ts
    // src/lib/env.server.ts
import { z } from "zod";

const serverEnvSchema = z.object({
  DATABASE_URL: z.string().min(1, "DATABASE_URL is required"),
});

export default serverEnvSchema.parse(process.env);
    • The database path is non-secret config, so it belongs in .env (committed) rather than .env.local
    # .env
DATABASE_URL=file:./local.db
    • Add the database file to .gitignore
    local.db

  4. Create drizzle.config.ts at the project root

    • Reads process.env directly rather than @/lib/env.server as drizzle-kit runs outside the app
    // drizzle.config.ts
import { defineConfig } from "drizzle-kit";

export default defineConfig({
  out: "./drizzle",
  schema: "./src/lib/db.schema.ts",
  dialect: "sqlite",
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
});

  5. Define the schema

    // src/lib/db.schema.ts
import { int, sqliteTable, text } from "drizzle-orm/sqlite-core";

export const notesTable = sqliteTable("notes", {
  id: int().primaryKey({ autoIncrement: true }),
  content: text().notNull(),
  createdAt: int({ mode: "timestamp_ms" }).notNull(),
  updatedAt: int({ mode: "timestamp_ms" }).notNull(),
});
    • int({ mode: "timestamp_ms" }) stores timestamps as milliseconds since epoch and maps them to JS Date objects — assign new Date() or Date.now() directly

  6. Wire into project

    • Create the client in src/lib/db.server.ts, using drizzle-orm/bun-sqlite (the Bun-native driver)
    // src/lib/db.server.ts
import { drizzle } from "drizzle-orm/bun-sqlite";

import * as schema from "@/lib/db.schema";
import env from "@/lib/env.server";

export const db = drizzle(env.DATABASE_URL, { schema });
    // src/lib/notes.server.ts
import { db } from "@/lib/db.server";
import { notesTable } from "@/lib/db.schema";

export async function listNotes() {
  return await db.select().from(notesTable);
}
    // src/lib/notes.functions.ts
import { createServerFn } from "@tanstack/react-start";

import { listNotes } from "@/lib/notes.server";

export const listNotesFn = createServerFn().handler(() => listNotes());

  7. Push the schema

    bun db:push

Verification

  • local.db was created after running bun db:push
  • bun db:studio opens and shows the notes table

Environment variables

  • DATABASE_URL — SQLite file path, e.g. file:./local.db. Non-secret, goes in committed .env

Gotchas

  • Must install drizzle-orm@rc5 / drizzle-kit@rc5 — plain bun add drizzle-orm pulls a version that doesn't match this setup

References