> Support RETURNING clause on DELETE, INSERT, and UPDATE statements.
I really enjoyed this one due to the elegance. We converted a bunch of normal methods into expressions because we could write them like:
public long CreateCustomerRecord(string name, string email) =>
sql.ExecuteScalar<long>(@"INSERT INTO Customers (...)
VALUES (...)
RETURNING Id", new
{
//Param bindings
});
“there is a common misconception that SQLite is a stagnant or outdated technology” this seems like a strawman argument. there seem few software projects with as consistent high levels of respect by everyone from seasoned neckbeard to serverless hipsters and everyone in between. even in the nosql peak when these arguments were maybe heard for mysql or postgres, sqlite was mostly spared as it was used as storage engine for more than one of the new kids.
It took me 13 years to move from "wow SQLite! yeah totally underrated!" to "Yet another article where the first thing is SQLite underrated/unappreciated/unknown...& its underpinned every document format for years and years and years..."
The clustered primary key (NO ROWID) setup that came in with SQLite version 3.8.2 (2013-12-06) makes it very fast indeed when used for a large persistent key-value store.
And later improvements have just continue to accrue.
This from a guy who just implemented a persistent object cache with it, and was blown away by how well it works. And my requirement was all SQLite versions 3.7 and later, so there's conditional code. (ROWID or not, UPSERT or not).
Not to mention there are probably ten or more of these databases in your mobile phone. We haven't heard, at least I haven't, about any monstrous day-1 vulnerabilities in this code.
A really good design choice, SQLite is, if your application can live with its local file system requirement.
I'm working on comparisons between redis and SQLite for object caching. So far things look favorable.
I did this project for WordPress, because object caching helps performance (and therefore carbon footprint) a lot and cheezy hosting services don't offer redis. But most of them have SQLite.
Oddly enough, SQLite is really slow on the one BSD hosting service I've tried.
I still find it funny that if I write something that's supposed to support MySQL/PostgreSQL/SQLite it's almost always MySQL that needs tweaking or outright doesn't support something, while between PostgreSQL and SQLite it's mostly same SQL
In fairness, Postgres seems to be the most "SQL" of the variants out there... at least in terms of my understanding of the common SQL dialect(s). So probably not a bad first choice.
By comparison, every single time I've touched MySQL/MariaDB I find at least one new thing that irks me to no end. UTF8 vs UTFMB4 or whatever it is for starters. Indexes on Binary data fields not being case insensitive (binary) by default, even if the default index for text is different is another. Not sure if it's still an issue, but that you can use ANSI quotes for everything but foreign key definitions was another I seem to remember. The fact that magic quotes could escape out of quoted strings another still.
And that's just off the top of my head, and doesn't get into some of the default data handling that's just bad. SQLite has some similar issues there, but imo it's far more forgivable given SQLite's footprint and embedded nature. I also really like(d) Firebird when I'd used it in the past, but it's not nearly as popular.
Postgres also appears to aim for very close compatibility with oracle; for a long time that was what you migrated your oracle workloads to when they got too expensive.
> You used the word "immense" which I like - it is an apt description of the
knowledge and effort needed to add windowing functions to SQLite (and
probably any other database engine for that matter).
It allows for recursion in the query (fetching a tree of a category structure for instance where it's just a id,parent_id,priority tuple)
Handling that efficiently without conditioning the data first using something like nested set or materialized paths is going to be a challenge when the depth is unknown.
One of the best baas out there is Pocketbase built on top of sqlite. I tried all the other popular ones out there like Supabase, NHost, appwrite but Pocketbase just blows them out of the water.
I worked on a system a few years ago that has timeboxed data... In other words, the bulk of the data was for a singular event at a time. I seriously pushed at the time to just use SQLite then the backup/recovery/archiving of data would be much easier. Instead, what we got was a rube-Goldberg contraption of MS-SQL with functions and stored procedures. Supporting multiple deployed versions became that much harder over time. The clients were different govt jurisdictions, so actually being able to deploy was complicated to say the least, and often required a month of planning.
In the end, I still think that SQLite would have been a much better approach for this particular use case.
> can use Sqlite from java without using any JNI library
Ah is that a mac thing? On linux, you just set up gradle with a dependency for the jdbc driver, and you get sqlite available to use without needing to install anything on the OS. It's pretty magic.
If you're a SQLite fan who does stream processing, we (Estuary) recently introduced a capability to write transformations as event-driven SQLite [0].
Basically you get a provisioned SQLite DB to which you apply whatever migrations you wish, and write SQL lambdas that are run with each input document, where your lambdas update your tables an/or publish outputs via SELECT.
Of of curiosity, how configurable are sqlite builds?
Can one disable json support, for example? I'm not sure what other "categories" of features three might be. Certainly there's a lot of builtin functions; how configurable is sqlite in picking buultins to omit?
A real exciting improvement would be support for NFS, instead of hiding behind "some server implementations don't implement locking properly" in 2023...
If you need your SQLite database to be available over a network I think you'd do a lot better layering a dedicated network protocol on top of it as opposed to trying to get something like NFS working, which is evidently a poor platform for files that need transactional updates made to them by multiple users at once.
https://access.redhat.com/solutions/120733 explains it. The critical bit is the root cause at the end. Which is that NFS sees access to any part of the file as access to all of the file. So any access locks it for anyone. Therefore shared access to the database will cause random hangs due to client behavior.
If you turn off locking, then there is no way to avoid data corruption.
And this is with NFS working correctly. Which is not a safe assumption given that widely used platforms like OS X implement it wrong.
In short, there is a reason that we've joked since the last millennium that NFS stands for "No File System". And the joke is still relevant today.
Note in particular that multiple processes can read at a time, and only slowly escalate into a write lock which is held as short a time as you can before going back to the normal state. While NFS assumes that if you read, you may write, and may not take care to make sure you have the most recent version WHEN you write. (These are all important assumptions to make for random programs written by random programmers. Few programmers can be assumed to take the care that databases do around getting locking logic correct.)
“To accelerate searching the WAL, SQLite creates a WAL index in shared memory. This improves the performance of read transactions, but the use of shared memory requires that all readers must be on the same machine [and OS instance]. Thus, WAL mode does not work on a network filesystem.”
“It is not possible to change the page size after entering WAL mode.”
“In addition, WAL mode comes with the added complexity of checkpoint operations and additional files to store the WAL and the WAL index.”
“SQLite does not guarantee ACID consistency with ATTACH DATABASE in WAL mode. “Transactions involving multiple attached databases are atomic, assuming that the main database is not ":memory:" and the journal_mode is not WAL. If the main database is ":memory:" or if the journal_mode is WAL, then transactions continue to be atomic within each individual database file. But if the host computer crashes in the middle of a COMMIT where two or more database files are updated, some of those files might get the changes where others might not.”
It does work... Sort of. From what I've read, the NFSv4 server properly implements locking (and I think most of those bits are in the Linux kernel now anyway), but sqlite won't support it anyway. And I am able to run sqlite on NFSv4 with minimal problems. Every once in a while I do get a hiccup, but it's not clear why.
It does work but it also rarely results in hanging locks, that's with NFSv3 with NLM. Actual data corruption only happened once or twice.
So it's not a super-reliable thing, but when you can't have a real database server or you can only make a network share accessible to the right groups, say, due e.g. organizational dysfunction, then it works, most of the time.
I've had the same experience. I have a NAS VM with nfs4 on Debian, but my services are running on other VMs (since they're in a DMZ vlan). A lot of selfhosted stuff runs on sqlite since its just easier. So there is only one user per db, and 95% of the time I have no issues. Every once in a while I get some sqlite i/o error, and most of the time it's fine but every once in a while I'll have to restart a container.
I've had applications (not on NFS, but with multiple processes accessing the same SQLite database file at once) which throw occasional I/O errors in default journal mode but didn't throw errors at all once I switched into WAL mode.
I really enjoyed this one due to the elegance. We converted a bunch of normal methods into expressions because we could write them like: