Marco "Ocramius" PivettaSQL migrations should be written in plain DDL/DML, without any SQL abstraction layer.
You will thank me when you'll have to figure out a migration gone sideways in-flight, and won't end up debugging the SQL abstraction.
WanWizard 🏴 🇳🇱@ocramius I always do.
The biggest issue in my experience is that you can only be sure model definitions are valid after all migrations have run, while migrations could be run at any time.
Bumped into this recently when a client wanted a new instance of an app we maintain since 2013, so needed to run them all from scratch, and leared some dev along the way used the ORM in some migrations which crashed because of schema differences between DB and code.
@ocramius The buggest downside of using SQL however is you loose app portability, as the DDL/DML will be RDBMS specific.
Fortunately the framework we use has two levels of abstraction, one that syntactically mimics DDL/DML, and a high-level ORM.
The low-level one is platform agnostic, so that allows us to work around that problem.
@wanwizard app portability is 99% pointless
@ocramius Because?
I'm currently working on an app that was originally designed to work party against an Azure MS-SQL database, and is now migrating to a local MariaDB cluster.
If the code had been full of native MS transact-SQL, we would have had a lot of reworking to do.
@wanwizard because you will need to design migrations specific to your DB engine of choice anyway.
The initial DB snapshot could be an SQL dump, but migrating requires different strategies based on DDL transactionality, locking, FK integrity check methods, constraint enforcement pausing, etc.
Migrating an SQLite, a MySQL or a PostgreSQL DB are completely different tasks.
@ocramius Can't say I have that experience, but most likely the apps we build and maintain aren't that big and complex.
@wanwizard it all becomes big and complex when uptime and existing data comes into play.
Adding/dropping columns is "hello world"
Guess I should call myself lucky then. Sofar.
I think in this app, about 60% of the migrations are schema changes (incl related data manipulations), the rest are data manipulations only, logical changes, consistency fixes, etc.
A quick count finds 210 migrations.