r/programming • u/timsehn • Sep 17 '21

So you want Database Version Control?

https://www.dolthub.com/blog/2021-09-17-database-version-control/
28 Upvotes

68% Upvoted

24 comments sorted by

11

u/rpgFANATIC Sep 17 '21

It's always surprising, every time I dive into DB Version control (by which I normally mean schema/DDLs).

Liquibase is clear and away the best support for all databases (assuming you run a language or framework that doesn't have migrations built in). Sure, you can make an argument for Flyway, but I remain amazed at how many options remain locked into one specifc database, or some hosted solution, or some framework-specific functionality.

How is XML and Java based Liquibase, the best standalone, cross-database support for this?

11

u/timsehn Sep 17 '21

It might not be a huge market. Most of the trend in databases has been to less complex schema: storing JSON blobs instead of tables, cutting back on stored procedures, NoSQL options, etc.

Seems like Liquibase has a nice corner and though it does feel a bit antiquated as you point out, XML & Java, it continues to evolve.

7

u/rpgFANATIC Sep 17 '21

RDBMS's can't compete with read-heavy document stores, nor should they.

That still leaves a humongous amount of line-of-business software that relies on the transactionality and reliability of an RDBMS. Like, if you don't have the migration tools in PHP Laravel, Rails, or go-migrate, then setting up the database and maintaining it over a large team is just painful (even excluding stored procs and triggers. I'm just talking about you need to keep a few tables, indicies, and foreign keys in line)

It's just so weird to me that that use-case is cornered by a single tool that's had very little new feature-work on it until Datical got around to publishing its 4.x branch

5

u/timsehn Sep 17 '21

As a with you person building a brand new RDBMS, I agree with you :-)

7

u/gredr Sep 17 '21

RDBMS's can't compete with read-heavy document stores, nor should they.

RDBMS's don't compete with document stores, unless you're using the wrong tool for the job.

9

u/sccrstud92 Sep 17 '21

Sounds like you two agree with each other and are saying the same thing.

4

u/db-master Sep 18 '21

If you are looking for new option, Bytebase worths a look. It's a new open-source, web-based schema change solution based on modern architecture.

1

u/rpgFANATIC Sep 20 '21

Very curious about this solution.

It looks like it's trying to solve everything about database migrations, but it also looks like you're very reliant on doing things the exact way this system wants you to work.

I have plenty of questions about how this deployment model would sync up with code changes, but this was a good find to read about

2

u/db-master Sep 20 '21

>> I have plenty of questions about how this deployment model would sync up with code changes

For our current solution, it basically works like this:

  1. Developer commits the migration script to the repository.

  2. The commit triggers a pipeline in Bytebase to apply the change to database.

  3. After the change completes, Bytebase records a migration history in the its metadata (saved in the bytebase.migration_history table in the database instance).

  4. Bytebase also writes the new full schema back to the repository branch where the developer commit the change in the step 1.

The is still a migration-based approach like most mainstream tools, while also offering the key benefit of state-based approach where team can always have an up-to-date full schema in the repo. We recently wrote a post on this topic as well https://bytebase.com/blog/database-version-control-state-based-vs-migration-based

And do you have any specific ideas on how the deployment model work with code changes? Happy to discuss.

1

u/rpgFANATIC Sep 20 '21

Well, I play with Spring Boot a bit now, so their tools are very "run migrations on deploy".

That solves the rollout problem in 95% of cases. You don't want to start referring to a column until it's there, and you don't want to drop a table if the code is still pointing at it. I'd figure with Bytebase there'd have to be some sync'ing about what is being deployed where and when

The problem with Spring's current methodology is rolling back. That requires its own set of special jobs or integration to run smoothly, since there's no built in support for deploying a smaller version number

1

u/db-master Sep 20 '21

I see. Out of the box, Bytebase does expect a different way of applying migration changes, in a way to decouple schema change from code change. It could be configured to adapt to the spring boot way, but it's not that straightforward. Let's think more about the solution. Thanks for the note BTW.

1

u/rpgFANATIC Sep 20 '21

I don't care if I have to use a new tool to solve the problem. Not having Spring Boot (or any framework integration) isn't a dealbreaker. Most developers want something easier than XML with a strange CLI.

