Drizzle ORM migrations in SvelteKit

Why Drizzle over Prisma for multi-tenant SaaS

This is a pragmatic comparison, not a religious one. The reasons the starter chose Drizzle are specific and verifiable:

• SQL-first, not ORM-first. Drizzle's column builders (text(), integer()) map directly to SQL column types. You're writing SQL concepts in TypeScript, not learning a proprietary query DSL. The generated migration SQL is readable because it was never abstracted away.
• No codegen step. Types are inferred from the schema definition at TypeScript compile time. Prisma requires prisma generate to produce a client with types; Drizzle's types come from importing your schema. Fewer moving parts in CI, fewer "types are stale" surprises.
• Smaller runtime. Drizzle ORM is a thin query builder — it doesn't ship a query engine, a connection pool, or a schema introspection layer at runtime. For a SvelteKit app deployed on Cloudflare Workers or a single VPS, the smaller bundle matters.
• SQLite is a first-class citizen. Prisma's SQLite support exists but historically trails PostgreSQL features. Drizzle was designed with SQLite from the start — WAL mode, better-sqlite3 synchronous driver, and file-based storage all work without workarounds.
• Portability without lock-in. The schema file defines column types that map to SQL primitives. Swapping from SQLite to Postgres means changing the driver import in one file (db/index.ts), not rewriting every query. The multi-tenant auth guide discusses this portability explicitly.

Where Prisma wins — auto-generated migrations from schema diffs, a visual studio, and a larger ecosystem — are real advantages for teams that want those tools. The starter trades those for directness and a smaller surface area, which fits the constraint of a solo-operated product with near-zero infrastructure cost.

Schema definition with Drizzle's column builder

The entire schema lives in one file: src/lib/server/db/schema.ts. Every table is defined with sqliteTable() and typed column builders. Here's the structure as shipped, with the key decisions annotated:

import { index, integer, sqliteTable, text, uniqueIndex } from 'drizzle-orm/sqlite-core';

// All timestamps: Unix epoch milliseconds (INTEGER). UTC everywhere.
// Primary keys: random UUIDv4 strings, generated application-side.

export const organizations = sqliteTable(
  'organizations',
  {
    id: text('id').primaryKey(),
    name: text('name').notNull(),
    slug: text('slug').notNull(),
    createdAtMs: integer('created_at_ms').notNull()
  },
  (t) => [uniqueIndex('organizations_slug_uq').on(t.slug)]
);

The pattern repeats for every table — users, sessions, memberships, invites, and audit_log. The schema defines six tables total, each following the same conventions:

• Users — email (lowercase, unique index), password hash, name, created timestamp. The email unique index prevents duplicate registrations at the database level.
• Sessions — PK is sha256(token), never the raw token. Foreign key to users with onDelete: 'cascade' so deleting a user revokes all sessions. Indexed on userId for lookup.
• Organizations — the tenant unit. Slug gets a unique index so URL-friendly identifiers don't collide.
• Memberships — the junction between users and orgs. Composite unique index on (org_id, user_id) prevents duplicate membership. The role column stores 'owner', 'admin', or 'member' — validated in the RBAC service layer, not the database.
• Invites — token hash (not the raw token), role, optional email note, inviter reference, expiry, accepted/revoked timestamps. Foreign key to the inviting user does not cascade on delete — invite history survives user removal.
• Audit log — append-only by design. Uses an autoincrement seq primary key instead of UUID. The org_id and actor_user_id columns are plain indexed text with no foreign key — audit rows survive org or user deletion.

Every column type maps to a Drizzle builder that infers a TypeScript type. When you write text('email').notNull(), Drizzle knows the column is string and not nullable — without a codegen step. This is the core of the "schema-first" claim: the schema is the type definition.

The drizzle-kit migration workflow

Drizzle separates schema definition from migration management. The flow has two distinct modes, and understanding when to use each prevents production surprises.

Generating migration files

After editing schema.ts, run:

npm run db:generate   # which runs: drizzle-kit generate

