When is the point where a JSON text file storage is not enough?

Question

I guess it was Robert C. Martin who campaigned against using SQL in one of his apps, where he was just fine without DB and SQL initially. Later, thanks to the abstraction a DB implementation could be built out on a single day (which later turned out to be unnecessary). I think his main point was just to not start out with the DB and then later implement it when it is deemed necessary.

So I was wondering when a DB is actually necessary, compared to writing the entire dataset into Json and reading it once the app starts?

Because my initial answer would be:

When the dataset cannot fit into memory.

At that point, there would be no workaround for ignoring an sql DB. I assume though the initial load times would get really high first, which would probably force me to reach for a DB approach.

But until then, I am far better off in terms of overall performance (everything is read from memory) and the amount of code I have to maintain (no SQL code and not to mention tests for the DB). I would only need a Parser that converts my text files to DTOs and a few Mapper functions that convert those DTOs to the classes I am using in the core of the app.

It seems like I probably would never even need the database, but I think that's a stretch and I may be missing something and the answer may not be dependent just on the sheer size of the dataset. What do you think is the turning point when a Json-text-file storage is not enough anymore?

Answer 1

There is no point.

Wow, talk about a "zen" answer. However, what I mean is that instead of a point, in reality you rather have a spectrum.

Hard criteria

Don't get me wrong, a hard criterium like your "the dataset cannot fit into memory", you were correct in it being "necessary" at that point. The same goes for other "hard" reasons, like several instances of your application wanting to access the data at the same time. (You can spend an enormous effort on making that work with a JSON file, but you'll pretty much end up writing your own SQ language, a.k.a. reinventing the wheel).

That's probably a little obvious and you wouldn't be asking the question here if it was about this. If it's really necessary, you don't have a choice either way, so it wouldn't make sense to ask for help in deciding. In the situation described by Martin, he didn't avoid SQL simply because it was not "necessary" - because neither was a simple configuration file. He avoided it because SQL is the more complex of the two and there was no (technical) reason for the added complexity.

Soft criteria

From here on out, I'll assume a situation where you aren't forced to use one over the other. From an engineering standpoint, there's pretty much only one soft reason to chose one over the other: Which one is more practical?

That's is a pretty general statement, but you can't go into too much detail here without the full context anyway. I assume that you as the developer will have an idea which one will e.g. be easier to use for you, or which one will have a bad performance for your specific needs.

The main point is that it's not about being necessary, but about which is more efficient in your situation.

Conclusion

Don't avoid an SQL DB until it's absolutely necessary. Use an SQL DB when it will make your life simpler.

What Martin tried to say was pretty much a different version of that statement; don't use an SQL DB (or any technology, really) just because everybody uses it and it's all the rage, when it actually makes things more difficult.

Additional info: Business reality

Even if we assume a clear metric for one being more practical than the other, that's not the end of it. In reality, your app's needs becoming more and more complex over time, until you switch over to SQL, would look something like this:

SQL < JSON
Starting out, JSON is simply the easier solution.

SQL > existing JSON
At some point SQL would be more practical/easier to use.

SQL - switching effort > existing JSON
At some point later, SQL being more practical starts to outweigh the effort it would take to switch.

SQL - switching effort - business needs > existing JSON
At some point even later, using JSON becomes so impractical that your company (or whoever calls the shots) thinks it's worthwhile to allocate the resources to actually make the switch.

Only at this point is the switch actually going to happen, but this is also heavily dependent on communication with higher-ups. It might have been better from a technical/engineering viewpoint and better for the company since 3 years, but just never happened because management does not deem it necessary.