Understanding SQLite Versions on Docker for Python Production Environments

I had an sqlite query that created a view. It looked something like this:

select
  json_group_array(
    json_object(
      'name',
      entity,
      'age',
      age,
      'origin_date',
      origin_date
    )
    order by
      origin_date desc
  )
from
  some_table

Prior to sqlite 3.44, the above syntax would fail since order by previously could not be used on an aggregate function like json_group_array.

At first, I thought I could just update sqlite inside my Docker image. I even built sqlite 3.50.4 from source and confirmed it was installed:

FROM python:3.13-slim-bookworm
RUN apt-get update \
  && apt-get install -y \
    build-essential \
    ca-certificates \
    curl \
  && rm -rf /var/lib/apt/lists/*

ARG sqlite_year=2025
ARG sqlite_ver=3500400
ARG sqlite_file=sqlite-autoconf-$sqlite_ver.tar.gz
RUN curl -LO  https://www.sqlite.org/$sqlite_year/$sqlite_file \
  && tar xzf $sqlite_file \
  && rm $sqlite_file \
  && ./sqlite-autoconf-$sqlite_ver/configure --disable-static --enable-fts5 \
  && make && make install
x x x

And this did update the sqlite version in the eventual container. However when python runs, such as with datasette, it doesn't use the version in the container. Rather it uses the sqlite version bundled when the python image was built. In this case, python:3.13-slim-bookworm. So this would result in the above sql query failing and thus the program (i.e. datasette) not running at all. On fly.io, the error would be indecipherable:

[error][PC01] instance refused connection. is your app listening on 0.0.0.0:8080? make sure it is not only listening on 127.0.0.1 (hint: look at your startup logs, servers often print the address they are listening on)

This was not a port issue but an application level issue. Took a few days of scratching head but finally (accidentally) used datasette's /-/versions endpoint and discovered that the sqlite version was still 3.40.1 and not the 3.50.4 version I had installed.

There doesn't appear to be an easy way to bundle a new sqlite version in the base python image but there is a monkey-patch available through pysqlite3.

The solution I settled with:

FROM debian:bookworm-slim AS builder
ARG LITESTREAM_VER=0.5.0

# Install only what's needed: curl for download, ca-certificates for HTTPS
# --no-install-recommends keeps the image as small as possible
# Download Litestream release tarball from GitHub, extract directly to /usr/local/bin
# Avoids intermediate files by streaming curl output straight into tar
# Ensure binary is executable
RUN apt-get update \
  && apt-get install -y --no-install-recommends curl ca-certificates \
  && rm -rf /var/lib/apt/lists/* \
  && curl -sSL "https://github.com/benbjohnson/litestream/releases/download/v${LITESTREAM_VER}/litestream-${LITESTREAM_VER}-linux-x86_64.tar.gz" \
     | tar -xz -C /usr/local/bin \
  && chmod +x /usr/local/bin/litestream

FROM python:3.13-slim-bookworm

COPY --from=builder /usr/local/bin/litestream /usr/local/bin/litestream

ENV PYTHONUNBUFFERED=1 \
    PYTHONDONTWRITEBYTECODE=1 \
    PIP_DISABLE_PIP_VERSION_CHECK=1

COPY app /app
RUN pip3 install --no-cache-dir -U pip \
  && pip3 install --no-cache-dir pysqlite3-binary \
  && pip3 install --no-cache-dir -r /app/requirements.txt \
  && mkdir -p /usr/local/lib/python3.13/site-packages \
  && echo "import sys, pysqlite3 as sqlite3; sys.modules['sqlite3'] = sqlite3" \
       > /usr/local/lib/python3.13/site-packages/sitecustomize.py \
  && chmod +x /app/scripts/run.sh

EXPOSE 8080
CMD [ "/app/scripts/run.sh" ]

The fix was to skip rebuilding Python entirely and use pysqlite3-binary, which bundles a newer SQLite (currently 3.46.1). It's still not 3.50.4 but by adding a simple sitecustomize.py that monkey-patches sys.modules['sqlite3'] to point to pysqlite3, all Python code—including Datasette—transparently gets the newer SQLite.