CLI Automation & Offline Sync Pipelines for SpatiaLite & GeoPackage

Field teams generate spatial data on disconnected devices and must reconcile it with a central store the moment a network appears — a problem that lives in shell scripts, cron jobs, and command-line tools far more than in interactive notebooks. This guide is the central reference for automating SpatiaLite and GeoPackage workflows from the command line and for building the offline-first sync loops that move edits between the field and the office without losing a single feature.

Where the Python Integration & Database Workflows section handles in-process library code, this section covers the layer above it: the sqlite3 and spatialite shells, ogr2ogr batch conversion, scheduled automation, change tracking, and the reconciliation logic that makes a single-file spatial database safe to edit in two places at once. Every technique here is grounded in the same engine internals documented in Core Architecture & Format Standards for Spatial SQLite.

Foundational Architecture #

An offline-first spatial system is a distributed database with an unusually forgiving consistency model: each device holds a complete, self-contained SpatiaLite or GeoPackage file, edits it locally, and periodically reconciles. Because a GeoPackage is just a SQLite database on disk, the entire toolchain is file-oriented — copy the file, diff two files, ship a file — which is exactly what makes command-line automation so effective here.

Three Responsibilities of the Sync Loop #

Every reliable pipeline separates three concerns that are tempting to blur together:

• Capture records what changed on the device. The cheapest durable mechanism is a change-log table maintained by triggers, combined with WAL mode so a background export never blocks the surveyor still collecting points.
• Transport moves a delta, not the whole database, whenever bandwidth is scarce. A delta is a small set of changed rows plus the tombstones for deletes, serialized as its own GeoPackage or as newline-delimited JSON.
• Reconcile applies deltas to the authoritative copy and resolves conflicts deterministically. The reconciliation rule (last-write-wins, field-level merge, or manual review queue) is a business decision that the script must encode explicitly.

Keeping these stages independent means a failure in transport never corrupts capture, and a botched reconcile can be replayed from the retained deltas.

ACID Guarantees Under Automation #

Because SQLite provides full ACID transactions, a sync script can wrap each apply step in a single transaction and trust that a killed process rolls back cleanly. The practical rule for automation is one delta, one transaction: begin, apply every row in the delta, verify counts, then commit. If the process dies mid-apply, the next run sees the database exactly as it was before the delta started, and the retained delta is simply re-applied.

The Command-Line Toolchain #

Four executables cover almost every automation need. Each is scriptable, returns a meaningful exit code, and reads SQL or arguments from standard input for use in pipelines.

A minimal but complete conversion — reproject a Shapefile into a GeoPackage layer with a spatial index — is a single line:

# GDAL 3.4+ — reproject a Shapefile into a GeoPackage layer with an R-tree index
ogr2ogr -f GPKG survey.gpkg parcels.shp \
    -nln parcels -t_srs EPSG:4326 \
    -lco SPATIAL_INDEX=YES -lco GEOMETRY_NAME=geom

The spatialite shell brings the full spatial function catalogue to interactive and scripted SQL without an explicit load_extension call:

# SpatiaLite CLI — initialise metadata and index a table in a scripted heredoc
spatialite field.sqlite <<'SQL'
SELECT InitSpatialMetaData(1);
SELECT CreateSpatialIndex('parcels', 'geometry');
SELECT COUNT(*) FROM parcels;
SQL

Deeper coverage of scripted shell patterns — heredocs, dot-commands, exit-code handling, and piping query output into other tools — lives in SpatiaLite CLI & Shell Automation.

Offline-First Sync Model #

Tracking What Changed #

The authoritative record of local edits is a change-log table populated by AFTER INSERT, AFTER UPDATE, and AFTER DELETE triggers. Each row records the affected feature id, the operation, and a monotonic version counter so the exporter can select only rows newer than the last successful sync.

-- GeoPackage context — a minimal change-log driven by triggers
CREATE TABLE IF NOT EXISTS _sync_log (
    change_id  INTEGER PRIMARY KEY,
    table_name TEXT    NOT NULL,
    feature_id INTEGER NOT NULL,
    op         TEXT    NOT NULL CHECK (op IN ('I', 'U', 'D')),
    changed_at TEXT    NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))
);

CREATE TRIGGER IF NOT EXISTS parcels_log_u AFTER UPDATE ON parcels
BEGIN
    INSERT INTO _sync_log (table_name, feature_id, op)
    VALUES ('parcels', NEW.fid, 'U');
END;

The mechanics of building, indexing, and pruning this log across a whole database are covered in Tracking Row Changes for Incremental GeoPackage Sync.

Resolving Conflicts Deterministically #

When two devices edit the same feature, the reconcile step must choose a winner without human intervention in the common case. The three workable strategies are last-write-wins by timestamp, field-level merge for non-overlapping attribute edits, and a review queue for genuine collisions. Whichever you choose, encode it in code, not in a person’s habits — a sync that resolves conflicts differently on Tuesday than on Monday is unauditable. The trade-offs, including why SpatiaLite and GeoPackage behave differently under mobile sync, are compared in Offline-First Sync Strategies.

