91–99 of 99 posts
Earlier quoted context omitted.
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;
That's the point of STRICT isn't it? 2 of the 3 types in your SQL statement aren't valid sqlite data types.
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.
You can reflect on your early experience with SQLite while simultaneously reflecting on why Postgres is a great database. If it wasn't that way, we'd all be running SQLite for our web services, but we're not. All technologies, and especially databases, are best applied with thoughtful consideration of the context. I didn't get the sense the author was suggesting their SQLite use case would actually be better served b…
Noting that every interactive site run under the Hwaci[^1] umbrella does, e.g. sqlite's own forum and source control system.
[1]: The company behind sqlite.
I think what the author misses is that SQLite's choices make a lot of sense in a world where working with the DB is super easy and you can move a lot of the complexity to the code around it. With traditional databases it used to be the case that you manage everything in the DB, because it's expensive to call it, and because you don't always know who's going to call it, but that's clearly not a typical scenario for SQ…
One concrete example of that is sqlite's own source control system, the Fossil SCM. Within Fossil, sqlite does _lots_ of the heavy lifting, replacing tens of thousands of lines of C code[^1]. Richard Hipp (of sqlite fame) recently mused that sqlite takes on at least the following distinct database roles in that project:
- Document database (how SCM records are natively stored[^2]).
- Graph database (queries which extract the lineages of projects' artifacts from directed acyclic graphs[^3]).
- Key-value store for config data of arbitrary types (all in the same table).
The first two can be done with any SQL db, but the latter requires sqlite's particular flexibility. Never once (literally never once) in the development of fossil has that flexibility caused us (==its many contributors) any grief.
[^1]: as a fossil contributor since 2008, i can say with complete confidence that that is no exaggeration.
[^2]: https://fossil-scm.org/home/doc/trunk/www/fossil-is-not-rela...>
[^3]: https://core.tcl-lang.org/tcl/timeline?c=2024-06-30> is a good example
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…
SQLite's idiosyncrasies make more sense when you realize: 1. It started as a way for the author to access databases from TCL, in which everything is a string. Sounds kind of mad now, but that was the kind of thing you did back in the 90's. 2. SQLite is fanatically backwards compatible. That means that once you get a system that works, it will continue to work through all newer versions of SQLite. But that also means…
The code is Public Domain, though. Anyone should feel free to scratch their itch.
Earlier quoted context omitted.
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…
> All of the criteria on your list are already fulfilled by Postgres, MySQL, etc. 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 competi…
Can you explain your thinking re a solution being both in-process/embedded and focusing on client/server use cases? On the surface, these seem contradictory to me.
> What does that even mean? Such a strange wording, as if it's a competition or a fight.
I don't know about a "competition or a fight" but forking a project to target contradictory use cases definitely involves trade-offs.
Earlier quoted context omitted.
> All of the criteria on your list are already fulfilled by Postgres, MySQL, etc. 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 competi…
> I'd kick a puppy if that means we can get in-process / embedded PostgreSQL. Can you explain your thinking re a solution being both in-process/embedded and focusing on client/server use cases? On the surface, these seem contradictory to me. > What does that even mean? Such a strange wording, as if it's a competition or a fight. I don't know about a "competition or a fight" but forking a project to target contradicto…
What seems contradictory, not sure I understand?
In my consulting and contracting practice I have only ever had 3 projects that actually needed a big dedicated database. Everything else would have done just fine with an in-OS-process model like SQLite. But SQLite is too lax with data typing and I am not keen on 40% of the invoice for my customers to be "+300% extra data validation code because SQLite devs used and loved TCL". Sorry for the snark, but TCL influencing SQLite is a historical reality, if my memory hasn't betrayed me that is.
I'd love it if we had the same DB engine have an embedded and client-server variants. You start off with the embedded and if the project grows then you simply modify its config and don't have to change one line of code in your project (though obviously your platform team has to then provision it but that's a given).
Today this is sadly a fantasy and does not exist. I want it to exist.
And why PostgreSQL? Well, I like data strictness, and PG has a lot of desirable features like DDL transactions and enum types.
Earlier quoted context omitted.
> I'd kick a puppy if that means we can get in-process / embedded PostgreSQL. Can you explain your thinking re a solution being both in-process/embedded and focusing on client/server use cases? On the surface, these seem contradictory to me. > What does that even mean? Such a strange wording, as if it's a competition or a fight. I don't know about a "competition or a fight" but forking a project to target contradicto…
> Can you explain your thinking re a solution being both in-process/embedded and focusing on client/server use cases? On the surface, these seem contradictory to me. What seems contradictory, not sure I understand? In my consulting and contracting practice I have only ever had 3 projects that actually needed a big dedicated database. Everything else would have done just fine with an in-OS-process model like SQLite. B…
In-process embedding implies making the DB engine part of the program itself, within a single runtime instance, in the same way as you'd import any other library. Client/server architecture is a situation in which one program is communicating with another one, running somewhere else, over some sort of messaging channel. On the surface, these seem to be mutually exclusive usage models.
> I'd love it if we had the same DB engine have an embedded and client-server variants.
It sounds like you want something that uses similar syntax and defaults as Postgre, but is used in an embedded fashion, a la SQLite. This is a reasonable idea, but it seems like you want to focus on embedded use cases, not client/server, and this sounds like something that would be best implemented as a third solution entirely, not a fork of either SQLite or Postgres.
Earlier quoted context omitted.
> Can you explain your thinking re a solution being both in-process/embedded and focusing on client/server use cases? On the surface, these seem contradictory to me. What seems contradictory, not sure I understand? In my consulting and contracting practice I have only ever had 3 projects that actually needed a big dedicated database. Everything else would have done just fine with an in-OS-process model like SQLite. B…
> What seems contradictory, not sure I understand? In-process embedding implies making the DB engine part of the program itself, within a single runtime instance, in the same way as you'd import any other library. Client/server architecture is a situation in which one program is communicating with another one, running somewhere else, over some sort of messaging channel. On the surface, these seem to be mutually exclu…
Not sure how I was unclear (sorry if I was), my take was basically "I want PostgreSQL[-like] engine that can work in embedded and client-server mode depending on project" really. I want more choice than we have right now, that is my wish.
> and this sounds like something that would be best implemented as a third solution entirely, not a fork of either SQLite or Postgres
I don't see why. Technically there are hurdles, sure, but there always are anyway -- I believe in the case of both SQLite and PostgreSQL it's either lack of resources or lack of motivation to go outside their niche. Whatever the case I am not judging them, it's their project and I am just a rando who wants to work less on their storage / validation layer.
But yeah, I don't see why must we get a 3rd player necessarily. You might still turn out to be correct, mind you, I am just saying that it's not necessarily the case that this DB engine (that will have both embedded and client-server modes) must be a separate project.
I love SQLite but I always end up having to write a lot of validation and at one point you do ask yourself whether your energy should not go somewhere else.
Time will tell, I suppose.