Database Runbook

Connecting

Two connection strings exist per project; use the right one for where your code runs:

# In-cluster (what the app container gets):
DATABASE_URL=postgresql://postgres:<pw>@postgres.<name>-dev.svc:5432/<name>?sslmode=disable
 
# Localhost (psql, ORM studio, local scripts — port varies per project):
DATABASE_URL=postgresql://postgres:<pw>@localhost:<port>/<name>

Never guess the port — kilter env | grep DATABASE prints both resolved URLs. A browser UI (pgweb) is also available; find its URL in kilter env.

Everyday commands

kilter db shell      # psql session (via kubectl exec — no local psql needed)
kilter db migrate    # run migrations (auto-detects Drizzle/Prisma/TypeORM/GORM)
kilter db seed       # run the seed script
kilter db reset      # drop + recreate + migrate + seed
kilter db studio     # launch the ORM's studio UI

kilter db migrate dispatches to your ORM: Drizzle runs drizzle-kit push, Prisma runs prisma migrate dev, TypeORM runs migration:run, GORM runs go run . migrate.

After every reset: restart Ory

Ory services cache a network ID in memory; kilter db reset invalidates it and login starts returning 500s. Liveness probes recover it in ~90 seconds, or force it immediately: kubectl rollout restart deployment ory-kratos ory-keto ory-hydra -n <name>-dev (with KUBECONFIG=~/.cache/kilter/<name>/kubeconfig). Details in the troubleshooting runbook.

Snapshots and restore

Snapshot before risky work (destructive migrations, bulk imports, auth changes):

kilter db snapshot [name]        # app database only
kilter db snapshot list
kilter db restore [name]

By default only the app database is captured. If the project runs Ory, identities and sessions live in separate databases — include them or a restore will desync app users from Kratos identities:

kilter db snapshot post-auth --include kilter_kratos,kilter_hydra,kilter_keto

After any restore that touches the Ory databases, roll the Ory deployments (same command as the reset callout above).

Declarative schema (pgschema)

Projects using pgschema keep the desired end-state in pgschema/schema.sql — no migration files. Apply it with:

kilter db push       # diffs schema.sql against the dev DB, applies the difference

Edit the schema file to what you want the database to look like; kilter db push computes and runs the DDL to get there. This replaces kilter db migrate for pgschema projects.

Inspecting Ory databases

When Ory is enabled, pgweb exposes one bookmark per database — append ?bookmark=<name> to the pgweb URL from kilter env:

Bookmark	Contents
`app`	Application schema
`kratos`	Identities — verify UUIDs after auth syncs
`hydra`	OAuth clients and tokens
`keto`	Permission tuples

Production databases

Local kilter db ... commands only ever touch the KIND dev cluster. Production schema and seed changes run operator-mediated as Jobs:

kilter deploy --migration            # apply committed migration files
kilter seed apply --dry-run          # preview seed plan
kilter deploy --migration --seed     # migrate → seed → deploy

Seeds must be idempotent (ON CONFLICT DO NOTHING, or delete-then-insert) — the operator re-runs them on every invocation and does not track applied state. Seed failures surface via kubectl describe kilterseed/<name>.