I wish there was some sort of an Access-like form/app builder tool for quickly building GUIs around SQLite databases. That's the one thing I miss. I'd love to reach for SQLite instead of Excel or something.
Finally, a software fad that I actually want to support! About time I get one!<p>SQLite is more than good enough for an awful lot of things. Most of the time that I do see software using MariaDB etc, I think to myself "they didn't need a server model for this, SQLite would have been fine".
I started to use Postgres on AWS for a personal project, after fighting with several non-docker VM's - I don't want to use docker. But I decided on SQLite + Litestream, hopefully for free on AWS. The simplicity of deployment is great.<p>SQLite to my surprise has much of the same SQL functions as Oracle and Postgres. I wanted to use window functions, which are supported.<p>Sqlite came to my attention when I read a Tailscale blog article stating they will be using it.
It certainly has some good use cases and is not a toy, but there are some critical SQL features (like say: `last_at < NOW() - INTERVAL '10 minutes'`) that are not easily compatible with SQLite and are with postgres, that would require a lot of code rewrite in case you need to scale upwards. Testing against multiple databases is hard, so migrating to postgres wouldn't be trivial either. Hosting small postgres databases on things like GCP are ~$8 a month... why not just save yourself from future pain?
Today I opened up the Windows Resource Monitor to see why my elderly neighbor's AOL Desktop Gold app was loading so slowly. The problem turned out to be network related, but I was intrigued to see in it's disk activity many reads and writes to a .sqlite file.<p>I wonder how long AOL's desktop app has been using SQLite - since the early 2000s maybe? If anyone has one of those original AOL disks lying around, it may be interesting to see if the installer unpacks a SQLite DB of some kind.
Discussed at the time:<p><i>SQLite is not a toy database</i> - <a href="https://news.ycombinator.com/item?id=26580614" rel="nofollow">https://news.ycombinator.com/item?id=26580614</a> - March 2021 (354 comments)
Shameless plug: <a href="https://github.com/Aperocky/sqlitedao" rel="nofollow">https://github.com/Aperocky/sqlitedao</a><p>For an out of the box python dao/orm with sqlite and deal with no SQL statement/strings.
Sqlite has really 1 big problem in my mind.<p>1 thread at a time. If you try to have 2 threads throwing stuff at it.<p>_sqlite.OperationalError: database is locked<p>This is what makes it a toy database. Mind you, sqlite will handle most projects, but those are also toy projects.
<a href="https://table.dog" rel="nofollow">https://table.dog</a> is a CLI that downloads your Stripe account to a SQLite db.<p>Would appreciate it if you could test it out if you are interested.
I like SQLite (use it for android apps), but there’s no reason to use it for apps over a network. SQLite is meant to be embedded (also works great with desktop apps and local-only web apps).<p>In fact, I’d argue there’s no <i>networked</i> use case in which SQLite is cheaper, easier to deploy, maintain or run than Postgres.<p>All that being said, absurdsql is great. There should be some optimizations in mirroring network state to IndexedDB that could be queried via SQl(ite).<p>The SQLite documentation literally says use Postgres and not it for networked use cases (unless you use WAL or rollback, in which case why not just use Postgres?)<p><a href="https://www.sqlite.org/useovernet.html" rel="nofollow">https://www.sqlite.org/useovernet.html</a><p>—-<p>As an aside, I think we need a new storage primitive. In the 2000s desktop apps were rampant and SQLite was more or less the standard.<p>We then moved over to networked apps where RDBMS had its day. There was a moment where nosql was booming for the scaling but it turned out people like relations.<p>We need an open source, distributed and relational store that’s easy to maintain build this decade.
Came over this article as I was looking for interesting resources in the SQLite ecosystem. I'm building mvsqlite (<a href="https://github.com/losfair/mvsqlite" rel="nofollow">https://github.com/losfair/mvsqlite</a>), as an attempt to turn SQLite into a proper distributed (not just replicated) database. Check it out if you are looking for this kind of stuff!