Migrations Rollback

[rafaell-lycan]

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 Drizzle currently doesn't support a way to rollback migrations I'm using a custom solution (hopefully temporary) with migrate with a specific Dockerfile configured and a more painful way to configure/deploy to run our migrations, which might be slightly painful sometimes:

• Create a new migration using Drizzle;
• Include the suffix *.up.sql the migration script;
• Create a new file with the same name and change the suffix to *.down.sql and manually add a rollback;
• Run migrations on a custom Dockerfile + run.sh script that doesn't look as pretty as I would like; 😅

I was wondering if on next releases, there will be any sort migration rollback like migrate does.

1481574547_create_users_table.up.sql
1481574547_create_users_table.down.sql

This would benefit many developers and wouldn't be a breaking change by keeping everything else working.

Possible solution:

• Implement up/down SQL scripts on a new major release with the same drizzle-kit generate command;
• When running migrations it would use the same command and ignoring scripts with suffix *.down.sql;
• When running a undo/rollback with drizzle-kit it cheks for the migrations table and identifying the latest migration ID to match with a <migration_name>.down.sql script on the folder to be executed otherwise exit;
• This would open a possibility for Drizzle Kit adopt include the new drizzle-kit rollback:*/drizzle-kit undo:* command;

The future

Using Drizzle is being a breazy so far (coming from Knex) and I wanna see what things going far beyond the rest of the community.

Let's see how the community will (or not) react to this idea (calling for help) and also the core team.

[devanfarrell]

I would love a migrate down option as well. My current approach is:

• create a sql dump of our staging database
• delete my local database
• recreate the local database
• load the dump into the new local database
• rinse and repeat until the migration is correct

[capaj]

you're using mysql, right? No-transaction migrations are definitely a pain.

[devanfarrell]

Postgres personally but it's probably the same.

[capaj]

oh I thought you're repeating your migrations because they fail and your DB is in inconsistent state, but you're probably just iterating on your migration SQL script so youre migrations actually run OK from SQL standpoint.
On postgres if they fail on something like a missing foreign key whole thing reverts back, so you wouldn't need to manually fix anything.

[ivawzh]

If implementing an automated rollback script will take a big effort, I'd like to have a way to opt in for a manual rollback script for the time waiting for the feature.

Being able to review and commit a rollback script like 1481574547_create_users_table.down.sql in a PR before we merge and deploy the migrations, that will give us much more confidence on dealing with production incidents with an option of rolling back.

[afogel]

I can't echo this request enough as an important aspect to mitigate unforeseen deployment issues.

