[Index] [Quick Jump] Package maintainers For package maintainers and hackage trustees Candidates Opinionated SQL schema migration management Migrant instruments SQL schema migrations in a semi-automated way. Writing the actual up- and downgrade scripts is still a manual effort; but Migrant takes care of tracking which scripts have been run already and which still need to be run, runs them for you. Databases are notoriously difficult to version-control. Versioning source code is a solved problem: we write code, put it in a repository, and the source control software gives us a unique identifier for that exact version. We can now deploy the code in whichever state we want, and as long as we keep code and data separated, we can do this in a fairly brute-force manner: we just delete the old code, copy the new code where it needs to be, and restart what needs to be restarted. Easy. And because we can deploy any version of the code we want on any host we want, we can test our code on one machine (a test server), and then deploy it on another (a production server), and be reasonably sure that if it works on the test environment, it will also work in production. We can also, just as easily, revert the code to an older version, if one of our changes turns out to have introduced a fault. But with SQL databases, this doesn't work. The schema and the data stored in the database are intertwined; we want to manage the schema, but we want to do it such that no data is lost. We cannot simply overwrite the schema: if we delete a schema, we also delete all the data in it, because in an SQL database, data cannot exist without the associated schema. Even small changes, such as changing the type of a column, can be destructive, and so there is a real risk of permanently losing data as a result of schema changes. And this means we must be more surgical about our database mutations. There are two fundamental approaches to this, which I call "snapshot-based" and "delta-based". The "snapshot-based" approach stores a snapshot of the schema at a given version in the source control system; to migrate the database to that version, it looks at the current schema, and infers the schema changes that are required to get the schema into the desired state (or a compatible one). For example, if there is a table products in the database that has three columns (id, name, price), and the version-controlled schema description says it should have columns id, name, price, image, then the migration code infers that the image column must be added. The "delta-based" approach, which is what Migrant uses, stores descriptions of the steps required to arrive at the current schema. A migration run, then, figures out which migration steps have already been executed, and which ones are needed to get the schema where we want it, and executes the required steps. In our example above, there may be two upgrade steps: create-products-table, and add-product-image. The migration code detects that only the create-products-table step has been run, and decides to run the add-product-image step. Migrant migrations are implemented using three key parts: Example: