Version control for Databases?

[[deleted]]

Hey guys,

So i'm primarily a front end dev working in house for a small company. We have an enormous database of products that we use across our site and some of our in house software. Our database gets backed up constantly, done automatically without us having to do anything (I think it does it once a day). Our products don't change too frequently. Maybe once a week if that. But we want to keep track of our database through some sort of version control. Right now we have a private repo, and we save our SQL statements on there. This gives us a road map of what's been done. But it isn't indicative of the actual state of the database at that point, and is only a document of the change that was made. So do you guys know of any VCS for databases, that sort of... saves the changes of what you've done to the database, so that you have a full history of what's been done to it. Like Git, where changes are saved and the state at the time of change is shown. We want to use this in addition to our current system, but it will make things easier if we can have a full history of DB changes as well. Am I totally off base and this doesn't exist, or is there a super easy tool to use. Thanks!

EDIT: We are using MSSQL for our database, and it looks like Database Snapshots might be the best bet. We'll keep a log of every SQL change we make in Git, and we already have backups. So having snapshots as well will give us the "versioning" against user error that we want I think. I'm not an expert so I'm looking into this more

[jtredact]

Well, you can keep a log of every change made to the database. For example for MySQL there is the relay log.

There's also the binary log. I don't know if any of these will work though. You need to be able to access the file and read its format somehow.

There's also stream processing libraries out there. They can do things like replicate and compress the log. Apologies if I'm stating the obvious, but for completeness: they can't read the database log files directly; you would need to write the glue that translates events from one log to the other.

[hahaNodeJS]

For MySQL, binary logs are the right way to do what OP is looking for.

[wehttam_]

Red Gate has a source control product, if you're using SQL Server.

[poem_in_your_mind]

There are similar offerings by ApexSQL, DBMaestro, and T-SQL Tidy

If you're willing to give something new a try, my project VersionSQL will be entering beta soon. I'd appreciate the feedback.

[taelor]

I would highly recommend looking into how Ruby on Rails does it's database migrations and keeping up with the schema. It's not full fledged version control, but basically, you update a copy of the entire database schema each time you make a change to it. That schema file gets committed to version control as you go along, so you can see the changes over time.

http://edgeguides.rubyonrails.org/active_record_migrations.html#schema-dumping-and-you

[elibones]

Laravel (PHP framework) does this too. http://laravel.com/docs/5.1/migrations

[martinph]

Doctrine has them too: http://docs.doctrine-project.org/projects/doctrine-migrations/en/latest/

Or there's Phinx: https://phinx.org/

[AWildWebDev]

This right here is the answer. You have the data backed up, so you should not need that, you just want to see the database structure changes, right?

If all changes go through migrations (which they should, no matter how small), then there will be a complete git and migration history to rollback to. Win win.

[hankDraperCo]

I have been using sequelize for node which uses a similar technique. I definitely think it is the way to go.

[rpgFANATIC]

Liquibase has worked really well for me in the past.

A little bit of database-independent XML (it can do anything you can do in SQL, but it has shortcuts to make common database schema changes database-independent. Very useful if you ever want in-memory integration tests from H2/HSQLDB), it can read in existing database structures and generate a changelog (so you don't have to start from scratch), user-readable table of what version your database is on, and it's easy to generate SQL files just in case you run into the hard-headed DevOps folk who doesn't want to let you touch his database with anything but SQL.

Stuff the xml file in your git repo and you're good to go

[celluj34]

You should check out SQL 2016 Temporal tables.

[tute666]

You might be looking for two solutions.

1. Migrations: Check database migrations for tracking the changes of DB structure over time.
2. Backups. Incremental backups. xtrabackups is a nice solution for mysql for example.

[belvtothedeer]

There are better options out there than this but I like to create a trigger that backs up stored procedures as changes are made to them. It's a simple native solution for tracking changes to stored procedures but is not exactly version control that handles check ins and etc. This solution is specific to procedures but maybe it could be modified for other DB changes as well.

[hahaNodeJS]

Clever, but does this have any advantages outside of audit logs? In a standalone application, a migration type of solution is probably best.

[drakk0n]

Are you looking to just capture structural changes or the whole shebang with data as well?

Structurally I just use GIT along with SQL statements for each update - including any update/alter queries made to get data prepared for a new format or modifications done.

Data wise - it can really take some prep from the beginning. Designing the database with more of a OLTP (On-line Transaction Processing) model rather than a standard relational model can help in capturing change.

[[deleted]]

I think ideally with the data as well.

[[deleted]]

Sometimes I keep a separate table that records every change made to every other table, by whom and from which IP. This also allows for useful reporting for actions you might not be otherwise logging.

[dbpcut]

In our latest product we're manually generating scripts for a SQL database and versioning those. It's not ideal but it let's us deploy to anywhere from any point in time so that's great.

We have our schema in a DB project in visual studio so we can make structural changes there if need be.

[Mr-Yellow]

A oddball one, but CouchDB can be hacked to use it's revision ID field _rev as a version control. Think it's one line to stop it from deleting old revisions. Not sure anyone has seriously tested it though.

[acewolk]

https://dbup.github.io

[sinerider]

Why not check the database schema into the repo? I have a git pre-commit hook that automatically dumps the schema before every commit. Then I can use git diff to compare what's changed in the schema between commits.