SQLite Turns 20 | Hacker News Reader

SQLite Turns 20

sqlite.org

186 pointsmarcobambini56 comments

There was a considerable SQLite thread less than a week ago: https://news.ycombinator.com/item?id=23281994.

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.

Pretty impressive it's so flexible.

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.

https://sqlite.org/copyright.html

Has there been much agitation for (or serious attempts at) forking SQLite? That's probably the real test of the stewardship of an open source project.

Nothing springs to mind, as of yet.

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.

I believe it's the same with lua

Turns out the company that employs all SQLite devs is based in NC! I wonder if the devs are based here, too.

The primary author is: https://en.wikipedia.org/wiki/D._Richard_Hipp

If you hear him on a podcast or speaking engagement he's also got the accent to match.

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

Typo. 33 not 23.

What amazes me the most, it's a work by mostly 2-4 long-standing developers, with about 40 contributors through the history.

On the other hand SQLite is being used by thousands of devs and likely billions of users (mobile at least).

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.

EDIT: Grammar.

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 .

I quite liked The Changelog's interview with him too: https://changelog.com/podcast/201

Where can you use SQLite?

Embedded Systems: Yes

Raspberry Pi : Yes

Mobile Apps. : Yes

Desktop Apps : Yes

Browsers : No

Servers : Yes

Supercomputers : Yes

WebSQL was an attempt at supporting sqlite in the browser. Now, you can use it via wasm!

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.

Browsers: yes

https://github.com/sql-js/sql.js

It's a sqlite browser port which doesn't persist data. I think that's kind of essential for a database.

Sure it does! You can `POST` the buffer that backs the database to your favorite blob store, and read it back when you're done. You can write to it as much as you like when the page is active.

So every time somebody loads up your website, you read a text blob, initialize a new database and populate it?

https://sqlite.org/about.html

"Think of SQLite not as a replacement for Oracle but as a replacement for fopen()"

You can actually partially persist data on the client side in SQLite, as you can embed the data in you html, which is exactly what we do with our product

There was an attempt to add SQLite to browsers at some point, but it was abandoned because we would never get two independent implementations.

https://www.w3.org/TR/webdatabase/

You can thank Mozilla and Microsoft for that. They had the swing votes on that issue - https://nolanlawson.com/2014/04/26/web-sql-database-in-memor...

That was a brilliant read!

Has anybody used Sqlite in a server side, production level capacity....

I know it works great on embeded cases, but how about server side with a decent user load?

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.

Not true. https://blog.expensify.com/2018/01/08/scaling-sqlite-to-4m-q...

Some of the limitations make it a no-go for me at least [0]

[0] https://www.sqlite.org/omitted.html

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.

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.

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.

If you do mostly reads, it works great. I use it on http://www.wikdict.com for years without problems. I for e have very much traffic, though.

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.

I always love reading the comments section of an SQLite post to hear about all of the incredibly unique ways people are using it.

While doing small project sqlite is a very good tool. Thanks to sqlite team for making such a great software.

I believe the SQLite license is one of the greatest contributions in technology. Hope to see it on something game changing one day.

I think the iPhone uses SQLite.

It does. It's an implementation detail below Core Data. I'm not sure if you are allowed to send raw queries to it.

I believe that app developers who want to interface directly with SQLite have to use something like FMDB.

https://github.com/ccgus/fmdb

They have pages on this.

https://www.sqlite.org/famous.html

https://www.sqlite.org/mostdeployed.html

I love how they have a cute logo for the Flame malware nestled between Facebook and GE.

Again I love SQLite! https://github.com/zubairq/pilot Would not be possible without it! Why? SQLite works on the server, in Electron (barely), and purely client side on the browser, and is fast!

Cool to see a well-loved, gold standard of an open source project reach a milestone like this. That said, the few cherry picked commits on that page were not particularly interesting (atleast to me, not a db expert by any means), and the color coding is inscrutable.

It's hard to guess what's sort of curiosity was leading you, but there's a mirror (unofficial, yet reasonably current) on GitHub.

I didn't understand your reply at first, and then it dawned on me that the linked page wasn't handwritten to celebrate the milestone (which is what i thought at first), but rather just today's version of a programmatically generated "today in history" type page. Ignore my misguided complaints :)

I just checked and I have been using it since 2003...

What a noble piece of software.

I would be proud to make something a hundredth as useful and prevalent as SQLite.

Reply on news.ycombinator.com