
SQLite Turns 20 - creolabs
https://www.sqlite.org/src/thisdayinhistory?today=2020-05-29
======
dang
There was a considerable SQLite thread less than a week ago:
[https://news.ycombinator.com/item?id=23281994](https://news.ycombinator.com/item?id=23281994).

------
deepspace
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.

~~~
ashtonkem
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.

------
oefrha
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](https://sqlite.org/copyright.html)

~~~
sjwright
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.

~~~
justinclift
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](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.

------
sargun
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.

~~~
lstamour
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/](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](https://caniuse.com/#feat=sql-storage)

------
anyfoo
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.

~~~
sradman
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.

------
nus07
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 .

~~~
mercer
I quite liked The Changelog's interview with him too:
[https://changelog.com/podcast/201](https://changelog.com/podcast/201)

------
me551ah
Where can you use SQLite?

Embedded Systems: Yes

Raspberry Pi : Yes

Mobile Apps. : Yes

Desktop Apps : Yes

Browsers : No

Servers : Yes

Supercomputers : Yes

~~~
wayneftw
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...](https://nolanlawson.com/2014/04/26/web-sql-database-in-memoriam/)

~~~
me551ah
That was a brilliant read!

------
ardit33
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?

~~~
sk0g
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.

~~~
marvel_boy
Not true. [https://blog.expensify.com/2018/01/08/scaling-sqlite-
to-4m-q...](https://blog.expensify.com/2018/01/08/scaling-sqlite-to-4m-qps-on-
a-single-server/)

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

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

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

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

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

~~~
DenseComet
They have pages on this.

[https://www.sqlite.org/famous.html](https://www.sqlite.org/famous.html)

[https://www.sqlite.org/mostdeployed.html](https://www.sqlite.org/mostdeployed.html)

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

------
zubairq
Again I love SQLite!
[https://github.com/zubairq/pilot](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!

------
melq
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.

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

~~~
melq
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 :)

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

------
ekvintroj
What a noble piece of software.

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

