SQLite Is Easy to Compile, Hacker News

In the last week I’ve been working on another SQL website (https://sql-steps.wizardzines.com/, a list of SQL examples). I’m running all the queries on that site with sqlite, and I wanted to use window functions in one of the examples (this one).

But I’m using the version of sqlite from Ubuntu 18. 04, and that version is too old and doesn’t support window functions. So I needed to upgrade sqlite!

This turned to out be surprisingly annoying (as usual), but in a pretty interesting way! I was reminded of some things about how executables and shared libraries work and it had a very satisfying conclusion. So I wanted to write it up here.

(spoiler: the summary is thathttps://www.sqlite.org/howtocompile.htmlexplains how to compile SQLite and it takes like 5 seconds to do and it’s 20 x easier than my usual experiences compiling software from source)

attempt 1: download a SQLite binary from their website

TheSQLite download pagehas a link to a Linux binary for the SQLite command line tool. I downloaded it, it worked on my laptop, and I thought I was done.

But then I tried to run it on a build server I was using (Netlify), and I got this extremely strange error message: “File not found”. I straced it, and sure enoughexecvewas returning the error code ENOENT, which means “File not found ”. This was kind of maddening because the file was DEFINITELY there and it had the correct permissions and everything.

I googled this problem (by searching “execve enoent”), foundthis stack overflow answer, which pointed out that to run a binary, you don’t just need the binary to exist! You also need itsloaderto exist. (the path to the loader is inside the binary)

To see the path for the loader you can useldd, like this:

$ ldd sqlite3 linux-gate.so.1 (0xf7f9d 000) libdl.so.2=>/ lib / i 386 - linux-gnu / libdl.so.2 (0xf7f 70000) libm.so.6=>/ lib / i 386 - linux-gnu / libm.so.6 (0xf7e6e 000) libz.so.1=>/ lib / i 386 - linux-gnu / libz.so.1 (0xf7e4f 000) libc.so.6=>/ lib / i 386 - linux-gnu / libc.so.6 (0xf7c 73000) /lib/ld-linux.so.2

So/ lib / ld-linux.so.2is the loader, and that file doesn ‘t exist on the build server, probably because that Xenial installation didn’t have support for 32 -bit binaries (?), and I needed to try something different.

attempt 2: install the Debian sqlite3 package

Okay, I thought, maybe I can install thesqlite package from debian testing. Trying to install a package from a different Debian version that I’m not using is literally never a good idea, but for some reason I decided to try it anyway.

Doing this completely unsurprisingly broke the sqlite installation on my computer (which also broke git), but I managed to recover from that with a bunch ofsudo dpkg --purge --force-all libsqlite3-0and make everything that depended on sqlite work again.

I also briefly tried to just extract the sqlite3 binary from the Debian sqlite package and run it. Unsurprisingly, this also didn’t work, but in a more understandable way: I had an older version of libreadline (.so.7) and it wanted .so.8.

$ ./usr/bin/sqlite3 ./usr/bin/sqlite3: error while loading shared libraries: libreadline.so.8: cannot open shared object file: No such file or directory

attempt 4: compile it from source

The whole reason I spent all this time trying to download sqlite binaries is that I assumed it would be annoying or time consuming to compile sqlite from source. But obviously downloading random sqlite binaries was not working for me at all, so I finally decided to try to compile it myself.

Here are the directions:How to compile SQLite. And they’re the EASIEST THING IN THE UNIVERSE. Often compiling things feels like this:

run./ configure
realize i’m missing a dependency
run./ configureAgain
runmake
the compiler fails because actually i have the wrong version of some dependency
go do something else and try to find a binary

Compiling SQLite works like this:

download anamalgamation tarball from the download page
rungcc shell.c sqlite3.c -lpthread -ldl
that’s it !!!

All the code is in one file (sqlite.c)), and there are no weird dependencies! It’s amazing.

For my specific use case I didn’t actually need threading support or readline support or anything, so I used the instructions on the compile page to create a very simple binary that only used libc and no other shared libraries.

$ ldd sqlite3 linux-vdso.so.1 (0x  (ffe8e7e) ) libc.so.6=>/ lib / x 86 _ 64 - linux-gnu / libc.so.6 (0x 00007 FBEA 4988000) / lib 64 / LD-linux-x 86 - 64 .so.2 (0x 00007 fbea4d 79000)

this is nice because it makes it easy to experiment with sqlite

I think it’s cool that SQLite’s build process is so simple because in the past I’ve had funediting sqlite’s source codeto understand how its btree implementation works.

This isn’t really super surprising given what I know about SQLite (it’s made to work really well in restricted / embedded contexts, so it makes sense that it would be possible to compile it in a really simple / minimal way). But it is super nice!