1

As the title says, my question is if it is Possible for a Database to version the contents like Git ? My use case would be for our business we go live 2-3 Times a Month all have specific Releas Names (Branches).

I am open to wich DB to use no matter if MongoDB or Mysql etc. And a Bonus would be to compare contents and or revert to an older version on the same branche. Later this will be programmed in Node.js

EDIT: to make it more clear. I want a row-version Controll. Not the shema

  • Yes. Look at [Liquibase](https://www.liquibase.org/) – Madhur Bhaiya Oct 18 '18 at 08:58
  • Please expand a bit on what stuff you want to version control. If you want row-level version control, you can easily do this with triggers inserting into a journal, or by doing what now is called "event sourcing" where you only have the journal table(s). If you want to switch between different schema versions, you need to think hard about what should happen to the data. You will need migrations in both directions, which is hard to do. – Corion Oct 18 '18 at 09:08
  • @Corion I want row-level version Controll. Sry if it wasn't clear. I am new to DB version-controll –  Oct 18 '18 at 09:14

1 Answers1

1

This is largely based on How to version control a record in a database and http://www.sqlitetutorial.net/sqlite-trigger/ :

You will need to find your business rules about what to version and what to update when. For example, if a customer changes her address, which orders do you want to update/change. Certainly you do not want to update orders with the new address when these orders have been fulfilled and paid a year ago already. But you certainly want do update the address in orders that have not already shipped. But for orders that have been shipped but not yet arrived, changing the delivery address will create lots of confusion for all parties involved.

Also, for auditing purposes, you may want to track all changes to certain fields of data. But for other regulatory reasons like GDPR, you maybe want to have a set date on when such changes expire.

So, before deciding on what versioning scheme to use, consider how the versioning will be used later in queries.

The approach that can most easily be tacked onto an existing schema is to have for each table mytable a second table mytable_log that gets filled through database triggers and stores the old row and the new row and the (database) user and action. That way, whatever your application does to the database is automatically recorded in the log without much impact on existing queries. You will need to find a way to communicate the acting user to the database, either through their database login or through an additional variable or stored procedure.

The other approach is to do away with the consolidated/current table and keep the history and the current state in the same table, and have views that reflect the current state of the world. This means you can do away with the database triggers completely and also revoke all delete/update permissions on the tables for the users. The downside is that you will have your program logic select the newest version of each customer (or whatever), which can be done through window functions, but will be slower than having a consolidated table around. Also, in this case, you need to take care when actually expunging a row from the database (say, for GDPR reasons). Even if a row is more than 10 years old, it may still be current because there is no newer version of the row available.

Corion
  • 3,649
  • 1
  • 15
  • 27