Discussion on why SQLite is gaining popularity [audio]
syntax.fm
syntax.fm
It has SO MANY advantages over every other solution. It can do the "just a bundle of files" thing, but it can also do all this rich data as well. And unlike most "just a bundle of files" formats, it's incrementally updatable, it's so good. We're using this in production right now and couldn't be happier with it, wouldn't dream of using anything else at this point.
One notable application that uses SQLite for this is Audacity: all the stuff you record is streamed into a SQLite database (the project file). This is a huge reason why Audacity is so good for recording long sessions, and why it's so resilient against crashes and failures.
None of the crashes I ever encountered with Audacity has been due to SQLite of course. If anything this article made me want to use SQLite in more of my projects where I need to store local data.
The software was writing vast arrays of numbers into a file, and reading them based on offsets.
As the software (and its data) evolved, versioning became a nightmare.
I ended up writing an ORM serializer which read from/wrote into an SQLite DB with a variation on an EAV schema.
One of my favorite projects, result-wise. Reading and writing was clean and efficient; the format was portable, language-agnostic, compact.
Versions of the new format were backwards- (and, within reason, forward-) compatible with each other (I forced specifying sane defaults for attributes if they're missing).
I'm still thinking of reimplementing a project like that as FOSS one day.
Serializing numeric data to something like JSON is a waste, and the structured binary formats I've looked at aren't nearly as thought out as SQLite.
(I have an idea to implement an LSM tree where each layer is an arrow file, which should allow for faster mutations while maintaining a lot of the benefits of arrow. But I haven’t got around to it).
Duckdb can also read / import SQLite natively so we could ingest lazily from sqlite disk -> duckdb.
Biggest issue with duckdb is some memory leaks and crashes for the nodejs driver. Seems not production ready.
And did I mention that the file handles are kept open for the whole program duration (might be multiple hours) ? That's fun, data loss is almost a daily occurrence. It's the main thing I'm refactoring right now.
Most data fits into hierarchical data structures that don't form a complicated memory graph with references.
Once you know how to write primitive types and arrays of them, you can write objects, and it means you can write anything (without having to worry about writing "before" or "after").
And yes, switching to SQLite absolutely addresses the "perpetual fopen” problem.
Nowadays I just can't see any other way to do this, other than by maintaining a well-ordered .zip file or filesystem structure, with strict validation being something that has to be programmed. I'd just prefer to sqlite all the things.
Bigger problem for me is that none of the code review systems I know of support this. At work, we've been wishing to have something like this in Gerrit, due to multiple SQLite DBs and other binary documents living in the repo, but as far as we can tell, we'd have to modify Gerrit sources directly to make it happen, which IT would frown at and nobody has time for anyway.
That’s one hell of an endorsement.
That argument implies that we're stuck with the browser's single built-in home page because every other web page has to be downloaded.
How is having to download sqlite3.wasm any different from having to download HTML, CSS, JS, images, etc.?
'unnecessary' file size. K matter on the web still.
I'm not normally a web guy, but from https://sqlite.org/download.html it looks like it's ~800k extra added to initial page load? Based on average mobile speed in US of 97.09Mbps; that's an extra half second added to initial page load. That's not trivial; maybe an extra 15% bounce rate on initial hits.
If you're serving it uncompressed, which no production-grade site will (for a given definition of "no"/"none").
> K matter on the web still.
Not, i opine, for the types of apps which want to host client-side databases. These are client-side applications, not "web pages."
Even a bare-bones, database-less google.com is now 3.14mb uncompressed (1.32 compressed), and that's not counting the pieces which uBlock Origin keep from loading. It loads somewhere around 2MB (uncompressed) of JS.
Last i checked, gdrive downloaded some 14mb to get up and running.
> that's an extra half second added to initial page load. That's not trivial;
We'll have to agree to disagree on whether half a second extra initial-hit-only load time is trivial.
That 847 KB is actually the size of the (compressed) zip file, but it does contain other files. The compressed WASM + the JS to load it is probably about 500K, depending on the specifics of compression & minification.
How things change.
Unless you had a 3KHz processor (and no processor ever had that), that statement is just not true.
From memory the wasm binary of wa-sqlite is ~1mb, which is certainly not nothing but is an acceptable one-time download size for many web apps. I've seen websites with single image files larger than that. Not an advocating for more bloated web apps, but a sqlite download might not be a deal breaker.
Or users could simply get confused about what to do with those files and contact support a million times just to ask why these files exist, whether they need to be copied as well, why they are getting large, etc.
These issues can be worked around, but I wonder how many apps that use SQLite actually bother doing that. Clearly not all of them [2].
Avoiding file corruption is non-trivial regardless of file format, but giving users additional ways to corrupt their data is never a good thing.
So I think for small, user visible application files that don't need any database functionality, the onus is still on engineers to justify their choice of SQLite.
[1] https://www.sqlite.org/howtocorrupt.html
[2] https://forum.audacityteam.org/t/aup3-wal-file-remains-and-l...
This reminds me of a college roommate who was dissatisfied with his computer, likely due to malware from sketchy sites. He decided to delete large or suspicious files from window’s system32 folder. By suspicious I mean he didn’t like the file name.
He had to get a new computer later in the semester.
I had actually meant JOURNAL_MODE=DELETE, though. This would not technically use a WAL file, though there would be a rollback journal file that exists only for the duration of the transaction.
The usual way to deal with this is to write to a temporary file and then atomically replace the original file with that temporary file. You could do the same thing with an SQLite database. This is the workaround I was referring to earlier.
journal_mode=delete may not be the worst compromise, but I hate the idea of exposing users to potential database corruption, even in a relatively unlikely event.
I’m surprised it would corrupt the database. The database could have included a unique identifier as well as in the journal and refuse opening the database if the identifiers don’t match.
If the user could benefit from either of those, atomically updated plain files are great. Otherwise, SQLite is perfect.
Of course there's always workarounds to use SQLite in memory and dump to SQL code and such.
Really convenient for the user, zero cost for the developer (same API for in-memory/fs db and smooth transition between them).
Or is this just a way to serialize and deserialization the game state to automatically save the game so it could be reloaded if it closed/crashed without explicitly running a 'save game' function?
Yes. That was the point of my experiments, after I realized that good chunks of the data structures I set up for my game look suspiciously similar to indices and materialized views. So I figured, why waste time reinventing them poorly, when I could use a proper database for it, and do something silly in the process?
In a way, it's also coming back to the origin of the ECS pattern. The way it was originally designed, in MMO space, the entity was a primary key into component tables, and systems did a multiple JOIN query.
Since I had already thrown out logic & reason for the sake of curiosity, I took it a step further and learned that the Bun JS runtime actually has SQLite baked in, which allows you to create a :memory: db that can be accessed _synchronously_ avoiding modification of most ECS implementations. (I'm not familiar with the larger SQLite ecosystem, but being a largely TS developer this was very foreign to me)
works faster AND more compact than .tar.zstd and .7z.zstd (patched 7z with zstd compression)
The article discusses storing files as BLOBs. But it looks to me like this isn't a good fit for sufficiently memory-constrained environments, given a task involving processing a very large file in a sequential manner, like with media reencoding, or video playback. It seems like the traditional file access patterns are better suited for those tasks.
If the application only runs on one server, then definitely SQLite. That means you do not need fail over, replication or other fancy features. Why not simple SQLite?
A complex system is more likely to have problems. Network, TLS, runtime, your application may have so many reasons to crash. But for SQLite, just check IO. If it does not have problem, then you check your application.
I agree. So many usecases require a bounded context, and even ephemeral data stores. Memory caches are all the rage but the cost of doing network calls to fetch data from a memory cache can eclipse the cost of simply fetching the data from a local disk.
A traditional DBMS is a quite complex application, sometimes using that may introduce more bugs than your application itself.
Of course, if you experience scaling issues there (you probably won’t if it’s anything less than enterprise-level usage), you can always just add a second db file!
If you decide to put your application on a single server, that means you do not care about the single point failure, and all your workload can be handled by the single server. So you can just run only one instance of your application, then you will not have such kind of problem.
Even if you want to have multiple instances of your application running on the same machine, SQLite can also handle that.
> what if you have separate services that need to access the same DB?
That means one single server cannot handle the workload. If the bottleneck is in the database module, a cluster is required to process the data. Clearly the SQLite is not a good option in this case.
If it is not that case, you can separate the database module and provide a lightweight wrapper around the SQLite to create a database service. And use multiple instances for calculation, then call the single database service instance for persistance.
I think comparing to the single instance of MySQL/Postgres/SQL Server, the performance of SQLite is not too bad. So we should keep the architecture simple, if possible.
But once you're at 100% of a core, then there aren't many orders of magnitude between that and "uses every core". In fact, the impact from not using a scripting language is on the same order as the threading would be! So, if it's worth spending effort squeezing out those last 1-2 orders of magnitude from the CPU, then it's probably worth thinking about the language as well.
If you've already blown through 4-5 OOMs going to a full core, then chances are you'll need it.
Now there are a bunch of new companies that also want it to take over in the more traditional domains.
So far I don't see a lot of uptake. It's still vastly inferior to the more entrenched databases. Both in terms of performance - especially under concurrency, and in available featureset.
It's inferior at a use case it wasn't designed for. It's definitely superior for at a use case it was designed for.
> available featureset
Also, as I understand, it is the mostly widely deployed database in the world. They have a whole page about it.
And yea they're probably the only feature I really miss in SQLite so far.
I always loved that an SQLite DB is just a single file. People say "Who cares how many files the DB is?". But in practice, I always found it to be very convenient. It makes the concept of a project easy to grasp when the data store is simply a file.
I recently realized that a Django project can also be done in a single file. That made me like Django even more.
I have the feeling that this type of logic and simplicty is indeed a factor in the long term success of a software project.
Windows software is often enough "portable executables" that can run off the folder they were unzipped into, and historically, even installed apps could often enough be copy-pasted from their installation folder into another machine and Just Work (it was helpful, in particular, that all the DLL files lived next to the executable instead of being installed system-wide).
Docker, obviously, because it eliminates all the bullshit of installing software - again, especially painful on Linux systems - and gives you a single file that drives it all. The platform itself may be system-wide, but the thing you care about: individual software - is contained (literally) so it doesn't spill out. Easy to reason about.
Modern web stack is a mess of tooling, some of which system-wide (so people will reach for Docker to contain it all!), with huge version churn and lots of moving pieces. Source code of your app doesn't feel like a major component of it; it's useless and unparseable without chains of finicky tooling. That's in contrast to "vanilla JS", old-school experience, where source files were the only thing that mattered. No transpilation, no build chains. You could fire a site up straight from your hard drive, or FTP/SCP it to a file host, and It Would Just Work.
Same with SQLite. To this day I don't like RDBMSes, because they're a system-level platform designed for admins, while all I care about is the RDB part. My database. SQLite maps conceptually to how I think about it - one database, one file (+/- WAL). One well-defined place my data lives, that I can move around and between machines using regular file operation tools.
Etc.
Files over apps, always.
EDIT:
This also makes me very eager to try doing something with RedBean[0] - a webserver in a single Actually Portable Executable[1] that's also a ZIP file into which you put all the data you want it to serve. A turn-key website in a single file!
--
[0] - https://redbean.dev/
This hasn't always been true. The portable executables are a nice user convenience for certain apps, but by no means all, and Windows is the platform for which "DLL hell" was coined. They even got briefly worse with the "GAC" for .NET Framework, although it rapidly became apparent what the problem with that was.
These days they really want you to use .appx and ship via the Microsoft Store, so of course nobody does that and all Windows software is (a) nice single executables (b) sprawling DLL monsters (c) javascript in a box (Teams!) or (d) games.
Now I'm wondering if SQLite could be a viable executable format to unify PE and COFF.
Isn't this already done better by jart's APE I mentioned earlier[0]? Though I imagine you could statically link SQLite to your APE and stuff a .sqlite DB into the ZIP file at the end, to use that for static storage. Or read-write storage if you also link zlib (though I'm not sure if you can portably overwrite your own executable on the hard drive).
--
You definitely cannot do this on Windows.
1. Write the new executable.
2. Rename the running executable.
3. Rename the new executable to the original name of the running executable.
4. Launch the new executable.
5. Exit the original executable.
6. Arrange for the new executable to delete the renamed original executable once the original has exited.
This is admittedly a bit fiddly, requires care if you need to preserve attributes and ACLs of the original file, and has obvious issues in scenarios where multiple instances of the original executable may be running concurrently, but it does work.
You also have to be careful to consider the ramifications of a system crash or snapshot backup in the middle of this process: ensuring the new executable is safely flushed to disk before it replaces the original is, under normal circumstances[1], easy (FlushFileBuffers); ensuring that one of the two executables always exists under the original name is hard (the Win32 ReplaceFiles API sounds like it should help here, but it doesn't).
The cleanest solution to the latter problem I've come up with is to use the HKLM\SYSTEM\CurrentControlSet\Control\Session Manager\PendingFileRenameOperations registry key as a backstop to ensure the move (3) happens, if necessary, as soon as the crashed/restored system is booted, but this requires local admin rights (or wanton disregard for system security, i.e., changing the ACL on this registry key to allow ordinary users to write to it, and therefore any file on the system on every reboot).
[1] Here "normal circumstances" exclude cases where hardware lies[2] or users explicitly disable buffer flushing[3].
[2] https://devblogs.microsoft.com/oldnewthing/20100909-00/?p=12...
https://devblogs.microsoft.com/oldnewthing/20170510-00/?p=95...
[3] https://devblogs.microsoft.com/oldnewthing/20130416-00/?p=46...
import os
import django
from django.core.wsgi import get_wsgi_application
os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'mysite.wsgi')
application = get_wsgi_application()
ROOT_URLCONF = 'mysite.wsgi'
SECRET_KEY = 'hello'
def index(request):
return django.http.HttpResponse('This is the homepage')
def cats(request):
return django.http.HttpResponse('This is the cats page')
urlpatterns = [
django.urls.path('', index),
django.urls.path('cats', cats),
]
ServerName mysite.local
WSGIPythonPath /var/www/mysite
<VirtualHost *:80>
WSGIScriptAlias / /var/www/mysite/mysite/wsgi.py
<Directory /var/www/mysite/mysite>
<Files wsgi.py>
Require all granted
</Files>
</Directory>
</VirtualHost>Not really in one file, is it?
'apt install sqlite3' will install more than one file too. But the DB that is in your project is just one file.
As a first approximation I grepped for PATCH in the web server logs and it seems to service about 30 of those per second on a $40/month vps. I haven't load tested it so not sure how high it could go. That's not including any POSTs etc which may also generate DB writes.
Bear in mind this isn't particularly performance tuned either as the webserver uses go (slow in general) and the portable go sqlite3 driver (also slow)
Go isn't slow in general, it's pretty damn fast without getting into the crazy handcraft artisanal stuff with e.g. C/C++. It's, IMO, the perfect balance between speed, features, language complexity, standard library richness.
For reference, a random benchmark that shows that even the optimised to hell Twister Python web server is slower than the out of the box, in the standard library, `net/http` in Go.
If it wasn't for non-technical editors requiring an interactive WYSIWYG backend, it could've been made with a static site generator, but as it is, Django needs a couple dozen megabytes of RAM at worst for logged in users.
To stretch the analogy: if you need a database server, neither SQLite nor the iPhone are a good fit.
While there is significant overlap in the basic DML operations due to them both being SQL based, getting SQLite working for my use cases would be like using an iPhone to serve my app over the internet: a whole lot of work, and many compromises, for no good reason.
There are of course solutions which wrap this fopen() replacement in a network/cluster-aware tools, e.g. https://github.com/rqlite/rqlite - these are competing with postgres.
Postgres does have replication out of the box but it has a bunch of limitations meaning you have to manage it yourself (ddl isn't replicated I think). And the replication will not get you strict serializability.
[0] https://litestream.io/ [1] https://www.sqlite.org/speed.html
I've never seen SQLite used in a setup which multiple machines connect to the same database over a network.
For example: A web application with a web server, a worker/job server, and a database server.
In these instances MySQL or PostgreSQL seem to be much better choices.
So will SQLite ever be able to "take over" in these scenarios?
Why does the api take 3s to respond? Well it needs to call 6 other apis all of which manager their own data. The problem compounds over time. APIs are not the way to solve cross organization data concerns.
You’ve fully misunderstood what I said. When you have 500 applications, the graph of calls for how any one api resolves will go deep. Api1 calls 2 calls 3 and so on.
Vs creating an organization wide proper way to share and manage data.
If you run a single application on a server that needs a database you might want to consider SQLite, regardless of your needs for concurrency/concurrent writes.
> trying to handle this in your own custom backend code
It's not writing some extra custom code, it's simply locating all of the code which interacts directly with your database on one host. Splitting up where your code is so that what would be function calls in some places if everybody interacted with the database are instead API calls. This kind of organizational decision is not at all unusual.
And if you're using SQLite it's probably because your application is simple and you should have some pushback anyway on people trying to mAkE iT WEBScaLE!! (can I still make this joke or has everybody forgotten?)
A lot of premature optimizers get very worried about concurrency and scalability on systems which will never ever have concurrent queries or need to be scaled at all. I remember making fun of developers running "scalable" Hadoop nonsense on their enormous clusters which cost more than my yearly salary to run by reimplementing their code with cut and grep on my laptop at a 100x speedup.
I've worked places where a third of our cloud budget was running a bunch of database instances which were not even 5% utilized because folks insisted on all of these database benefits which weren't ever going to be actually needed.
A lot of factors play into this and it certainly does not work in every case. But I recently got to re-write an application at work in that way and was baffled how simple the application could be if I did not outright overengineer it from the start.
That is just anecdata. But my guess is that this applies to a lot of applications out there. The choice is not between PostgreSQL/MySQL and SQLite, but between choosing a single node to host your application or splitting them between multiple servers for load balancing or other reasons. So the choice is architectural in nature.
Cloudflare D1 https://developers.cloudflare.com/d1 offers cloud SQLite databases.
> For example: A web application with a web server, a worker/job server, and a database server.
I've been giving it a run on a blogging service https://lmno.lol. Here's my blog on it https://lmno.lol/alvaro.
If you actually mean "database server", i.e., SQL is going over the wire, I don't see why you'd ever structure things that way. You lose both SQLite's advantages (same address space, no network round-trip, no need to manage a "database server") and also lose traditional RDBMS advantages (decades of experience doing multiple users, authentication, efficient wire transfer, stored procedures, efficient multiple-writer transactions, etc).
Assuming that it's the worker / job server which is primarily issuing SQL queries, what you'd do is move the data to the appropriate server and integrate SQLite into those processes. (ETA: Or to think about it differently, you'd move anything that needs to issue SQL queries onto the "database server" and have them access the data directly.) You'd lose efficient multiple-writer transactions, but potentially get much lower latency and much simpler deployment and testing.
The "one writer at a time and the rest queue" caveat is fine for most web applications when writes happen in single digit / low 10s of ms
From the webapp angle check out, for example, pocketbase.io which is an open source Go supabase style backend which wraps sqlite. They have benchmarks etc available.
Noting that the sqlite developers recommend against such usage:
https://sqlite.org/whentouse.html
Section 3 says:
3. Checklist For Choosing The Right Database Engine
- Is the data separated from the application by a network? → choose client/server
But...
Many projects won't ever need that. A bare metal machine can give you dozens of cores handling thousands of requests per second, for a fraction of the cost of cloud servers. And a fraction of the operational complexity.
Single point of failure is really problematic if your system is mission critical. If not, most apps can live with the possibility of a few minutes of downtime to spin up a fail over machine.
SQlite, generally speaking, is a FANTASTIC local database solution, like for use in an application.
I mean look at the prices from Hetzner.com.
Shared vps: 16 core cpu, 32 GB ram, 320 GB disk for € 38.56
Dedicated vps: 48 core cpu, 192 GB ram, 960 GB disk for € 343.30
Any cloud offering feels to me like offering a custom STL as a SaaS: if you're going over the network, why not use a database designed for this?
The only usecase I can see would be something like mini-DBs in an S3, which again would just be a library to abstract away.
A few issues I have with it:
- Migrations are a real PITA, you often need to copy whole tables over since the DDL available is so limited. Even basic things that are not breaking change like dropping a foreign key is not possible
- Single file doesn't scale that well, you will see select performance degradation, vacuuming will be extremely long/take a lot of disk space. We started splitting our databases in multiple files and using ATTACH but you lose a lot of nice things like FK support.
- No parallelism except on sorting, this one is annoying when you have compute heavy select (like searching on compressed data). It will max out a core but it can't spawn workers for the select. It can for the ordering part so it's not really a "technical" limitation.
Still grateful it exists though.
https://syntax.fm/show/779/why-sqlite-is-taking-over-with-br...
The beauty of SQLite is single process, same process simplicity and there are lot many cases where SQLite is more than enough.
Basecamp is using SQLite in their on premise offering.
And these are all hairy problems. At that point it might be just simpler to use a centralized Postgres or a proper distributed database.
SQLite really shines when you know that a database can only get so large, let’s say you have a paid product that is only ever going to have a moderate number of users.
With WAL mode Sqlite is much much better, in the past we had all kinds of problems with trying to read/write at the same time. At that point in time every table was a separate database as a workaround.
https://stackoverflow.com/questions/1005206/does-sqlite-lock...
SQLite has already taken over. <10 years ago.
I use it for client applications that need to store local data -- normally temporary data to send to the server, etc.
I am now using it for a Message Queue middle-man (broker) program I wrote, which uses sqlite to store the messages. They tell us the queue they belong to, if there is a delay before handing out to a Worker, the current status, etc. It has worked very well. The other reason why I was not concerned using sqlite was because the program would do one thing at a time.. so there would not be multiple requests trying to insert/update data at the same time. To me it was a win-win.
Designed to be fast and light.
Thumbs up to sqlite team!
You people have got to be kidding, or aren't professionals.
Well HN has never been one to make a whole lot of sense.
What's next, plain text file for saving data? Ridiculous
The Chrome browser stores many pieces of the user profile (bookmarks, browsing history, etc) in SQLITE databases. I'm not sure if you consider that "professional".
Is there something new?