News for hackers. Stuff that matters.
Re:Collection of insane and fun facts about SQLite by TekMol on Monday December 30, 2024 @02:33PM (#42552614)
My latest favorite fun fact about SQLite is that it is not only my favorite SQL database, but also my favorite NoSql database.

Since 2024, all my new database tables have only one column. Everything just goes into one single JSON column, which I always call "data".

    SELECT 
        cities.data->>'name' city_name,
        countries.data->>'name' country_name
    FROM cities
    JOIN countries
         ON countries.data->'id' = cities.data->'country_id'
I looked around a bit if some NoSql database would make this easier. But it turned out no, even now that I only use Json everywhere, SQLite is still the best tool.
Re:Collection of insane and fun facts about SQLite by RadiozRadioz on Monday December 30, 2024 @03:30PM (#42553169)
> Since 2024

So you have only just started doing this

Re:Collection of insane and fun facts about SQLite by breakingcups on Monday December 30, 2024 @05:29PM (#42554251)
Well, they did say "latest". 2024 has also been going for 364 days now.
Re:Collection of insane and fun facts about SQLite by rafram on Monday December 30, 2024 @03:02PM (#42552854)
But why?
Re:Collection of insane and fun facts about SQLite by TekMol on Monday December 30, 2024 @03:31PM (#42553182)
Multiple reasons. Let me start with one: Flexibility.

I can add a new attribute the very moment I create a new row.

When I want to add "not_available_before: 2026" to a car, I can do that right away. No need to alter the table and add a new column.

Re:Collection of insane and fun facts about SQLite by rafram on Monday December 30, 2024 @11:40PM (#42556454)
That just seems like it encourages bad practices. There’s nothing but upside to tracking your schema changes.
Re:Collection of insane and fun facts about SQLite by MrLeap on Tuesday December 31, 2024 @02:46AM (#42557201)
Cognitive overhead is a cost!
Re:Collection of insane and fun facts about SQLite by TekMol on Tuesday December 31, 2024 @03:24AM (#42557343)
What do you mean by "tracking"? So far, we have not talked about tracking anything. Only about how to add a new field.

I just named a downside of the columns approach: It slows things down.

Being able to add add fields faster leads to faster experimentation and development.

Re:Collection of insane and fun facts about SQLite by Althorion on Tuesday December 31, 2024 @03:33AM (#42557377)
I mean, sometime along the way you’re going to have something that consumes the data (otherwise, why bother keeping it in the first place), and that something will have certain expectations about the way things are structured.

It will probably become a good idea then to have some clue as to what structure used to be at one point or the other, and for that you’d want to keep track as to what got added/removed and when.

If it’s a very early stage in the development, and you don’t expect any of the current data to survive to the final version, I guess that’s fine. But when you have an actual running product that has to keep running, dealing with the multiple versions of the data scheme is a pain, and dealing with the multiple untracked versions of the data scheme is a pain squared.

Re:Collection of insane and fun facts about SQLite by TekMol on Tuesday December 31, 2024 @03:59AM (#42557474)
How does "have some clue as to what structure used to be at one point or the other" relate to the topic of columns vs json fields?
Re:Collection of insane and fun facts about SQLite by Althorion on Tuesday December 31, 2024 @04:12AM (#42557525)
It is what they meant by ‘tracking’, which is what you’ve asked about.
Re:Collection of insane and fun facts about SQLite by TekMol on Tuesday December 31, 2024 @04:52AM (#42557662)
I have the feeling you both are confusing your development workflow around database structure with the structure itself.

You seem to think that using columns somehow results in some type of automatic "tracking" (versioning?) of something.

Re:Collection of insane and fun facts about SQLite by Althorion on Tuesday December 31, 2024 @06:04AM (#42557962)
Not quite. We are saying that adding `not_available_before: 2026`, or similar, ‘right away’ is usually not a good thing; therefore anything that helps with doing that is not something we desire.

It should be a multistep process. Yes, it will waste time now, but it will save it in a long run.

Re:Collection of insane and fun facts about SQLite by TekMol on Wednesday January 01, 2025 @12:38PM (#42567557)
How will it save time in the long run?
Re:Collection of insane and fun facts about SQLite by jitl on Tuesday December 31, 2024 @10:21AM (#42559192)
I use a lot of JSON columns w/ SQLite and find a programming language type is a great way to specify schema for such a column without having to write any migrations. No one has written a sternly worded comment at me on the internet yet for adding or removing fields from my struct types without writing a separate migration file.

Lots of use-cases for SQLite are not like Big Iron SQL Database Of Record where every change must be tracked because it's a shared stateful single point of failure and there's hell to pay for mistakes or confusion.

Re:Collection of insane and fun facts about SQLite by advisedwang on Monday December 30, 2024 @03:24PM (#42553109)
To get the worst of both worlds
Re:Collection of insane and fun facts about SQLite by actuallyalys on Monday December 30, 2024 @08:02PM (#42555351)
I’m curious what the reasoning behind having no id or key column. Do you use indexed expressions to index JSON fields? Do you use Rowid for certain queries? Or do you not bother with indexes?

My understanding is that NoSQL databases still have indexes and it seems like using SQLite as you demonstrate could be worse in that regard.

Re:Collection of insane and fun facts about SQLite by TekMol on Tuesday December 31, 2024 @03:28AM (#42557357)

    reasoning behind having no id or key column
The entries in the data column already have an id field. What would be an upside of having another id in a column? It seems that only complicates things and has the potential for invalid states (different id in the id column than in the json field).

So far I have not used indexes because things are fast enough as they are. I would expect that when I need more speed, I can easily add indexes on expressions.

Why do you expect SQLite indexes on expressions to be worse than what NoSQL databases do for indexing?

Re:Collection of insane and fun facts about SQLite by actuallyalys on Tuesday December 31, 2024 @01:24PM (#42560650)
I wouldn’t suggest duplicating the id field, but moving from the data field.

> Why do you expect SQLite indexes on expressions to be worse than what NoSQL databases do for indexing?

I don’t have a concrete reason. It’s just that indexes on expressions are not intended as the main index for a SQLite database to my knowledge. Having thought about it more and read the page on expression indexes more thoroughly[0], I think it’s probably unlikely to be a noticeable downside.

The only remaining downside is that the item being indexed might be outright missing, but it sounds like that’s basically a feature in your case, as you’re opting for flexibility.

[0]: https://sqlite.org/expridx.html

Re:Collection of insane and fun facts about SQLite by TekMol on Wednesday January 01, 2025 @12:40PM (#42567571)
What do you mean by "the item being indexed might be outright missing"? What is "item" here?
Re:Collection of insane and fun facts about SQLite by rob137 on Tuesday December 31, 2024 @05:50AM (#42557899)
What are the most obvious downsides to this approach?