How to do migrations that require data changes?

Hi, how can I migrate a live database that requires to do a more complicated data shuffling, e.g. I have this schema:

const Pages = pgTable('pages', {
    id: uuid('id').defaultRandom().primaryKey(),
    title: text('title').notNull(),
    status: text('status', { enum: ['draft', 'published', 'deleted'] })
      .notNull()
      .default('draft'),
    // ...other columns
});

const Pages = pgTable('pages', {
    id: uuid('id').defaultRandom().primaryKey(),
    title: text('title').notNull(),
    status: text('status', { enum: ['draft', 'published', 'deleted'] })
      .notNull()
      .default('draft'),
    // ...other columns
});

If I do this change:

// remove
status: text('status', { enum: ['draft', 'published', 'deleted'] })
  .notNull()
  .default('draft'),
// add
status: integer('status').notNull().default(1),

// remove
status: text('status', { enum: ['draft', 'published', 'deleted'] })
  .notNull()
  .default('draft'),
// add
status: integer('status').notNull().default(1),

It will need more than drizzle-kit is providing, e.g. update query to map existing data to new data. Is drizzle-kit able to handle this case by providing additional code/sql and if not does it mean that I need to do it manually and then reset drizzle snapshots to be able to continue with drizzle-kit from there?

2 Replies

Andrii Sherman•15mo ago

You can modify sql provided by drizzle before applying it to database Additionally you can generate a new migration using —custom cli param It will generate an empty sql where you can put anything you need on top of what drizzle can do already It’s still a good practice to check on a local database firstly and then move to further envs you have

Dan•15mo ago

That's exactly what I was looking for, thank you!

Gaming

Programming

How to do migrations that require data changes?