I use SQLite frequently, often without thinking about it, but every once in a while I stand back and reflect on what an amazing piece of software it is. An RDBMS without a standalone database engine, delivering rock-solid performance, through exceptional code quality.
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.
The code quality of SQLite is often understated to a hilarious degree. The project has four independent test harnesses totally 644x as many lines of test code as there is actual code, with the final test count in the millions. This includes the things most of us do, fuzz tests, regression tests, performance tests, as well as out of memory tests, IO failure tests, power loss tests, and more.
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.
I recently learned that Expensify build a database server (BedrockDB) on top of SQLite. At first I was like "that's absurd!" but after reading a bit more and realizing SQLite's "library" nature could just be a building block, it seems less absurd.
One of the notable aspects of SQLite as a open source project is being open source but not open-contribution. drh put it in public domain, and doesn’t accept outside contributions to prevent it from being contaminated in any way. In an open source landscape where bazaar seems to have mostly won, SQLite remains one of the (last?) cathedral strongholds, and serves as a good reminder to aggressive contributors that open source maintainers don’t have to accept or even consider their PRs.
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.
As I understand the reason is that you can pay for a commercial licens to SQLite, meaning DRH has to keep track of contributer agreement. Having signed contributer agreement for any minor PR would be a major hassel.
Edit: well apparently that basically the text you linked to.
If you contribute to any open contribution project by Apple, Google, Microsoft, Facebook, etc. you’ll have to sign a CLA first, however minor the PR is. Same goes for some non-corporate projects, e.g. CPython. There are readily made forms and enforcement bots for CLAs at least within the GitHub ecosystem, so you can even deploy them to your one man project within an hour; it’s also not hard to write the tools from scratch, especially considering that drh wrote his own SCM. Therefore, keeping track of CLAs is pretty far down the list of reasons not being open contribution, if it is on the list at all.
SQLite is a much younger project than I thought it was. Given the ubiquity, and capabilities that it provides, it's impressive. On the other hand, Linux and Python are 29 years old.
Postgres, in its current form is ~23 years old. It started in the 1980s.
I’m also surprised — it feels both like a long time and yet not that long at all. SQLite felt like it had been around forever when Rails adopted it to make development environments simpler, and I remember finding it easy to use when writing iPhone apps for the first time. It was remarkably stable at an early age, it seems.
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
To be honest I’m quite surprised it’s already 20 years old.
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.
Literally the only criticisms I have is that sqlite is actually dynamically typed, not statically as you would expect! The schema only defines a field's "recommended" type.
The documentation is very explicit about that[1], 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 find the type system unique compared to other RDBMS systems but I too wish that constraint integrity was enforced by the engine. I don’t want to give up on variant types (call them ANY types) as they can solve some interesting problems that fall in the flexible schema space but I don’t want to go through hoops to constrain something like a fixed length binary column to hold a UUID.
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.
I heard the creator Richard Hipp speak at a conference a few years back and I absolutely loved listening to him . He is humble and seems a bit of a throwback compared to Silicon Valley tech moguls .
May the genius and simplicity of SQLite shine on .
No it was an attempt at supporting SQL in the browser.
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.
Pieter Levels (levelsio) of Nomadlist famously gets mocked for using a single index.php and sqlite but does 5 figure MRR. https://nomadlist.com/open
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.
There has always been a case for server-side embedded databases for app servers. It can be an in-process library like SQLite or Apache Derby, but databases like SQL Anywhere and SQL Server also have local IPC protocols that achieve the same effect.
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.
SQLite is totally capable of delivering a good performance. The main problem is that you have to take care of efficiently opening and closing the file yourself. Naively doing it for every user/transaction will quickly put an end to your scalability plans and in fact, it is something you get with a DB server applications for free.
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.
I was about to ask what the use-case would be, since I assumed server SQL databases would use Paxos or Raft or something, but apparently I was wrong! I guess durability is done through persisted volumes, storing tables in files and being deliberate about journal structure and fsync?
In that kind of world I guess sqlite is as good a choice as any if you don't need a SQL endpoint.
SQLite is for use cases where a dedicated DB is overkill/impossible. Considering the limitations it has, it probably isn't usable for most production DBs.
Readit News
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.
https://sqlite.org/copyright.html
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.
https://sqlite.org/forum/forumpost/a48e4672da
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.
EDIT: Grammar.
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[1], 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.
https://github.com/sql-js/sql.js
https://www.w3.org/TR/webdatabase/
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.