websilvercraft

Posted on Aug 10

How does Drizzle handle migrations - Part 2: Changing database structure

#typescript #drizzle #javascript #webdev

Drizzle is built for that. You change the TypeScript schema, Drizzle generates a new migration that alters your SQLite/D1 tables, and you apply it with Wrangler. High-level loop:

Edit TS schema (add/rename/drop columns, tables, indexes, constraints).
npx drizzle-kit generate → emits a new migrations/00xx_*.sql diff.
Review the SQL (important for destructive changes).
Apply it: wrangler d1 execute DB --local/--remote --file migrations/00xx_*.sql.

Because D1 is SQLite, some changes are done via table rebuilds under the hood (SQLite can’t do every ALTER TABLE). Drizzle handles that by:

creating a temp table with the new shape,
copying data over (mapping/transforming columns),
dropping the old table,
renaming the temp table.

So yes-schema changes work; just be mindful of data migrations. Here are common recipes:

Add a column (safe)

TS:

creditDelta: integer('credit_delta').notNull().default(0)

Run drizzle-kit generate. It will emit ALTER TABLE ... ADD COLUMN credit_delta INTEGER NOT NULL DEFAULT 0; (or a rebuild if needed). Apply with Wrangler.

Make a column NOT NULL (with data)

Backfill a default in a migration:

UPDATE billing_price_map SET credit_delta = 0 WHERE credit_delta IS NULL;

Then change TS to .notNull() (and maybe .default(0)), generate migration. Drizzle will rebuild the table so the constraint holds.

Rename a column

Change the field name in TS and use .as('old_column_name')? (Not needed.) For SQLite, Drizzle will usually rebuild the table and map old → new:

You’ll see a create/copy/drop sequence in the generated SQL.
If you also need to transform data, add a custom UPDATE new_table SET new_col = old_col step between copy and drop (or tweak the generated SQL before applying).

Change a column type

Again, SQLite → rebuild.

Drizzle generates new table, copies data (SQLite will try to coerce).
If you need specific transforms, add an UPDATE in the migration file.

Drop a column

SQLite can’t drop columns directly → rebuild.

Be careful: verify you don’t rely on that data anymore.
Consider doing a two-step “soft drop” (stop using it, deploy; later actually drop).

Add / drop unique constraints or indexes

Update TS (.unique(), .uniqueIndex(), .index()), generate, apply.
Drizzle will CREATE UNIQUE INDEX / DROP INDEX or rebuild to embed constraints.

Rename a table

Change sqliteTable('new_name', ...). Drizzle will create new, copy, drop old.
If you want to preserve the exact name, do it in two steps or edit the generated SQL.

Data migrations alongside structure

Drizzle generates structure changes; you can (and often should) append your own SQL to the same migration:

-- backfill new column based on price_id
UPDATE billing_price_map
SET credit_delta = CASE provider_price_id
  WHEN 'pri_...' THEN 100
  WHEN 'pri_...' THEN 1000
  ELSE 0
END;

This is how you keep credits consistent when changing mappings.

D1-specific tips

Apply order matters: migrations are numbered; run all in order for each env.
Review generated SQL: especially rebuilds-make sure the copy step includes all columns you care about.
Idempotency: use IF NOT EXISTS / INSERT OR IGNORE for seeds.
Prod safety: for breaking changes, do “expand → backfill → switch → contract”:
- Add new columns/tables (expand).
- Dual-write or backfill.
- Switch code to read new fields.
- Drop old columns later (contract).

Workflow you can stick to

Update src/db/schema*.ts.
npx drizzle-kit generate (commits new SQL).
(Optional) Edit the generated SQL to add backfills/transforms.
Dev DB: npm run db:migrate:local (your wrapper over Wrangler).
Test app.
Prod DB: npm run db:migrate:remote.

That’s it. If you tell me the exact structural change you have in mind (e.g., “split transactions into invoice + payments” or “rename provider_price_id to price_id and make it NOT NULL”), I’ll draft the precise TS change and the migration SQL you should expect (including safe backfills).

DEV Community