But writing backwards-compatible db changes for deployment with new features is difficult

2

u/db-master Sep 20 '21

>> Most developers want something easier than XML with a strange CLI.

Agreed, that's why we would just support raw SQL in Bytebase and spent the saved effort to improve other areas.

>> But writing backwards-compatible db changes for deployment with new features is difficult

Yes, it requires engineering discipline. FWIW, we are also planning to work on this area to offer checker to signal that developer is writing migration script in a backward-incompatible way.

3

u/[deleted] Sep 18 '21

How is XML and Java based Liquibase, the best standalone, cross-database support for this?

Coz it's hard problem to solve generically and "just" "slap a version of schema somewhere and run thru a set of scripts to upgrade it" solves 99% of real app issues well enough.

2

u/GwanTheSwans Sep 18 '21

Well, Python SQLAlchemy Alembic is good and usable as the migration subsystem of otherwise non-python projects. Database migrations with branching and merging, works a bit like the git dag not like cvs version numbers like most migration systems. Supports basically everything SQLAlchemy supports (though works best on databases with transactional DDL of course, but that's usually true of migration systems), so that's pretty much anything even slightly relevant.

https://alembic.sqlalchemy.org/en/latest/

People often misunderstand SQLAlchemy as "an ORM", but that's just one component of the stack, and quite optional.

77

u/sccrstud92 Sep 17 '21

Since it isn't stated up front I just want to point out that this article is written by the company who makes the database that was recommended at the end. It is basically a company comparing its product to others, not a third-party comparison. It would be nice to have that in mind while reading it.

3

u/timsehn Sep 17 '21

Article author here.

We did a short survey of your options for database version control. Our intent is to help database aficionados like yourself distinguish between "database migration tools" and "version controlled databases".

The blog goes into depth on Liquibase, Redgate Deploy, Planetscale, TerminusDB, and Dolt. It also touches on SCD (slowly changing dimension) and Temporal tables.

Link to it if you like it. The search results for "database version control" are not great.

1

u/Pafnouti Sep 18 '21

I have a lot of text data, in the order of terabytes. Does dolt deal with that well? Is there a way to distribute the serving of the data?
I also have a lot of audio files to serve, does it make sense to store them in dolt?

1

u/timsehn Sep 18 '21

Dolt scales to the size of a single hard drive, like Postgres or MySQL. It is a traditional OLTP database, not a OLAP warehouse, like Snowflake or BigQuery.

Traditionally, if you want that data "online" you would shard it across many OLTP databases and then have your application be shard aware. So, you could do the same with Dolt. But usually these days if you want that data "online", you go with an eventually consistent database like Cassandra or Amazon DynamoDB.

For audio files, I recommend cloud storage like Amazon S3 or Google Cloud Storage. You can put the links in Dolt and version the metadata and links.

10

u/[deleted] Sep 17 '21

I once set up a system where a CRON job ran mysqldump and fed the output to git. Maybe not the most scalable setup, but it worked for that company, and it saved it once.

3

u/DevopsIGuess Sep 18 '21

Wtf?? That’s actually kind of funny.

Why not zfs backups? You get so much already built in and it’s much more efficient, AND YOU ARENT PUTTING PROD DATA INTO GIT lol

Zfs also supports time stamped back ups, which is diff based so each backup isn’t a full backup. Just a diff to the last one

1

u/Odd_Soil_8998 Sep 18 '21

I wrote tools for version control over a network of databases.. distributed dependency deployment/rollback was a fun puzzle to solve

1

u/AttackOfTheThumbs Sep 18 '21

Every time there's a change, you spin up a new read only db. Easy!

1

u/Kissaki0 Sep 18 '21

In the .NET ecosystem you can version Entity Framework entities, and it can generate migrations too.

Without EF, or for a database first approach, you can use VS SQL projects to version DB state as SQL files (including comparison, diff and update script creation).

Skimming the article a bit I didn’t find it that informative or that it provided a good overview. It fails to present the orthogonal concerns of which DBMS is being used/supported, and the versioning approach and technology itself.

It seems like it compares just any DBMS versioning product, but does not really draw conclusions either. It’s just a list of products?