This diffs your current schema against the last snapshot in drizzle/meta/, produces a numbered SQL file in drizzle/ (e.g. 0001_add_roles.sql), and updates the journal. The generated SQL is plain DDL — you can read it, edit it, or reject it. The starter ships with one migration file: 0000_init.sql, containing the full DDL for all six tables.

Commit the migration files. They're checked-in artifacts, like a lockfile. The drizzle/ directory contains the SQL plus a meta/ subdirectory with a JSON journal tracking which migrations have been applied. Both belong in version control.

Dev vs. production: push vs. migrate

drizzle-kit push applies schema changes directly to a database without generating migration files. It's fast and convenient for local development — you change the schema, push, and move on. The starter's drizzle.config.ts documents this explicitly:

// drizzle-kit only reads this for push/studio;
// migrations are applied at runtime.

For production, use the generated migration files. The migrate() function in db/index.ts applies them sequentially at startup:

import { migrate } from 'drizzle-orm/better-sqlite3/migrator';

export function createDb(file: string): Db {
  // ...
  const db = drizzle(sqlite, { schema });
  migrate(db, { migrationsFolder: MIGRATIONS_DIR });
  return db;
}

This is safe for SQLite because better-sqlite3 serializes all access — two concurrent migrate() calls can't conflict. For Postgres or MySQL, you'd run migrations in a CI/CD step or a startup lock rather than relying on driver serialization. The multi-tenant architecture guide notes this as the first thing to change when you swap databases.

How the starter kit structures its schema

The file layout is intentionally flat for a small project:

src/lib/server/db/
  schema.ts    ← all table definitions, imports from drizzle-orm/sqlite-core
  index.ts     ← createDb(), getDb(), exports the Db type + schema
drizzle/
  0000_init.sql ← generated DDL, checked into git
  meta/
    _journal.json ← migration journal
    0000_snapshot.json
drizzle.config.ts ← drizzle-kit config, points to schema.ts and output dir

The Db type is exported from index.ts so every service can accept it as a parameter:

export type Db = BetterSQLite3Database<typeof schema>;

This is the seam that makes services testable: tests pass createDb(':memory:'), production passes a file path. The services never know the difference. This pattern is why the starter's 77 tests run against in-memory SQLite with no mocking — every service function accepts a real Db and does real work.

Running migrations in CI/CD vs. at startup

The starter runs migrations at application startup — a deliberate simplification for single-process SQLite. Here's why that works and when it doesn't:

• SQLite startup migration is safe because better-sqlite3 is synchronous and serialized. Only one process accesses the file. The migrate() call either succeeds fully or throws — there's no partial state.
• Multi-instance Postgres needs a different story. If two instances start simultaneously and both run migrate(), you need a advisory lock or a dedicated migration step in your deployment pipeline. Drizzle's Postgres driver supports advisory locks for this.
• CI/CD migration is the production best practice for anything beyond SQLite. Run drizzle-kit migrate as a deployment step before the application starts, with the database URL from environment variables. The application then starts knowing the schema is current.
• The tradeoff the starter makes: startup migration means zero deployment infrastructure beyond "start the process." For a solo-operated SaaS at $0 infrastructure cost, that's the right call. When you outgrow it, the migration step moves to CI without changing the schema or the application code.

Type-safe queries with Drizzle

Because the Db type is parameterized with the full schema, every query is checked at compile time. Here are the patterns the starter actually uses:

Selecting with typed results

// Find org by slug — returns { id: string; name: string; slug: string; createdAtMs: number } | undefined
const [org] = await db.select().from(organizations).where(eq(organizations.slug, slug));

The result type is inferred from the schema. If you add a column to organizations, the return type updates automatically — no re-running codegen.

Inserting with returning

const [row] = await db.insert(organizations).values({
  id: crypto.randomUUID(),
  name,
  slug,
  createdAtMs: Date.now()
}).returning();

The .returning() clause returns the inserted row with all typed columns. This is SQLite-specific — PostgreSQL supports RETURNING natively; MySQL doesn't, so Drizzle emulates it.

