81–90 of 99 posts
Perhaps the debate might be over how much trouble is too much. AFAICT automated "find and replace" can cure just about every "incompatibility".
Fact of the matter is that SQLite shouldn’t be used in a non embedded context. There’s a reason people use Postgres. There are some neat things you can do with it like using HTTP range queries to query it directly from object store or Litestream, but my point stands.
I wish SQLite would officially fork itself and: - So make breaking changes as needed. (it was first released in 2000 and has fantastic backwards compatibility, but hardware & OS have radically changes over the last 24 years - as well as use cases). - put more focus on client/server use cases - make things more 'strict' (types, checks, etc) Note: I say this with tremendous love for SQLite. There's just so many attempt…
I guess I don't really understand the point of expecting distinct solutions to all converge toward the same general case. All of the criteria on your list are already fulfilled by Postgres, MySQL, etc. -- why dilute the optimality of SQLite for its specialized use cases just to target other use cases that are already served superbly with other tools? This is like arguing how great bicycles would be if they had four w…
They are not, they introduce management of another node / VM / pod which I'll boldly say that likely at least 80% of all projects everywhere do not need.
I'd kick a puppy if that means we can get in-process / embedded PostgreSQL.
> why dilute the optimality of SQLite
What does that even mean? Such a strange wording, as if it's a competition or a fight.
> This is like arguing how great bicycles would be if they had four wheels, enclosed cabins, and gasoline engines.
No, that's akin to arguing that a bicycle would benefit from crash protection if it came with just 2-3kg extra weight.
I find comparing SQLite with Postgres moot to begin with. I use SQLite when I don't want to run a database server out-of-band, or when I want to need to copy a single file to copy an entire database. For that, it is unparallelled, easily the best in the world, by far. I don't understand the comparison here at all.
1. Strictness by default and with no escape hatches;
2. Proper data types like all dates / times / datetimes / JSON etc.
3. In-process / embedded database. Almost all projects I worked on over the course of a 22 years of career did not need a separate node / VM / pod for a database.
PostgreSQL / MySQL and SQLite are not some ideal polar opposites. There is potential for a lot of cross-pollination of features and the ability to achieve the perfect DB.
There. Now you understand the comparison.
Earlier quoted context omitted.
Do you think reading the manual is supposed to make those problems disappear or make them less frustrating for some reason? These kind of personal attacks just because their opinion differs from yours is low effort, specially given that they clearly mentioned "official recommendation" in multiple places implying they did go through the manual.
to be fair, it sounds like the author only referred to the manual after making uninformed decisions.
And if you decide you want to "win" here and thus stubbornly reply "yes" then I'd say "you are the vanishing minority".
Approaching a piece of tech -- that's yet unknown to you -- with expectations is natural. Sometimes with tragic results but still completely natural for us the humans.
Earlier quoted context omitted.
to be fair, it sounds like the author only referred to the manual after making uninformed decisions.
Do you read, in an exhaustive manner, the manual of each thing you ever buy? Or each programming language and library you ever used? And if you decide you want to "win" here and thus stubbornly reply "yes" then I'd say "you are the vanishing minority". Approaching a piece of tech -- that's yet unknown to you -- with expectations is natural. Sometimes with tragic results but still completely natural for us the humans.
My approach is to firstly skim the index and look at anything interesting, secondly just use the docs as a reference while I learn the technology, and thirdly come back and read them cover-to-cover (skimming over the boring/irrelevant bits).
> On off days, I sometimes wonder if I’m bought into some narratives too strongly. Like, is Postgres really the world’s best database? Experiences like this certainly cement my conviction. Yes, it is. I find these 'different therefore wrong' takes to be immature. Yes, SQLite is idiosyncratic in comparison to other relational database engines. There are reasons behind those idiosyncrasies: SQLite is designed for other…
The wacky approach to column types came from SQLite's origins of being closely integrated with Tcl. Knowing that doesn't somehow mean it was a good default worth carrying on for decades.
Earlier quoted context omitted.
Firstly, while it's the default, it's not mandatory. As the author discovered you can use STRICT to make the columns typed, and types to be enforced. The reason it remains unaltered by default is because one of the goals (and accomplishments) of SQLite is that the on-disk-data-file is completely backwards compatible, and cross-platform. This is a very important feature in some situations, and not lightly tossed aside…
STRICT only works for simple types though as the article noted, so you can't do CREATE TABLE mytable ( id INTEGER PRIMARY KEY, created DATETIME, mything JSON ) STRICT;
Earlier quoted context omitted.
Agreed. Most of that can be achieved with SQL (how hard is it to add STRICT?) or the code you use to interact with SQLite. I don't see how it's worth sacrificing backwards compatibility to achieve those things, which are mostly non-issues in practice.
I don't see what is so appealing about backwards compatibility. The database is an implementation detail and its specifics should not block anything. Seeing several comments like yours is puzzling to me because I find myself unable to understand what are the devs preaching for it gaining from SQLite's extremely conservative backwards compatibility policy. For example, PostgreSQL has `pg_upgrade`. You run that after y…
If you have a "closed" system (such that "you" control the database, and all access to it) then upgrades are "easy" to do. You just upgrade the server and all clients.
If your system is more diverse then it's harder because in that case server and clients gave to be upgraded together. If I'm using multiple different programs (Accounting, Payroll, Access control etc) possibly from multiple vendors, then coordinating everything to happen at the same time can be impossible. If the server is not backwards compatible with old clients then you can get stuck.
In the case of SQLite for example lots of systems rely on the stability of the file format. Breaking that would not be welcome.