Is a migration script wrapped inside a transaction?

[AviBueno]

Can't find it in the docs and would like to ask if an executed migration is wrapped inside a transaction?

I have a migration that will modify existing columns in several tables and I would like to understand what will happen if the it would somehow break half way through (e.g. lost connection)

[ryands17]

Hey @AviBueno 👋
Currently migrations are not wrapped inside a transaction, so if you break halfway, you would be left with an intermediate state, but if you run migrate again, it will start where it left from so the already performed steps would not be re-applied.

Also wrapping migrations in a transaction is considered in the future, but it's currently not implemented.

[tomhoule]

Thanks a lot @alecmev :) We coincidentally had an internal discussion today about the right default for Microsoft SQL Server, and we're probably going to go with transactions by default. Making that configurable on postgres will feel like a more natural step after that.

[ruohola]

Currently we are not recommending Migrate for production use so introspection should be the way to go.

What do you mean by this?

[tomhoule]

This is a very old comment, we definitely recommend Migrate for production use now :)

[ruohola]

This is a very old comment, we definitely recommend Migrate for production use now :)

Excellent. Started to question my sanity for a moment there 😅

[revero-doug]

are migrations still not wrapped in a transaction? if so, is there any public documentation/discussion of this decision that you could share? it seems like a strange decision to allow partial migration to be applied rather than leveraging the transaction features of the backing RDBMS

[chris-braidwell]

This is ~ impossible to implement. Each DBMS (and version) has different rules about what DDL (if any!) is subject to transactions. Lots of subtleties, like Postgres will allow a CREATE INDEX in a transaction, but not CREATE INDEX CONCURRENTLY. MySQL supports some DDL in a transaction, but it depends on which storage engine you use -- MyISAM supports NO transactional DDL.

Prisma has no way to know about these issues. When Prisma generates a migration for me for Postgres that includes a CREATE INDEX, I may choose to manually edit that to add CONCURRENTLY -- that would fail if Prisma wrapped every migration in a transaction. When Prisma generates a migration for me for MySQL, Prisma has no idea what storage engine I'm using -- and I may actually use different ones in different deployments! The transactionality decisions are far outside of what prisma migrate can know.

Prisma can't insist on transactions unilaterally. It has to be optional, and not just at the whole-migration level -- sometimes there will be blocks that are transactional and others that aren't in one migration. There needs to be way for us as developers to indicate what should be transactional and what shouldn't. That already exists -- you manually add BEGIN ... COMMIT; where you need it.

[why-el]

still safer to slap a BEGIN; COMMIT on migrations if it's postgres and have it fail for the 0.01% of cases where folks pick a DDL that's not transactional. I think this is the wrong default; puts more work on people to fix their production environments in the worst case, and to not trust what Prisma generates as a migration in the best case.

[aseemk]

+1 to all the requests asking for transactions by default. Just got bitten by this, and it really sucks to have to debug and fix a DB with a half-migrated schema.

The lack of this safe behavior is what results in the need for prisma migrate diff and resolve complexity. In 99% of cases, I just want my migrations to succeed all-or-nothing. And in the few times where I need to make a change that can't run in a transaction (like CREATE INDEX CONCURRENTLY), I'd rather wrap that transaction in COMMIT; ...; BEGIN; rather than wrap every other transaction in BEGIN; ...; COMMIT;

Please consider changing! Thank you.

[trulyronak]

curious - can you just wrap your generated migration code with a BEGIN and COMMIT? will that achieve the same effect here, or does prisma's rust layer make that not really work like you'd expect.

[aseemk]

(i noted this in #3774 (reply in thread) but if helpful: i'd rather not have to remember to wrap 99% of my migrations in BEGIN and COMMIT. this is an issue about what prisma's default behavior should be, IMHO!)