5 ms·
Exciting SQLite Improvements Since 2020
- marvel_boy 4y ago" Often overshadowed by newer and flashier database management systems, many overlook the continued innovation and evolution of SQLite." Absolutely, the last improvements of sqlite are just incredible.
- xhkkffbf 4y agoI like improvements, but I like speed and light demands for RAM or computation even more.
- rektide 4y agoOf 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?
- justinclift 4y ago> Can one disable json support ... 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 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 https://github.com/nalgeon/sqlean GIS stuff, encryption support (several varieties), Excel/ODS support, and tonnes of other things
- ilyt 4y agoExcel/ODS support ? I only see csv support in provided links?
- justinclift 4y agohttps://github.com/x2bool/xlite https://github.com/x2bool/xlite
- ilyt 4y agoThanks. Shame it's read only, althought I guess using odbc driver in excel to read sqlite is the way to go
- justinclift 4y agoThere's also a fairly popular JDBC driver for SQLite as well: https://github.com/xerial/sqlite-jdbc 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. :)
- bearjaws 4y agoI briefly looked to see how its footprint has increased, but couldn't find anything compelling. 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?
- silvestrov 4y ago> compiling SQLite to WASM Is there support for "compiling" WASM to java so we can use Sqlite from java without using any JNI library?
- petercooper 4y agoThere might be a route if using GraalVM which supports WASM: https://www.graalvm.org/latest/reference-manual/wasm/ https://www.graalvm.org/latest/reference-manual/wasm/
- smallerfish 4y ago> 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.
- folmar 4y agoIf you do not insist on the on-disk format then H2 is pretty much the Java world equivalent.
- rvcdbn 4y agoI hope we see more of this sort of use of WASM (creating universal libraries). Same argument applies for Go/cgo.
- bob1029 4y ago> 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 });
- deleted 4y ago[deleted]
- InfosecIcon 4y ago[dead]
- deleted 4y ago[deleted]
- asimjalis 4y agoNeat use of Haskell for scripting with Stack: https://docs.haskellstack.org https://docs.haskellstack.org
- kccqzy 4y agoOlder than 2020 but another indispensable feature SQLite added was window functions in 2018.
- btilly 4y agoThis is my favorite feature.
- mongol 4y agoIt is interesting to read about it proposed in 2014 https://www.mail-archive.com/sqlite-users@mailinglists.sqlite.org/msg85628.html https://www.mail-archive.com/sqlite-users@mailinglists.sqlit... 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).
- swyx 4y agowhy is windowing hard? isnt it kind of a rolling filter?
- thesz 4y agoExactly because it is so. You have to compute as little as possible and make it O(1) for a row added and deleted, not O(window_size).
- tehbeard 4y agoIt 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.
- kayson 4y agoA real exciting improvement would be support for NFS, instead of hiding behind "some server implementations don't implement locking properly" in 2023...
- wahern 4y agoIs there something SQLite would need to do to support an NFS-mounted filesystem that already supported proper locking (fcntl) and sync'ing (fsync)?
- formerly_proven 4y agoIt 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.
- kayson 4y agoI'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.
- simonw 4y agoAre you running it in WAL mode? 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. https://til.simonwillison.net/sqlite/enabling-wal-mode https://til.simonwillison.net/sqlite/enabling-wal-mode
- throwawayjs 4y agoI really love SQLite as a technology. My apps don’t require much data so it’s always been my main choice.
- jFriedensreich 4y ago“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.
- refulgentis 4y agoIt 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..."
- Freedom2 4y ago[dead]
- ilyt 4y agoI 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
- mulmboy 4y agoI believe SQLite generally uses Postgres as a reference https://changelog.com/podcast/201 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.
- danielheath 4y agoPostgres 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.
- tracker1 4y agoIn 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.
- OliverJones 4y agoThe 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.
- dashmeet 4y agoI wonder how this compares to using redis for key value caching purposes? Definitely reduces the need for another dependency if that’s your thing and it fits your needs
- meepmorp 4y agoWhy use redis at all if you don't need shared cache?
- OliverJones 4y agoI'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.
- jgraettinger1 4y agoIf 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. [0]: https://docs.estuary.dev/concepts/derivations/#sqlite https://docs.estuary.dev/concepts/derivations/#sqlite
- nickpeterson 4y agoHas SQLite ever attained multiuser functionality or is that still the main thing it lacks?
- tehbeard 4y agoMultiuser in what fashion? 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. 1. https://www.sqlite.org/whentouse.html https://www.sqlite.org/whentouse.html
- justinclift 4y agoThe SQLite developers seem to be experimenting with a solution to that: https://sqlite.org/hctree https://sqlite.org/hctree The announcement blog post: https://sqlite.org/forum/forumpost/d9b3605d7ff40cf4 https://sqlite.org/forum/forumpost/d9b3605d7ff40cf4 HN thread about it from a few months ago: https://news.ycombinator.com/item?id=34434025 https://news.ycombinator.com/item?id=34434025
- jsmeaton 4y agoI assume you’re asking about concurrent writes and yes, if you use WAL mode you can have that too. https://www.sqlite.org/wal.html https://www.sqlite.org/wal.html
- justinclift 4y agoSQLite does have an optional "user authentication" extension, though I've not personally tried it out: https://www.sqlite.org/src/doc/trunk/ext/userauth/user-auth.txt 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: https://github.com/mattn/go-sqlite3#user-authentication https://github.com/mattn/go-sqlite3#user-authentication
- lenkite 4y agoHow does SQLite compare to newer in-process db's like DuckDB ?
- FridgeSeal 4y agoSQLite is primarily a transactional, row-oriented (OLTP) database, whereas DuckDB is an analytical, column oriented (OLAP) database.
- ilrwbwrkhv 4y agoOne 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.
- tracker1 4y agoI 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.