What's a Good Way (Or Tool) to Version Control a SQLite Database (Schema Only)

Does it make sense to store a SQLite database in version control?

Instead of storing the binary file for the SQLite database you should store the source material - either some XML/CSV/... file, or SQL file containing the insert statements.

This way you will get proper support for merging and viewing history/comparing (which does not really work for binary files, only text files).

How do you version your database schema?

See

Is there a version control system for database structure changes?

How do I version my MS SQL database in SVN?

and Jeff's article

Get Your Database Under Version Control

I feel your pain, and I wish there were a better answer. This might be closer to what you were looking for.

Mechanisms for tracking DB schema changes

Generally, I feel there is no adequate, accepted solution to this, and I roll my own in this area.

How to do version control for SQL Server database?

Martin Fowler wrote my favorite article on the subject, http://martinfowler.com/articles/evodb.html. I choose not to put schema dumps in under version control as alumb and others suggest because I want an easy way to upgrade my production database.

For a web application where I'll have a single production database instance, I use two techniques:

Database Upgrade Scripts

A sequence database upgrade scripts that contain the DDL necessary to move the schema from version N to N+1. (These go in your version control system.) A _version_history_ table, something like

create table VersionHistory (
    Version int primary key,
    UpgradeStart datetime not null,
    UpgradeEnd datetime
    );

gets a new entry every time an upgrade script runs which corresponds to the new version.

This ensures that it's easy to see what version of the database schema exists and that database upgrade scripts are run only once. Again, these are not database dumps. Rather, each script represents the changes necessary to move from one version to the next. They're the script that you apply to your production database to "upgrade" it.

Developer Sandbox Synchronization

1. A script to backup, sanitize, and shrink a production database. Run this after each upgrade to the production DB.
2. A script to restore (and tweak, if necessary) the backup on a developer's workstation. Each developer runs this script after each upgrade to the production DB.

A caveat: My automated tests run against a schema-correct but empty database, so this advice will not perfectly suit your needs.

Is there a version control system for database structure changes?

In Ruby on Rails, there's a concept of a migration -- a quick script to change the database.

You generate a migration file, which has rules to increase the db version (such as adding a column) and rules to downgrade the version (such as removing a column). Each migration is numbered, and a table keeps track of your current db version.

To migrate up, you run a command called "db:migrate" which looks at your version and applies the needed scripts. You can migrate down in a similar way.

The migration scripts themselves are kept in a version control system -- whenever you change the database you check in a new script, and any developer can apply it to bring their local db to the latest version.

How can I put a database under git (version control)?

Take a database dump, and version control that instead. This way it is a flat text file.

Personally I suggest that you keep both a data dump, and a schema dump. This way using diff it becomes fairly easy to see what changed in the schema from revision to revision.

If you are making big changes, you should have a secondary database that you make the new schema changes to and not touch the old one since as you said you are making a branch.

Right way of tracking a sqlite3 binary file in git?

You could define a custom merge driver specifying to always "keep theirs" (copy the version you are pulling) on top of your current version.

.gitattributes

mysqlite3.db merge=keepTheir

(That being said, remember binaries aren't always best managed with Git, especially if they are modified often)

Best Database Change Control Methodologies

The easiest way I have seen this done without the aid of an external tool is to create a "schema patch" if you will. The schema patch is just a simple t-sql script. The schema patch is given a version number within the script and this number is stored in a table in the database to receive the changes.

Any new changes to the database involve creating a new schema patch that you can then run in sequence which would then detect what version the database is currently on and run all schema patches in between. Afterwards the schema version table is updated with whatever date/time the patch was executed to store for the next run.

A good book that goes into details like this is called Refactoring Databases.

If you wish to use an external tool you can look at Ruby's Migrations project or a similar tool in C# called Migrator.NET. These tools work by creating c# classes/ruby classes with an "Forward" and "Backward" migration. These tools are more feature rich because they know how to go forward as well as backwards in the schema patches. As you stated however, you are not interested in an external tool, but I thought I would add that for other readers anyways.

---

Related Topics