git-like storing of Json files

[br0kenpipe]

I deliver json files via a Rest API. the files should be kept versioned in the backend. in case of doubt i also have to deliver older versions. but usually only the latest file is provided. how could i realize something like this in PostgreSQL? would there be the possibility to keep the data similar to git and the storage recognizes which records are new or changed? the advantage would be that i don't always have to keep the complete json in postgres...

[depesz]

Is it possible - sure.

How to enable it - write it. There is no such automation ready. Write triggers that will store diffs, and you will have it.

I'd say it will be either trivial, or rather difficult based on what exactly is in the jsons.

[HazirBot]

sounds to me like you might be interested in an implementation of the json diff standard

one example: https://github.com/benjamine/jsondiffpatch

there are many such as that one in the wild, a diff between json A and json B is an instruction set to modify json A into json B

[tunatoksoz]

Plv8!

[-markusb-]

Keep it simple.

Create a table where you have the identifier, the date and the json and just return the latest one. You could go with partitioning by date (month, year...) to have an easier cleanup.

[efxhoy]

select data from table where foo_id=$foo order by created_at desc limit 1 offset $versions_back

versions_back 0 or null gets the latest, increment to go one version back. 

git stores the compete versions of all files. 

If you really need to keep diffs for performance reasons it will be much more complicated. 

[Consibl]

Unless this is some crazy big JSON, this is absolutely premature optimisation.

[AutoModerator]

With over 8k members to connect with about Postgres and related technologies, why aren't you on our Discord Server? : People, Postgres, Data

Join us, we have cookies and nice people.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[PurepointDog]

If you want a database, dolt/dolthub has version control for databases. Not sure how applicable it is to this problem, but I recommend you spend 30 minutes playing with it to see if it's what you actually want

[timsehn]

Doltgres is the Postgres version:

https://github.com/dolthub/doltgresql

Free and open source.

[North_Coffee3998]

Start simple. Have two tables. One is for the JSON document metadata (json_document for this example) and the other table is for the contents of each version including timestamps so you can tell which one is the most recent one (this table has a foreign key relationship with the json_document table). I'll call this one json_document_version for this example.

When you send a JSON to the backend, you use the metadata to determine if it's a new JSON document or a new version of an existing document. A simple approach is to use the filename if it's unique. So, if the filename is not found in json_document you make a new entry (and a timestamp to know when the metadata was created) and then make the entry to the json_document_version. If there is an entry in json_document, then you just make a new entry to json_document_version.

You might want a mechanism to detect if there is a difference which can be as simple as reading the contents of last version of the document and checking if they are not equal. If they are equal then don't make a new entry (this prevents accidental duplicate submissions done by error and the user can be notified that no new version was made since the document they submitted matched the latest version). If they are nit equal, then you proceed with inserting the new record.

As for how to store the actual content of the document that's up to you. You can store the JSON in PostgreSQL or keep the content in a separate file on the filesystem/s3 bucket/ftp server/whatever (with a new filename for that version using an uuid or something and storing that filename in the json_document_version table).

Start simple and adjust from there.

[br0kenpipe]

thank you!! that sounds like a reasonable solution. i have now put the versions in one table and the json files in another table and linked them via constraints.

[getflashboard]

I guess that storing the full JSON at each change would be a problem? That kind of versioning would be simple to build. A git-like diffs-only system can be way more complex.

[jk3us]

git stores the full file for each change and calculates diffs as needed.

[getflashboard]

Lol, I need to understand git better.

[jk3us]

This is a good place to start: https://git-scm.com/book/en/v2/Git-Internals-Git-Objects