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

5 points by TekMol 18 hours ago

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.
pavel_lishin 18 hours ago

> 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 18 hours ago

    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.

karimfromjordan 18 hours ago

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 18 hours ago

    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 18 hours ago

      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 18 hours ago

        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 15 hours ago

    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.