Is it possible to install SQLite 3.24+ on Ubuntu 18.04?

I am trying to update my SQLite version to 3.24 or above so that a Python app can make use of the new "UPSERT" queries. I have been trying for a few hours to do this with little success; it refuses to update past 3.22.

I have attempted:

• Using apt to install and reinstall sqlite / libsqlite3-dev (and various versions of this)

• Downloading packages from launchpad (such as https://launchpad.net/ubuntu/+source/sqlite3/3.26.0-2) and attempting to install them

• Using Python pip to try and update sqlite3

• Adding a few PPA repos to try and grab it from there

• Other various suggestions found from google

What I have not tried:

• Building SQLite from source (this is a bit of a last resort for me)

Is it possible to install a version of SQLite 3.24+ on Ubuntu 18.04? If so, is the only way to build from source or is there an easy way to pick up a more recent version through apt (or similar)?

Here are some options:

Docker

This is the actual use case for docker. There are no official Docker images with the latest sqlite - but it's easy to make one:

$ mkdir sqlite333 && cd sqlite333
$ cat > Dockerfile
FROM ubuntu:groovy
RUN apt-get update && \
    DEBIAN_FRONTEND=noninteractive apt-get -yq --no-install-recommends install sqlite3 python3 && \
    rm -rf /var/lib/apt/lists/* /tmp/* /var/tmp/* 

WORKDIR /root
ENTRYPOINT python3.8 -c "import sqlite3; print(sqlite3.sqlite_version)"

Press CtrlD to save, then:

$ docker build -t sqlite333 .
$ docker run --rm -ti sqlite333
3.33.0

To run your existing app, just bind mount it:

docker run --rm -ti -v /path/to/myapp:/tmp sqlite333 /tmp/myapp.py

Or you can copy it into the directory with your Dockerfile, then copies of it will be placed in the actual image - but then you need to rebuild the image whenever you make changes.

If there was an official Sqlite image with the right version, and if you've installed and could run Docker, you can use any version of SQLite and any version of anything else, neatly packaged eg. (from https://hub.docker.com/r/nouchka/sqlite3)

docker run --rm -ti -v `pwd`:/tmp nouchka/sqlite3 /tmp/myapp.py

This mounts your current directory inside /tmp in the docker image, and runs the image built from this Dockerfile - which you can trivially modify to also install Python, and to run any command you like.

Front door (community) approach

You can forcibly install .deb's from other distributions - or versions - that use the same C library - I usually search on Distrowatch to find these - but besides the standard C library version, there could be other unexpected dependency problems, so your "quick fix" could turn out to be not-so-quick in the long term, if not downright breaking parts of your OS.

The proper way to do this is to volunteer your time to update the official repo - this way all security and compatibility issues will have the best way to find their way to the right channel, and you stand the best chance of soliciting the help of the community in maintaining things going forward. The starting point would be to contact the current Ubuntu SQLite package maintainer to find out what you can do to help to get it up-to-date. https://launchpad.net/ubuntu/+source/sqlite3 (3.33 is in fact part of the latest Ubuntu, which is why the above Dockerfile piggy-backs on) This initial list showing more than 20 related or dependent packages, alludes to the reason why older versions might be lagging: https://packages.ubuntu.com/search?keywords=sqlite3

Compile from source

Compiling from source is not that much trouble either - if you've got the steps - so lets put SQLite's claim to fame of "small, fast, reliable" to the test, from https://www.sqlite.org/download.html:

$ wget https://www.sqlite.org/2020/sqlite-autoconf-3330000.tar.gz
$ tar -zxvf sqlite-autoconf-3330000.tar.gz
$  cd sqlite-autoconf-3330000/
$ ./configure
$ make -j4

But this is all answering your question as asked. Your real question is:

Can I install SQlite 3.33 for Python under Ubuntu 18.04?

Edit: Python does indeed use the system SQLite - so after compiling it as per above, installing it under /usr/local, you should theoretically be able to tell it to use a specific one with the LD_RUN_PATH environment variable as per below - but it doesn't work:

$ sudo PREFIX=/usr/local make install

Would normally make this use the new SQLite:

$ LD_RUN_PATH=/usr/local/lib python3 

But it seems that there is more to SQLite or Python3, as just this isn't enough - it might work if you install the new version system wide (sudo PREFIX=/usr make install) - but this is not recommended as it which might break compatibility with other system applications and break system dependencies and your OS might overwrite it if it updates - you can reference http://charlesleifer.com/blog/compiling-sqlite-for-use-with-python-applications/ for more detailed instructions, but there should be no need as I've copied the essentials below.

Considering this, the docker method seems to be not only a scalable and maintainable quick-fix but actually the preferred way - as upon closer inspection, it becomes clear that SQLite is actually more tightly integrated with the main Python code base, leaving the only alternative as compiling your own Python package as per below.

The other solution: compiling Python

So what you really want to do is to compile your own copy of Python.

I was surprised how quickly this compiled, even on my underpowered laptop. The guide linked above is quite detailed, so I won't duplicate anything but the main steps here - simplified:

# Follow the steps copied above to compile SQLite 3.33 - and then after the make, do:
$ sudo PREFIX=/usr/local make install

# Then compile Python
$ sudo apt-get build-dep python3.6 # thanks Ubuntu community!
$ git clone --depth=1 https://github.com/python/cpython
$ cd cpython
$ LD_RUN_PATH=/usr/local/lib ./configure CPPFLAGS="-I/usr/local/include/sqlite3" #updated
$ make -s -j4
# sudo PREFIX=/usr/local make install # if you like, which will install it in /usr/local/bin
# but you can just run it directly from the source directory too

This should leave you with a binary version of Python that has SQlite 3.33 support (or whichever version you installed under /usr/local), which you can copy over, or package and copy over, or package into a deb and copy over, or package into your own PPA for distribution and apt installing. Or if someone already did that, you can search for it on Ubuntu launchpad.

Edit: Tested and working. After testing the above without the LD_RUN_PATH environment variable, and noticing that it still used the distribution version (3.22 in this case), it became clear that it's not compiled in - just the path to the library it is supposed to use is compiled in, as it does when compiling it like this. So recompiling Python might not be necessary, just installing a new SQLite system-wide - over the OS packaged version - considering that the packaged version might overwrite it if it ever updates, or that removing the packaged version might break your OS packaging for other packages that depend on it so that's not a great solution.)

(The easy way to learn about all of this is by just installing Slackware or Gentoo, which is how I learned. )

Other options

There might be Flatpak or Snappy options, but nobody has packaged it yet as of this writing.

This version of SQLite3 is tied to the official Ubuntu 18.04 as Murphy mentioned. I created a fresh Ubuntu 18.04.5 LTS in Azure and tried several things including installing Python3.7 and Python 3.8 and the SQLite version was the same.

root@test-011:/home/carles# python3.8 -c "import sqlite3; print(sqlite3.sqlite_version)"
3.22.0

I also tried downloading precompiled SQLite binaries for a more recent version, and neither they worked.

I would say, even if you make it work, it would be a pain distributing your program to other Servers. And I would not recommend you to not use the official packages provided by LTS version, as you would not get upgrades and security patches for those.

So I think that the easiest way is either you install a newer version of Ubuntu, or you can run a Docker instance with newer Ubuntu image version and try there the new feature with your program. Building SQLite3 from the sources will not work for you, you would have to build Python3 from the Sources + SQLite3. My advice is: Keep it simple.