Idempotent Push #

The single most important property of a field sync script is idempotency: running it twice must have the same effect as running it once. Achieve this by giving every delta a stable id, recording applied delta ids in the target database, and skipping any delta already present. A complete, runnable push script with checkpointing and retry is walked through in How to Build an Offline Sync Push Script for GeoPackage.

Automation & Scheduling #

A pipeline that a human must babysit is not automation. Wrap each stage in a shell script that fails loudly, then schedule it.

• Exit codes are the contract. sqlite3 and ogr2ogr return non-zero on failure; check $? after every command and abort the run rather than pushing a half-built file. Use set -euo pipefail at the top of every Bash script.
• Logs are the audit trail. Append a timestamped line per stage to a rotating log so a failed overnight sync can be reconstructed the next morning.
• Locks prevent overlap. A cron job that fires while the previous run is still writing will corrupt the WAL. Guard the critical section with flock on a lock file.

#!/usr/bin/env bash
# Nightly sync guarded against overlapping runs
set -euo pipefail
exec 9>/var/lock/gpkg-sync.lock
flock -n 9 || { echo "sync already running"; exit 0; }

DB=/var/data/field.gpkg
sqlite3 "$DB" "PRAGMA integrity_check;" | grep -q '^ok$' || {
    echo "integrity check failed for $DB" >&2
    exit 1
}
ogr2ogr -f GPKG -update -append central.gpkg "$DB" parcels

Validation in Pipelines #

Never distribute a database that has not passed an automated gate. Two checks catch the overwhelming majority of field-data defects: SQLite’s own PRAGMA integrity_check, which verifies the b-tree and page structure, and an OGC-compliance pass that confirms the GeoPackage metadata tables and R-tree spatial index triggers are intact.

# Validation gate — fail the pipeline unless both checks pass
sqlite3 field.gpkg "PRAGMA integrity_check;" | grep -q '^ok$'
ogrinfo -al -so field.gpkg >/dev/null

The exact SQL assertions for OGC conformance are documented in How to Validate GeoPackage OGC Compliance, and a full ETL example that bakes validation into the pipeline is in Shapefile to GeoPackage ETL Pipeline with Validation.

Performance & Concurrency #

Automation runs unattended, so it must be gentle with device resources and resilient to the concurrency quirks of a live field database.

• Checkpoint on a schedule, not on every write. Under WAL mode the -wal sidecar grows until checkpointed; a nightly PRAGMA wal_checkpoint(TRUNCATE); keeps it bounded without stalling daytime collection.
• VACUUM after large deletes, never inside the hot path. Reclaiming space with VACUUM rewrites the whole file and takes an exclusive lock — schedule it at the end of a sync cycle, not between deltas.
• Batch inside one transaction. Thousands of single-statement sqlite3 invocations are dominated by process startup; feed a whole delta to one sqlite3 process wrapped in a transaction. See Scripting Batch Spatial Queries with the sqlite3 CLI for the batching patterns.

Security & Access Controls #

Automated distribution multiplies the blast radius of a mistake, so the pipeline is also where access policy is enforced. Ship field devices a read-only WAL-mode copy when they only need to consume reference data, encrypt sensitive extracts at rest with SQLCipher as shown in How to Encrypt a GeoPackage with SQLCipher in Python, and write staging files to a sandboxed temporary directory rather than shared storage — the platform-specific pitfalls are covered in Safe Temp-File Handling for SpatiaLite on Android. The complete threat model sits in Security Boundaries & Access Controls.

Topic Areas in This Section #

Each topic below drills into one part of the automation and sync stack with runnable scripts and diagnostic steps.

SpatiaLite CLI & Shell Automation — Driving the sqlite3, spatialite, ogr2ogr, and ogrinfo tools from shell scripts: heredocs, dot-commands, exit-code handling, batched transactions, and piping query output between tools.

Offline-First Sync Strategies — Change tracking, delta transport, conflict resolution, and idempotent push for disconnected field devices, including how SpatiaLite and GeoPackage differ under mobile sync workloads.

Frequently Asked Questions #

Related #

• Python Integration & Database Workflows — the in-process library layer beneath this section: connection lifecycle, serialization, and transaction control
• Core Architecture & Format Standards for Spatial SQLite — the engine internals and OGC format contracts every automation script depends on
• Transaction Scoping & Rollback Strategies — WAL mode, BEGIN IMMEDIATE, and retry logic that make unattended sync scripts safe
• Security Boundaries & Access Controls — encryption, read-only distribution, and safe path handling for automated rollouts
• End-to-End Spatial Automation Recipes — complete pipelines that combine ingest, indexing, validation, and sync push