It scales unexpectedly well with large data sets, and the performance is way beyond what you would expect from a library (as opposed to a 'real' database).
Hats off to the developers for creating and maintaining this amazing software.
Of course, it’s also easy to forget that the original application of SQLite was guiding missiles, so it has to work. Well, at least from the operator’s perspective.
Pretty impressive it's so flexible.
However, with the very latest release series (3.32.x), one of the pieces ("SQLITE_HAS_CODEC") used by third party encryption libraries (eg SQLCipher, various others) was removed without notice.
There does seem to be a way forward, via reimplementing the encryption as a SQLite VFS. If that didn't exist though, then some kind of forking would be on the cards.
Edit: well apparently that basically the text you linked to.
If you hear him on a podcast or speaking engagement he's also got the accent to match.
Postgres, in its current form is ~23 years old. It started in the 1980s.
I still think it’s a shame we never got SQLite into the browser because every browser implemented the same backend and therefore “it wasn’t a standard” — https://www.w3.org/TR/webdatabase/ (note the box in red) Chrome still supports it, but not in “modern” places like web workers: https://caniuse.com/#feat=sql-storage
The first time I ever encountered SQLite was when poking through what made the very first iPhone tick, “way back” in 2007 (a whopping 13 years ago).
Before then it had never occurred to me that one might use a database-as-an-executable as the generic information storage back-end for pretty much any application... I thought of SQL and databases as enterprise-type deployments and thought of applications as having custom data formats.
Looking back, Wikipedia tells me that Apple had made CoreData available with Mac OS X 10.4 ‘Tiger’, released in mid-2005, so I wasn’t exactly up-to-date at the time... but still, it feels like a remarkably modern development. And an impressive one in any timeframe.
On the other hand SQLite is being used by thousands of devs and likely billions of users (mobile at least).
The documentation is very explicit about that, but it might still come unexpected, be forgotten, or, worst of all, cause problems that manifest much much later than if they would have at insertion. I remember one particular very ugly episode of that.
But that is all. As a whole, sqlite is amazing, rock solid, amazingly documented, and my immediate go-to if I need a (serverless) database. I don't remember encountering a bug, in mission-critical software.
I understand the historical reason; it allows for almost any flavor of SQL to behave as expected. SQLite is often used in a developer sandbox to work on a copy of an existing enterprise SQL engine. A workbench SQL if you will.
As mentioned in last week’s SQLite thread, it doesn’t have a storage type for datetime. I also miss exact NUMERIC(p,s) types but I don’t want to expand the storage classes, I just wish the Domain/Type constraints could be optionally enforced in a simple way.
Embedded Systems: Yes
Raspberry Pi : Yes
Mobile Apps. : Yes
Desktop Apps : Yes
Browsers : No
Servers : Yes
Supercomputers : Yes
It fell through because everyone choose SQLite as the implementation, and having one implementation means it's quirks would be enshrined as the standard.
Really speaks to SQLite's quality that major vendors would just reach for it without considering making their own.
"Think of SQLite not as a replacement for Oracle but as a replacement for fopen()"
I know it works great on embeded cases, but how about server side with a decent user load?
He used to get a lot of flack for it and i never understood why since you use whatever gets the job done. Too many people doing the "gatekeeper" thing.
App Embedded databases are not the main use case for non-SQL transactional engines like Berkeley DB and Microsoft ESE (used for ActiveDirectory and Exchange Server).
SQLite is a better server-side engine than the critics think but it is ill suited with the default options. WAL mode and Shared Cache mode must be enabled for decent performance. The popular runtime libraries for SQLite don’t expose the required C API calls nor even the enhanced functionality of the FILE URL optional connection string.
Even when configured properly, concurrency in SQLite is limited for apps that have a hot row or page. Row level locking or snapshot isolation are sometimes a huge help but fast local reads go a long way in alleviating the need. The main obstacle, however, is that the popular web frameworks assume a wire protocol SQL engine running on a separate tier like Heroku’s 12-Factor App architecture.
That said, it's strength is its simplicity and not its scalability. So if you plan to run a database server cluster, you probably want to look for other technologies. But as long as you are on a single machine, your SQLite performance problems are probably a case of 'you are doing it wrong' ;-)
So when you are talking about server-side, you probably don't want to limit your technology choice to a database engine that can not be clustered. However, if you are offering software which should be simple to install on a server (e.g. Nextcloud), providing an SQLite option is probably a good idea, because nobody has to care about DB-Server administration and connection setup.
In that kind of world I guess sqlite is as good a choice as any if you don't need a SQL endpoint.
I believe that app developers who want to interface directly with SQLite have to use something like FMDB.