4 ms·

I recommend the following broad approach: - Maintain your current SQL schema in one place, using normal version control. - Use that form to set up new develop

I recommend the following broad approach: - Maintain your current SQL schema in one place, using normal version control. - Use that form to set up new development versions and so on. - Also maintain migrations (maybe using a migration tool, maybe just write them by hand). - Make your system for applying migrations guarantee that the result of applying the migration to the previous schema exactly matches the new schema.

samatman 7y ago
For extra credit, have the last step involve the version control system, so that each migration contains a commit of the last version which contains the old schema, and each version contains a (database | data seed) sufficient to verify the validity of the migration step. So to test from any given migration: - Checkout the code at that point - Apply the migration - Apply tests And repeat as necessary.
0xffff2 7y ago
Any advice on how to implement that last bullet point? I've got an experimental project that currently does the first two things. I'm getting close to a point where it actually needs to be deployed and I have to figure out the last two. Keeping both a complete up-to-date schema and a set of migrations seems like a good idea in theory, but I don't have any idea how I would automate a step to make sure they stay in sync in my CI workflow.
- genuine_smiles 7y ago
  If you were using rails you would run migrations and then with git check if the schema file has any changes.
- mjw1007 7y ago
  Something like this simpleminded approach: Use VCS tags to identify schema versions, and a naming convention including those tags to track the migration files. Have a script that validates a migration in the obvious sort of way (check out the project that contains the schema at tag a; add some data; apply the migration; dump the schema; compare that against a dump made directly from tag b). Maintain a store of migrations that have been validated in that way; the way to add a migration to that store goes through the checking script. Store the tag corresponding to each database's current schema in the database itself (and have something automated to check it regularly if you like; that's basically the same code as the migration-checking script). Make the only way to change an important database's schema be to run a script that takes the migration from that store, reading the starting tag from the database.
ufmace 7y ago
Rails ActiveRecord kind of already does this. To update the database schema, you write migrations, either in Ruby pseudo-SQL or in actual SQL as desired. When you run the migration, it auto-generates a schema file that is also checked into the repo. Ideally, any commit with a migration should also have the updated schema file from applying that migration.
evanelias 7y ago
When talking strictly about DDL (managing schemas, not row data migrations) the third bullet doesn't really provide much benefit, IMO. As long as your tooling knows how to properly transition from any live schema state to the new desired state, the actual migration / specific ALTER statement is basically unnecessary. Facebook has been using the declarative approach (repo of CREATE statements, pure SQL) company-wide for nearly a decade and it works extremely well. Conceptually, this approach allows you to treat database schemas just like code: request a schema change by pull request; CI validates and sanity-checks; review the PR within your own team just like code; merge to kick off the change using a CD pipeline. I liked this approach so much that I started an open source project in early 2016 to help enable this workflow for MySQL and MariaDB, https://www.skeema.io https://www.skeema.io. It has a number of validations and safety checks built in, including the one described in your 4th bullet.
- mjw1007 7y ago
  If I had tooling which I trusted to be able to transition from any schema state I might want to use to any other such state, I would happily use it. I've never found any, though it's a few years since I last looked. (I use Postgres, which has a fairly rich set of possible schema objects, and adds new bits frequently; I'd be reluctant to rely on a project that wasn't actively maintained and starting from 100% support). The principle here is that in this case (as often), it's much easier to teach a computer to check that you've done manual work correctly than it is to teach the computer to solve the general problem. This has been working well for me for many years, but I'm typically only dealing with a few dozen schema changes (in production) a year; I can imagine that Facebook sees the world just slightly differently.

samatman 7y ago

For extra credit, have the last step involve the version control system, so that each migration contains a commit of the last version which contains the old schema, and each version contains a (database | data seed) sufficient to verify the validity of the migration step. So to test from any given migration: - Checkout the code at that point - Apply the migration - Apply tests And repeat as necessary.

0xffff2 7y ago

Any advice on how to implement that last bullet point? I've got an experimental project that currently does the first two things. I'm getting close to a point where it actually needs to be deployed and I have to figure out the last two. Keeping both a complete up-to-date schema and a set of migrations seems like a good idea in theory, but I don't have any idea how I would automate a step to make sure they stay in sync in my CI workflow.

genuine_smiles 7y ago
If you were using rails you would run migrations and then with git check if the schema file has any changes.
mjw1007 7y ago
Something like this simpleminded approach: Use VCS tags to identify schema versions, and a naming convention including those tags to track the migration files. Have a script that validates a migration in the obvious sort of way (check out the project that contains the schema at tag a; add some data; apply the migration; dump the schema; compare that against a dump made directly from tag b). Maintain a store of migrations that have been validated in that way; the way to add a migration to that store goes through the checking script. Store the tag corresponding to each database's current schema in the database itself (and have something automated to check it regularly if you like; that's basically the same code as the migration-checking script). Make the only way to change an important database's schema be to run a script that takes the migration from that store, reading the starting tag from the database.

ufmace 7y ago

Rails ActiveRecord kind of already does this. To update the database schema, you write migrations, either in Ruby pseudo-SQL or in actual SQL as desired. When you run the migration, it auto-generates a schema file that is also checked into the repo. Ideally, any commit with a migration should also have the updated schema file from applying that migration.

evanelias 7y ago

When talking strictly about DDL (managing schemas, not row data migrations) the third bullet doesn't really provide much benefit, IMO. As long as your tooling knows how to properly transition from any live schema state to the new desired state, the actual migration / specific ALTER statement is basically unnecessary. Facebook has been using the declarative approach (repo of CREATE statements, pure SQL) company-wide for nearly a decade and it works extremely well. Conceptually, this approach allows you to treat database schemas just like code: request a schema change by pull request; CI validates and sanity-checks; review the PR within your own team just like code; merge to kick off the change using a CD pipeline. I liked this approach so much that I started an open source project in early 2016 to help enable this workflow for MySQL and MariaDB, https://www.skeema.io https://www.skeema.io. It has a number of validations and safety checks built in, including the one described in your 4th bullet.

mjw1007 7y ago
If I had tooling which I trusted to be able to transition from any schema state I might want to use to any other such state, I would happily use it. I've never found any, though it's a few years since I last looked. (I use Postgres, which has a fairly rich set of possible schema objects, and adds new bits frequently; I'd be reluctant to rely on a project that wasn't actively maintained and starting from 100% support). The principle here is that in this case (as often), it's much easier to teach a computer to check that you've done manual work correctly than it is to teach the computer to solve the general problem. This has been working well for me for many years, but I'm typically only dealing with a few dozen schema changes (in production) a year; I can imagine that Facebook sees the world just slightly differently.