SpatiaLite & GeoPackage Automation with Python

Read-Only WAL Mode for Distributed Field Devices

Before distributing a reference GeoPackage that devices may only read, checkpoint and strip its -wal file, ship it in a rollback-journal state, and open…

Before distributing a reference GeoPackage that devices may only read, checkpoint and strip its -wal file, ship it in a rollback-journal state, and open it with PRAGMA query_only = ON plus the immutable=1 or mode=ro URI parameters so no device can ever mutate the shared copy.

This page isolates one operational corner of the Security Boundaries & Access Controls guide: the awkward interaction between write-ahead logging and read-only distribution. It is deliberately narrower than the full field-deployment pipeline in securing GeoPackage files for field use — here the concern is a base map or reference layer that many devices consume identically and none should change.

Why This Matters

When you push one canonical GeoPackage — a road network, a parcel base map, a survey control grid — to a fleet of tablets, “read-only” has to mean read-only at every layer, or the copies silently diverge. A single accidental UPDATE on one device makes that device’s answers disagree with the rest of the fleet, and there is no server to reconcile them because these devices are offline by design.

The subtlety is that WAL mode — normally the best choice for concurrency — assumes the database directory is writable, because opening a WAL database creates the -wal and -shm sidecars even for a read. If you ship a WAL-mode GeoPackage to a read-only mount or a device that mounts the media read-only, the very first connection fails because SQLite cannot create those sidecars. So the goal is twofold: neutralize WAL for the distributed copy, and stack runtime and filesystem controls so a read stays a read.

Prerequisites

  • Python 3.9+ with the standard-library sqlite3 module (GDAL/OGR optional for validation)
  • A source .gpkg staged on a writable host where you can run the checkpoint
  • Ability to set filesystem permissions on the destination (chmod, read-only mount, or Android app-private storage)
  • Understanding of SQLite transaction scoping and journal modes
  • The file header knowledge to confirm the shipped file is in the journal mode you expect

Primary Method

Prepare the file on the staging host, then open it read-only on the device. Preparation checkpoints the WAL back into the main database and switches the persisted journal mode to DELETE, so the distributed file needs no writable sidecar. The device then opens it with an immutable URI and query_only as belt-and-braces.

python
# GeoPackage context: prepare a read-only reference file, then open it safely.
import os
import sqlite3


def prepare_readonly_reference(src_path: str) -> None:
    """Fold the WAL back in and switch the file to DELETE journal mode.

    Run on the writable staging host BEFORE distribution. Afterwards the file
    carries no -wal sidecar, so it can live on a read-only mount.
    """
    if not os.path.exists(src_path):
        raise FileNotFoundError(src_path)
    conn = sqlite3.connect(src_path)
    # Fold every committed page from the WAL into the main database file.
    conn.execute("PRAGMA wal_checkpoint(TRUNCATE)")
    # Persist a journal mode that needs no sidecar on open.
    conn.execute("PRAGMA journal_mode = DELETE")
    conn.commit()
    conn.close()


def open_readonly_reference(path: str) -> sqlite3.Connection:
    """Open a distributed reference GeoPackage with no possibility of writes."""
    # immutable=1 promises SQLite the file cannot change, so it skips creating
    # -wal/-shm and skips locking entirely — ideal for a read-only mount.
    uri = f"file:{path}?immutable=1&mode=ro"
    conn = sqlite3.connect(uri, uri=True)
    # query_only rejects any INSERT/UPDATE/DELETE at the SQL layer as well.
    conn.execute("PRAGMA query_only = ON")
    return conn

mode=ro opens the file read-only through SQLite’s VFS; immutable=1 goes further and tells SQLite the bytes will never change under it, letting it skip lock and sidecar handling that a read-only directory cannot satisfy. PRAGMA query_only = ON is the third, independent guard at the SQL layer.

Step-by-Step Walkthrough

1. Decide how the WAL leaves the building

You have two shippable end states. Either checkpoint and switch to DELETE journal mode (shown above), or ship the file with WAL removed but leave the device to open it immutable. The decision below drives everything else.

Preparing a WAL GeoPackage for read-only distributionStart from a WAL-mode GeoPackage. Checkpoint with TRUNCATE to empty the write-ahead log. Then choose one of two shippable states: switch the persisted journal mode to DELETE for the widest compatibility, or keep the file as-is and require devices to open it with immutable equals one. Both states are then opened with query_only on and read-only file permissions.WAL-mode .gpkgwal_checkpoint(TRUNCATE)Switch to DELETE journalwidest device compatibilityKeep file, open immutable=1no sidecar on read-only mount

