HN Explorer

Ask HN: Should I switch away from SQLite if I only use JSON fields?

5 points by TekMol ↗ HN
I noticed that in 2024 I completely stopped using database tables with more than one column. Everything just goes into one single JSON column, which I always call "data".

So to query all entries from the table "cars", I do:

    SELECT * FROM cars
    WHERE data ->> '$.color' = 'blue';
That seems a bit redundant, considering all my tables have just the data column. So if the DB knew this, the query could be just

    SELECT * FROM cars
    WHERE color = 'blue';
Should I start looking for a database that works like this? Are there any databases like this out there? It would have to be a database that stores the data in a single file like SQLite. Because I would not want to give up that convenience.

9 comments

[ 4.6 ms ] story [ 36.3 ms ] thread
karimfromjordan
SQLite doesn't support indexing JSON (yet) unless you use generated columns. So performance-wise SQLite would probably not be the best option if that's all you do.
TekMol
So far, it's fast enough for me. If I ever need more performance, I guess I'll figure it out.

Couldn't Indexes on Expressions be used to index JSON fields?

https://www.sqlite.org/expridx.html

KMnO4
The “right” tool for the job depends on a number of factors. Performance is one of them, but ease of implementation is just as important depending on the scale.

Since you already have it working sufficiently using SQLite, why would you switch?

TekMol
As I said, to get rid of the superflous ".data->>" everywhere. Take this query for example:

    SELECT 
      cities.data ->> '$.name' AS city_name,
      countries.data ->> '$.name' AS country_name
    FROM 
      cities
    JOIN 
      countries ON cities.data -> '$.country_id' = countries.data -> '$.id';
It could be much smaller if the DB knew that all my tables are of the "single data column with JSON" form.
SQLite
SQLite supports indexes on expressions. So if you have an expression that accesses some subcomponent of JSON that you want to index, just create an index on that expression.
pavel_lishin
> I noticed that in 2024 I completely stopped using database tables with more than one column. Everything just goes into one single JSON column, which I always call "data".

Inquiring minds want to know: why?

TekMol
I used tables with many columns for over a decade. Also a lot of EAV tables. But over time I more and more switched to the JSON model, because it is more intuitive and makes many tasks much easier.

Like adding "max_speed=100" to just one car. You can do that easily in an EAV table, but then EAV tables are annoying to query. JSON data covers all my use cases elegantly.

It's also nice that you can just convert the data to a file (for example in a directory "cars" where each file is one car like 1.json 2.json 3.json ...) and then edit them with a text editor. Often I also start the other way round. I have my data in JSON files and then move it into SQLite at some point.

[deleted]
(comment deleted)