Migrate Up/Down to specific migrations

[mishase]

Problem

Migrating db to specific (non-latest) migration

Suggested solution

Reference: https://github.com/prisma/migrate/issues/494

Alternatives

To migrate up to specific migration temporary moving upper migration files to another directory and running prisma migrate deploy works well

Additional context

This was already implemented but removed somewhat

[janpio]

This was already implemented but removed somewhat

Note that this was only present in an earlier iteration of our Migration tooling - and got removed when we completely rewrote and rearchitected everything some time ago: https://www.prisma.io/blog/prisma-migrate-ga-b5eno5g08d0b

Your feature request is very valid of course.

[cunisia]

Hi team! 👋

I would need the exact same feature in order to :

• create some tables in first migration
• run a node script to migrate some data from old tables to new tables (underlying logic would be hard to translate into SQL)
• delete old tables in second migration

Thought it'd help to add a concrete use case 🙂

[molomby]

Rather than moving files, I find it easy to checkout a specific commit that has the migrations I want to apply then run prisma migrate deploy. You script the process something like this:

RESET_TO_MIGRATION="20230710234514_initial_schema" 
COMMIT=$(git log --follow --find-renames=40% --oneline -- "migrations/${RESET_TO_MIGRATION}" | tail -n 1 | cut -f1 -d' ')
git checkout "${COMMIT}"

[craig-pyrra]

This would be helpful for me, too. Here's my use-case:

I need to work on a migration that requires updates to data in existing tables. As an example

• I have existing table A and I am adding a new table B
• After the migration A is going to have a non-nullable foreign key reference to B called b_id
• During the migration I need to create a row in B for each existing row in A, and I need to update A's new b_id column with the ID of the corresponding row in B. This is SQL I'm writing by hand in the migration.sql file, it's not something Prisma can generate.

I want to test this by

1. Running all my existing migrations except for the new one that adds table B
2. Inserting some test data into table A, maybe with a little test script
3. Running the last migration to verify that the SQL is valid and that the existing data in the DB was updated correctly (each row in table A got linked to a new row in table B)

Pretty much all I need for that is a way to tell prisma migrate where to stop, eg prisma migrate dev --stop-after-migration=20230816235946_previous_migration_already_run_in_prod

[mjy9088]

Maybe most use cases related to this subject are for data migration.

See https://github.com/prisma/prisma/issues/11194, https://github.com/prisma/prisma/issues/15384 as well :)

Are there any use cases which are not for data migration? (just curious)

[chris-hailstorm]

One use case for other than data migration has to do with more complex relationships among databases. For example, when I used foreign data wrappers or logical replication, it's common that I need to make a change in one DB that's managed by one Prisma schema, and only after that make a change in another DB that's managed by a different Prisma schema (in a different app). I need to make sure app 1 migrates to at least its migration N before app 2 migrates past migration M. Then later each app / schema will go into more of a parallel operation until there's a change that requires coordination again.