Drizzle ORM Postgres migrations in SvelteKit

The SvelteKit + Postgres Starter manages its schema the Drizzle way: your schema lives as a typed TypeScript file, drizzle-kit turns changes into SQL migrations checked into the repo, and the app applies them. This guide walks the schema-first workflow end to end, grounded in the real schema and migration in src/lib/server/db/schema.ts and drizzle/.

The workflow has five steps, and they are deliberately simple because drizzle-kit does the heavy lifting:

1. Edit src/lib/server/db/schema.ts — add a table, a column, or an index in TypeScript.
2. Run npm run db:generate — drizzle-kit diffs your typed schema against the previous migration and emits a new SQL migration under drizzle/.
3. Review the generated SQL — read the new file in drizzle/ to confirm it does what you intend.
4. Apply it — either let the app apply it automatically at boot, or run npm run db:migrate on demand.
5. Verify — run the 194-test suite against the test database to confirm the fresh schema holds up.

This is called schema-first: the TypeScript schema is the single source of truth, and SQL is derived from it rather than hand-written. Toolkit choice: Drizzle ORM on top of the asynchronous postgres.js driver, installed as drizzle-orm/postgres-js, with drizzle-kit generating and applying migrations.

Migrations are checked in under drizzle/ as plain SQL files. The initial migration, drizzle/0000_init.sql, creates all six tables — audit_log, invites, memberships, organizations, sessions, users — plus their indexes and foreign keys. A representative excerpt of that catalogue:

            
drizzle/0000_init.sql (abridged)
            CREATE TABLE "audit_log" (
  "seq" bigint PRIMARY KEY GENERATED ALWAYS AS IDENTITY (sequence name "audit_log_seq_seq" ... CACHE 1),
  "org_id" text,
  "actor_user_id" text,
  "action" text NOT NULL,
  "target_type" text,
  "target_id" text,
  "metadata_json" text,
  "created_at_ms" bigint NOT NULL
);
--> statement-breakpoint
CREATE TABLE "users" (
  "id" uuid PRIMARY KEY NOT NULL,
  "email" text NOT NULL,
  "password_hash" text NOT NULL,
  "name" text NOT NULL,
  "created_at_ms" bigint NOT NULL
);
          

Note the conventions visible in the generated SQL, which match the TypeScript schema exactly: uuid primary keys (app-supplied UUIDv4), and Unix epoch milliseconds stored as bigint, used everywhere — created_at_ms, expires_at_ms, accepted_at_ms. This bigint-millisecond, UTC-everywhere convention is an intentional portability choice that keeps the service layer identical to the SQLite starter.

Because migrations are checked in, they are part of the repo like any other source change — reviewed in PRs, applied in CI, and never regenerated ad hoc on a production box without going through the generate-and-review loop.

Migrations are applied in two places, so your schema is very hard to get out of sync. First, on demand: npm run db:migrate applies the migrations in drizzle/ to the DATABASE_URL. The scripts/migrate.ts on-demand migrator is run via tsx and reads the connection string from DATABASE_URL — the same variable the app and the test suite use.

Second, and more importantly, the app applies them automatically at boot: createDb() (and openDb()) runs the Drizzle migrator, so an app instance can never run against an unmigrated schema. That guarantee is called out explicitly in docs/deployment.md: migrations are applied automatically at app boot via the Drizzle migrator, so an app instance can never run against an unmigrated schema.

In practice this means: deploy the new code, and the first boot that connects to a fresh DATABASE_URL brings the schema up to date before any request runs. For local development you can still apply explicitly with npm run db:migrate after docker compose up -d (Postgres 16: dev on :5433, test on :5434).

Drizzle-kit's job is to diff your TypeScript schema against the last-known migration and emit SQL. The canonical example from the docs — upgrading audit_log.metadata_json from JSON text to native jsonb — shows the edit in schema.ts that you would then run npm run db:generate over:

            
src/lib/server/db/schema.ts
            // src/lib/server/db/schema.ts
import { jsonb } from 'drizzle-orm/pg-core';

export const auditLog = pgTable(
  'audit_log',
  {
    // ... other columns ...
    metadataJson: jsonb('metadata_json').$type<Record<string, unknown>>(),
    // ...
  },
  (t) => [index('audit_org_seq_idx').on(t.orgId, t.seq)]
);
          

Running npm run db:generate then emits a new numbered SQL file in drizzle/ that alters the column to jsonb. The same loop applies to adding a whole table: define a pgTable, generate, review, apply. The schema is the source of truth, and everything downstream — indexes, foreign keys, types — comes from it.

This is how the JSONB metadata upgrade, the full-text tsvector search column, and even read-replica handles are all meant to be introduced: edit the TypeScript schema (and service code), generate a migration, review and apply it. See the JSONB metadata guide for the full pattern.

• Review the generated SQL. Every migration lands in drizzle/ as a file you can read before it applies. Confirm that dropping columns, adding NOT NULL constraints on populated tables, and index changes are what you intended.
• Test against the test database at :5434. The suite runs against TEST_DATABASE_URL (docker-compose's test-db, port :5434). Apply migrations there first and run npm test — 204 tests across 9 suites against a real Postgres — so a broken migration is caught locally, not in production.
• Don't hand-edit the generated SQL unless you know what you're doing. drizzle-kit tracks what's been applied, so a hand-tweaked migration that drifts from the schema can leave the two out of sync. When a manual escape hatch is truly needed (e.g. the GIN tsvector full-text pattern), add it deliberately and keep the TypeScript schema and the SQL in agreement.
• Follow the repo's conventions: uuid primary keys (app-generated UUIDv4) and bigint-millisecond timestamps, UTC everywhere. New columns should match so the service layer stays portable and consistent.

One honest caution from the kit's docs: several Postgres-native features — jsonb metadata, full-text search, read replicas — are documented add-yourself patterns, not shipped defaults. When you adopt one, the migration is yours to generate and apply the same way, and the test database is where you prove it.

Two conventions recur across every table, and they are worth internalizing because any table you add should follow them. Primary keys are uuid, with the app supplying random UUIDv4s — so ids can be generated before insert, safely shared across tenants, and never depend on a counter. Timestamps are Unix epoch milliseconds as bigint (mode: 'number' in Drizzle, so you work in JS numbers), UTC everywhere, matching the application's contract throughout.

The reason for both is portability: the application-facing data model is deliberately identical to the SQLite starter, so the entire service layer, RBAC, billing, and audit code ports between the kits unchanged — swap the driver, keep the services. A schema that used Postgres-only types or timestamptz offsets would break that symmetry. When you extend the schema, keep those conventions and you keep the dual-kit portability that the starter is built on.