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

ID : 10310

viewed : 22

Tags : databasegitversion-controlpostgresqldatabase

Top 5 Answer for How can I put a database under git (version control)?

vote vote

97

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.

vote vote

80

I'm starting to think of a really simple solution, don't know why I didn't think of it before!!

  • Duplicate the database, (both the schema and the data).
  • In the branch for the new-major-changes, simply change the project configuration to use the new duplicate database.

This way I can switch branches without worrying about database schema changes.

EDIT:

By duplicate, I mean create another database with a different name (like my_db_2); not doing a dump or anything like that.

vote vote

71

Use something like LiquiBase this lets you keep revision control of your Liquibase files. you can tag changes for production only, and have lb keep your DB up to date for either production or development, (or whatever scheme you want).

vote vote

70

  • Irmin (branching + time travel)
  • Flur.ee (immutable + time travel)
  • Crux DB (time travel)
  • TerminusDB (branching + time travel)
  • DoltDB (branching + time-travel)

I have been looking for the same feature for Postgres (or SQL databases in general) for a while, but I found no tools to be suitable (simple and intuitive) enough. This is probably due to the binary nature of how data is stored. Klonio sounds ideal but looks dead. Noms DB looks interesting (and alive). Also take a look at Irmin (OCaml-based with Git-properties).

Though this doesn't answer the question in that it would work with Postgres, check out the Flur.ee database. It has a "time-travel" feature that allows you to query the data from an arbitrary point in time. I'm guessing it should be able to work with a "branching" model.

This database was recently being developed for blockchain-purposes. Due to the nature of blockchains, the data needs to be recorded in increments, which is exactly how git works. They are targeting an open-source release in Q2 2019.

Because each Fluree database is a blockchain, it stores the entire history of every transaction performed. This is part of how a blockchain ensures that information is immutable and secure.

Update: Also check out the Crux database, which can query across the time dimension of inserts, which you could see as 'versions'. Crux seems to be an open-source implementation of the highly appraised Datomic. However, it doesn't support branching like Dolt and Git.

Crux is a bitemporal database that stores transaction time and valid time histories. While a [uni]temporal database enables "time travel" querying through the transactional sequence of database states from the moment of database creation to its current state, Crux also provides "time travel" querying for a discrete valid time axis without unnecessary design complexity or performance impact. This means a Crux user can populate the database with past and future information regardless of the order in which the information arrives, and make corrections to past recordings to build an ever-improving temporal model of a given domain.

Update II Check out Terminus DB: "Documentation for TerminusDB - an open-source graph database that stores data like git". It supports branching like Dolt and Git, but does not look very mature yet.

Update III NomsDB was forked and is now in use in DoltDB: https://github.com/dolthub/dolt

vote vote

56

There is a great project called Migrations under Doctrine that built just for this purpose.

Its still in alpha state and built for php.

http://docs.doctrine-project.org/projects/doctrine-migrations/en/latest/index.html

Top 3 video Explaining How can I put a database under git (version control)?

Related QUESTION?