Exciting SQLite Improvements Since 2020
blog.airsequel.com
blog.airsequel.com
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
});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.
Definitely reduces the need for another dependency if that’s your thing and it fits your needs
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.
https://changelog.com/podcast/201
> JEROD SANTO
> ...Postgres, which you say you use as kind of a reference implementation of at least the SQL stuff.
> RICHARD HIPP
> Yes.
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.
Hipp:
> 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).
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.
Absolutely, the last improvements of sqlite are just incredible.
In the end, I still think that SQLite would have been a much better approach for this particular use case.
Is there support for "compiling" WASM to java so we 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.
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.
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?
Yeah, it's pretty configurable, including the JSON support.
Being able to include/exclude things can be done at compile time:
https://www.sqlite.org/compile.html ← lists the options for including/excluding stuff
And there's a function for loading 3rd party extensions at run-time too, which itself can be turned off. :)
There's a fair amount of 3rd party extensions too.
eg: https://github.com/nalgeon/sqlean
GIS stuff, encryption support (several varieties), Excel/ODS support, and tonnes of other things
https://github.com/xerial/sqlite-jdbc
Mentioning that because from (very) rough memory, Excel can work with JDBC too.
So if the ODBC approach doesn't work for someone, there's potentially another thing they can try. :)
The binary has remained tiny, with most OS's under 2MiB, but that won't really indicate memory usage.
Do you have any articles to show its resource usage over time?
If you absolutely need high concurrency, then by their own admission pick something else. [1]
But for most things you can survive queuing up writes.
The announcement blog post:
https://sqlite.org/forum/forumpost/d9b3605d7ff40cf4
HN thread about it from a few months ago:
https://www.sqlite.org/src/doc/trunk/ext/userauth/user-auth....
The widely used Go SQLite library by mattn says it supports it, if that's useful:
What makes this the case?
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.
How do other file systems do it differently that allows for concurrent access to the file?
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.)
Below are well-known limitations of WAL mode.
https://www.vldb.org/pvldb/vol15/p3535-gaffney.pdf
“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.”
https://www.sqlite.org/lang_attach.html
“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.”
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 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.