SQLite is not a toy database (2021)
antonz.org
antonz.org
You just need to create your API definitions and you get APIs + Auto management UI for free, here's our most recent video showing how you can build a multi-user Bookings App in minutes [1].
With that easy of use comes its reputation of being a nightmare to manage, mostly because it's easy enough for people with no knowledge of the relational model to use.
If there was a similar solution utilising sqlite as the backend I'd definitely be using it.
[2] https://www.hytradboi.com/2022/ultorg-a-user-interface-for-r...
Mostly just analysis, no app building. But it is a great tool
https://wiki.documentfoundation.org/Documentation/HowTo/Base...
Although it could use some more Linux in that link.
My main rant is the lack of import/export standardization in this space.
I can't recall for certain, but I feel at least 75% sure there is an included demo project that shows how to wire up a simple CRUD app in, like, seven mouse clicks.
This totally fills the Access gap for me, assuming that you are looking for / happy with a web interface and a SaaS model which is paid per user (but free for first 5)
If your use case requires everything to be local and offline it won’t work, but if you can be online it’s a great replacement.
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".
Haha I'm waiting for those "SQLite in Rust Posts" :D
SQLite to my surprise has much of the same SQL functions as Oracle and Postgres. I wanted to use window functions, which are supported.
Sqlite came to my attention when I read a Tailscale blog article stating they will be using it.
$8/month of GCP gives you 0.6GB memory and a single shared vCPU (no SLA), and you still need an application server. So with the same 'micro' specs for an application server, you are paying $16/month. With SQLite you can easily deploy to services like Hetzner Cloud and get 4vCPU + 8GM RAM for ~$15/month and use Litestream for backup.
There are definitely trade offs with SQLite around low write performance (due to single writer limitation), and definitely not as feature filled as Postgres, but I think a lot of people underestimate the read performance and simplicity of a single host setup. And now with sidecar setups like Litestream, backup and disaster recovery of data is also a lot simpler and again, cheaper. With added attention, hopefully SQLite will continue to improve with extensions like Spatialite and others. Schema changes are still a point of friction as your data grows larger, however fairly stable heavy read scenarios, the simplicity of a SQLite setup is very nice to use.
Don't get me wrong, Postgres is a great default, but for a growing number of use cases, SQLite can be an extremely portable, cheap and efficient setup.
With containers you’d have the same experience. It’s also trivial to move your Postgres database to another machine.
For the right use cases, SQLite's embedded nature can provide simplicity other offerings can't. Is Postgres able fill more use cases? Absolutely, it is an amazing piece of software.
edit: It doesn't look like the use of PgBackRest composes as well as something like Litestream, eg it looks like PgBackRest has to be baked into the image running Postgres as opposed to a separate process monitoring changes to files like Litestream. Again, not huge issues, but definitely not as simple. Still very useful tech I'm glad you mentioned it, but a big draw card of SQLite is the simplicity of use.
For this particular one, you can replace interval with something like `last_at < DATETIME(CURRENT_TIMESTAMP, '-10 day')`
> Hosting small postgres databases on things like GCP are ~$8 a month... why not just save yourself from future pain?
I think this is a fair point, if you think you'll run into sqlite's limitations in the future. litestream and friends make sqlite an option for some non-embedded use cases.. though they're hardly a panacea.
SQLite definitely has its own problems, but using Postgres over SQLite and vice versa is still making a trade off. There is a massive spectrum between "tiny app" and "global scale SaaS", and with hardware improvements, SQLite can handle quite a lot. Would I pick SQLite as the default to use when aiming to build another "global scale SaaS"? No, I would more likely default to Postgres as well, but it definitely shines in a lot more scenarios than just in a local app.
So does SQLite.
I'm sure I could find reasons to use sqlite, but I'd have to find reasons NOT to use postgres if I ever thought I'd need its capabilities.
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.
"... the next tech giant to reach out was America Online... They needed a database on that [AOL] CD, and they had some ad hoc thing and they wanted to use SQLite on that. They had limited space, and so, “Hey, we need to put this on the CD.”"
SQLite is exactly what WASM is good at, now the only thing holding SQLite/WASM back is a low level filesystem/block store api. But this is coming soon! It will enable so much more than SQLite too, you will be able to use custom SQLite builds with extensions, and other databases (DuckDB, MongoDB/Realm and others)
Turned out, every browser used SQLite as an implementation backend which lead to the deprecation of WebSQL.
SQLite is not a toy database - https://news.ycombinator.com/item?id=26580614 - March 2021 (354 comments)
For an out of the box python dao/orm with sqlite and deal with no SQL statement/strings.
1 thread at a time. If you try to have 2 threads throwing stuff at it.
_sqlite.OperationalError: database is locked
This is what makes it a toy database. Mind you, sqlite will handle most projects, but those are also toy projects.
So whilst they're not technically concurrent, as far as a human using your app will be concerned it will feel concurrent because they were able to write at the same time as someone else, just at an imperceivable fraction of a second slower.
Would appreciate it if you could test it out if you are interested.
Now you have a network layer on top of sqlite.
In fact, I’d argue there’s no networked use case in which SQLite is cheaper, easier to deploy, maintain or run than Postgres.
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).
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?)
https://www.sqlite.org/useovernet.html
—-
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.
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.
We need an open source, distributed and relational store that’s easy to maintain build this decade.
> https://www.sqlite.org/useovernet.html
It's not what this page says. This page suggests PostgreSQL if accessing the SQLite file from the application would induce a network connection (because of a network filesystem). Not if the application is accessed over the network.
Using SQLite for a network service when the app runs on the same machine as the SQLite file is perfectly fine.
> (unless you use WAL or rollback, in which case why not just use Postgres?)
Again. WAL / rollback is how SQLite works by default. The page does not suggest using WAL, it suggests using WAL, "but do all reads and writes from processes on the same machine that stores the database file", is you use a networked FS. And yes, in this case, you might as well use PostgreSQL indeed.
> In fact, I’d argue there’s no networked use case in which SQLite is cheaper, easier to deploy, maintain or run than Postgres.
I disagree. I now use SQLite whenever I can for low traffic services, unless the app developers strongly advise something else.I'd say there's no reason to bother with a client/server database like PostgreSQL / MariaDB / MySQL if you can help it. It's one less database to administer, one less user and password to create and manage, and the SQLite file is saved using my regular backup routine. It works great and it's less work for me. You also get to use the database engine with probably the most extensive and comprehensive test suite in the world , by far[1]. It is probably more robust than anything else. SQLite is probably capable of handling high traffic too, especially if the majority of accesses are reads and there are only a few writes.
Of course, Postgres is a really good choice too and you can't really be wrong when choosing it. It's very robust and works very well too, and does not have loosy typing.
https://www.postgresql.org/docs/current/continuous-archiving... (section 26.3.3.3)
You might want to configure the database to put binary logs into a separate directory (or you might want to leave them there, depends on your requirements).
If preventing data loss is important for your app then this would be more hassle than using a networked db because now you have to replicate SQLite across a network to another machine.
How so? A networked database requires a persistent connection, while SQLite backups work fine with an intermittent connection. The best case (100% network uptime) and worst case (total network outage) are the same, but in the middle, SQLite-with-backups is more resilient to brief outages.
Of course, if your coder already knows and does this, your last resort will be moving the DB closer to the app (the last 20% of optimisation).
So, yes, in an n-tier database architecture, 80% of your optimization work might go into meticulously minimizing your query load with better-designed queries. The point is: it doesn't have to be that way.
The more important thing I think is, again, thinking separately about reads and writes.
The issue is most apparent in edge-optimized applications, almost all of which are overwhelmingly read-heavy. You've got a (say) 100ms budget for the whole request, and "edge-optimized" with traditional n-tier databases practically implies that your database isn't always in the same data center as your app, so you can eat that budget up real, real quick going back and forth with Postgres. Even inside a database, you can be looking at a couple milliseconds per database hit, which adds up as you do multiple queries.
SQLite with replication is a nice solution for that problem. Writes get handled roughly the way they would with Postgres, serialized into a single write master; reads get satisfied instantaneously from NVMe.
If not then that is the use-case for SQLite.
It could still collect data to the server on a delayed basis for the benefit of the app-owners and for back-ups but that is not time-critical.
Lots of things predictably don't. It seems like bad engineering to operate as though everything will. Especially in appliances/"IoT" and other embedded use cases, that line of thinking can drive production costs up a lot.
In the context of embedded things of course you should use something like SQLite as opposed to postgres, because there aren't going to suddenly be millions of people using your CO2 monitor (for example). But for web stuff you can plausibly have user counts that span 6 or 7 orders of magnitude, so you want to be prepared for that.
It's already happened to:
- Juju
- Upstart
- Unity
- Mir
- Quickly
- Ubiquity
- Landscape (barely maintained)
- Ubuntu One
- Ubuntu Phone
- you_name_it
I think it's due to Canonical thinks that they have their own very special dedicated way and vision. But always ends up screwed.
It's like asking if Python still doesn't support running without a garbage collector.
It's a serious database for anything that doesn't require concurrent multiuser access.
sqlite.com seems a pretty good cut off traffic volume, I would guess between 95-99% of all web apps are probably consistently getting less traffic than sqlite.com
Most of the dynamic content is generated by the version control system, Fossil (https://fossil-scm.org/). For example: https://sqlite.org/src/timeline or https://sqlite.org/forum/forum
Fossil is hosted on the same machine as SQLite. Fossil is self-hosting and the Fossil website is 100% dynamically generated. Every HTTP request against https://fossil-scm.org/ does about 200 SQLite queries (give or take - depending on the page).
Though a cache may have been what most of the traffic is hitting.
Since only a single process may write to a database, it would be best if that process were local.
NFSv4 has built-in file locking, which is likely the most preferable protocol. SMBv2 is a massive rewrite of that protocol, and I expect any file locking problems would be addressed (especially as Microsoft sponsored SQLite changes specifically for Windows 10).
Remote readers in explicit read-only mode likely won't hurt, but there is a deeper discussion below.