Versioning Postgres objects

For a personal project, I wanted to have objects stored in a PG database, with the history of their versions. I read a lot of solutions on the web, the best inspiration was this article from Kaustav Das Modak. Thanks to him!

But his solution was not perfect for my case, since it stores values of each object column in a separate column in the history table, and my object has 100+ columns. Also, I wanted a procedure to restore a version, and I wanted to see the differences between each versions of the object.

The only thing I’m not too happy about is in the restore function, I’m deleting the record, then recreating it, and possibly there could be a clever update function directly using the json column. I would be nicer, because then the change in the history table would only show the column diff and not the entire object.

Implementation of the UI

Here is a very simple implementation, with the list of objects on the top, the list of versions below. The column “Changements” shows the changes since previous version. While not super readable for non tech users, it’s still much better than showing the entire json of the record. Restoring a version creates a new version of the object, equal to the version you chose to restore.

Note: the change column of the table actually has always two fields more, updated_by and updated_at, but they are not interesting since they are already in the other columns of the table. So I remove them in before showing the value by using delete function of the hstore object:

delete(delete(changes, 'updated_at') , 'updated_by') Changements

The UI was done using Appsmith, which is a quite new low-code solution, opensource, that you can self-host very easily. I highly recommend it, it’s very powerful and you can get started in no time. Plus, they are a super nice team, always super responsive and helpful on their discord.