Skip to main content

​
Applying a Drizzle migration to Neon

Quick reference for running a SQL migration in server/drizzle/ against a Neon database. Convention in this repo is one apply-NNNN.ts script per migration — re-runnable, fail-soft on “already applied”.

​
When to use this

  • A new server/drizzle/NNNN_*.sql file has been written and committed but not yet applied to a DB.
  • You want to re-apply against another environment (dev → staging → prod) and need the same idempotent behavior.

​
What’s safe to run unattended

Migrations in this repo are additive only by convention: CREATE TYPE, ALTER TABLE ADD COLUMN, CREATE INDEX. Destructive changes (DROP COLUMN, DROP TABLE, breaking type changes) need a manual review + maintenance-window plan and are explicitly out of scope for the apply-NNNN.ts pattern.

​
How to apply a migration

​
1. Confirm you’re on the right git ref

cd /Users/ssh/Documents/Beta-App/odontoX
git log --oneline -5
# Make sure HEAD includes the feat commit that introduced the migration file.

​
2. (Optional) Test against local dev first

If you have a local Neon branch / dev DB, run there before touching prod: 
cd server
DATABASE_URL='postgresql://...local-or-dev-url...' pnpm tsx scripts/apply-NNNN.ts
Inspect the output. You should see ✓ for each statement, then a “columns now present” sanity-check.

​
3. Apply to the target DB

cd server
DATABASE_URL='postgresql://neondb_owner:<password>@<host>/neondb?sslmode=require&channel_binding=require' \
  pnpm tsx scripts/apply-NNNN.ts
Always use the pooler endpoint (the host segment ends in -pooler.<region>.aws.neon.tech). Direct endpoints have lower connection limits and shouldn’t be used for one-off scripts.

​
4. Verify the result

The apply-NNNN.ts script ends with a SELECT column_name FROM information_schema.columns ... block that lists the new columns. Eyeball it. If columns or enums are missing, the migration didn’t fully apply.

​
5. Re-run if needed

The scripts are written to be safe to re-run. Statements that already applied print ⏭ already-applied: and are skipped. Statements that fail for other reasons fail loudly with the SQL text and the error.

​
What to do when a migration fails mid-way

  1. Read the error. The statement that failed is printed verbatim.
  2. Manually fix the underlying issue (usually a typo in the SQL or a missing dependency).
  3. Update the SQL file + the apply-NNNN.ts script (if needed).
  4. Re-run. Already-applied statements will skip; the broken one will retry.

​
Writing a new apply-NNNN.ts

Copy server/scripts/apply-0053.ts — it has the right pattern:
  • Strips leading -- comments per chunk before splitting on ;\n (so chunks that start with a comment header don’t get dropped).
  • Catches already exists / duplicate column / duplicate object errors per statement (idempotent).
  • Ends with a sanity-check SELECT … FROM information_schema.columns so you can eyeball success.
Don’t copy apply-0051.ts — it has a trailing junk </content></invoke> tag from an earlier tool error and produces TS noise. (Pre-existing; unrelated to current work.)

​
Reference: how migration 0053 was applied

For the public booking widget (2026-05-24): 
cd /Users/ssh/Documents/Beta-App/odontoX/server
DATABASE_URL='postgresql://neondb_owner:[email protected]/neondb?sslmode=require&channel_binding=require' \
  pnpm tsx scripts/apply-0053.ts
Output (truncated): 
Applying 0053 (booking forms + submissions — additive, fail-soft on already-applied)…
  ✓ CREATE TYPE app.lead_form_type AS ENUM ('contact', 'booking')
  ✓ ALTER TABLE app.lead_form_configs ADD COLUMN form_type ...
  ✓ ALTER TABLE app.lead_submissions ADD COLUMN requested_date date, ...
  ✓ CREATE INDEX lead_submissions_clinic_requested_date_idx ...
  ✓ CREATE UNIQUE INDEX lead_submissions_clinic_idempotency_idx ...

lead_submissions booking columns now present:
  • appointment_id
  • idempotency_key
  • phone_match_patient_id
  • requested_date
  • requested_duration_minutes
  • requested_time
  • treatment_type
lead_form_configs booking columns now present:
  • booking_config
  • form_type
lead_form_type enum present: yes

Done.

​
Why not just drizzle-kit migrate?

drizzle-kit migrate would also work — Drizzle tracks applied migrations in a meta table — but the per-file apply-NNNN.ts pattern gives us:
  • Explicit confirmation of each statement (✓ / ⏭ per line),
  • Idempotent re-runs (Drizzle errors out if you try to re-apply),
  • A built-in post-condition check that proves the columns landed.
The server/package.json db:migrate script references a scripts/run-drizzle-migrations.ts runner that doesn’t exist in this repo. Until that’s added, use the per-file scripts.