SpatiaLite & GeoPackage Automation with Python

Safe Temp-File Handling for SpatiaLite on Android

Put the SpatiaLite database and every sidecar it spawns — -wal, -shm, -journal, and sort spill files — inside the app-private directory (cacheDir or…

Put the SpatiaLite database and every sidecar it spawns — -wal, -shm, -journal, and sort spill files — inside the app-private directory (cacheDir or filesDir), point SQLITE_TMPDIR and PRAGMA temp_store there too, and never let any of them land on shared external storage.

This page narrows the Security Boundaries & Access Controls guide down to one platform-specific hazard: on Android, SQLite writes several temporary files next to (or elsewhere from) your main database, and if any of them leak onto shared storage the sandbox that protects your spatial data is defeated. It complements the runtime hardening in securing GeoPackage files for field use by focusing on the transient files rather than the container itself.

Why This Matters

SpatiaLite is SQLite with the spatial extension loaded, and SQLite is chatty on disk. In WAL mode it maintains a -wal write-ahead log and a -shm shared-memory index alongside the database; in rollback mode it writes a -journal; and any large ORDER BY or R-tree rebuild can spill a temporary sort file to whatever directory SQLite considers “temp”. Each of those files contains real geometry and attribute bytes.

Android isolates each app in a private sandbox under internal storage, where files are readable only by that app’s UID. Shared external storage — the legacy /sdcard world — has no such guarantee: other apps and the user can read it, and on removable media the bytes survive the app’s uninstall. If a sort spill or a -wal file is written there, sensitive survey data is exposed even when the main .gpkg is carefully encrypted. Scoped storage, enforced since Android 10, tightens this further: apps can no longer freely write arbitrary paths on shared volumes, so a mis-targeted temp directory does not just leak, it can fail outright with a permissions error.

Prerequisites

  • An Android app embedding Python via Chaquopy or BeeWare, or a native layer using android.database.sqlite
  • SpatiaLite / mod_spatialite bundled for the target ABIs (arm64-v8a, x86_64)
  • Access to the app context so you can resolve context.getCacheDir() and context.getFilesDir()
  • Target SDK 29+ so scoped storage rules apply
  • Familiarity with the SpatiaLite metadata tables and how the R-tree spatial index spills to temp during rebuilds

Primary Method

The reliable pattern is to derive every path from the app context, keep the database in filesDir, and force all transient files into a temp subdirectory of cacheDir. The example runs under Chaquopy, where the app context is passed into Python; the PRAGMAs and environment variable are identical for any embedding.

python
# SpatiaLite context: keep every sidecar and temp file inside the Android sandbox.
import os
import sqlite3


def open_sandboxed_spatialite(files_dir: str, cache_dir: str, db_name: str):
    """Open a SpatiaLite database with all temp/sidecar files kept app-private.

    Args:
        files_dir: context.getFilesDir().getAbsolutePath() — persistent, private.
        cache_dir: context.getCacheDir().getAbsolutePath() — transient, private.
        db_name:   File name of the database, e.g. "field.gpkg".
    """
    db_path = os.path.join(files_dir, db_name)

    # Force SQLite's temp directory into a private cache subfolder.
    tmp_dir = os.path.join(cache_dir, "sqlite_tmp")
    os.makedirs(tmp_dir, exist_ok=True)
    os.environ["SQLITE_TMPDIR"] = tmp_dir  # read at connection time

    conn = sqlite3.connect(db_path)
    conn.enable_load_extension(True)
    conn.load_extension("mod_spatialite")

    # temp_store = 2 (MEMORY) avoids temp files entirely for small spills;
    # for large spills that must hit disk, temp_store_directory (deprecated but
    # still honored) and SQLITE_TMPDIR both point inside the sandbox.
    conn.execute("PRAGMA temp_store = 2")
    conn.execute(f"PRAGMA temp_store_directory = '{tmp_dir}'")

    # WAL sidecars (-wal, -shm) live next to db_path, i.e. inside filesDir.
    conn.execute("PRAGMA journal_mode = WAL")
    conn.execute("PRAGMA synchronous = NORMAL")
    return conn

Because -wal and -shm are always created in the same directory as the database file, placing the database in filesDir automatically keeps those two sidecars private. SQLITE_TMPDIR and PRAGMA temp_store_directory handle the separate case of sort/index spill files, which SQLite otherwise tries to write to a system temp path that may be unwritable under scoped storage.

Step-by-Step Walkthrough

1. Never derive paths from external storage

The single most important rule: resolve paths from getFilesDir() / getCacheDir(), not from Environment.getExternalStorageDirectory(). Passing an external path into Python is how spatial data leaks.

python
# Correct: sandbox paths handed down from the Android context.
db = open_sandboxed_spatialite(files_dir, cache_dir, "field.gpkg")
# Wrong: anything under /sdcard, /storage/emulated/0, or getExternalFilesDir
# for temp — those bytes may be world-readable or blocked by scoped storage.

2. Send in-memory spills to RAM, on-disk spills to cache

PRAGMA temp_store = 2 keeps small temporary b-trees in memory. Large operations — rebuilding a spatial index over hundreds of thousands of rows — can still exceed memory and spill to disk, which is exactly why SQLITE_TMPDIR must already point inside cacheDir.

python
# SpatiaLite context: a spatial index rebuild may spill a temp file to disk.
db.execute("SELECT DisableSpatialIndex('parcels', 'geom')")
db.execute("SELECT CreateSpatialIndex('parcels', 'geom')")

3. Checkpoint and truncate the WAL before backgrounding

When the app goes to the background, checkpoint the WAL so its contents fold back into the main database and the -wal file shrinks. This limits how much live data sits in the sidecar.

python
# SpatiaLite context: fold the WAL back into the database on pause.
db.execute("PRAGMA wal_checkpoint(TRUNCATE)")

4. Clean up sidecars on close

After a clean close SQLite removes -wal and -shm itself, but a killed process can orphan them. Sweep the sandbox on startup and delete stragglers whose parent database is closed.

python
import os


def sweep_sidecars(files_dir: str, db_name: str) -> None:
    """Remove orphaned WAL/journal sidecars for a closed database."""
    for suffix in ("-wal", "-shm", "-journal"):
        stray = os.path.join(files_dir, db_name + suffix)
        if os.path.exists(stray):
            os.remove(stray)

Verification

Confirm that no SpatiaLite artifact was written outside the sandbox. List the app-private directories and assert the system temp path stayed empty.

python
# Python context: prove every SpatiaLite artifact lives inside the sandbox.
import os


def assert_sandboxed(files_dir: str, cache_dir: str) -> None:
    private_roots = (files_dir, cache_dir)
    for root in private_roots:
        for name in os.listdir(root):
            print("sandbox artifact:", os.path.join(root, name))
    # The default system temp must not hold any SQLite spill files.
    system_tmp = os.environ.get("TMPDIR", "/data/local/tmp")
    strays = [f for f in os.listdir(system_tmp) if "sqlite" in f.lower()]
    assert not strays, f"SQLite temp leaked outside sandbox: {strays}"

On a connected device you can cross-check from the host with adb:

bash
# Shell context: only app-private storage should contain SpatiaLite sidecars.
adb shell run-as com.example.fieldapp ls -l files cache/sqlite_tmp

Alternative Approaches or Edge Cases

Sharing an exported file with another app. When a technician needs to hand a .gpkg to another app, do not copy it to /sdcard. Expose it through a FileProvider content URI so the OS grants a scoped, revocable read grant while the bytes stay in your sandbox. The diagram contrasts the safe and unsafe placements.

SpatiaLite temp-file placement on AndroidInside the app-private sandbox, filesDir holds the database plus its WAL and SHM sidecars while cacheDir holds the temp spill directory, all private to the app UID. Outside the sandbox, shared external storage is world-readable and must never receive any SpatiaLite file.App-private sandbox — safefilesDir/field.gpkg (+ -wal, -shm)cacheDir/sqlite_tmp (spill files)readable only by the app UIDShared external storage — unsafe/sdcard, /storage/emulated/0world-readable, survives uninstallnever

BeeWare / native SQLite parity. Under BeeWare’s Rubicon bridge or a pure Kotlin layer, the same rules apply: open the database with an absolute path under filesDir, and set the process environment SQLITE_TMPDIR before the first connection. The PRAGMA names are identical because they belong to SQLite, not to any binding.

Encrypted databases still spill plaintext. Even a SQLCipher-encrypted database decrypts pages into temp files during sorts. Keeping temp_store = 2 (memory) is therefore a confidentiality control, not just a performance one — pair it with the encryption steps in the SQLCipher walkthrough.

Troubleshooting

OperationalError: database or disk is full during a spatial index rebuild Cause: SQLITE_TMPDIR points at a path with little free space, or scoped storage blocked the write and SQLite fell back to a tiny partition. Fix: set SQLITE_TMPDIR to cacheDir/sqlite_tmp, and prefer PRAGMA temp_store = 2 so small spills stay in RAM.

disk I/O error opening the database after an upgrade to Android 10+ Cause: the app still resolves the database or temp path from legacy external storage, which scoped storage now denies. Fix: move the database into filesDir and re-point every temp path to cacheDir; do not request MANAGE_EXTERNAL_STORAGE to work around it.

Orphaned -wal file keeps growing across sessions Cause: the process is killed before a checkpoint, so the WAL never folds back. Fix: run PRAGMA wal_checkpoint(TRUNCATE) on pause and call sweep_sidecars() on startup to clear orphans left by a crash.