Why you should probably be using SQLite
epicweb.dev
epicweb.dev
But... for an app running multiple instances, the article suggests a lot of extra complexity. Managing that complexity makes less sense to me than just firing up a managed database server (RDS, or whatever).
Even if you're self hosting, I think running a MySQL/Postgres cluster is a lot less complex than the options this article calls out.
edit: and more to the point - MySQL / Postgres replication is boring. It's done by a ton of deployments, and isn't going to surprise you, at least at smaller scale.
> One huge benefit to SQLite is the fact that it runs as an embedded part of your application
But all production workloads I've been involved with the last ≈5 years have either been containerised apps offloading state to databases/caches/blob storage etc, or have strived towards that.
What I do think would be awesome would be an embeddable Postgres library/binary that could use a single state file on your local filesystem for development and use a networked database in production. Getting the benefits I like most about sqlite locally, and not having to deal with files in production.
This article really needs to be taken in the context of the author’s target audience: He sells courses that primarily target web dev juniors doing learning projects and small projects. That’s why he includes the “most of you reading this” disclaimer in the article:
> So, can you use SQLite? For the vast majority of you reading this, the answer is “yes.” Should you use SQLite? I’d say that still for the majority of you reading this, the answer is also “yes.”
Pushing strong contrarian opinions is part of his social media marketing style. He’s also pushing a “why I won’t use next.js” article and opinion on his social media, while conveniently omitting the obvious conflict of interest that he sells courses that don’t happen to cover Next.js.
I think some people are missing the conflict of interest because the author blurs the lines between his personal opinion and his business. He turned his personal brand into his business and uses his name as his brand.
EDIT: I think people are missing the meaning of "conflict of interest". Having a conflict of interest doesn't mean something is wrong. You can be right and have a conflict of interest.
The issue is that conflicts of interest exist whenever someone's paycheck depends on an opinion being true. If someone on Twitter was alternating between trying to sell you vitamin supplements and then posting articles about why those vitamin supplements are good for you, HN would have no problem with pointing out the conflict of interest and taking it into consideration in the context of evaluating the claims.
If someone's entire job and personal brand are built around selling courses for particular stacks, that's important context to bring up when they start writing about why other technologies are bad. Agree or disagree with the conclusion, but you have to acknowledge that the writings should be read with the conflict of interest taken into account.
No, what's going on is that you don't understand what "conflict of interest" refers to.
Different people having different interests, as when the apple company says you should buy apples even though you prefer oranges, is just a regular conflict. A conflict of interest is when one person has two different interests. The apple company isn't experiencing a conflict in your example.
Pretty sure that's just marketing.
A lot of product pages will spell out features they have and list competitors that don't have those features.
What you describe is simply a bias.
Conflict of interest would be if I take money from parties A and B. Party A pays me to give a professional opinion about which nutritional supplement to take, which frontend javascript framework to use, etc. Party B pays me to endorse their special Snake Oil Pills and Ointment or React or something else. I take Party B's money and make those endorsements to Party A without their knowledge and without any actual consideration of what is truly best for Party A. There is a conflict of my interests in Parties A and B.
Your definition is excessively broad and could be used to suggest, e.g., that Nike has a conflict of interest because it is their opinion that you should purchase their shoes.
It's even worse: that Nike has a conflict of interest because they think that wearing shoes while running is good, even though it would be extremely weird if a running shoe company thought that running with shoes is bad.
Remove the business from the equation and the opinion is fine — it's the existence of a business that aligns with the opinion that creates the conflict! Galaxy brain definition.
He has motivations that make him not impartial, but that's not usually termed a "conflict of interest."
You're just using the term strangely. Usually the phrase "conflict of interest" implies the person has some sort of official commitment / obligation / duty to another party that is put in jeopardy because of another conflicting interest.
But this is just some guy selling stuff. He hasn't made any official commitments; he doesn't have any official duty or obligation to remain impartial.
By your definition, anyone selling anything has a conflict of interest because they want money from their customers, which may be against the customers' best interests.
I really don't get the reasoning here...
I wouldn’t be surprised if he offered Next.js courses if it becomes popular enough in the future. For now, the anti-Nextjs push seems like a clear defensive play to steer people back to the course material he already had for sale.
The whole course he's been advertising is based on remix, which came up YEARS after next.js rose to popularity.
Where did you get this info that you were so confident on?
https://www.reddit.com/r/nextjs/comments/17giozq/why_i_wont_...
https://www.reddit.com/r/reactjs/comments/17gl76d/why_i_wont...
Last time I truly used NextJS was in 2020 (it was v9), and that was only to make a statically generated brochure type site. I had started the site with Gatsby and didn't love Gatsby, so I switched to NextJS and loved it.
Recently I did start up a Next v13 project using the new App Router. But I haven't gotten that far past the initial scaffold and creating a couple components. Seeing all of these complaints about v13, especially the App Router, are making me want to scrap that and use something else.
I mean this is more of less what using a local container for Postgres is. It’s not exactly one file but it’s hidden in a volume somewhere where you don’t need to go file digging, so for all intents and purposes sure, it’s one file.
Most ORMs also will let you swap out your RDBMS so you can run sqlite locally and Postgres in prod via the same code. I think this is a bit of an anti pattern though. While the two are similar in most standard use cases, it’s not always apparent when you veer off that standard use case and into something that’s not supported by sqlite. And then all of the sudden you either have a divergence between what works locally vs in production or you start to intentionally limit your app’s capability to only what works with sqlite because you can’t locally validate it.
I run a single container Postgres for my homeserver, and have to deal with occasional issues that wouldn’t apply to SQLite (which I run as well).
I don't understand this. How would you solve backups without using the filesystem?
What's the conceptual difference between backing up your Oracle database to an Oracle Cloud Infrastructure bucket and backing up your sqlite database to an S3 bucket?
My point was more that with a networked database, you can easily centralise your backing up of databases. If you are in the cloud with managed databases, the backing up is kinda solved out of the box. If you are doing it yourself or on-prem, you probably have a database cluster of some kind where backing up can be managed (which of course involves a filesystem at some point).
But if you are using sqlite, every sqlite file across your organisation needs to be backed up. You'll probably have some scripts creating the actual snapshot and then some other scripts shipping that away somewhere. Those scripts probably need to be quite bespoke for different hosting solutions. Are you going to ssh into the vm's running the apps and do the backup that way, or will you be running the backing up scripts on each vm?
Of course if you have a single VM hosting a few apps with one sqlite database each, none of this is an issue, and it might be easier in the short term than a networked database. Or even if you are operating at "scale" and treating your VM's more like pets than cattle, it's probably not going to be an issue either.
Absolutely! And also, short of that but incrementally building toward it, some sort of simplification of initial db setup and configuration, including really solid, thoughtful defaults (many of them are but I am guessing they could be substantially improved).
I feel like half of people’s attraction to SQLite is the easy setup and first deploy story. Postgres will never match that but could get closer.
That is an incredibly important part of the story!
Spending a lot of time on configuring and monitoring and operationalizing your database can kill a business iterating on its minimum viable project.
You can delete and recreate the volume when you want with `docker compose down --volumes` flag, which is great for testing.
Shameless plug: https://mahesh-hegde.github.io/posts/postgres_local_using_do...
I wired Postgres up for our local dev. I don't believe in mocking the database, so all our tests and local dev run against a real Postgres instance.
The main tricks:
- Store the Postgres installation in a known location.
- Set the dynamic loader path to the installation lib dir, e.g., LD_PRELOAD.
- Don't run CI as root (or patch out Postgres' check for root)
- Create and cache the data directory based on the source code migrations.
- Use clone file to duplicate the cached data directory to give to individual tests.
One thing I'd like to pursue is to store the Postgres data dir in SQLite [1]. Then, I can reset the "file system" using SQL after each test instead of copying the entire datadir.
https://github.com/zonkyio/embedded-postgres
Each test just copies a template database so it’s ultra fast and avoids the need for complicated reset logic.
1. Each test runs all migrations on a fresh database. Each test spends 2.1 seconds on db setup.
2. Each test suite runs all migrations. Each test copies the template database from the test suite. Each suite takes 2.1 seconds to run migrations, but cloning a template database takes 300 ms.
3. Bazel caches the data dir and rebuilds it once for all test suites. Reduces the initial test suite setup from 2.1 seconds to 160 ms (to copy the datadir).
4. Each test in a suite uses clonefile to copy the datadir. Reduces db setup overhead from 300 ms (to copy a template database) to 80 ms (to clonefile a datadir).
Currently, most of our testing overhead is clonefile and cleaning up the datadir. I'm interested in a single file sqlite FS because I could be clever and use LD_PRELOAD to replace Postgres's file system operations with sqlite, avoiding most syscalls altogether.
Another problem is that parallel tests can exhaust memory.
The slowest part of our tests is syscall overhead. A mostly empty Postgres data dir for a medium-sized database with a few hundred tables consists of 3k files. On my M1 macOS, it takes 120 ms to delete the entire data dir. Copying is cheaper at 80 ms using clonefile.
In $oldjob I designed and managed a system that was eventually running many hundreds of MySQL 3-node groups. MySQL in particular has a lot of nobs that you need to understand to set up a cluster that doesn't lose data during a fail over (semi sync, sync bin log, trx commit flush, ....). And then of course: neither MySQL nor PostgreSQL have a built-in mechanism to handle fail overs at all. It's another software stack around that, or even something custom built.
Again, not saying I agree with the author. I'm just saying you're portraying it easier than it actually is.
You obviously don’t need a cluster if you’re considering SQLite as an alternative.
Comparing a full database cluster to SQLite doesn’t make sense.
If you just want peace of mind and better disaster handling, and your current uptime, concurrent workload split, and performance is fine -- maybe your hosting provider eats it for an hour or two and you don't want to be hamstrung -- I would suggest just using a tool like Litestream, replicating the DB to S3, and setting up a hot standby server in some alternative region that you can fail over to and that actively synchronizes the working set. Always keep them up to date with deployment automation. Set up some alerting, and if a failure happens, terminate the main instance with prejudice, let the replica catch up by replaying any latest changes, and reorient your load balancer to point to it (Cloudflare tunnels are a good low-tech solution you could use to do that.) This might imply a small downtime window for the hot failover. For bonus points, you can automate this whole task, and actively perform it regularly, swapping between servers on regular cadence -- thus turning the design from having a primary/standby to simply having two interchangeable systems that swap roles. And then you have good confidence in hot-failover disaster recovery. e.g. just do this whole dance every week on Monday at 1am UTC, and you can have confidence it works and will stay working.
I do not know what other architectural constraints you might have. These are just suggestions. Good luck!
This feels like an uncharitable interpretation of the maturity of projects like LiteFS/rqlite/dqlite.
That said, I looked at 2/3 a while ago:
- rqlite looks great and mature, though it has a HTTP interface, so I'll need to rewrite my database layer anyway to use it. At that point might as well to Postgres
- dqlite seemed like a failed Canonical experiment to me last time I checked, but looking again I think I may have had the wrong impression.
And: the SQLite homepage runs on - guess it. Maybe they have some doc/blog describing how they manage their traffic.
On the other hand, read replicas are also the easy case for database clusters. And just making your one database server bigger also scales a lot, there is nothing stopping you from adding a terabyte of RAM.
And just making your one database server
bigger also scales a lot, there is nothing
stopping you from adding a terabyte of RAM.
Additionally, I think a large percentage of the HN crowd underestimates what a single large-ish single server can handle these days. Probably because we are all used to only seeing tiny virtualized slices these days.
192+ threads, 2TB of RAM, terabytes of crazy fast PCIe storage. This was multiple racks of servers not too long ago, now in ~2U and it costs less than a car.
There exist varying levels of complexity. Here at my company, we have a very small cluster comprised of a main PostgreSQL instance and four replicas. It just works and requires very low maintenance effort.
But I imagine that things can get really messy if you're dealing with dozens or hundreds of instances, for sure, especially if you need sharding.
As you said, it depends on what you want:
- If you are okay with losing data in the event of a failover, then a simple setup is fine. - If you want lossless failovers (i.e. not a single transaction is lost), then that's hard. - If you want entirely automatic failovers, that's also very hard. - ...
My experience stems from MySQL, and not PostgreSQL, so this may or may not apply to pg. I wrote about my experience in this blog post: https://blog.heckel.io/2021/10/19/lossless-mysql-semi-sync-r... --- TLDR: Not losing data with MySQL is really really hard.
What concerns me more than anything with a process like LiteFS is the 'unknown unknowns' perspective of what can go wrong - whereas I think MySQL and Postgres have generally more understood failure modes.
I also think that a lot of people in middle sized projects where they're not big enough to have a dedicated database team, etc will end up using some kind of managed database solution - something like Amazon Aurora, or RDS, or even someone like DigitalOcean's managed database product? Those generally take care of sensible configuration, backup, and failover at least.
edit: FWIW, I've done both ways - self-managed MySQL & Postgres clusters and managed services. You're right - getting MySQL in particular set up to be crash-safe etc requires careful documentation reading, and it's very frustrating that default installs of MySQL don't come already configured like this. In my eyes, safe-but-slow is a much better default than fast-but-risky :/
SQLite can handle multiple processes: https://www.sqlite.org/faq.html#q5
I am not sure it could back then
If the load is very read heavy the write locks aren't an issue, but if you're tracking page views, comments or any other write-heavy feature, it'll be harder to get good performance.
Alter table is solvable too. I built tooling for that here: https://sqlite-utils.datasette.io/en/stable/cli.html#transfo...
I'm not sure how else you could do that besides a DHT without a central database or manually assigning certain pieces of data to specific servers.
People like messing around with meme features but I don’t think there’s many companies in the world using more than that.
Also, column types are not checked and you can easily insert a string into numeric column.
Also it doesn't allow you to use multiple application servers.
So it can be used only with small, simple sites.
This is only true if you don't use strict tables (implemented end of 2021): https://www.sqlite.org/stricttables.html
If you define a table as strict, the data is coerced, and if not successful an error is thrown.
Coercing the data is hardly better. Now you have data integrity issues and you have no errors!
Seriously? And there I was thinking they actually fixed this mistake... they just made another schoolboy error.
This can be fixed in the connection string.
> column types are not checked
Now available, the STRICT keyword.
https://news.ycombinator.com/item?id=28259104
It’s also only two years old which is forever in the web world and brand new by Databases ops/maturity standards. There are likely still warts waiting to be discovered (there always are, but the discovery rate tapers over decades).
Not a solid guarantee. More prone to bugs, errors, etc. What is someone changes something using the comnandline client and forgets to issue the pragma command?
I would feel a lot happier using SQLite if this was a per DB setting rather than a per connection one.
How so? Are you saying that sqlite would ignore the connection string?
Judging by the documentation, if you issue a PRAGMA foreign_keys; and no row is returned containing a 0 or 1, then you are using an unsupported version of SQLite, or the library was compiled with foreign key support disabled. I am struggling to find any documentation that states if anything will occur if enabling foreign keys in the connection string, when the version does not support foreign keys.
Worth mentioning that numeric in Sqlite is still just a float.[0] A table of two rows where column a is 0.1 and 0.2, respectively, sum(a) will not yield 0.3.
These are all backed by integer data storage and arithmetic, but the database handles scaling the values for you, to whatever number of decimal places you have configured. SQL Server and Postgres MONEY type will additionally format values with a currency symbol, when converted to a character string.
In SQLite you're out of luck - if you want accounting values you'll have to store them as integers and scale the values yourself.
Source: I work on a mobile app with offline storage of pricing and weighed quantities, using SQLite in the app, and SQL Server on the back end.
I still wouldn't use floats/numerics/decimals to store currency either in any db generally, as said by others you're going to end up with inaccurate numbers [2].
Therefore using integers is in fact very good for this use-case, especially if you are in accounting or book-keeping!
Source: I work for a Fintech company that processes millions of payments.
[1] https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use... [2] https://www.youtube.com/watch?v=PZRI1IfStY0
If you add 0.1 to 1000000 with an integer representation scaled by 10^6 then you are still boned.
Binary floats may actually produce a better result here.
What you really want is a decimal floating point type with a suitable amount of significant digit precision.
> sqlite3 --interactive ./floats.db
SQLite version 3.37.2 2022-01-06 13:25:41
Enter ".help" for usage hints.
sqlite> create table floats(colA number);
sqlite> insert into floats(colA) values (0.1);
sqlite> insert into floats(colA) values (0.2);
sqlite> select sum(colA) from floats;
0.3
sqlite> create table floats2(colA numeric);
sqlite> insert into floats2(colA) values (0.1);
sqlite> insert into floats2(colA) values (0.2);
sqlite> select sum(colA) from floats2;
0.3
sqlite>SQLite is super versatile, but very different from other more traditional RDMBS.
The natively supported statements are as easy to perform as in MySQL or Postgres, for example.
> "The only schema altering commands directly supported by SQLite are the 'rename table', 'rename column', 'add column', 'drop column' commands shown above. However, applications can make other arbitrary changes to the format of a table using a simple sequence of operations.
https://sqlite-utils.datasette.io/en/stable/python-api.html#...
https://sqlite-utils.datasette.io/en/stable/cli-reference.ht...
> Also it doesn't allow you to use multiple application servers.
Not in the same way you use postgres etc, but you can do it with sharding or with LiteFS, but you do have to consider carefully how you scale your app.
I'm not _really_ disagreeing with you, but I think you're painting with a bit too broad of a brush.
If modifying the column's type, yes. But you don't if you're just renaming, adding, or dropping a column.
> column types are not checked and you can easily insert a string into numeric column.
Can be fixed with CHECK constraints in the table definitions.
> Also it doesn't allow you to use multiple application servers.
This is mentioned in OP.
> So it can be used only with small, simple sites.
It can be used in a large variety of applications, but it's more appropriate for some than for others.
Just seems strange that is actually a problem.
Don't get me wrong, I use SQLite ... it's just not the database for my company's management web portal.
SQLite is fantastic, it absolutely should be used where appropriate.
I don't however understand those who argue that replacing the likes of PostgresQL / SQL Server with it is generally appropriate.
What I replaced it with was a Go binary with SQLite, where installing it would be a matter of installing the package or just the files and starting it up, it would take care of the rest.
SQLite is great for systems you don't control or have to set up, but maybe not for webservers. Common use cases are apps' internal storage (does not need to be shared with other applications or distributed), you don't want to have to install, configure and run MySQL or Postgres in those cases.
For simple apps I just run Postgres on the same server and size things appropriately in settings. It’s really not hard and it’s well-trodden territory.
If someone is making a single-tenant app that runs on an embedded device or something then SQLite is awesome.
The way this developer concludes that “most of you reading this” should use SQLite is very strange. Is that indicative of his audience building small or toy websites rather than actual production websites?
Most production websites are not multi million dollar businesses.
If you use an ORM from the beginning, you might not even have to change very much of your application code.
I see the sqlite hype as a (legitimate) pendulum swing away from defaulting to 'web scale' everything to a realization that most of the systems we design don't need it.
On the other hand, when you start mixing in new tech like litefs to paper over shortcomings in the fundamental nature of an alternative, I start to question the sanity of the choice.
We have screwdrivers, hammers, wrenches, let's not select a wrench and wrap it in a 'screwdriver adapter as a service'.
My hats off to you on this comment, even more so if you are the originator. Personal experience shows that I have added more complexity by spinning up a postgres server 'in case I need it' more often than not.
That was exactly my thought, the majority of work that gets put out there isn't going to need to scale to many different nodes to serve thousands+ of concurrent users. If you're actually serving tonnes of traffic and need clustering or high end redundancy, have at 'er. But for most of the professional work I've done, SQLite is more than enough (provided you back it up properly!)
I think this is also influenced by my philosophy of keep things simple and only scale when you need to. I haven't had need of k8s or anything like that and suspect many companies serving small-medium traffic loads don't either.
And I also use SQLite in places I definitely shouldn't be.
You may be noticing it more out of a frequency bias (baader-meinhoff effect) situation.
Not a knock against SQLite at all, I use it in production for my own projects and some professionally as well.
Now ... it didn't do anything but it was live and you could login, create users etc.
Generally, you can just run the `postgres` command, but it creates many unnecessary hurdles:
* Just want to invoke `postgres`? Not possible, you need to invoke `initdb` first. Why can't `postges` do that for automatically you if the DB dir doesn't exist, like other servers do (e.g. `consul`)?
* Depending on your invocation, you're likely to run into errors mentioning "initdb: invalid locale settings; check LANG and LC_* environment variables". Why isn't there a simple UTF-8 default?
* Postgres refuses to run as `root`; that's unoverridably hardcoded in its code. So you can't "just start it" when you're `root` inside a container or VM in which only postgres runs. It can't run well under `unshare` when you want things to shut down reliably on cancellation in CI. This constraint should just be removed; no other software refuses to run as root. This mindset is indeed from 27 years ago.
* You can't start an in-memory postgres for quick testing or CI. This feature does not exist.
All of this is fixable, but it currently creats the "oh you can 'just run' postgres, but not in random conditions A, B, C and D".
Also an in memory Postgres would be nice, but Docker again gives an easy way of setting up “throwaway Postgresses”.
This all assumes Linux ofc.
Works fine for almost any team. Obviously it would be great if they would try to understand it, but it's really not necessary.
Not like most web devs know how most of the tools they use work (Source: All the teams I worked at)
Devs not knowing docker? Just install docker on their machine and ask them run the command above. One day has to be the first.
So ya, in process is faster for trivial stuff. But it's not faster when doing heavier workloads that benefit from things being in memory or having a better query planner.
The more you know :)
The query planner is still not nearly as good as PG's, but it's okay for simple applications.
I have actually used sqlite "at scale"... I'm still not sure people know that system (30k+qps dumping data into kafka) is running on sqlite on an EBS volume lol. But in this case it's just streaming stuff from disk, so pretty much any DB would have worked.
Your comment is basically the famous HN Dropbox comment but for databases: "are people really having problems getting an FTP account, mounting it locally with curlftpfs, and then using SVN or CVS on the mounted filesystem?"
I'm not saying "don't use SQLite", I'm saying "reduced n+1" problems ISN'T a good reason.
If you find out your data model was bad, and is a constraint to scaling, you can find it out without spending a lot on insanely scaled RDB instances.
I'm for the build one to throw away approach, and SQLite is often a good tool in the toolbelt for the initial attempt of this.
I did this kind of exploratory development both with SQLite and Postgres, both had different strengths and weaknesses. Had the purist clean data model with Postgres cost more than it should have when the product had to pivot the product. Also have seen the scalability limits of SQLite. Just shutting down a POC project backed by SQLite, which failed to get traction mostly because of business execution problems, and SQLite saved a lot of effort for the implementation, and using a "cleaner" and "more scalable" solution would have only reduced velocity, but would not have got us more users, the system worked fine this way (even has N+1 queries in some places). Should we got to the point that SQLite starts to become a bottleneck we could have afforded to clean the data layer (we had to rework countless times already as requirements were in a flux) up a bit and move to more scalable solution. Others might stick Firebase or MongoDB there, and it also does work for a while, and that is also a valid decision IMHO from a business perspective.
I think this is a kind of topic where the premature optimization is the root of all evil meme can apply, depending on the product you are making and the available resource pool. Not for the hello world examples of course, and not for the well defined well funded project, but there are lot of exploratory attempts out of the bigco/vc funded unicorn world.
But saying it SQLite good because no latency is weird, and saying that THAT is good because you can continue writing avoidable "I am not just lazy but I don't care about thinking about my database for 30 minutes" code... now that's a leaning tower of pisa of arguments that I can't follow.
It’s not worth our time unless they work into pages with large data sets or core flows.
We have an old web based tool that uses Java and luncene indexed file system based database. It’s a pain to work with frankly.
https://remix.run/docs/en/main/guides/performance#this-websi...
Elasticsearch waves
[1] for very small values of suddenly.
NVMe arrays are weird, can be better to just rip it from the drives if you need bandwidth over latency
Even then, with how easy the memory cache is to invalidate... I'm not sure even that's worthwhile.
The caching isn't some Paragon of performance - there's only so much it can do, when looking at it funny invalidates it.
In-process memory cache hit is faster than separate-process memory cache hit over ethernet.
Almost. lol
In that context, a local disk has virtually zero latency for most use cases.
Also, SQLite can run in-memory. If your process is long running and keep the DB in RAM, the latency is even closer to zero.
https://news.ycombinator.com/item?id=35903878#35915789
> Kent is the only blogger that I've felt compelled to tell all my less experienced colleagues to completely ignore
Honestly this is the kind of stuff written to fluff out a social presence and offers nothing to a technical audience.
For me personally, I have only used SQLite for 2 scenarios.
1) When developing. SQLite is a simple method to getting something together, before building a proper MySQL/SQLServer/Postgres one.
2) Client software. Some client software needs a temporary data store and find SQLite great for this. These are not complicated databases. They barely have about 5 tables. Nothing more than a Queue system than anything, which goes over the wire via Rest API to a Messsaging Queue. Once sent over with a reply of "success" - it is removed from SQLite. It is a nice, simple system.
There can be a few other reasons.
Other than the above, I don't think I would be confident of SQLite file for a moderately sized database, likely to be used for some web portal or CMS system we created where more than one request could be talking to the database at the same time. This is why you should stick to those central, relational databases.
MySQL and Postgres, especially, are not that difficult to install, whether on the same system or its own dedicated server.
I hope my comment is not seen as a negative for SQLite. If anything, my attitude is the opposite. I really like the work and effort that has gone into SQLite. A virtual pat-on-the-back by those involved! It is a great approach to having data in 1 file, rather than writing your own.. or having lots of "records" in their own files (in a directory) which I have done.
There are replication "solutions" for SQLite, but then it stops being simple and you're better off using Postgres.
Admittedly, I cannot comment on replication options for Sqlite. I have not bothered to even look into this. To me, it already goes beyond the purpose of what makes Sqlite great --- to be a powerful sql in one file. Great for programs that need a storage solution without the reliability of being connected to another machine.
In my client programs using Sqlite, it has been designed in such a way that if the Sqlite file is corrupted then it is not the end of the world. We know what didn't get sent to the server so we recreate the file and resend, etc.
(However, our Sqlite files have been very reliable with the exception of software updates, but we ensure the sqlite db tables are empty before you can update the client software)
Thats why I specified "client software" in my original comment, but I don't see why server-side daemons cannot use it as well. I guess If more than 1 program needs it, though, a combination of CRUD operations, I would recommend something else.
HN's bizarre groupthink occasionally decides to subvert architectural norms for fun, and any blog post that's trending on the front page suddenly counts as "good advice". Even if it's literally insane, if it's on the front page it's considered sage wisdom, and naysayers are downvoted for being negative about the insanity.
Those 15 years of architectural norms started at a time where a multi-core 2GB server was an expensive luxury!
We know a LOT more today about building web applications than we did 15 years ago. That's why some of us are ready to say that for a lot of applications, a single server running SQLite (with streaming backups to S3 via Litestream) is actually a pretty great option.
We used to use .csv and .ini files that way. After that we used BerkeleyDB, since it was built into so many tools. Or serialized objects. If you got fancy you'd read/write them to a temporary filesystem, and occasionally sync the files to NFS; if the app restarts, sync the files from NFS back to temporary storage, or if the temporary file isn't there, read it from NFS and write it to temporary storage.
Today, if you have to write a web app that has dynamic content and writes and reads user data, you can do it a bunch of ways:
- "Mostly-JS": Write your app in JavaScript. Some hosted provider out there allows you to submit data to some backend app, and read it from the backend app. (This, by the way, is how CGI-BIN web apps worked 20 years ago)
- "Mostly-CGI": Use some managed hosting provider to host a web app with some framework they support, like Wordpress, or Django, or something else. Typically they bundle a networked relational database with it, but I suppose you could trade that out for an sqlite file. Probably wouldn't support Litestream but you could probably have a cron job copy the file to s3.
- "Mostly-standalone": Buy some virtual machine or physical server, set it up, maintain it, write your web app, run a web server that runs your web app, write the file to local disk, set up Litestream. Never touch it, patch it, upgrade it, etc, so it always has security vulns, but it will just keep running, so it will be much easier to get going.
- "Mostly-managed": Pay for some hosted provider to give you the ability to run your app in containers. You build it, you push it, and then set up the thing to run it. Like the previous option, you can opt to never patch or upgrade anything so there's nothing to maintain. But since you can use a bunch of SaaS providers to build, test, and deploy automatically, it's way less to maintain and much more automated. Again, typically you'd use a networked relational database, since they're largely designed to run one app at a time, but you can customize them to have some background task try to upload your sqlite file out of the container or a sidecar.
- "Completely managed": You upload your code. They completely manage the versions of your app platform, doing patches, upgrades, running different instances of your app, databases, web servers, whatever you need. You basically don't have to think about anything but code. You could use sqlite but there's probably no way to back it up.
If you don't need to read and write user data, you don't need a web app at all. A static site works just fine. Hell, you don't even need a static site generator anymore, just link to the right css or js files in your html to DRY up your static content.
Vast vast majority of web apps run fine on a relatively modest VPS. And almost all will run fine on a beefy dedicated server. No amount of best practice changes that.
The reason why running an app on a VPS is not best practice isn't that the VPS can't handle it; it's not a question of scale. The issue is that there are many different problems with running an app on a VPS, and it is much better to avoid those problems by not using a VPS at all, rather than to spend a lot of time trying to mitigate those problems on the VPS. If there is an alternative that is lower cost than the cost of mitigating the issues on the VPS, then that's the better solution.
It turns out that there are solutions like that, among them Heroku/DigitalOcean App Platform, DigitalOcean K8s/AWS EKS/GCloud GKE, Fly.io/AWS Fargate, Google Cloud Run/AWS App Runner, AWS Lambda/Google Cloud Functions, and many more. Almost all of these are better than running your own VPS, because they are designed to remove the problems you will inevitably run into with your own VPS.
Some people buy cheap things and replace them frequently. Some people invest in better quality that lasts longer. If you'd rather patch the holes in things rather than simply last longer, a VPS may be the solution you want. But it's certainly not the best solution.
SQLite has its uses for sure - just not on a system that might need horizontal scaling, HA, or separate worker processes.
Sure, sure, you can duct tape a few SQLites together but then you're dealing with this
> LiteFS only allows a single node to be the primary at any given time. The primary node is the only one that can write data to the database. The other nodes are called replicas and they provide a read-only copy.
Might as well have just set up a separate DB server, less faff. At the point where you need to scale MySQL/postgres beyond vertical scaling you can probably afford to pay someone to do it for you
I have never really examined how this performs, and it would be best if all access was explicitly via SQLITE3_OPEN_READONLY.
It is built on a SQLite db and has real-time pub/sub capabilities. Its JS SDK is incredibly easy to use and setup for CRUD as well. For side projects and some medium tasks, I’d say SQLite/Pocketbase has been super easy to work with.
OR somehow derive it from the user ID/username. Keeping all the customer databases in a single directory/disk and then constantly "lite streaming" to S3.
Because each user is isolated, they'll be writing to their own database. But migrations would be a pain. They will have to be rolled out to each database separately.
One upside is, you can give users the ability to take their data with them, any time. It is just a single file.
Anyway, query and app logic modifications aside, I quickly ran into two unsolvable issues for me.
1. The lack of a robust web based admin tool comparable to phpmyadmin (no, phpliteadmin doesn't even come close) 2. No alter table. This hit pretty hard.
Plus lots of other idiosyncrasies - no enums, no type for dates, etc. In the end I decided it's not worth the extra work, trouble and risks and stayed on Maria.
https://simonwillison.net/2020/Sep/23/sqlite-advanced-alter-...
My Datasette tool doesn't quite serve the same purpose of phpMyAdmin unless you're OK with read-only access, in which case it works great: https://datasette.io
I have also found the CHECK constraint on an INTEGER column to serve well as an ENUM: https://stackoverflow.com/questions/5299267/how-to-create-en...
For dates I happily use unix epoch integers or iso8601 date strings.
It's a project where multiple other applications want to process data produced by my application. Instead of forcing them to make API calls to retrieve all of the data and all the network overhead and latency that entails, periodically write Sqlite DBs to S3 and post an event that the database is available. Can create one database per customer, or whatever your natural partition key is. Then it's just pull the database to local disk, pull all the data you need with SQL queries, and delete from the bucket when every client has had a chance to process it.
I believe it offers much better throughput than going through an API.
If we have a customer that is OK with RPO measured in minutes-to-hours and they have fewer than 5k active users, we are perfectly happy running with SQLite on a customer-managed VM and instructing them to use snapshots for backup & restore. Everything on one simple cheap VM in Azure/AWS/on-prem. One storage volume to snapshot. No weird tricks.
If the customer demands stronger consistency between their underlying record system and our system (specifically at time of catastrophe) and/or has more than 5k active users, then we are starting to push towards a separate cloud-native stack. Azure SQL Server Hyperscale with geographic replication to another region within ~100ms of the primary region. I looked long & hard at some of the SQLite replication options, but there is no way in hell I could get them to pass due diligence with the kinds of CTOs I have to argue with regularly.
SQLite is incredible for keeping it light & simple, and we still advocate for this where it fits the overall technical strategy. In terms of performance, before you reach a certain breakpoint, it is very hard to beat SQLite. You have to do it wrong on purpose to make it slower than a single node hosted solution on equivalent hardware.
Handles tens of thousands of requests a day very smoothly! :)
As an aside, has anyone tried using a RAM-disk as the storage medium for SQLite DB files? We've started experimenting with it lately and results have been promising!
- Local storage for a mobile/desktop app, instead of a DIY file type.
- Local cache storage for a distributed system, instead of a DIY file type.
There's plenty of free DBaaS these days that are frankly incredible and FREE. I work for one, so might sound like I'm chilling that space. But I recently went to work for one exactly because I think the new offerings are an incredible leap forward in developer productivity, and there's a lot of cool stuff to be done here. I could instead have gone to work for one of those who are working on SQLite, but I just don't believe they're real products for real web workloads. They're a marketing catch for curious learners and experimenters. I respect the intellectual curiosity but it's not how I would build anything.
Heroku was free for 15 years!
Personal side projects are the most obvious example.
I do a lot of work in journalism. Newsrooms don't have a budget for maintaining interactive apps they built for a story that ran ten years ago, so they often made the sensible (at the time) decision to deploy them as a free, scale-to-zero Heroku instance.
Those apps are all gone now, thanks to Heroku bait-and-switching.
Many of those same newsrooms were paying customers of Heroku for other purposes!
Microsoft Access since at least 30 years? MdB file? Accessible with various dB APIs? And wasn't dBASE also the same thing?
I get SQLite for mobile and toy projects but really, a PG set up in docker-compose.yml in your project is the easiest thing to do these days.
Also, about PG: I run each project in a different port. Not defaulting to PG's default 5432 makes it able to easily run several projects at once without clashing, just like you would with several SQLite files. I don't really see any drawbacks in using a little docker compose for your local dev set-up.
I spent a little time trying to learn docker fundamentals and it was so obtuse and broad that it really scared me off.
I like containers in theory, but I'm not huge on the overhead of VMs, and I'm leery of getting such a complex tool involved with my deployments.
For something as simple as having your own little DB side by side to your project, it is really not that complicated. A 5 line docker-compose.yml gets you a fully encapsulated PG instance on a custom port. One per project.
I hope you give it a try someday if you get the chance - also, it's a process in a "jails"-like environment, not a VM, not another full blown kernel running - so really the performance overhead is minimal to none - just like if you were running PG "locally" (well, you actually are)
You’re going to end up creating a cobbled mess and were better off using a server side database
"SQLite is a sql-based database with a particularly unique feature: the entire database is in a single file."
Digital Equipment Corporation (DEC) developed a SQL database known as Rdb for VMS. It could store everything in a single file. Oracle bought it, and maintains it here:
https://www.oracle.com/database/technologies/related/rdb.htm...
SQLite's ATTACH command allows the spanning of files. Be careful how you use it in WAL mode.
"SQLite being a file on disk does make connecting from external clients effectively impossible."
SQLite does support access over NFS and SMB, unless you are in WAL mode.
For Deno Deploy, they went in a different direction using FoundationDB, even though they use SQLite for Deno itself.
(It’s also true that some data centers have machines with no local storage. They use network storage for everything.)
SQLite, for all it's features, has some pretty significant limits. In my case, I needed better date/time and decimal type handling than what's on offer in SQLite. I still test my code against SQLite because that is a useful way to lint my data model—plus it's nice for small-scale proof-of-concept deployments—but I expect deployers to use PostgreSQL or MariaDB in production.
Edit: I've had a chance to read the article. The comment about "zero latency" is wrong because it ignores disk I/O. And yes, I know SSDs are super fast. Still not zero. The bit about Docker Compose rings hollow. It sounds like they need to learn how to use multiple Compose files, e.g., https://mindbyte.nl/2018/04/04/overwrite-ports-in-docker-com.... As for development and testing, mock database setup should be handled by your unit test framework. I'm familiar with pytest, which makes testing the same model or ETL across multiple database fixtures very, very easy. SQLite has nothing to do with that.
Its a similar take to a lot of the negativity surrounding Next 14's stabilized server actions. The negativity is academic; the productivity is industrial.
Here's my counter-hype take: SQLite actually kind of sucks. It has a place, but that place isn't significantly different from where it was five years ago despite all the "serverless read replicated VC funded hacker news hype startups" work that's happened since then.
If you're in crazy-enterprise hell-engineering; no one is going to reach for SQLite when more robust alternatives like Postgres exist.
If you're trying to get something out the door fast, you've got Postgres on Supabase, you've got MySQL on Planetscale, you've got Firebase, fifteen years of database-platform development, all of these aren't just cheap, they're free, and zero maintenance, you're not going to pay more money to do more work for a worse product by setting up SQLite on your single DigitalOcean VPS. You probably won't even reach for something like Cloudflare D1; sure, its interesting, but why? Its just Planetscale, but not better in any way and worse in plenty (its not even really much cheaper).
SQLite doesn't support alter table. It doesn't have a date-time type. It doesn't enforce columnar types outside of strict mode, which isn't enabled on e.g. Cloudflare D1. Someone stop me. Everyone says "KISS, you can get so much performance out of one application server with SQLite running locally" but you can get the same f^cking performance out of one application server, and a DBaaS, like Planetscale, and its easier, and its cheaper, and you get backups, and you get full MySQL, and you get a paved-road to actually paying them $30/mo if you need to instead of hitting a bottleneck and suddenly having to wonder how the hell you're going to scale SQLite beyond this one instance ("we need VC funding for more engineers", you'll think in that moment)
Kent's statement "However, SQLite is capable of handling databases that are an Exabyte in size" actually makes me mad. Its so ridiculously academic I'm aghast anyone takes this seriously. "Oh, well, hc-tree uses 48 bit page numbers so its an exabyte, and you'll hit other application problems first anyway". No; you'll hit problems with SQLite first, ten times out of ten, it'll be long before you hit an exabyte, it'll be before you even hit 100gb.*
I think SQLite is awesome for some things. It’s also hard to beat in some cases. But if you use it in a non-ideal situation, you need to know when, why, and what your refactor path will be to get to the right technology. And there should be a good reason for doing that, and I can’t think of one that makes sense.
The teams I helped often said something to the effect of “SQLite is great because it got us this far!”, and while I appreciate the optimism, they wouldn’t have gone a shorter distance with MySQL or Postgres. They made the wrong choice, period, and lost a huge amount of productivity to it.
Yet there are people out there who have chosen something like firebase where SQLite would be perfectly fine and far simpler, too. I love SQLite, but I think it’s promoted incorrectly at times. It makes for some brutal growing pains when it’s used in the wrong places.
I fully agree that there are projects out there which don't need to grow, won't grow, or otherwise perfectly suit the use of SQLite. I've encountered a lot of cases where it was simply the wrong choice, though. Growth or no growth.
I think Kent likes doctrine and tends to encourage practices without addressing nuance sufficiently. The kinds of people he teaches aren't likely to understand where SQLite falls short, and it's also difficult to explain to people with less experience. The advice rubs me the wrong way as a result; in practice, I don't see people use SQLite properly more often than not.
I'd say the same thing about MongoDB. It gets abused like crazy. It has a great fit in some applications and I love using it when that's the case. Yet it was promoted as the easy and scalable database for ages, and I can't count how many projects I've encountered which were badly encumbered by the misuse of that database. Is it a bad database? No, it works well in the right place. In the wrong place, it's a remarkably poor database though. SQLite is much the same (though I'd argue a better piece of technology all around).
config.services.postgresql.enable = true;
And then when it made sense to use a managed Postgres instance, it was trivial to migrate.
Ironically, this is also the easiest way to set up PostgreSQL. At least in the distributions I use, it is already configured to connect locally over UNIX sockets, and all I have to do is create a system user to connect with.
PostgreSQL has a bad reputation for being difficult to set up because people expect it to be exposed on the network by default, and then they have to dig into the obscurely named pg_hba.conf and change multiple lines.
There is ongoing cargo cult wisdom that "the RDBMS HAS to be a standalone service" which is just nonsense and extremely counterproductive for the median use case.
I got jazzed up on articles like this, only to figure out the hard way that it's ill-suited to apps where concurrency matters.
Sqlite is an incredible piece of software, but it doesn't do scalable, concurrent things well.
The other thing I miss often is better support for schema migrations. For a lot of things (like adding certain constraints after table creation) the "create new table, copy data over, delete old table" workaround is needed. However, it's only a minor annoyance, and if it allows the codebase to be more maintainable, that's OK with me (given the fantastic quality track record of SQLite).
> So, can you use SQLite? For the vast majority of you reading this, the answer is “yes.” Should you use SQLite? I’d say that still for the majority of you reading this, the answer is also “yes.”
If you’re making a simple proof of concept app, a side project you just need to get working, or a toy project for learning then SQLite will be fine.
If you’re trying to learn how to build maintainable web apps for a business, getting PostgreSQL or MySQL or similar up and running shouldn’t be that big of a hurdle.
He also either doesn’t understand the limitations and complexities of using SQLite (migrations, foreign key quirks, other issues mentioned in this thread) or he’s deliberately ignoring them because it would weaken his point.
For what it’s worth, we’ve had to un-teach some of this author’s material to junior devs after they read into it a little too literally. He likes to push his way as the “right” way to do things, which can turn into a cargo cult mentality for junior devs buying his courses. He pushes his new “Epic web stack” in the same way.
While it's good to have a simple storage solution, it does have its drawbacks.
Then again, yesterday I was working a bit with MBTiles, which are SQLite databases with per-z,x,y-protobuf-blocks of vector data, and in that case it does make sense to use SQLite. Or the things browsers use them to store bookmarks and stuff.
You can enable WAL mode if you're having trouble querying them due to locking issues.
Before any DB, you're trying to just do it all in memory.
Then we get to storing for persistence and going from memory -> pickle -> disk file is pretty quick; but we know a real DB is needed.
So you deploy sqlite3 and it'll work amazingly right up until the dreaded:
SQLITE_ERROR: "database table is locked"
and now you're not on sqlite3 anymore.
Using SQLite as a backend for everything from session management to logging. No elk stack, no separate db. Just a single file.
Development is so simple so far. Maybe I’ll need to move off of it later, but who knows maybe not. And the speed of development and simplicity is what matters more now.
Is there an alternative embedded database with stronger types?
On the contrary, I think that everything should be fine with Diesel since that leverages the rust ecosystem to manage types and migrations of the sqlite database.
For inserts, sure (but then, it's straightforward to use the host language for that).
But once you move beyond simple cases, data changes over time, not atomically. It's also common for data to be distributed. Static type system can handle either of these things.
If your database can't go down for the duration of a migration (which will need more time now, to type-check all the data), and you don't have a way to ensure all clients are updated at the same time also, then you can't use static type checking like programming languages have.
On a serious note, there is definitely a time and a place for SQLite - it's a great technology - but the engineering being done to make it scalable across multiple servers etc. seems ill-placed. For applications of moderate complexity or scale, I suspect developers will quickly see issues trying to monitor and interact with SQLite in ways that they wouldn't with other tech.
A growing number of real-world production apps are running on SQLite these days, precisely because it's so mature, robust and performant at this point - and, unlike Microsoft Access, is very actively maintained.
While you can do it, especially with tech such as LiteFS, I much prefer using database systems with built-in support for replication, failover, and splitting data storage from the app server.
Zero Latency - irrelevant for 99% of use cases One Less Service - premature optimization - saves almost nothing Multi-instance replication - where is the advantage over say postgres? Database size - irrelevant for 99% of use cases today Development and Testing - no advantage here
Except in the case of sqlite, which is serverless and relying on the filesystem itself for synchronization.
In theory it's going to be worse, though if there is low contention you might have some latency advantages due to not having the server hop.
So, no, you should probably not be using SQLite.
MySQL has the excellent SQLYog which is like a better version of MySQL Workbench.
Not specific to SQLite itself, but that would enable a lot of uses for it.
You should probably be architecting your application to use the data management solution that fits your use case.
Is SQLite not ergonomic? I’ve previously played with mongodb through work and it was super simple and intuitive. But SQLAlchemy is super verbose and not at all intuitive despite being intellectually easy to grasp. It feels to me like it has decades of cruft built into the syntax and while I can appreciate that it means options it has not at all been nice to work with. I understand the issues with mongodb, but I managed to get it working in 30 minutes. Meanwhile, I’m 3 days into trying to get SQLAlchemy working. It feels to me that perhaps there may be a hole in the market for “GPT” friendly databases. Because SQLAlchemy is so verbose it kills the context window of GPT lmao.
Most important thing is to get your actual application idea functioning quickly. Sqlite is fast to work with and low maintenance (your db is one file), and later if you found you made a mistake (too slow, no support for advanced sql features you need, you want concurrent access across multiple boxes) you can write a program to migrate your data in a single afternoon. Wasting time on more complex solutions early on is a pitfall that has stolen years of project time from me, just avoid misusing dependencies too badly and get your thing working first.
My https://sqlite-utils.datasette.io library might be a better fit for you. It's a much thinner abstraction than SQLAlchemy.
Or just use sqlite3 from the Python standard library directly.
Does not handle concurrent writers
SQLite can apply tens of thousands of small writes a second, so queueing them up works fine for all but the absolute largest scale applications.
https://github.com/eatonphil/databases-intuition#go-mattngo-...
Not quite impossible (if you design an appropriate VFS), but it doesn't work as well as a real client/server database, since SQLite is not designed for this use.
> SQLite does not support enums which means you're forced to use strings. ... The main drawback to this is when it comes to the typings for the client which doesn't allow you to ensure all values of a column are only within a set of specific possible values for the string.
In SQLite you can use CHECK in a table definition to require all values of a column within a set of specific possible values.
However, I think there are some actual flaws in SQLite, such as:
1. It uses Unicode (but only partially; case-insensitive is only with ASCII). This can make it less efficient than it should be if you are dealing with ASCII only, and makes it difficult (and somewhat inefficient) to deal with non-Unicode text. Although it is possible to store non-Unicode text in a TEXT value or in a BLOB value, and to use CAST everywhere or to override the built-in functions with your own, neither is really ideal, for several reasons (including causing some optimizations to not work, making your code even longer and less efficient, etc). It is also possible to patch SQLite to do this, but then if you upgrade, you must patch that one too.
2. The URI file name mechanism is a bit messy. Using a separate argument for the parameters might be less messy.
3. There is no standard way to define the time zone used for functions that deal with local time. (You can override the function to find the current time by the VFS, but overriding conversion to local time is only possible by use of an undocumented function (which is not guaranteed to stay the same in future versions).) Better (in my opinion) would be to add such a function into the VFS (since the current time function itself is in the VFS anyways, so it should go together).
4. It might have been better to store the journal in the same file as the database. One idea how this might be done: Set the read version to 1 and the write version to 3. If you begin a write transaction, lock the database and then make copy-on-write of any modified pages into free pages, but do not change the references to the free pages in the header (so other programs still believe they are free). To commit a transaction, make a table of the required page linking changes in the file, and then temporarily change the read version to 3, and then change all of the links (so that the pages containing the old data would now be considered free), and then change the read version back to 1 (the write version will still be 3) and then unlock the file. To roll back a transaction, simply unlock the file (which will be done automatically if the program crashes); there is no need to write or delete any file. However, one disadvantage of this method is that the database file is now up to twice as big (depending on how many pages need to be modified for each transaction), and there may be other disadvantages too.
5. You need file names (which is necessary for separate journal files, anyways). However, in my opinion it might be better to pass file descriptors or stream objects. (SQLite and some other libraries annoy me that they do not do such a thing, and require file names.)
However, I always design with the intent to use PostgreSQL or some other database. Sometimes with the intent of using two different data stores in the scaled up version. For instance an SQL database for stuff that has low volumes, and perhaps a time-series database for the high volume stuff.
This is why I always design a persistence interface. An internal persistence API layer inside the application. I also write the test suite to this interface. This makes adding support for other databases much easier later.
Note that I say "add", because I usually keep the SQLite support after I've added support for the databases I want to use in production. Keeping SQLite is extremely useful for when people want to do integration testing or even when they are learning to use your server/service. Rather than having to fire up a database, people can just run with the built in SQLite. You download and run the binary and stuff just works. It also makes cleanup a lot easier. Not least if you run an in-memory database (use ":memory:" as the DB spec).
In the persistence layer I tend to avoid using too much abstraction. The interface is my persistence abstraction. I don't need more layers. I think ORMs are incredibly limiting and unnecessary. However, I do use sqlx (Golang) to make the SQLite implementation of the persistence layer less verbose (faster turnaround when figuring stuff out). I usually use it for PostgreSQL too since the performance penalty usually isn't significant enough to matter. (If you haven't used sqlx before: it is essentially like using the DB driver directly, but with a lot less boilerplate). sqlx is trivial to just rip it out if you think it introduces too much overhead.
Usually you will know ahead of time when you potentially need more than one data store. I tend to use SQLite for storing everything during initial development, but the API is usually designed in a way that doesn't make it awkward to split the store in two or more domains.
I sometimes write storage API middleware. For instance statistics, adapters that can combine different permutations of different storage domains, sharding, throttling, specialized logging etc. I've done ACLs, failover, housekeeping middleware as well, but usually they do not belong in persistence middleware.
I never put business logic or "cleverness" in the persistence layer. It should worry about storing and retrieving data.
You can probably use SQLite as your database for much longer than you think. And some projects honestly will never need more than SQLite. However, I think that if you start doing sharding, replication and dealing with situations where throughput and concurrency is high, you really want to use something a bit more beefy. You have to think about how much time you'd spend getting SQLite to do things versus how much effort it is to just fire up database instance(s).
http://www.paulgraham.com/submarine.html
Which makes sense given they are a Y Combinator company.
LiteFS isn't a Fly.io feature; it's Ben Johnson's open source project, and it runs just fine on GCP and AWS. Try it out!
Thanks for mentioning the LiteFS origin, Ben Johnson of BoltDB writes good stuff