2. Checkpoint the write-ahead log

PRAGMA wal_checkpoint(TRUNCATE) copies every committed frame from the -wal file into the database and then shrinks the log to zero bytes, so no committed data is stranded in a sidecar you are about to delete.

python
# GeoPackage context: empty the WAL so nothing is stranded in the sidecar.
conn = sqlite3.connect("basemap.gpkg")
conn.execute("PRAGMA wal_checkpoint(TRUNCATE)")
conn.close()

3. Persist a sidecar-free journal mode

Switching to DELETE mode is stored in the database header, so consuming devices do not attempt to create a -wal. This is what makes the file openable from a directory the device cannot write.

python
conn = sqlite3.connect("basemap.gpkg")
mode = conn.execute("PRAGMA journal_mode = DELETE").fetchone()[0]
assert mode == "delete", f"journal mode is {mode}, expected delete"
conn.close()

4. Enforce read-only at runtime with query_only

On the device, PRAGMA query_only = ON makes the connection reject any write statement with a clear error instead of silently succeeding against a local copy. It is a per-connection guard, so set it right after opening.

python
conn = open_readonly_reference("/data/app/basemap.gpkg")
conn.execute("PRAGMA query_only = ON")
# Reads work; a write now raises OperationalError.
conn.execute("SELECT count(*) FROM gpkg_contents").fetchone()

5. Harden the file permissions

Strip every write bit so even a process that ignores query_only cannot alter the bytes. On POSIX devices this is chmod 444; on Android, place the file in app-private storage where the OS enforces isolation.

python
import os
import stat


def make_readonly(path: str) -> None:
    """Remove all write bits so the reference file cannot be modified."""
    mode = os.stat(path).st_mode
    os.chmod(path, mode & ~(stat.S_IWUSR | stat.S_IWGRP | stat.S_IWOTH))

Verification

Prove that reads succeed and writes are rejected on the prepared file.

python
# Python context: a distributed reference file must read but never write.
import sqlite3


def verify_readonly(path: str) -> bool:
    conn = sqlite3.connect(f"file:{path}?immutable=1&mode=ro", uri=True)
    conn.execute("PRAGMA query_only = ON")
    # A read must succeed.
    conn.execute("SELECT count(*) FROM gpkg_contents").fetchone()
    # A write must be rejected.
    try:
        conn.execute("CREATE TABLE _probe(x)")
        conn.close()
        return False  # Write succeeded — file is NOT read-only.
    except sqlite3.OperationalError:
        conn.close()
        return True


assert verify_readonly("basemap.gpkg"), "reference file is writable"

Confirm the shipped journal mode from the command line so no -wal sidecar is expected:

bash
# Shell context: the distributed file should report 'delete', not 'wal'.
sqlite3 basemap.gpkg "PRAGMA journal_mode;"

Alternative Approaches or Edge Cases

Keeping WAL for on-device read concurrency. If a device runs several reader threads and you genuinely want WAL’s concurrent reads, you cannot ship a read-only directory — WAL needs to create -shm. Put the file in a writable app-private directory instead, and rely on PRAGMA query_only = ON plus chmod 444 on the .gpkg itself (leaving the directory writable so the sidecars can appear). This trades a strictly read-only mount for in-process read scaling.

Immutable on truly read-only media. When the file lives on a read-only mount, a CD image, or an OS-enforced read-only partition, immutable=1 is mandatory: without it SQLite tries to take a lock the filesystem cannot grant and fails with unable to open database file. Never set immutable=1 on a file that some other process might still change — SQLite will return stale or corrupt reads because it assumes the bytes are frozen.

Combining with encryption. A read-only reference layer and an encrypted container are orthogonal controls and stack cleanly — open the file with the SQLCipher key first, then apply the same query_only and immutable flags, as covered in the SQLCipher encryption walkthrough.

Troubleshooting

OperationalError: unable to open database file on a read-only mount Cause: the file is still in WAL mode, so SQLite tries to create -wal/-shm in a directory it cannot write. Fix: run PRAGMA wal_checkpoint(TRUNCATE) then PRAGMA journal_mode = DELETE on the staging host, or open with immutable=1.

OperationalError: attempt to write a readonly database Cause: a write statement reached a connection opened with mode=ro or guarded by query_only. This is the control working as intended — fix the calling code to route writes to a separate, writable working copy rather than the shared reference file.

Stale rows after the reference file was updated in place Cause: a device opened the file with immutable=1 while the staging host overwrote it, so SQLite kept serving cached, frozen bytes. Fix: distribute updates as a new file path or bump a version in the filename, and only mark a file immutable=1 once it will never change under an open connection.