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.

SQLite writes far more than the database file, and on Android each of those writes has to land somewhere the app actually owns. The three destinations are not interchangeable:

Where each class of SQLite scratch data belongs on AndroidThree destinations. Internal app storage holds the container itself and its write-ahead log sidecars, and is private to the app and never reclaimed by the system. The app cache directory holds on-disk temporary spills from sorts and index builds, and the system may delete it when storage runs low. Memory holds small temporary results when the temp_store pragma is set to memory, which avoids the filesystem entirely. External storage holds none of them.internal app storagethe container itself-wal and -shm sidecarsprivate to the appnever reclaimedapp cache directoryon-disk temp spillssorts, index buildsmay be deleted by the OSnever put the container herememorytemp_store = MEMORYsmall results onlyno filesystem at allbounded by heapexternal storage holds none of themscoped storage revokes the path, and its lock semantics are unreliable
Scoped storage turned "works on my device" into "works until the OS update" for anything writing outside app-private paths.

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')")
Android lifecycle and sidecar cleanupAn app moves from the foreground, where the database is open and the write-ahead log is growing, into the background. Before that transition the app must checkpoint and truncate the log and close the connection. If it does not, the system may kill the process while sidecar files are still live, leaving a log the next launch has to recover from — or, if the cache directory was reclaimed in the meantime, cannot.foregroundconnection openlog growingonPause / onStopcheckpoint TRUNCATEthen closebackgroundno live sidecarssafe to be killedskipped — process killed with the log livenext launch recovers, unless the cache was reclaimed first
Android gives no guarantee the process survives backgrounding, so the cleanup has to happen on the way out, not on the way back in.

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)
Which files must be gone after a clean closeAfter a correct shutdown the app-private directory should hold only the container itself. The write-ahead log and shared-memory files must both be absent, and the cache directory should hold no SQLite temporary files. A leftover log after close means the checkpoint did not run; leftover cache spills mean a query was interrupted.State of the app-private directory after closefield.gpkgpresentself-containedthe only expected filefield.gpkg-walmust be absentpresent means no checkpointrecovery on next opencache spillsmust be absentpresent means an aborted querygrows unbounded if ignoredassert this in an instrumented test — the failure is invisible until storage fillsand then it presents as a write error, not as a cleanup bug
Leftover sidecars are cheap to assert on and expensive to diagnose from a field report.

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.

Frequently Asked Questions

Does `temp_store = MEMORY` risk an out-of-memory kill?

It can, and the risk scales with the query rather than with the database. A sort or a hash join over a large intermediate result will happily consume whatever heap the app has, and Android kills a background process long before a desktop would notice. Pair the pragma with PRAGMA temp_store_directory pointing at the cache directory as a fallback, and treat memory temp storage as an optimisation for small results rather than a blanket setting.

Where should the container itself live on a modern Android target?

In app-private internal storage, reached through the context’s files directory. Scoped storage means an app no longer has a durable, writable path on shared external storage, and the paths that appear to work are frequently mediated by a content provider whose file descriptors do not support the locking SQLite needs. Internal storage is private, durable, and backed by a real filesystem — the three properties the engine assumes.

What should happen if the cache directory is reclaimed mid-query?

The query fails with a disk I/O error, and that is the correct outcome — there is no recovery that preserves the result. Design for it by treating any spill-dependent query as retryable work rather than as something that must succeed on the first attempt, and by keeping the containers themselves out of the cache directory so a reclaim costs a query rather than a database.