Json-Base – Database built as JSON files

JSON file as backend, So can I directly edit the JSON file?

Someone at my old company basically did this and put it into production.

The first problem he encountered was that multiple connections couldn't both be using the database at a time without clobbering each other. "No problem," he thought, this is a good use case for micro services. A service sitting on top would ensure that there was only one operation being performed at a time.

Next, his problem was that the database would get corrupt sometimes when something bad happened in the middle of writing the file. His solution was to put the entire JSON format inside of a JSON string. If it could be parsed successfully, then he knew the whole file was written. Then all he needed were "backup" files for each table, in case the current one was corrupt.

Next, his problem was that querying and iterating through a large table performed badly, since it required parsing the entire thing first. Querying several times required the whole file to be parsed every time. The solution was to move SOME of the tables over to JSON-inside-SQLite.

EDIT: Oh yeah, the next problem was how to structure the data inside of sqlite. He decided to make a single table called "kitchen_sink" that held every JSON value. There was a column that said which "collection" it belonged to. There was another column that represented the row's primary key. So you could quickly query for a collection name, and a primary key, and get the full JSON row.

So the next problem was that you couldn't query quickly for things that weren't the primary key. So new columns had to be added called "opt_key1" and "opt_key2" where certain rows could put key values, and indexes could be added on those columns, so you could quickly query by it's first optional key, or it's second optional key.

I work on a system with a JSONB column with 11k unique paths with no schema. I created some tools to generate jsdoc which is a start, but please no.

Maybe similar to things like json-server (mockup a REST api by making a few json files) which is useful during the development process?