Database Migrations
vadimkravcenko.com
vadimkravcenko.com
1. rollbacks are bullshit, stop pretending they aren't. They work fine for easy changes, but you can't rollback the hard ones (like deleting a field), and you're better off getting comfortable with forward-only migrations
2. never expose real tables to an application. Create an "API" schema which contains only views, functions, procedures, and only allow applications to use this schema. This gives you a layer of indirection on the DB side such that you can nearly eliminate the dance of coordinating application changes and database migrations
You can get away without these rules for a long time, but 2 becomes particularly useful when more than one application uses the database.
What I've done instead is change the underlying table as needed, and ensure the view interface stays the same, by changing the view definition. This lets you refactor the low-level schema without breaking clients, and this migration can be done in a single transaction.
Tbh, views are a bit of a leaky abstraction (for example when it comes to constraints) so adopting them gradually seems like a good idea.
At a high level it looked like
1. create API schema and start filling it out with useful stuff
2. one by one, migrate applications to the new schema. You'll find stuff missing from the API schema, and you'll add it, to support each application. Ideally you'll find commonality across applications, so you end up with a cohesive API schema and not a jumble of application-specific stuff. But this takes discipline!
3. as each application moves over, remove their access from the low-level schema(s)
The weird thing here is your interface is still SQL. Sometimes that's fine, but I think this API-schema approach really takes off when you use something like PostgREST, PostGraphile, or Hasura to automatically turn this API schema into a web API. Lots of nice benefits to those tools, and then you have a single service that is more what users might expect.
I actually discovered this API-schema approach from the docs of one of those tool...I just can't remember which one.
Maybe from https://postgrest.org/en/v10.2/schema_structure.html
A number of Schema changes and query optimizations could be handled by updating the stored procs without having to recompile the application.
But, I'd argue the API schema gives you better control over this by pushing what might otherwise be application-level logic into the database. I've solved a lot of bad ORM behavior this way.
Unfortunately, modern development favors having all logic on the application side. There are a lot of benefits that brings including better testing (stored proc testing frameworks never really caught on) however I find now a lot of people I work with rely too heavily on the ORM and don't know how to work with the databases (writing sub optimal queries, not properly indexing, etc)
In the example above, the select statements were inside stored procedures, and applications were only granted permissions to execute those with appropriate parameters.
When I showed up we had a single Postgres database with a half-dozen different clients. One managed the migrations in addition to doing real work, and the others were a mix of "direct" users and API layers used elsewhere. Tons of overlap, tons of repeated logic, tons of inconsistency.
I used this approach to first stem the plague of direct DB access, and later to consolidate our APIs. It worked pretty well, and the folks I left it to seemed to appreciate my approach.
We did have a lot of debates about what is or isn't "business logic", what belongs in this DB or not. We tried to keep it pretty light; our raw data was many independent tables, and our API schema had functions for insertions and views to make that data usable in ways that didn't feel very application-specific.
I was pushing to replace our handwritten API service(s) with something like Hasura, though that didnt happen before I left. I think that's where this approach gets more powerful: manage your data with your database, get a web API for free. Then your data store is encapsulated behind a singular service with a well-defined API.
You're right that it requires some serious attention to the DB. We wrote all migrations in PL/pgSQL (managed with sqitch), giving us a level of control that is hard to get from a language-specific migration framework. That required a lot of upskilling, but I think it was worth it for us.
This once was the "correct" way to do things, period. At least that's how I learned it originally. If you go a back a couple of decades, "users" in a RDBMS meant literally end users, using a client application to directly query the DB from their desktop machine and whatever permissions they had was what they had. In this environment locking down everything except stored procedures and views was the only way to go. (Interestingly, we've sort of seen a resurgence of this design recently with some of these PaaS based web applications where all the logic is living client-side somewhere in a React app.)
After that indoctrination I remember feeling very resistant initially to a lot of this and in particular the practice of just granting full read/write access to a service account and letting developers write whatever they wanted, but times change. We were working under different constraints at the time. If you have services scoped to a specific responsibility and they completely own their own data and don't have the situation you describe of many clients touching the same DB, a lot of the problems that the proc+views only pattern were intended to solve just go away on their own.
“ 1. rollbacks are bullshit, stop pretending they aren't. They work fine for easy changes, but you can't rollback the hard ones (like deleting a field), and you're better off getting comfortable with forward-only migrations”
Think of database migrations like you would versioning an API, deprecate and then drop old columns only after declaring a breaking change and giving consumers time.
Or take the Google approach where the protobufs contain all the fields from the start of time or something along those lines so that old applications can still work and the service devs can choose to migrate as needed.
But yeah re: point 1 databases are best thought of like the arrow of time, just evolve forward not backwards and you’ll be fine and not lose data or worse.
This ties into my second point as I eventually made an "api_v2" database schema and migrated users as you describe.
We try to put a clean API on everything, except the one place where it really matters...
Chasing our tech-tail.
I have a question regarding this point:
> 2. never expose real tables to an application. Create an "API" schema which contains only views, functions, procedures, and only allow applications to use this schema.
Would a view-based indirection help with rollbacks? For example, in scenarios where a column is added/dropped, would it work to just join with a new relationship column? Rolling back would consist of updating the view to include/exclude the join operation, and the old data would remain in place.
But dropping a column from the view is an API-breaking change, so rather than updating the existing view I'd make a new view, possibly in a new API schema (e.g. "api_v2"). Then you migrate clients/applications to the new view, and only when nothing uses the old view would I drop both the old view and the column from the underlying table in new migrations.
It covers one of the most common things people miss with regards to running migrations: it isn't possible to atomically deploy both the migration and the application code that uses it. This means if you want to avoid a few seconds/minutes of errors, you need to deploy the migration first in a way that doesn't break existing code, then the application change, and then often a cleanup step to complete the migration in a way that won't break.
Knowing how to do this isn't a common skill. It's probably a good topic for an interview question for senior engineering roles.
We (at Xata) have tried for a while to come up with a generic schema migration system for PostgreSQL that makes this easier. We ended up using views and temporary columns in such a way that we can provide both the "old" and the "new" schema simultaneously. Up/down triggers convert newly inserted data from old to new and the other way around. This also has the advantage the it can do rollbacks instantly by just dropping the "new" view.
We were just planning to announce this as an open source project this week, but actually it is already public, so if you are curious: https://github.com/xataio/pgroll
If your're not too cool for MySQL, check out skeema.io for a declarative, platform-agnostic approach to schema management and path to happiness.
Skeema is used by several hundred companies, including GitHub, Twilio, and Etsy. We have a lot of fans in the MySQL community, and just because someone enjoys the product does not mean they’re a shill.
Most newer entrants in the schema management space are VC-funded, and went wide instead of deep in terms of the range of supported DBs. I personally believe in deep expert-level coverage of a specific DB, resulting in better functionality and a safer schema management toolchain. However it does make word-of-mouth more challenging, since MySQL is unpopular here. So often these blog posts don’t mention Skeema, since blog authors coming from other DBs haven’t ever encountered it.
This makes schema changes easy to perform (just create a new database and let the system populate it from the events), easy to do without downtime (keep the old database available while the new database is building), and easy to roll back (keep the old database available for a while after the migration).
The trade-off is that changing/adding event types now needs to be done carefully (first deploy code able to process the new event types, then deploy the code that can produce the new events), whereas a SQL database supports new UPDATE or INSERT without a schema change.
If not, did you leave out a ‘copy history from source to target’ step?
You can migrate or you can have no downtime, you cannot do both.
We at Bytebase also recognize this and have spent over 2 years to build a solution for team to coordinate the database migrations better.