
Work on SQLite4 has concluded - joewalnes
https://sqlite.org/src4/info/c0b7f14c0976ed5e
======
gregmac
The relevant change:

> This directory contains source code to an experimental "version 4" of SQLite
> that was being developed between 2012 and 2014.

> All development work on SQLite4 has ended. The experiment has concluded.

> Lessons learned from SQLite4 have been folded into SQLite3 which continues
> to be actively maintained and developed. This repository exists as an
> historical record. There are no plans at this time to resume development of
> SQLite4.

[https://sqlite.org/src4/artifact/56683d66cbd41c2e](https://sqlite.org/src4/artifact/56683d66cbd41c2e)

------
Scaevolus
For context, SQLite4 explored reimplementing SQLite using a key-value store on
log-structured merge trees, like RocksDB and Cassandra.

I'd be interested to hear why they stopped. Presumably reimplementing SQL on a
KV store was seen as not worth it, when applications that are satisfied with
an embedded KV store backend (which is much faster and simpler to write!)
already have many options.

~~~
sliverstorm
Everything I hear about SQLite3 always suggests that, essentially, it is
considered "done". It does what it is supposed to do with great performance.
There is nothing major left to do. If it doesn't meet your needs, pick a
different SQL database.

Which, while a totally alien concept in the modern software world, is actually
a pretty cool thought.

(I'm sure under the hood bugs are getting fixed and all)

~~~
ianamartin
Well, obviously they should rewrite it in Rust.

~~~
greenhouse_gas
From the canonical source (as in, [Richard
Hipp]([https://en.wikipedia.org/wiki/D._Richard_Hipp)](https://en.wikipedia.org/wiki/D._Richard_Hipp\))):

Rewriting SQLite in Rust, or some other trendy “safe” language, would not
help. In fact it might hurt.

Prof. Regehr did not find problems with SQLite. He found constructs in the
SQLite source code which under a strict reading of the C standards have
“undefined behaviour”, which means that the compiler can generate whatever
machine code it wants without it being called a compiler bug. That’s an
important finding. But as it happens, no modern compilers that we know of
actually interpret any of the SQLite source code in an unexpected or harmful
way. We know this, because we have tested the SQLite machine code – every
single instruction – using many different compilers, on many different CPU
architectures and operating systems and with many different compile-time
options. So there is nothing wrong with the sqlite3.so or sqlite3.dylib or
winsqlite3.dll library that is happily running on your computer. Those files
contain no source code, and hence no UB.

The point of Prof. Regehr’s post (as I understand it) is the the C programming
language as evolved to contain such byzantine rules that even experts find it
difficult to write complex programs that do not contain UB.

The rules of rust are less byzantine (so far – give it time :-)) and so in
theory it should be easier to write programs in rust that do not contain UB.
That’s all well and good. But it does not relieve the programmer of the
responsibility of testing the machine code to make sure it really does work as
intended. The rust compiler contains bugs. (I don’t know what they are but I
feel sure there must be some.) Some well-formed rust programs will generate
machine code that behaves differently from what the programmer expected. In
the case of rust we get to call these “compiler bugs” whereas in the
C-language world such occurrences are more often labeled “undefined behavior”.
But whatever you call it, the outcome is the same: the program does not work.
And the only way to find these problems is to thoroughly test the actual
machine code.

And that is where rust falls down. Because it is a newer language, it does not
have (afaik) tools like gcov that are so helpful for doing machine-code
testing. Nor are there multiple independently-developed rust compilers for
diversity testing. Perhaps that situation will change as rust becomes more
popular, but that is the situation for now.

[https://blog.regehr.org/archives/1292#comment-18452](https://blog.regehr.org/archives/1292#comment-18452)

~~~
Santosh83
Even if Rust is at parity with C in terms of tooling and ecosystem (which it
probably will be in a few short years), SQLite is probably not high on the
list of software to rewrite in Rust, given its fairly high quality. Let's
rewrite the vulnerability-ridden ones first.

------
gtrubetskoy
I have to say I learned more about databases from just studying SQLite code
than any book on the subject. I've bought a bunch of books on DB's, some very
expensive ones, but I wish someone pointed me to SQLite source early on.

To internalize it better I invented a "project" for myself -
[http://thredis.org/](http://thredis.org/) which was (and is, but I'm not
maintaining it) a Redis/SQLite hybrid. It was fun to hack on.

Another invaluable source of DB internals information is PostgreSQL. Both
projects have amazingly well written and detailed comments.

------
bane
SQLite is one of those awesome things that's the exact opposite of magic. It's
beautiful, jaw dropping, engineering that exercises so many technical muscles.

The number of oddball, often critical, places where I've found SQLite being
used would defy belief. As far as I can tell, the "expected" place for SQLite
to work seems to be almost anything that's not your normal dB driving some
web-based CRUD app...all kinds of embedded systems, easy to manipulate in-
memory scratch pads for bioinformatics, lots of data analysis tools in mobile
communications.

It's so good, and so obvious, that I think sometimes it makes other tools that
might be simpler fits for many use-cases less likely to be used, like leveldb.

~~~
hasenj
> your normal dB driving some web-based CRUD app

That can totally be handled with SQLite.

~~~
sametmax
I have several crud apps running sql. With moderate write load and a good
concurrency write error handling code it works very well. Good when the
product size is not worth a postgres full blown setup.

~~~
raphaelj
I backup this. I've been using SQLite for some low to moderate load CRUD apps,
and this always worked like a charm. SQLite also make backuping, testing and
moving apps so much easier.

~~~
ngrilly
How do you workaround the fact that SQLite only supports a single writer? For
example, your app could be blocked when you run a long operation like creating
an index.

~~~
sametmax
For moderate load: just catch the exception and try again. It will rarely
happen, and when it does, you can easily recover from it since your site is
not hammered.

For bigger load, have a worker that does the writes with a queue.

~~~
ngrilly
Hello Sam and/or Max,

I'm French too and I read your blog sometimes ;-)

Is the following what you suggest:

You create an index, and the index creation takes 30 seconds. Then instead of
writing directly to SQLite (which won't work since the index creation blocks
other writers) you suggest to store the write in a queue (for example another
SQLite database for durability), and have a worker apply the write to the main
database when the index creation is done?

------
assface
Richard Hipp has said that they have signed contracts to support SQLite3 for
35 years. SQLite4 is never going to happen.

~~~
oh_sigh
What kind of companies sign 35 year support contracts?

~~~
dflock
I read somewhere the other day that sqlite is used somewhere in Airbus A380
passenger jets, and Airbus have a support contract for it.

~~~
nandhp
It's the A350:
[https://sqlite.org/famous.html](https://sqlite.org/famous.html) And a video:
[https://twitter.com/copiousfreetime/status/67583454330408140...](https://twitter.com/copiousfreetime/status/675834543304081409)

------
sharpercoder
Every time I see something about sqlite, I become sad. It reminds me of the
failure of the w3 standards comittee to accept it as web standard. They
rejected sqlite because no competing implementation existed. Furthermore,
"public domain" license of the software was also a hurdle, iirc.

~~~
dude01
We literally lost several years for web app advancement because of that.
Reading the decision making, it seemed like overly-legalistic engineers, but
I'm open to conspiracy theories that this decision enhanced mobile app store
adoption.

~~~
smitherfield
No, it was Mozilla who killed it[1] over Apple and Google's strong objections.

[1] For pretty much complete nonsense NIH and standards-lawyering reasons.

~~~
bambax
I too am so disappointed SQLite isn't available in modern browsers (esp. since
it was, for a time). But can't it be resurrected? Couldn't we set up a
petition somewhere to bring it back?

~~~
dspillett
It is a bit of a "would you really do that in production?" type hack, but
there is a pure JS compile of SQLite3 that you could use:
[https://github.com/kripken/sql.js/](https://github.com/kripken/sql.js/)

Possible problems:

* Nearly half an MB of library to add to your project which might be a concern on mobile (~2.1MB uncompressed.

* It handles the whole DB in memory rather than trying to use any sort of local storage as a block store, which pumps up memory use (again, mobile user may particularly find this an issue) and to persist data you have to pickle the whole DB as a single array (which could be a significant performance issue if the data changes regularly and is not very small) and reload it upon new visit.

* Concurrency between multiple tabs/windows is going to be an issue for the same reason.

------
hoodoof
The biggest thorn I found working with sqlite was the lack of ability to
modify columns with ALTER TABLE which was a real pain.

Doesn't look like this is fixed in sqlite4 though...

~~~
kamac
Same here. Had to switch to dockerized mariadb for local tests, because
migrations wouldn't work.

~~~
jey
Wouldn't you want your tests to be run against the same DB family (and
version) as production anyway?

~~~
mst
Depending on the situation, it can be well worth it to have your test suite
run against SQLite while doing active development to be able to iterate faster
and then run it again against the target database before pushing the branch.

Where possible I much prefer to spin up a version of my target database in a
tempdir but "faster test cycles" is sometimes worth accepting the trade-offs.

------
Afton
Would love to hear some of the lessons learned...

~~~
k__
Their main idea was that B-Trees are slow and LSMs are fast.

This was a partially right assumption, but only for writes.

If you write something in a DB you check some constraints and those checks are
reads.

So most DB writes come with a bunch of reads.

The reads were slower with the LSMs, so the B-Trees performed better in "real
world" writes (which come with reads) and LSMs only performed better in
"artificial" writes (without reads).

------
NelsonMinar
There's an excellent ~80 minute podcast interview with the sqlite author here:
[https://changelog.com/podcast/201](https://changelog.com/podcast/201)

------
shalabhc
SQLite is great. For an unusual application see actordb.com - a server side
database that uses a large number of independent SQLite databases.

~~~
bane
Woah, that's awesome! Any performance information anywhere?

------
maxpert
Interesting and I saw title and thought to my self hmmmm... may be SQLite4 is
just around the corner. This is a good case study to show people look
sometimes classic works better and NoSQL coined terms and techniques might
work in limited scenarios. Still makes me wonder if LSM would have been faster
for mobile devices though, I know it might not work well for embedded devices;
but with modern mobile devices (1+ GB of RAM) it might have some speed
benefits.

Shameless plug [https://github.com/maxpert/lsm-
windows](https://github.com/maxpert/lsm-windows) (I did port the LSM storage
to windows).

------
the_common_man
Anyone know how sqlite makes money?

~~~
jbarham
[http://www.hwaci.com/sw/sqlite/prosupport.html](http://www.hwaci.com/sw/sqlite/prosupport.html)

~~~
ktta
>SQLite License. Warranty of title and perpetual right-to-use for the SQLite
source code.

<from more info>

Obtaining A License To Use SQLite

Even though SQLite is in the public domain and does not require a license,
some users want to obtain a license anyway. Some reasons for obtaining a
license include:

    
    
        Your company desires warranty of title and/or indemnity against claims of copyright infringement.
        You are using SQLite in a jurisdiction that does not recognize the public domain.
        You are using SQLite in a jurisdiction that does not recognize the right of an author to dedicate their work to the public domain.
        You want to hold a tangible legal document as evidence that you have the legal right to use and distribute SQLite.
        Your legal department tells you that you have to purchase a license.
    

If you feel like you really need to purchase a license for SQLite, Hwaci, the
company that employs all the developers of SQLite, will sell you one. All
proceeds from the sale of SQLite licenses are used to fund continuing
improvement and support of SQLite.

</from more info>

How is it possible that they can sell licenses to the code that was put into
the public domain by other contributors?

A contributor must attach the following declaration[1] to contribute. So now
their contributions are in public domain. Now in a place where the law doesn't
recognize public domain, doesn't the code belong to the original authors? How
can an unaffiliated company license it as if they wrote the code?

[1]: "The author or authors of this code dedicate any and all copyright
interest in this code to the public domain. We make this dedication for the
benefit of the public at large and to the detriment of our heirs and
successors. We intend this dedication to be an overt act of relinquishment in
perpetuity of all present and future rights to this code under copyright law."

~~~
hnarn
>How is it possible that they can sell licenses to the code that was put into
the public domain by other contributors?

"Public domain" does not mean: this has to be free, nobody can ask for money.

"Public domain" means: noone has the right to stop anyone else from doing what
they want.

The same is true for things like the Mona Lisa. You can snap a photo of it
(that photo may or may not be copyrightable, depends on the country, meaning
you may also "steal" someone elses photo if applicable), print posters and
sell them. This is possible because the intellectual property is in the public
domain. It doesn't mean you have to give your posters away for free. You can
even charge people to look at your photo -- disregarding the fact that noone
probably would pay.

~~~
ktta
My question was the assertion of the ability of granting licenses in a place
which doesn't recognize public domain. If the place doesn't recognize public
domain, then in that place, the copyright of the code contributed by a user is
still the users. So where does the company get the authority to license it?

Couldn't the person who put in into public domain go to the country and sue
them saying the company is licensing their code? How can the company defend
itself? We all know how it should go but that might not be what actually might
happen.

~~~
hnarn
>So where does the company get the authority to license it?

The company doesn't need any authority because anyone can "license" public
domain software, just like I can sell you a star in the sky. It's a worthless
piece of paper, its a gimmick and a way of making a donation to a cause you
appreciate.

>Couldn't the person who put in into public domain go to the country and sue
them saying the company is licensing their code?

Again, you're misunderstanding what public domain means. "The person" you
mention has no right to sue anyone because the thing being discussed has been
released into the public domain. Nobody owns it.

To illustrate: when you say (paraphrased); "The author could sue you for
misusing what he/she put in the public domain" the logic doesn't hold, because
if the author could to that it wouldn't be released into the public domain.

------
chmaynard
The title of this post, a true statement removed from its context, attracts
readers like me because its implied meaning has considerable shock value. Of
course, Dr. Hipp didn't help matters by naming his experimental fork
"SQLite4".

------
adekok
I'm surprised there wasn't more investigation of SQLite and LMDB:

[https://github.com/LMDB/sqlightning](https://github.com/LMDB/sqlightning)

The performance there shows either little to no performance difference, up to
substantial speed increases.

------
coleifer
The source tree for sqlite3 now contains an extension named lsm1 that contains
both the standalone lsm kv database as well as a virtual table extension which
allows you to use it directly from sqlite3. Some info on python integration
can be found here:

[http://charlesleifer.com/blog/using-sqlite4-s-lsm-storage-
en...](http://charlesleifer.com/blog/using-sqlite4-s-lsm-storage-engine-as-a-
stand-alone-nosql-database-with-python/)

In peewee 3.0a I've also added built-in support for using the lsm1 virtual
table if you're interested.

------
tomphoolery
instead of pretending to release a new version, it might be better to just
call this fork sqlite-failed.

------
cyberferret
Doubt still exists... Does this mean 'concluded' as in "We've finished
polishing the pre production code and are close to releasing it" or
'concluded' as in "We have thrown our hands up in the air and won't be working
on this thing any more to bring it to production" ??!!??

 _EDIT_ Seeing as I am getting slammed by downvotes, my comment here was
simply pointing out that the headline I saw on HN could be read in multiple
ways. As a long time user of SQLite3, I was initially excited when I read the
title as I had thought it meant something good coming from the SQLite team.
Turns out not to be. That, to me, still entails doubt.

~~~
fgonzag
" Lessons learned from SQLite4 have been folded into SQLite3 which continues
to be actively maintained and developed. This repository exists as an
historical record. There are no plans at this time to resume development of
SQLite4."

Right there on the web page. Highlighted in green even.

~~~
cyberferret
Below the fold even, after about 1000 other words of text. Would be better if
the title had used 'cancelled' or 'terminated' instead of 'concluded' to
explain the situation at the outset?

~~~
labster
It's a code revision, not an article. If you're a programmer the change is
super obvious.

