Show HN: Version Control for Databases
github.com
github.com
Restoring snapshots isn't really what you want with databases. The data in production is only in production so you need to refactor it. The issue in today's 365/24/7 always online is that refactoring database structure in that way with a lot of data causes downtime so these sorts of tools require a lot of orchestration around them.
Liquibase can also capture an existing structure and data set with an export as well from just about any database engine out there, so you can capture your initial point as you are and then move forward with updates being individual changes so it is relatively easy to adopt.
--liquibase formatted sql
--changeset alice@example.com:1
create table person (
id int not null primary key,
name varchar(100)
);I lay out this approach here: https://djrobstep.com/talks/your-migrations-are-bad-and-you-...
hbm2ddl.auto=update
But it isn’t recommended for production because the automatic diff-based approach can lead to unpleasant surprises. With individual migration files, you always know exactly what they are going to do and can think and plan for how a rollback might be necessary if needed.
It’s fair to say that you don’t necessarily need to keep 100 individual files around once they’ve been deployed to every environment for a while and there’s no chance you’d rollback. But I think people typically do this approach because it is simpler and easier than attempting to export the database structure at #100 or whatever and setting a new baseline every few months.
In fact, probably the main benefit of the diff approach is that you can rigorously test the outcome, and explicitly confirm a matching schema will result.
You're just storing a migration script in the repo still, but it's generated and works against only a single schema "revision"... ?
So it's not really applying to different schemas, each single migration script applies against the same state that the previous one produced - but collectively they can be applied against a database from any point in time while that migration system has been used.
You've simultaneously said that storing scripts is "bad" and that a live "diff" is better, but then also said that those generated "diff" scripts can be reviewed, which means you have to store them.
I'm completely aware of using tools to generated scripts by diffing a model schema against a DB, and using a different tool to apply the finalised, reviewed scripts without invoking the "diff" tooling outside of development environments.
What I cannot grasp is your convoluted claims that storing scripts is bad, and that diff's can be reviewed before they're applied, without storing them, and will work against some cowboy-esque DB modifications.
How about you just give a UUID to everything in the schema?
The technical challenge with generated scripts (that you could edit by hand, but that just means that you now don't have an automated system) is that they don't understand changes at a deep enough level - they lack the context to see that a table or column has been renamed because they have no understanding of identity.
So - just give them identity.
Schema at commit 20b1ea23
03496418-e44c-42a6-a6a4-6563b7ae7bfb users
25233812-9a95-4bc3-893e-6accb935fa49 name
2f4c79c3-81b6-42d4-8379-ce5f0ed8ef62 address
83fc34c8-56c7-49d4-94d0-150cd76204bc password
Schema at commit c0d07562
03496418-e44c-42a6-a6a4-6563b7ae7bfb users
89482484-8205-40ad-a73b-a1bb988dc1d9 firstname
25233812-9a95-4bc3-893e-6accb935fa49 lastname
2f4c79c3-81b6-42d4-8379-ce5f0ed8ef62 address
c9f0d35d-439e-488c-a6d5-7a144c54335c address2
83fc34c8-56c7-49d4-94d0-150cd76204bc password
@@ -1,4 +1,6 @@
03496418-e44c-42a6-a6a4-6563b7ae7bfb users
- 25233812-9a95-4bc3-893e-6accb935fa49 name
+ 89482484-8205-40ad-a73b-a1bb988dc1d9 firstname
+ 25233812-9a95-4bc3-893e-6accb935fa49 lastname
2f4c79c3-81b6-42d4-8379-ce5f0ed8ef62 address
+ c9f0d35d-439e-488c-a6d5-7a144c54335c address2
83fc34c8-56c7-49d4-94d0-150cd76204bc password
Assuming any non-trivial software deployment (multiple servers), it's impossible to execute the rename DDL at the exact same time as new application code goes live. So either you end up with user-facing errors in the interim, or you can try to write application logic that can interact with both old and new names simultaneously. That's overly complex, typically not supported by ORMS or DAOs, and very risky in terms of bug potential anyway.
I'm a database expert, and among 100% of the companies I've worked at or consulted for, renames were either banned entirely or treated as a very rare special-occasion event requiring extra manual work. Either way, lack of rename support in diff-based schema management isn't really a problem, as long as the tooling has these two properties:
1. Catches unsafe/destructive changes in general and require special confirmation for them (preventing accidental rename-as-drop in the rare case where a rename truly is desired)
2. Offers a way to "pull" from the db side, so that if a rename is actually needed, it can be done "out of band" / manually, and the schema repo can still be updated anyway
> Either way, lack of rename support in diff-based schema management
You're trying to polish a turd here, this isn't "lack of support" it's "it will drop objects in production."
> Catches unsafe/destructive changes in general and require special confirmation for them
Again, polishing a turd: the only way your "automation" works is through manual intervention.
> Assuming any non-trivial software deployment (multiple servers), it's impossible to execute the rename DDL at the exact same time as new application code goes live.
You can construct a view referencing the old table, and rename the table. Yes, it has to be an updateable view and you need transactional DDL, but within those constraints, it's doable.
> I'm a database expert, and among 100% of the companies I've worked at or consulted for, renames were either banned entirely or treated as a very rare special-occasion event requiring extra manual work.
If they're using a DBMS that doesn't support transactional DDL, completely understandable. If their tools are liable to drop production data due to renames, also completely understandable.
But the fact that they ban a trivial operation is a symptom of the problem with all the half solutions and snake oil in SQL schema management. It's so bad that you have large companies investing heavily in ripping out the schema entirely, which itself is just more snake oil.
In the problem space of trying to keep a schema in sync, we know that diffing leads to unacceptable answers, that is an indicator that it's the wrong conceptual basis for a correct solution.
That's a strawman argument. Any reasonable schema management implementation has safeguards against DROPs. If your tooling blindly executes a DROP without extra confirmation, use better tooling.
> Again, polishing a turd: the only way your "automation" works is through manual intervention.
There's absolutely nothing wrong with requiring extra human confirmation for destructive actions. Quite the contrary. I've spent most of the last decade working on database automation and operations at social network scale, and will happily say this is a common practice, and it's a good one at that.
> You can construct a view referencing the old table, and rename the table. Yes, it has to be an updateable view and you need transactional DDL, but within those constraints, it's doable.
So you're assuming that every single table has a view in front; or you're dynamically replacing the table with a view and hoping that has no detrimental impact to other database objects or app performance. Either way, you're talking about something operationally complex enough that it isn't fair to say that production table renames or column renames are a "trivial operation" at the vast majority of companies.
> It's so bad that you have large companies investing heavily in ripping out the schema entirely, which itself is just more snake oil.
This is frequently overstated. For example, although Facebook uses a flexible solution for its largest sharded tables, there are many tens of thousands of other tables at Facebook using traditional schemas.
> In the problem space of trying to keep a schema in sync, we know that diffing leads to unacceptable answers, that is an indicator that it's the wrong conceptual basis for a correct solution.
The only "unacceptable answer" you've cited is rename scenarios, which even if it incorrectly leads to a DROP, the tooling will catch.
If you need crazy view-swapping magic to support an operation (renames), that is an indicator that it's a conceptually problematic operation that should be strongly reconsidered in production.
As I've already stated elsewhere in this thread, declarative schema management has been successfully used company-wide by Facebook by nearly a decade, and is also a common practice in the MS SQL Server world. If you're unconvinced, that's fine, but many companies have found it to be a great workflow!
IMO you're inventing problems to justify your own preferences.
For context: I use, and am a proponent of the 'series of patches' approach for SQL migrations. I wrote a tool to apply them, because I wasn't happy having to constantly patch the existing one I'd been using.
I don't really understand what you mean about "managing versions" - you just need a naming scheme that ensures the items can be applied in order. Dates (as a prefix, with a descriptive suffix) usually works well.
I also don't see how you think having the patches over time in in the repo is a problem. If you don't like seeing the full history of changes - just remove the old ones after a while.
The "series of patches" approach also has a benefit that I don't see how your "diff" approach would solve (without some overly complex tooling): upgrading a previous version of the database to the current schema.
With our current system, I could take a database dump from 2 years and dozens of migrations ago, run the migrations on it, and it would come up to the current schema.
They're also impractical to use in environments where your one versioning system isn't the only use of the data. If you or some DBA needs to make an emergency database change - suddenly the real DB doesn't match your versioning.
But if you compare directly against production, it's not a problem at all.
Not directly checking against the production schema is rather like flying a plane with no reference to what is actually outside the windows - rather an unreliable experience.
Nothing is stopping you from keeping a long chain of these scripts if you want - you'll lose nothing and gain more automation and testability.
But in practice nobody wants to keep these files around, and nobody wants to restore 2 year old backups to production.
And if you did, a diff-based approach will do just as well, more automatically.
Where the diff-based approach probably shines the most is when making experimental changes locally during development. Play around with your models, add a column, add a constraint, rename the column, make it not null, change your mind and drop it again. One command syncs the dev database to your models automatically and instantly.
I literally said there are no version numbers to track. If you find identifying the current date either manually or in some automated tool tedious, I don't know how to help you.
> If you or some DBA needs to make an emergency database change - suddenly the real DB doesn't match your versioning.
Well by that logic, if I suddenly need to make some "emergency" fix to the code, it won't match the version control system.
The solution there, is to have a method in place to rapidly deploy a change, not to make your migration tool also work around cowboy solutions.
Your previous comments also imply that to be usable, the "diff" system needs to be reviewed (i.e. to handle table renames, and to be considered safe for production).
So how does that handle the cowboy approach where the schema doesn't match? Either your "diffs" are generated at the time of execution, so the previous state is regardless but you can't review them, OR your diffs are generated ahead of time, so they can be reviewed, but will not necessarily work against the live database.
So which is it?
I'm not against a "make changes against the DB directly" workflow for development. I've written code that does exactly that, as you describe, from models. But it's not practical for production use.
It's usable as a development tool, and to produce static migration scripts.
If an on-call SRE calls me in the middle of the night asking me if he can add an index to solve a performance problem, I'd rather say "yes, no problem", not present a series of additional steps for them to jump through.
You review the script, and when the time comes to apply, recheck the database to make sure it's still the same state you generated the script against. Generally people tell you if they've made changes on live dbs that you're working on, but it's nice to double-check regardless.
What external information? Whether each migration has been applied or not is stored in the database itself. The dates are literally used just to ensure correct ordering - that's literally their only purpose.
> not present a series of additional steps for them to jump through.
If someone can't write the change they want to make into a file, write the opposite action into another file, commit and push that change to a VCS repo, I don't think they should be given access to a god damn toaster oven much less your production database.
> You review the script, and when the time comes to apply, recheck the database to make sure it's still the same state you generated the script against.
.. How can that possibly work with automated deployments? And how on earth do you "recheck the database to make sure it's still the same", with any degree of certainty?
Your entire approach smells like a very manual process that doesn't work for teams any larger than 1 person.
You're dragging this further into pedantic territory here. A chain of scripts and a version table is external to the structure of the database itself.
> If someone can't write the change they want to make into a file, write the opposite action into another file, commit and push that change to a VCS repo...
The recurring theme here is that you have a preference for mandatory busywork instead of a direct approach. People putting out fires ought to be focused on what will directly solve the problem most quickly and safely. In larger environments with dedicated ops people supporting multiple applications/environments/databases, not every ops person is going to be familiar with your code and preferred workflow.
> How can that possibly work with automated deployments? And how on earth do you "recheck the database to make sure it's still the same", with any degree of certainty?
...with a diff tool.
> Your entire approach smells like a very manual process that doesn't work for teams any larger than 1 person.
The whole point is that it is automatic rather than manual. I've used it before in teams "larger than 1 person" and it has worked fine.
You may be misunderstanding the concept. Automated declarative schema management (AKA diff-based approach) has been successfully used company-wide by Facebook for nearly a decade, to manage schema changes for one of the largest relational database installations on the planet. It's also a widely used approach for MS SQL Server shops. It's not some newfangled untested crazy thing.
I have a post discussing the benefits here: https://www.skeema.io/blog/2019/01/18/declarative/
I wrote the same functionality into a library.
What I cannot comprehend is the poster who claims that such an approach can simultaneously:
- be automatically applied
- be reviewed and even edited after generation to handle e.g. renames
- handle previously unknown changes in the DB schema (aka handling cowboy behaviour from other ops).
All three are simply not possible at once.
Ideally this workflow is wrapped in a CI/CD pipeline. To request a schema change, you create a pull request which modifies the CREATE statement in a .sql file in the schema repo. CI then automatically generates and shows you what DDL it wants to translate this change to.
If that DDL matches your intention, merge the PR and the CD system will apply the change automatically. If that DDL doesn't match your intention, you can either modify the PR by adding more commits, or take some manual out-of-band action, or modify the generated DDL (if the system materializes it to a file prior to execution).
In any case, renames are basically the only general situation requiring such manual action. Personally I feel they're a FUD example, for reasons I've mentioned here: https://news.ycombinator.com/item?id=21758143
- Develop intended schema (I)
- Inspect production schema (P), save as P0
- Generate migration (M) by comparing to production (P0): I
- P0 = M
- Edit M as necessary, test for correctness, commit to master (meets your second criteria)
- Deploy code, with the migration running as a deploy step (meets your first criteria)
- Migration works as follows:
- Inspect P again, save as P1. If P0 != P1, abort process (this prevents any issues from out-of-band changes as per your third criteria, and means the pending script won't run more than once)
- Apply M.
- Inspect P once more save as P2. Double-check P2 == I as expected.
It's main goal is to simplify development - it's not intended as a replacement for writing database migrations or to work with production databases. I would be grateful for any feedback, preferably in the form of pull requests ;-)
But a bind mount (which is usually the recommended way to do it so that data survives the container lifespan) will avoid that and just leave you with whatever "problems" the host filesystem has re: database files.
how? note that docker commit/export does not include volumes
There was also a github issue with numerous people voting for it (https://github.com/moby/moby/issues/33782)
But for whatever reason, the docker team apparently decided not to build it. We still don't have a properly easy way to snapshot or share docker volumes.
So I came up with my own method.
Basically, you have to mount the volume and then tar the parts of it that you want snapshotted (typically your data directories). You can do this with any linux machine image. Here's an example (where "foo" is the docker container you want to snapshot):
mkdir -p docker-volume-snapshots && docker run --rm --volumes-from foo -v $(shell pwd)/docker-volume-snapshots:/docker-volume-snapshots debian:stretch-slim tar -czvf /docker-volume-snapshots/snapshot.tar.gz /var/lib/my_data_folder
docker run --rm --volumes-from mysql8.0 -v $(shell pwd)/docker-volume-snapshots:/docker-volume-snapshots:delegated debian:stretch-slim /bin/sh -c "cd / && tar -xvf /docker-volume-snapshots/snapshot.tar.gz"
If I had one wish it'd be native support for syncing generated snapshots to S3 or Google Storage for easy transfers between authenticated systems and users. Maybe I'll take a look at building out a PR for that over the weekend if that's okay
- flyway
- liquibase
- dbmate
- alembic for python/sqlalchemy
- django migrations for python/django
- diesel migrations for rust/diesel
- echo migrations for elixir/echo
- persistent migrations for haskell/persistent
All of these, in one way or another, provide an ability to create sequential database schema changes that can be stored in version control and applied/rolled back.
What is your system doing that is different from all of these?
I haven't tried to maintain one in Git before, but I've been curious about this functionality.
You can import from either CSV or directly from XLS.
There are some extremely serious caveats here that need mentioning. A production DB is typically taking application writes 24/7. Restoring from a snapshot would cause data loss of any writes that occurred after the snapshot and before the restore.
You would need to also stop prod traffic and then replay the binary logs beginning from the same point as the snapshot, but that can be complicated if you're trying to reverse some DDL operation that is inherently incompatible with the transactions in the binlogs.
Also, if I'm not mistaken, your tool's invocation of mysqldump is not using the --single-transaction option. Without that, mysqldump doesn't create consistent snapshots for InnoDB tables. This means what you're dumping and restoring doesn't actually represent a single valid point-in-time of the DB's state.
I can see myself using a tool like this to streamline testing of destructive data changes.
https://github.com/flyway/flyway
https://github.com/liquibase/liquibase
A proper source control system for databases should handle both migrations and diffing across migrations. I should be able to compare any two commits and view exactly what is different between them.
If migrations are kept small there isn't usually much confusion over what changed (see migration), or what exists (see db).
There are a number of other existing tools that can compare/diff schemas on 2 live databases, but Skeema is designed to also actually manage your database structure through a declarative repo of CREATE statements. It works at any scale (natively supports sharding and external OSC tools) and is trusted by several large users, including GitHub [2] and Twilio SendGrid [3].
[2] https://fosdem.org/2020/schedule/event/mysql_github_schema/
[3] https://sendgrid.com/blog/schema-management-with-skeema/
I've added these to https://sirix.io, which fastens diffing considerably, especially with deep trees. Otherwise indexing changes could be done :-)
One thing which sets jOOQ apart from most other tools out there is the fact that it supports many different SQL dialects. Thus the schema diff tool can for instance also parse DDL in one dialect and render the diff in another SQL dialect. For certain applications this could be of interest.
Disclaimer: I am an active committer on the jOOQ project.
We are focused on the data distribution/open data use case (https://www.liquidata.co/blog/2019-10-09-where-is-the-data-c..., https://www.liquidata.co/blog/2019-12-06-the-history-of-data...) but I think a number of use cases mentioned in this thread are covered by Dolt.
You do have to move a copy of your data to Dolt, it does not sit on top or beside of your current database. So, it is an extra step in your data modification workflow, similar to versioning code. You had to add a version control step into your code modification workflow.
But what if the data store would do all of that already?
- retaining the history of your data:
- free deduplication through copy on write and sharing unchanged structures
- further store space savings through page-level versioning (not all records in a page have to be written: just the changed/inserted/deleted ones plus depending on the algorithm a few more to fast track it's reconstruction)
- read and write performance is balanced using a sliding snapshot algorithm: This avoids having to read long chains of incremental page changes and it avoids write peaks from intermittant full page snapshots. Instead they get trailed over a few (usually three or four revisions).
- the sorage manager stores timestamps for all revision in a RevisionRootPage, which itself is indexed
- Optional commit author and message
- Reverting to and committing a past "good" revision is just adding a few bytes to index a new RevisionRootPage which references the past revision data (simple pointer)
- You can diff taking rolling hashes into account
- Currently I'm working on a diffing JSON format and a web front-end to display these in special space filling approaches
- Sophisticated time travel queries
- typed, user-defined and versioned secondary index structures (based on AVL trees)
- a path summary kept up to date always
- a lot of metadata stored and kept up to date for each record/node
I'm working on getting it stable, but help from you, the Open Source community is always needed :-)
Each automation approach (SQL, dumpfiles, or storage) carries different tradeoffs, but we believe there's a real problem facing developers when it comes to managing data within their workflows. Would love to learn more about what some of the killer use cases are, and how these approaches can complement each other.
I don't think adding Postgres support would be that far fetched, have a look at https://github.com/infostreams/db/tree/master/bin/drivers/my... for example to see the meat of the load & save operations. You'd have to implement it twice though, once for local servers, and once for remote servers (via ssh).
> At the moment, only mysql is supported.
Seems like I won't be able to test this for work since we are on SQL Server. Too bad.
[0]: https://github.com/infostreams/db/blob/master/readme.md#synt...
Github link - https://github.com/dotmesh-io/dotmesh
> The text mentions connection type phpmyadmin, but it doesn't actually work. Don't use it.
Will phpmyadmin support work in future? My use case is making backups of a DB where I don't have SSH access but do have a phpmyadmin account.
Does the snapshot generated include the views, triggers and stored procedures?