Anti-pattern? Storing JSON table in SQL table

Question

For an application that I currently plan to scale up, there's a bunch of JSON dicts and directories making up the "database". The reason I didn't use SQL initially, is simply because I've never learned the language, and right now I'm forcing myself to.

The application has a constantly updated data structure (I'd not reject calling it schema), so it is important that newer versions of the app must accept old existing data, and preserve obsolete fields that might otherwise be reintroduced in later versions. - This is why I've always stored records in JSON files, as it's easy for me to handle the data.

I have basic knowledge about the relational model, and

Q1: I appreciate some advice on how to idiomatically write code that cope with and operate on different versions of the data and the schema.

I know something about the diversity (incompatibility I mean) of SQL implementations, and I'm planning to choose just one specific implementation to adapt my application for.

I've come up with something that I can sensibly tell that it's an anti-pattern:

Q2: Would storing JSON data along side unique table key and sorting key, just for the purpose of using SQL implementation to scale up the performance, and parse JSON data using application code an anti-pattern? And how should I avoid it?

Answer 1

Storing JSON in an SQL database is a perfectly reasonable architectural choice for this requirement:

The application has a constantly updated data structure (I'd not reject calling it schema), so it is important that newer versions of the app must accept old existing data, and preserve obsolete fields that might otherwise be reintroduced in later versions.

It is not a perfect fit for a relational database - what you really want is a document-oriented database. Some so-called "NoSQL" products specialize in this, and typically offer better performance for really big workloads.

But using SQL databases as document stores is not unusual and some databases even support it directly. It's still useful to have a mature, stable, performant product that many developers are very familiar with, and the additional advantages that a specialized document database would bring are not that big.

Answer 2

Take a look at FossilSCM

It uses a relational database (SQLite), and also uses a flat file document system (called Artefacts).

It has the philosophy that the Artefacts are the store of truth. And indeed what is in each artefact varies considerably both by version, and purpose.

The relational database itself serves two purposes:

1. A resilient data format for storing artefacts.
2. A speed optimised index, and common property access system.

It is not unheard of for a new release to delete all previous tables and regenerate them from the artefacts.

There are of course other ways to structure a system, the point is to look at what is provided by having a document/relational/hierachial/etc system and combine their strengths so as to address weaknesses in each.

Consider Versioned Tables

Instead of having a single table with everything in it. Have a family of tables:

• Entity_v1
• Entity_v1_1 (for extend properties on a v1)
• Entity_v1_SLAC (for whatever SLAC means)
• Entity_v2 (for the v2 version)

Of course this will complicate any queries being run on this data. It might pay to perform online/off-line data migration. What you pay for in time to migrate is made up for by a simplified implementation.

This would be aimed at completely replacing the JSON with a well defined (though fluid) schema.

Triple Store

When you get right down to it, each thing is described by properties that have a set value.

(thing, property, value)

With this any data-structure can be generated. This is essentially a property bag, or a JavaScript Object. A good breakdown of this is given by Steve Yegge he calls it the Universal Design Pattern.

The downside is that it will be slow due to all of the joins/round trips.

It is general a good augment to a well defined schema of common properties, with this containing the less regular/rarer/defunct properties.

Answer 3

If you have ordinary data, convert it to JSON, and store it in a database, that’s not clever. But if the same JSON can arrive from your server or can be sent to the server, and you need to persist it as well, then you store it in some persistent store, and that persistent store could be SQL of course. I’d expect that you have code that expects to be given JSON somewhere. So you’d have code like:

let json = getJSONfromServer()
processJSON(json)
storeJSONinDatabase(json)

let json = getJSONfromDatabase()
processJSON(json)

The disadvantage is that your database may not be able to process the JSON data. So no “get all records where the “lastname” Inside the JSON equals “Smith”.