Migrations (push vs migrate)

CareNova uses Drizzle ORM to manage the database schema. Drizzle provides two distinct approaches for applying schema changes to PostgreSQL — push and migrate. Understanding the difference between them determines how safely you can evolve the schema in development versus production.

The Two Approaches

db:push

npm run db:push
# runs: drizzle-kit push

push compares the current state of lib/db/schema.ts against the live database and applies the diff directly — no migration files are created or tracked.

It is fast, direct, and requires no extra steps. It is the right tool for:

• Initial setup on a fresh database

• Local development where schema iteration is frequent

• Experimenting with schema changes before committing them

It is the wrong tool for:

• Production databases with live data

• Any environment where you need a traceable history of schema changes

• Team environments where multiple developers need to apply the same change consistently

push will prompt you before applying destructive changes such as dropping columns or tables. Read the prompt carefully — destructive changes cannot be undone.

db:generate + db:migrate

npm run db:generate
# runs: drizzle-kit generate
# creates a new SQL migration file in migrations/

npm run db:migrate
# runs: drizzle-kit migrate
# applies all pending migration files to the DB

generate reads the current lib/db/schema.ts, compares it to the last known migration state, and produces a new .sql file in the migrations/ folder describing exactly what changed.

migrate applies all migration files that have not yet been run against the target database, in order. Drizzle tracks which migrations have been applied in a __drizzle_migrations table it manages automatically.

This approach is the right tool for:

• Production deployments

• Staging environments

• Any change that needs to be reviewed before being applied

• Team workflows where schema changes are committed to version control alongside the code that requires them

INSTALL.sql via Supabase SQL Editor

If you run into issues with the terminal commands — connection errors, pgBouncer conflicts, environment variable problems — you can always fall back to running INSTALL.sql directly in the Supabase SQL Editor. No terminal, no environment variables, no configuration required.

Supabase Dashboard
→ SQL Editor
→ paste the contents of INSTALL.sql
→ click Run

INSTALL.sql is idempotent — it is safe to run multiple times. It uses CREATE TABLE IF NOT EXISTS and DO $$ BEGIN ... EXCEPTION WHEN duplicate_object THEN NULL; END $$ guards throughout, so running it again will never duplicate tables, enums, or indexes that already exist.

This is the recommended path for:

• First-time installation on a fresh Supabase project

• Anyone not comfortable with terminal-based migrations

• Recovering from a broken migration state

• Any situation where db:push or db:migrate fail and you need a reliable fallback

Recommended Workflow

Local Development

Use push freely during development. Schema changes are applied instantly without the overhead of managing migration files while the data model is still being designed.

# Edit lib/db/schema.ts
# then apply immediately:
npm run db:push

When the schema change is finalized and ready to ship, generate a migration file to commit alongside the code change:

npm run db:generate
# commit the generated migration file 
# in migrations/ with your code changes

Production Deployment

Never use push on a production database. You have two options:

Option A — Terminal (recommended for teams):

npm run db:migrate

Run this as part of your deployment process — after the code is deployed but before the new version receives traffic, or as a pre-deploy step in your CI pipeline.

On Vercel the recommended approach is to run db:migrate as a build step:

// package.json
"scripts": {
  "build": "npm run db:migrate && next build"
}

Option B — Supabase SQL Editor (easiest, always works):

Supabase Dashboard
→ SQL Editor
→ paste the contents of INSTALL.sql
→ click Run

Use this if the terminal migration fails, if you are setting up for the first time, or if you simply prefer not to run CLI commands against a production database.

Connection URL for Migrations

Drizzle migrations require a direct PostgreSQL connection — not the transaction pooler used at runtime.

Set DIRECT_DATABASE_URL in your .env.local for local migration runs:

DIRECT_DATABASE_URL=postgresql://postgres.[ref]:[PASSWORD]@aws-0-[region].pooler.supabase.com:5432/postgres

If DIRECT_DATABASE_URL is not set, drizzle-kit falls back to DATABASE_URL. This works for push in development but may cause issues with migrate in production due to pgBouncer's transaction mode not supporting the full SQL required for migrations.

If you keep running into connection issues with db:migrate, skip it and use the Supabase SQL Editor with INSTALL.sql instead — it is the most reliable path and requires no local configuration.

Adding a Column to an Existing Table

A typical schema change workflow from start to finish:

# 1. Edit lib/db/schema.ts
#    Add the new column definition

# 2. Apply locally
npm run db:push

# 3. Test the change works as expected

# 4. Generate the migration file
npm run db:generate
# Creates migrations/0001_add_column_name.sql

# 5. Commit both files
git add lib/db/schema.ts
git add migrations/0001_add_column_name.sql
git commit -m "add [column] to [table]"

# 6. On production deploy
npm run db:migrate
# or paste INSTALL.sql in Supabase SQL Editor

Drizzle Studio

Drizzle Studio is a browser-based GUI for browsing and editing your database directly — useful for inspecting tables, verifying migrations applied correctly, or making quick manual edits during development.

npm run db:studio
# Opens Drizzle Studio at https://local.drizzle.studio

Drizzle Studio connects directly to your database. Do not run it pointed at a production database unless you are intentionally making manual changes — there is no undo for direct edits.

Full Database Reset (Development Only)

To wipe and rebuild the local database from scratch during development:

npm run setup:db
# runs: npm run clean && npm run db:push && npm run db:seed

This clears the Next.js and Turbo caches, pushes the current schema, and seeds demo data. Only use this on a development database — it is destructive.

Troubleshooting

db:push prompts about destructive changes unexpectedly:

• Review the prompt carefully before confirming — it lists exactly which columns or tables will be dropped

• If the change is unintentional, cancel and review lib/db/schema.ts for accidental deletions

db:migrate fails on production with a connection error:

• Confirm DIRECT_DATABASE_URL is set to the direct connection URL on port 5432 — not the pooler on port 6543

• Confirm the database password is correct and the IP is not blocked by Supabase network restrictions

• If the issue persists, use the Supabase SQL Editor fallback — paste INSTALL.sql and click Run

Migration file already applied but schema is still out of sync:

• Check __drizzle_migrations table in your database to see which migrations Drizzle has recorded as applied

• If a migration was applied manually via SQL but not through Drizzle, the tracking table will be out of sync — insert the record manually or run db:push to force alignment

db:generate produces an empty migration file:

• The schema in lib/db/schema.ts matches the last migration state — no changes were detected

• If you expected changes, confirm the edits were saved to schema.ts

Not comfortable with the terminal at all:

• Use the Supabase SQL Editor with INSTALL.sql — it handles the full schema setup in one click with no local configuration required