The query builder pattern

Drizzle's query builder composes naturally. The starter uses it for complex lookups — finding a user's membership in a specific org, listing all members with their emails, or checking invite validity:

const membership = await db
  .select()
  .from(memberships)
  .where(
    and(
      eq(memberships.orgId, orgId),
      eq(memberships.userId, userId)
    )
  )
  .limit(1);

Every column reference, every eq() call, every .from() target is type-checked. If you reference a column that doesn't exist, TypeScript catches it before you run a query. This is the practical payoff of the schema-first approach — not an academic feature, but a daily time-saver.

SQLite-specific considerations for multi-tenancy

The starter uses SQLite because it requires zero infrastructure — no database server, no connection pooling, no credentials. But SQLite has specific behaviors that matter for a multi-tenant app:

WAL mode

By default SQLite uses the rollback journal, which locks the entire database on writes. WAL (Write-Ahead Logging) mode allows concurrent reads while a write is in progress. The starter enables it explicitly:

if (file !== ':memory:') sqlite.pragma('journal_mode = WAL');

In a single-process SvelteKit server, this means a long-running query (like listing audit log entries) doesn't block writes (like recording a new session). Without WAL, every request would serialize behind the write lock.

Foreign keys

SQLite has foreign key support but disables it by default. The starter enables it on every connection:

sqlite.pragma('foreign_keys = ON');

Without this, ON DELETE cascade clauses in the schema are silently ignored — deleting a user would leave orphaned sessions, memberships, and invites. The schema defines the cascade behavior; the pragma makes it real.

Busy timeout

When SQLite's WAL mode hits a write conflict (two processes writing simultaneously), it returns SQLITE_BUSY. A busy timeout tells SQLite to retry instead of failing immediately. For single-process SvelteKit this rarely triggers, but it's a safety net:

sqlite.pragma('busy_timeout = 5000'); // 5 second retry window

If you move to multiple processes (e.g., clustering, or a separate migration runner), this becomes essential.

File-based storage

The database lives at ./data/app.db by default, configurable via DATA_DIR. The createDb() function creates the directory if it doesn't exist. For production deployments, this means backing up a single file — and restoring it is a file copy, not a pg_dump pipeline. The tradeoff is that SQLite doesn't support network-mounted storage reliably (NFS has known locking issues), so the database must be on local disk.

Production checklist

• Define all tables in a single `schema.ts` file — one source of truth for columns, types, indexes, and foreign keys. Never hand-edit migration SQL as the canonical schema.
• Use `drizzle-kit generate` after every schema change and commit the resulting SQL files in the `drizzle/` directory. Migration files are checked-in artifacts, not generated-at-build-time.
• Run migrations at application startup with `migrate()` from `drizzle-orm/better-sqlite3/migrator`. For SQLite this is safe because the driver serializes access; for Postgres you would run them in a CI step instead.
• Enable WAL mode and foreign keys on every connection. SQLite defaults to rollback journal mode and foreign_keys=OFF — neither is correct for a multi-tenant app.
• Use typed column builders (`text`, `integer`) with explicit column names — Drizzle infers TypeScript types from the schema so your queries are checked at compile time without a codegen step.
• Place composite unique indexes on columns that must not duplicate (org_id + user_id on memberships, slug on organizations). The schema enforces invariants that application logic would otherwise have to remember.
• Store all timestamps as integer milliseconds in UTC. Display-edge timezone conversion belongs in the template, not the database — the schema contains zero timezone columns.
• Keep the `Db` type exported from your database module so services accept it as a parameter. This makes every service testable with a `:memory:` database and framework-independent.
• Treat the `drizzle.config.ts` as a development tool only — it configures `drizzle-kit` for migrations and studio, not your runtime database connection.
• Default to file-based SQLite for single-process deployments. When you outgrow it, the schema and query patterns port to Postgres by swapping the driver — the column types and relations stay identical.

Related guides: Multi-tenant auth architecture · Rate limiting · Seat billing adapter