Update sqlite3 version on base python docker image

There’s plenty of reasons to update to the latest versions of sqlite, specifically if you want to use JSON functions and operators. But base python docker images come with sqlite 3.40.0 which is missing a lot of the good stuff (JSONB).

I’ve seen many ways of updating sqlite and I am still not sure I understand how sqlite3 is bundled with python—I assume it gets the whatever version was linked during compilation. One of the solutions is to pip install pysqlite3-binary and honestly that sounds fine and is probably what I would go with if I had found the answer before.

There’s another way, which is to download and install sqlite to whatever version you need. Here are some quick Dockerfile sample steps that worked for me.

FROM python:3.12

# Get sqlite 3.47
ENV LD_LIBRARY_PATH=/usr/local/lib:${LD_LIBRARY_PATH}
RUN mkdir -p /tmp/build/sqlite && \
    curl -L https://www.sqlite.org/2024/sqlite-autoconf-3470000.tar.gz | \
        tar xz -C /tmp/build/sqlite --strip-components=1 && \
    cd /tmp/build/sqlite && \
    ./configure && \
    make && \
    make install && \
    python -c "import sqlite3; assert sqlite3.sqlite_version == '3.47.0'" && \
    cd / && rm -rf /tmp/build

# ...