Honestly in that case you should just use Postgres locally. SQLite was never meant to be a stand-in for a true RDBMS. As they say, it's intended to be a replacement for fopen(). The fact that it works for local development is a fluke.
Yeah it does a lot more than fopen(). So does SQLite. It's still, bottom line, just secondary storage with a fancy API.
Not a dependency in the sense of not requiring a specific version of a Postgres db provider, or a specific version of a Postgres itself, or able to do any work without Postgres existing? Laughably no.
DB agnostic software is very uncommon, even though ODBC is well-established and mature.
I haven't spoken to too many folks who've had to make this decision, but off the top of my head I think there's a number of factors that aren't obvious at first glance that factor into this equation:
1. You need authors of queries to understand the least common denominator of supported SQL features and syntax (probably ANSI SQL, but there may be cases where it isn't)
2. Such projects would need to test or otherwise verify that all SQL queries execute in the ways they're expected to. This can be tricky when you have regressions in query performance from one database flavor to the next.
3. Your application can't have features that are made or broken by features specific to a particular database. I can see this being a very limiting constraint in a world where business intelligence and data analysis at scale has become a the norm.
Are there other factors here that would compel projects to stick to something like ODBC (or your database abstraction layer of choice) rather than against it?
For me it's about using the full power of my underlying technologies. Just like i don't restrict myself to a subset of C that would compile with any C compiler ever, i don't restrict myself to generic SQL.
It's designed to be embedded into software and used to replace raw files. That's why the database is a single file, which is something that is a feature in every situation except if you have a server doing many concurrent writes.
The only reason that SQLite even works as a stand-in for an RDBMS is because they did such a good job implementing the SQL standard.
Testcontainers are the proper solution for automated tests, but it is not trivial to set up CI as AFAIK it requires access to the Docker socket.
It keeps databases in a single local file like SQLite, and it supports PostgreSQL syntax (not sure which version). It does a load of funky stuff to be really fast at analytics workloads, but I would guess it's fast enough at transactional workloads for small-scale use.
This is not a challenge commonly faced on on the enterprise server side, hence why many of us cannot imagine a use case for SQLite. Our workloads involve multiple readers/writers for both scaling and availability. An RDBMS cluster is the obvious choice.
But for mobile/embedded, all the data is local to a single device and commonly also a single process. In this case, an RDBMS would add unnecessary overhead. Additionally, there's desktop software that has to work with relatively large datasets, which require reliable persistence and efficient searching/reading. Adding an RDBMS would complicate installation and support.
Further, researchers and data analysts benefit from SQLite databases when the data is too large to hold in memory, yet not large enough to warrant a proper data warehouse. Even ~100 MB datasets can benefit from SQLite if you're performing a lot of random writes/reads or want to execute complex queries. There are other alternatives such as Apache Arrow, but SQLite is tried and tested option. It can be populated and queried similar to SQL-based data warehouses, and it also includes secondary indexes, an efficient query engine, and fast random writes with durability and atomic transactions.
There's a reason why SQLite can reputably claim to be the "Most Widely Deployed and Used Database Engine" with an estimated one trillion SQL database in use. [0]
Sqlite is a beast, there's a lot you can do with it. But there's no shared buffer cache or strategy for keeping disk pages in-memory across transactions and files, so you pay for it with random disk I/O access proportionate to your client load. But for many workloads, it's a really low-maintenance option with lots of low tech and effort back-up/disaster recovery options and depending on how chatty your clients are you can get a lot out of modern hardware/clouds.
You could also get clever, and provide pure in-memory access when pinning clients to hosts and treat it like a write-through cache.
A common use case is using SQLite as a local stand in replacement for "whatever production database" people are using.
Commonly PostgreSQL, but not always. So, my idea was just a way to make that work better, nothing more.
I have small scripts that do things like send an email on a given business event. These scripts pull data from the main ERP. To keep the system upgradable we avoid changes to the ERP's database. So these scripts need their own DB to store state, such as when the email was sent. At first this involved one Postgres instance with databases for each script or application. If that Postgres server moves IP or has a version upgrade often every script or app needs a config change or library upgrade. Everything gets coupled together. By using an SQLite DB per script a range of problems go away. Of course anything that has multiple users or needs to be reported on has to go into a central DB but a surprising amount can happly run on it's own independent SQLITE DB.
To move hosts or backup the application and DB you take a copy of the folder. It's so easy.