Drizzle Team•5mo ago

Drizzle Migration but with TS code

Does anyone have an idea about running TS code during migration. I'm thinking of moving from knex.js to drizzle (a migration part). In knex.js, it's quite convenient since the migration is TS code, example: if I want to alter a nullable column to non-nullable column, I would want to run a script to set value for it before alter it and to do that, I could use a complex update like ...

# add value to null column
db
 .update(table)
 .set({ column: db.query.anotherTable.findFirst({...}).returning("id") })
 .where(...)

# alter once all value is not null
db.table(table).alter(...).notNullable()

# add value to null column
db
 .update(table)
 .set({ column: db.query.anotherTable.findFirst({...}).returning("id") })
 .where(...)

# alter once all value is not null
db.table(table).alter(...).notNullable()

Or I could even re-use the function I have been already wrote in service layer like, for example, since it's already tested and limit the risk.

# add value to null column
tableService.updateAllInColumn(anotherTable.getX())

# alter once all value is not null
db.table(table).alter(...).notNullable()

# add value to null column
tableService.updateAllInColumn(anotherTable.getX())

# alter once all value is not null
db.table(table).alter(...).notNullable()

Does anyone have this kind of use case? how do you run a one-time script, do you run it in TS migration file or how do you solve this case with SQL migration file?

3 Replies

Mario564•5mo ago

Hi there. Drizzle doesn't provide any way to use TS code during migrations, you'd have to use SQL If you already have the project in prod, it would be ideal to test the migration locally regardless if it's TS or SQL code

FirstOP•5mo ago

Drizzle doesn't provide any way to use TS code during migrations, you'd have to use SQL

Ah, sure then. seems it's not possible if I could leverage the migration schema created by drizzle to do some one-time scripting along with migration code. 🙇🏼‍♂️ Thank you! Found it, This is what I mean.. I think payloadCMS has some custom drizzle version made for them, like this import type { DrizzleSnapshotJSON } from 'drizzle-kit/payload' (code) which they have a support of up() and down() function, where you can write your own typescript along with SQL by payload.db.drizzle.execute and it would save into migration schema

export async function up({ payload, req }: MigrateUpArgs): Promise<void> {
await payload.db.drizzle.execute(sql`
 CREATE TABLE IF NOT EXISTS "users" (
    "id" serial PRIMARY KEY NOT NULL,
    "updated_at" timestamp(3) with time zone DEFAULT now() NOT NULL,
    "created_at" timestamp(3) with time zone DEFAULT now() NOT NULL,
    "email" varchar NOT NULL,
    "reset_password_token" varchar,
    "reset_password_expiration" timestamp(3) with time zone,
    "salt" varchar,
    "hash" varchar,
    "login_attempts" numeric,
    "lock_until" timestamp(3) with time zone
);
....

export async function up({ payload, req }: MigrateUpArgs): Promise<void> {
await payload.db.drizzle.execute(sql`
 CREATE TABLE IF NOT EXISTS "users" (
    "id" serial PRIMARY KEY NOT NULL,
    "updated_at" timestamp(3) with time zone DEFAULT now() NOT NULL,
    "created_at" timestamp(3) with time zone DEFAULT now() NOT NULL,
    "email" varchar NOT NULL,
    "reset_password_token" varchar,
    "reset_password_expiration" timestamp(3) with time zone,
    "salt" varchar,
    "hash" varchar,
    "login_attempts" numeric,
    "lock_until" timestamp(3) with time zone
);
....

This way, we could re-use a lot of SQL query by save into some utils file. e.g. we could have some trigger PSQL function that we might run every end of some migration, we could make the migration code cleaner by extract it into iteral string in TS. here is a code (https://github.com/payloadcms/vercel-deploy-payload-postgres/blob/main/src/migrations/20240709_153941_initial.ts) Not sure when the official drizzle-kit will support this kind of functionality?

FirstOP•5mo ago

Ah, my bad, found the GitHub issue (https://github.com/drizzle-team/drizzle-orm/discussions/1339) 🙇🏼‍♂️

GitHub

Migrations Rollback · drizzle-team drizzle-orm · Discussion #1339

Hi #drizzle-team & community folks 👋 I'd like to suggest something that I believe more people might be looking for. Is there any plan for rollback migrations? My day-to-day workflow Because...

Gaming

Programming

Drizzle Migration but with TS code