setup drizzle migrations

[rgarcia]

right now we're using db:push for everything, which is annoying since (1) it is interactive sometimes, so it hangs in CI and (2) it's kind of flying by the seat of our pants

Package.json should have some new commands e.g.

"db:generate": "drizzle-kit generate:pg --schema <>/db/schema.ts --out <>/db/migrations",
"db:migrate": "bun run ./src/db/migrate.ts",

migrate.ts looks something like this (docs):

import 'dotenv/config';
import { migrate } from 'drizzle-orm/mysql2/migrator';
import { db, connection } from './db';

// This will run migrations on the database, skipping the ones already applied
await migrate(db, { migrationsFolder: './drizzle' });

// Don't forget to close the connection, otherwise the script will hang
await connection.end();

There should probably also be code in the db/index.ts file that runs migration logic on startup when in development. Example: https://github.com/ixartz/Next-js-Boilerplate/blob/4dcace6ea983ce599d87a66fcdae302f192acb7f/src/libs/DB.ts#L3

The process for running in prod should probably be some mechanism to run db:migrate against prod.

One way to do this (that I just used to db:push against prod) is

1. run railway link and choose the prod env
2. run railway shell to get a subshell with prod env vars set
3. run bun run db:push to make the changes to the prod db ❗

Update: looks like there's now a "migrate" command in drizzle-kit itself so maybe the script isn't necessary? https://github.com/drizzle-team/drizzle-kit-mirror/releases/tag/v0.21.0 https://orm.drizzle.team/kit-docs/commands#apply-migrations

[linear[bot]]

MET-28 setup drizzle migrations