I recognize that the drizzle team (as per @AndriiSherman 's comments in the discord) suggest that we leverage the push API for development.

However, sometimes a mismatch between what the data layer supports and the application layer requests after a migration runs successfully and is deployed (and this mismatch isn't caught in QA). Having a down migration would be incredibly helpful in that circumstance.

The drizzle toolkit is an awesome and incredibly sharp tool, but it would be nice if we had some method to sheath those tools at times.

Thank you!

[kkoyot]

I second that - not implementing migrations rollback/resetting is a huge omission. It's an awful pain to correct your migrations when developing schema change in a single PR doing changes on the way, since you have to delete or merge the migration files, modify the journal, revert the DB state manually and whatnot.

[rizen]

Can I pay Drizzle Team to add this?

[Ehtisham-Ayaan]

That was the same thought as i had. As rails has already this from years.

[darrenbarklie]

Also commenting to request this feature. Don't make me go back to TypeORM, please...

[nvegater]

Please add a rollback option for migrations :) would be awesome.

[lucasltv]

Don't have it yet? Wow... it's so essential!

[toshmukhamedov]

agree

[MChangFoundry]

Yeah this would be huge

[JasBogans]

[dragonfleas]

Your software doesn't have the rollback migrations feature being discussed here, why even mention it?

[FR073N]

Needing this feature so badly

[suzubki]

Hope it releases soon

[Leo-Henrique]

Just need this feature to use in production :(

[StuartMorris0]

Can we get any update on this from the drizzle team? I've posted the same in Discord, but haven't received a response. Be good to know if this is being worked on or not.

Not, is fine, we'd just need to work on some approach that builds the reverse migrations using something else, but I'd be keen to know if it's something in the pipeline just to avoid recreating the wheel here. Please and thanks 🙏

[afogel]

I made a feature request #2352 just in case

[kirkas]

+1 on this, this is the only thing preventing us from using Drizzle for production as migration rollback can be crucial when deploying.

[Twiddlle]

rollback pleaseeeee...

[metaforx]

Puzzled that this is missing. I am used to the Django way, which makes managing migrations less painful. To have an equivalent is almost a must to work on larger projects in my opinion.

[afogel]

AlexBlokh is now working on it.

[BenjaOliva]

AlexBlokh is now working on it.

Any news on this ? :(

[drepkovsky]

Hi, till this is implemented by drizzle team directly.
I've tried to replicate the up and down migration generation the same way as PayloadCMS does it like @TomDo1234 mentioned. I've only tested it on pg but should work on mysql and sqlite too. Please give it a try and maybe contribute if you find some issues.

https://github.com/drepkovsky/drizzle-migrations

[KT071]

I was exited and about to use it in new project but without migration down it is useless for me :(

[TArch64]

That doesn’t have much sense actually because the way you described has huge possibility to forgot call commit or rollback methods. Callback style is way more safer and usually it’s enough. If you really want to store transaction and do some things you can make feature specific class like this

class ComplexLogic {
  constructor(tx) {
    this.tx = tx;
  }
  
  async execute() {
    ….
  }

drizzle.transaction(tx => new ComplexLogic(tx).execute())

but I believe it’s better to omit any have logic within transaction callback. Like you can prepare some data and do stuff with it in transaction

[KT071]

So if I have an action in system that changes User, Order, Invoice, with sequelize approach I can pass transaction to every model.update (it is located in different repositories) and at the end if every thing goes well just commit. And its convenient for me and I dont se how i could do this way in Drizzle. Maybe im blind, thats why im asking about that...

Fake code for example

try {
tx = new tranasction()
tx.start()

user = userRepo.get(userId, tx);
user.changeSomeThings()

userRepo.update(user, tx);

order = orderRepo.get(userId, tx);
invoice = invoiceRepo.get(userId, tx);

////// some complex calculations on orders and money and all that staff

order.changeSomeThings()

////// MORE complex calculations on invoices and orders and money and all that staff

invoice.changeSomeThings()

orderRepo.update(order, tx);
invoiceRepo.update(invoice, tx);
userBalanceRepo.updateUserBalance(userId, tx)

tx.commit()
} catch (err) {
tx.rollback()
}

In that case im just passing transaction to DB operation ans separate logic. And if something goes wrong just rollback and nothing happens and I can do this across application without wondering how to compose transaction on some DB access level.

I would love to use Drizzle for app that im building now, that is why im asking, is this is possible in any way to use transactions like that with Drizzle?

[TArch64]

But… you still manually passing transaction to methods… you also can make a repository class and take a transaction as an argument. The only difference you will have that all this code will be wrapped to the transaction callback

even if any of your methods require its own transaction drizzle has nested transactions

[TArch64]

Let’s stop it. This discussion has no connection with the issue

[KT071]

I asked is it possible nad if yes then how :)
Not if I should :D different developers different application different styles

So I assume that you dont know how to do this or it is impossible with Drizzle.
Thank you for answers :)

[killjoy2013]

a migration should definitely be reversible. up() & down() is very crucial

[jsaunie]

After 3 days of using Drizzle, I was loving it until I found out there was no way to revert back without pain. Luckily for me, I'm early in my project, I hope they release this feature soon. Thanks for Drizzle though ❤️

[AlexBlokh]

will duplicate here too

yep, we've just landed a major release with a massive update on the docs

my personal next backlog is:

• enums migrations fixes/upgrade
• drizzle-kit dotenv consume upgrade with templates and stages support
• update migration folder structure, key upgrade before rollbacks
• rollbacks
• github hook for migrations for teams

[theogravity]

Same, was using Drizzle for the past two weeks, then migrations really started becoming an issue. It does too much magic, and a traditional up / down definition would have been significantly better. We need to be able to write business logic in our migrations and I don't see a way to do that with Drizzle other than to somehow translate Typescript code into SQL statements directly in the generated files.

It's such a deal-breaker that we've moved off using Drizzle and converted everything to Kysely in the past day.

Drizzle could definitely be competitive with Kysely if it got rid of its magical migrations (JSON metadata files, generated SQL files, then also a migration table?) and just allowed developers to define a Knex-style up/down mechanism. We want direct control over migrations.

[gvkhna]

I've encountered several situations where drizzle simply says it doesn't support the functionality to migrate and a manual migration is needed anyway. Please add up/down manual migration support as soon as possible otherwise drizzle is only useable for the most basic of applications.

The goal of keeping the schema the sole source of truth across all databases and having it automatically create migrations would be amazing, but is extremely lofty and likely will take years.

[adamgreg]

Being able to transform existing data as part of migrations is definitely important, and often needs to be done in-between statements that Drizzle will generate for you. I've got along ok so far by editing the generated SQL files by hand. Not being able to rollback migrations can be a problem though.

[fccoelho7]

@AlexBlokh, the missing migration rollback (up/down) blocked us from using Drizzle; we needed to roll it back to Prisma. Please, address it soon 🙏🏻

[AlexBlokh]

will duplicate here too

yep, we've just landed a major release with a massive update on the docs

my personal next backlog is:

• enums migrations fixes/upgrade
• drizzle-kit dotenv consume upgrade with templates and stages support
• update migration folder structure, key upgrade before rollbacks
• rollbacks
• github hook for migrations for teams

[Jsbbvk]

@AlexBlokh +1 for fixing enums! Running into enum conversion errors during migrations, so that would be a wonderful fix!

[pppdns]

any update on this? 👀

[AlexBlokh]

yep, we've just landed a major release with a massive update on the docs

my personal next backlog is:

• enums migrations fixes/upgrade
• drizzle-kit dotenv consume upgrade with templates and stages support
• update migration folder structure, key upgrade before rollbacks
• rollbacks
• github hook for migrations for teams

[pppdns]

Here are some well working examples from Laravel, Knex.js, and Lucid for up and down migrations

https://knexjs.org/guide/migrations.html#transactions-in-migrations
https://laravel.com/docs/11.x/migrations#migration-structure
https://lucid.adonisjs.com/docs/migrations#migration-class-structure

Knex.js:

knex migrate:up
knex migrate:down

Lucid:

node ace migration:run
node ace migration:rollback

Laravel:

php artisan migrate
php artisan migrate:rollback

Apart from up and down migration commands, they also support migrating/rolling back one by one, in batch, by specifying a migration file name explicitly, etc.

More examples:

knex migrate:latest
knex migrate:rollback
knex migrate:up 001_migration_name.js
knex migrate:list

node ace migration:rollback --batch=0

php artisan migrate:rollback --step=5

Here's a Lucid migration file:

import { BaseSchema } from '@adonisjs/lucid/schema'

export default class extends BaseSchema {
  protected tableName = 'users'

  async up() {
    this.schema.createTable(this.tableName, (table) => {
      table.increments('id')
      table.timestamp('created_at', { useTz: true })
      table.timestamp('updated_at', { useTz: true })
    })
  }

  async down() {
    this.schema.dropTable(this.tableName)
  }
}

Here's a Knex.js migration file:

exports.up = function (knex) {
  return knex.schema
    .createTable('users', function (table) {
      table.increments('id');
      table.string('first_name', 255).notNullable();
      table.string('last_name', 255).notNullable();
    })
    .createTable('products', function (table) {
      table.increments('id');
      table.decimal('price').notNullable();
      table.string('name', 1000).notNullable();
    });
};

exports.down = function (knex) {
  return knex.schema.dropTable('products').dropTable('users');
};

exports.config = { transaction: false };

Also, these migrations are in Typescript, not SQL, TS is more familiar and convenient to devs

[ivosabev]

I also agree that a simple up and down functions in TS would be much better than SQL files. Sometimes migrations are very complex with data transformations, insertions, deletions, fetching from 3rd parties, and so on. I don't see a reason why the Drizzle team should reinvent the wheel when there is already a proven pattern.

I am sticking with Knex until these are added.

[ironbyte]

Its crazy that this is still not supported to date. Would be nice to know if they're actually working on it. I love Drizzle and I use Neon which has DB branching feature that's really handy when it comes to situations like this.

[AlexBlokh]

will duplicate here too

yep, we've just landed a major release with a massive update on the docs

my personal next backlog is:

• enums migrations fixes/upgrade
• drizzle-kit dotenv consume upgrade with templates and stages support
• update migration folder structure, key upgrade before rollbacks
• rollbacks
• github hook for migrations for teams

[CestDiego]

Now that my application has grown more, i am experiencing the same issue. I am considering migrating back to prisma because no matter how 'fast' drizzle can be, database integrity is more important when building products that have users that care about it.

[jkazimierczak]

While my schema hasn't grown that in size (yet), I was considering using Prisma just for the sake of migrations. Yes, having two ORMs sound crazy, but using one just for the purpose of migrations and the other in the runtime doesn't conflict with me personally. Also, this seems kind of supported - otherwise "Drizzle extension for Prisma" would not exists.

If I'm reading the docs correctly, drizzle tables could be generated from prisma, so that one way to save yourself some effort. Also, if:

Relational queries are not supported due to a Prisma driver limitation. Because of it, Prisma unable to return query results in array format, which is required for relational queries to work.

means, that drizzle relations is not generated, I would be willing to create them manually in a separate file (to avoid being overriden when generating tables), import the necessary tables in the relations file, and feed both relations and tables (aka schema) into the drizzle client.

That's just an idea. I haven't personally tested it, but it seems like it could work.

[AlexBlokh]

will duplicate here too

yep, we've just landed a major release with a massive update on the docs

my personal next backlog is:

• enums migrations fixes/upgrade
• drizzle-kit dotenv consume upgrade with templates and stages support
• update migration folder structure, key upgrade before rollbacks
• rollbacks
• github hook for migrations for teams

[pppdns]

thanks! What's the best place for us to follow the progress of the work? Is there a Git branch maybe? Is there something we can help you with?

[anthonyhagi]

I'm curious how migrations will work out for SQLite?

I know that right now, Drizzle isn't able to make migrations that drop columns that have FKs attached to them. It involves turning the PRAGMA off for FKs, re-creating the table, migrating all the data over (except for that column), deleting the old table, renaming the new table to the existing table name, then finally turning the PRAGMA back on

This can work locally with SQLite, but I don't believe Turso (in it's current state with the LibSQL driver) can handle the PRAGMA on/off in a transaction

[hi-reeve]

any eta on this? @AlexBlokh

[AlexBlokh]

I'm currently working on Migrations folder V3 structure + commutative migrations, down migrations + rollback next

[hi-reeve]

I'm currently working on Migrations folder V3 structure + commutative migrations, down migrations + rollback next

thankyou! and really looking forward to this 👀

[afogel]

Agreed! Thank you @AlexBlokh !

[LeulAria]

i feel like i wana go back to knexjs or kysely
Im actually using drizzle only for it's studio and migration and kysely for all my query and operations
now the studio which they are planning to make it payed via gateway or stuff and the migration also sucks.

[chris-allen]

That thought was better kept to yourself.

Thanks to the drizzle team for building this really needed feature!

[coopbri]

That thought was better kept to yourself.

Thanks to the drizzle team for building this really needed feature!

@chris-allen made my night 😂

[AlexBlokh]

@LeulAria we are planning to make studio payed? wtf are you talking about
drizzle gateway is a separate deployable version of drizzle studio to make us some revenue, because "duh" people are not willing to pay for an ORM. To overcome this and make everything sustainable - we either provide value/products for businesses or build b2c solutions around Drizzle ecosystem, while providing Drizzle ORM/Kit/studio for local development for free

as of your feedback - feel free to hop back to Kysely or Knex, we're completely fine with that and we also have native adapters for them

cheers!

[LeulAria]

@LeulAria we are planning to make studio payed? wtf are you talking about
drizzle gateway is a separate deployable version of drizzle studio to make us some revenue, because "duh" people are not willing to pay for an ORM. To overcome this and make everything sustainable - we either provide value/products for businesses or build b2c solutions around Drizzle ecosystem, while providing Drizzle ORM/Kit/studio for local development for free

as of your feedback - feel free to hop back to Kysely or Knex, we're completely fine with that and we also have native adapters for them

cheers!

I agree that even the smallest OSS needs revenue to support all these issues and that's totally fine I'm just stating here that i had a hard time deploying the studio plus i don't see any value on the project apart from this and the migration for me not being a jerk here, just saying b/c i love this ecosystem this will help to keep moving forward lets hear the -ves as well.

[nobledossy]

This. So much this. Drizzle-kit is still horridly flaky for me and seems to botch local migrations more than they work successfully. The most recent somehow only picked up some of the new tables and not others even though they're all imported properly in the schema.ts file! Dealing with botched Drizzle migrations is a huge pain in the butt due to the lack of any kind of rollback mechanism. I would HAPPILY take a Sequelize-style manual migration system over this any day of the week.

Drizzle is great, but drizzle-kit is still buggy and lacking in vital features.

[pppdns]

if you would take a manual migration tool over Drizzle, I believe you're better off with Kysely. It has a type safe migration builder with up and down support. The main difference between Drizzle and Kysely in this regard is that Drizzle auto-generates the migrations based on the schema changes which you store in a file

[SSTPIERRE2]

This should be a core feature of every single ORM. It is very common to write a migration and want to change something about it before actually committing/submitting the branch. I had to fix merge conflicts due to another developer creating and merging a migration before I submitted my branch, and it broke the migrations leading his to not run (since mine with the same number id already ran). Now I'm stuck manually querying the migration tracking table, but it is unclear which migration(s) I need to delete for the new one to run since they're stored with a hash, there's no way to identify which row corresponds to which migration file. I'm probably going to have to recreate my whole db just for this.

[ginkcode]

Agree.
Keeping only the hash in migrations table is a very bad idea.
Why we don't have the file name inside it?

[pppdns]

+1
This is how Laravel does it. Same for Adonis.js.
It stores the migration file name and the batch number (migrations running together can be rolled back together).
It works very well. You can simply do this infinitely while developing, testing, and changing migrations:

php artisan migrate
php artisan migrate:rollback

This will apply the missing migration(s) and then roll them back together. Between the 2 commands you can do your post-migration testing.
If you need to change something, just roll back the migration, change the code, and run it again.