5 ms·
We built it ourselves. * We have migrations written in SQL files named like 45_add_column_x_to_view_y.sql * We track the migrations in git and we do merge req
by ggregoire 7y ago
We built it ourselves.
* We have migrations written in SQL files named like 45_add_column_x_to_view_y.sql
* We track the migrations in git and we do merge requests and reviews
* We include a comment in each migration containing the original DDL of the object (1. it makes it easier for the reviewer to see what's changing, 2. it gives us a way to rollback, though it never happened so far)
* We track the applied migrations in a table migrations looking like "2019-10-30T10:49:00" ! "45_add_column_x_to_view_y.sql"
* We have a CLI in the same repo that allow us to apply the migrations. It's 50 LOC of python that basically 1. gets the DB host/user/pwd from an .env, 2. checks the last applied migration in the table migrations, 3. finds the new migrations to apply, 4. applies them following the order of the ids e.g. 45 before 46, 5. updates the table migrations
* We have 3 DBs (dev / staging / prod) in AWS RDS and I have an .env for each one of them
* We have a CI pipeline on the repo to automatically apply the merged migrations in staging and check if nothing goes wrong
* When the staging or dev is trashed, I delete it and recreate it from a snapshot of the prod (it takes 5 min) https://stackoverflow.com/a/49878477/652669 https://stackoverflow.com/a/49878477/652669
- diminoten 7y agoWhich existing technologies did you look at and why didn't they fit your needs?
- ggregoire 7y agoWe looked at Alembic and our main issues were that 1. we don't have the entire database modeled into SQLAlchemy and we don't plan to 2. we have A LOT of views, triggers and functions and they are not very well supported by SQLAlchemy/Alembic (? I may be wrong)
- uranusjr 7y agoPoint 1 is not an issue IMO, you definitely don’t need to model anything schema-wise into SQLAlchemy ORM to make Alembic useful, since Alembic operations translate pretty much directly to SQL. I’ve just built a project last month that uses Alembic but otherwise connects to the database and issues SQL directly. Point 2 is indeed a big factor though. Alembic doesn’t have much first-class operation support beyond data storage constructs, and if most you need is op.execute() (which runs plain SQL) one might just as well build their own tool like you did.
- nilram 7y agoWe have something very similar, written in Java. Our extensions specify whether the migration is run pre-release or post-release, in order to facilitate no-downtime releases. The pre-release migration is responsible to not break the running code. The released code is responsible for detecting (if necessary), the pre- or post-release state and behaving appropriately. Sequence is to run pre-release migrations, deploy the release, then run post-release migrations. Sometimes days apart, in case things need to be stabilized. Testing environment runs only pre-release migrations, until some time before the release, then runs post. We also have a handful of keywords to run operations in ways that don't lock large, frequently-used tables, or do other environment- or application-specific operations in standard ways.
- notatoad 7y agoThis is almost exactly what i built at my company. It felt like a dirty hack at the time, and i assumed we'd transition to something "real" over time, but it's been working flawlessly for quite a few years now and i've yet to see any reason to use something different.
- neurostimulant 7y agoHow the system handles rolling back a migration?