What's the correct way to deal with databases in Git?

Question

I am hosting a website on Heroku, and using an SQLite database with it.

The problem is that I want to be able to pull the database from the repository (mostly for backups), but whenever I commit & push changes to the repository, the database should never be altered. This is because the database on my local computer will probably have completely different (and irrelevant) data in it; it's a test database.

What's the best way to go about this? I have tried adding my database to the .gitignore file, but that results in the database being unversioned completely, disabling me to pull it when I need to.

Answer 1

While git (just like most other version control systems) supports tracking binary files like databases, it only does it best for text files. In other words, you should never use version control system to track constantly changing binary database files (unless they are created once and almost never change).

One popular method to still track databases in git is to track text database dumps. For example, SQLite database could be dumped into *.sql file using sqlite3 utility (subcommand .dump). However, even when using dumps, it is only appropriate to track template databases which do not change very often, and create binary database from such dumps using scripts as part of standard deployment.

Answer 2

you could add a pre-commit hook to your local repository, that will unstage any files that you don't want to push.

e.g. add the following to .git/hooks/pre-commit

git reset ./file/to/database.db

when working on your code (potentially modifying your database) you will at some point end up:

$ git status --porcelain
 M file/to/database.db
 M src/foo.cc
$ git add .
$ git commit -m "fixing BUG in foo.cc"
M  file/to/database.db
.
[master 12345] fixing BUG in foo.cc
 1 file changed, 1 deletion(-)
$ git status --porcelain
 M file/to/database.db

so you can never accidentally commit changes made to your database.db

Answer 3

Is it the schema of your database you're interested in versioning? But making sure you don't version the data within it?

I'd exclude your database from git (using the .gitignore file).

If you're using an ORM and migrations (e.g. Active Record) then your schema is already tracked in your code and can be recreated.

However if you're not then you may want to take a copy of your database, then save out the create statements and version them.

Heroku don't recommend using SQLite in production, and to use their Postgres system instead. That lets you do many tasks to the remote DB.

If you want to pull the live database from Heroku the instructions for Postgres backups might be helpful.

• https://devcenter.heroku.com/articles/pgbackups
• https://devcenter.heroku.com/articles/heroku-postgres-import-export