SpatiaLite CLI & Shell Automation for SpatiaLite & GeoPackage

Every SpatiaLite and GeoPackage file is a plain SQLite database on disk, which means the fastest path to automating it is often not a Python import but a four-tool command-line stack — sqlite3, spatialite, ogr2ogr, and ogrinfo — wired together in a shell script that fails loudly and exits with a meaningful code. This guide, part of the CLI Automation & Offline Sync Pipelines section, shows how to script those tools reliably: heredocs and .read for batched SQL, dot-commands for import and export, set -euo pipefail for exit-code discipline, single-transaction batching for speed, and pipes that stream one tool’s output straight into the next.

The techniques here run identically on a developer laptop, a CI runner, and a headless field gateway, because none of them depend on an interactive terminal. Where the Python Integration & Database Workflows section drives the same engine through in-process library calls, this guide stays at the shell layer, where a cron job or a systemd timer can invoke it without a Python interpreter loaded at all.

Prerequisites #

Confirm each of these before scripting the tools. A missing binary or an unset extension path is the most common reason a script that works interactively fails under cron.

• SQLite 3.37+ with the sqlite3 shell on PATH (sqlite3 --version)
• spatialite shell from the SpatiaLite distribution, or the loadable mod_spatialite extension reachable on the library search path
• GDAL/OGR 3.4+ providing ogr2ogr and ogrinfo with the GPKG and SQLite drivers compiled in (ogrinfo --formats | grep -E 'GPKG|SQLite')
• A POSIX shell (Bash 4+ or a compatible shell) for heredocs, flock, and set -euo pipefail
• Write access to the target directory so -wal and -shm sidecars can be created alongside the database
• Familiarity with SpatiaLite metadata tables so scripted schema changes register geometry columns correctly

Verify the whole toolchain in one pass before wiring it into a pipeline:

# Confirm all four command-line tools resolve and report their versions
set -euo pipefail
for tool in sqlite3 spatialite ogr2ogr ogrinfo; do
    command -v "$tool" >/dev/null 2>&1 \
        || { echo "missing required tool: $tool" >&2; exit 1; }
done
sqlite3 --version
ogr2ogr --version

If spatialite is absent but sqlite3 is present, you can still run spatial SQL by loading mod_spatialite into the plain shell — covered below. If ogr2ogr is missing the GPKG driver, the GDAL build lacks the GeoPackage plugin and must be reinstalled from a distribution that includes it.

Concept & Specification Reference #

The four tools divide cleanly into two families: the SQL shells (sqlite3, spatialite) that execute statements against an open database, and the OGR utilities (ogr2ogr, ogrinfo) that treat the database as one format among many.

The Tool Stack #

Dot-Commands That Matter for Automation #

Dot-commands are shell-level directives interpreted by sqlite3/spatialite, not SQL. They configure how the shell reads input and formats output, which is exactly what a non-interactive script needs to control.

Two Ways to Get Spatial Functions #

The spatialite shell preloads the entire spatial function catalogue, so ST_Area, CreateSpatialIndex, and InitSpatialMetaData are available immediately. The plain sqlite3 shell does not — it needs an explicit extension load. Loading mod_spatialite with the .load dot-command turns the standard shell into a spatially aware one:

# Load mod_spatialite into the plain sqlite3 shell, then call a spatial function
sqlite3 field.gpkg <<'SQL'
.load mod_spatialite
SELECT ST_AsText(ST_GeomFromText('POINT(11.2 46.1)', 4326));
SQL

Prefer the spatialite shell when it is installed and prefer .load mod_spatialite when you only have the base SQLite tools; the SQL you run afterward is identical. On some platforms the extension is named mod_spatialite.so, mod_spatialite.dylib, or mod_spatialite.dll, and the bare .load mod_spatialite resolves the right one if it sits on the library search path.

Step-by-Step Implementation #

1. Run Scripted SQL with a Heredoc #

A heredoc feeds a block of SQL to the shell on standard input, so the whole script lives inside your .sh file with no temporary artifacts. Quoting the delimiter as 'SQL' prevents the shell from expanding $ and backticks inside the block — essential when the SQL contains parameters or JSON.

# Initialise spatial metadata and index a table in one scripted session
set -euo pipefail
DB=parcels.sqlite

spatialite "$DB" <<'SQL'
SELECT InitSpatialMetaData(1);
SELECT CreateSpatialIndex('parcels', 'geometry');
SELECT COUNT(*) AS indexed_rows FROM parcels;
SQL

The spatialite shell exits non-zero if any statement raises an error, so with set -e the script aborts on the first failed statement rather than plowing ahead.

2. Keep Reusable SQL in a File and Run It with .read #

For SQL you version-control and reuse across runs, keep it in a .sql file and invoke it with .read. This separates logic from orchestration and lets the same migration run from a script, a CI job, or an interactive session.

-- SpatiaLite context — migrate_parcels.sql, run non-interactively via .read
SELECT InitSpatialMetaData(1);
UPDATE parcels SET geometry = ST_MakeValid(geometry)
 WHERE ST_IsValid(geometry) = 0;
SELECT DisableSpatialIndex('parcels', 'geometry');
SELECT CreateSpatialIndex('parcels', 'geometry');

# Execute the versioned SQL file against the database
spatialite parcels.sqlite ".read migrate_parcels.sql"

3. Control Output Format with Dot-Commands #

To produce a machine-readable extract, set the mode and headers, redirect with .output, then run the query. Everything printed between .output file and the next .output stdout lands in the file.

# Export a filtered query to CSV using dot-commands
sqlite3 field.gpkg <<'SQL'
.headers on
.mode csv
.output parcels_large.csv
SELECT fid, area_m2 FROM parcels WHERE area_m2 > 10000 ORDER BY area_m2 DESC;
.output stdout
SQL

4. Bulk-Load Rows with .import #

.import reads a delimited file directly into a table, bypassing per-row INSERT parsing. It is the fastest way to ingest a large attribute CSV in the shell. Set .mode csv first so the delimiter and quoting are interpreted correctly, and use --skip 1 to drop a header row.

# Bulk-load an attribute CSV into an existing table without per-row INSERTs
sqlite3 field.gpkg <<'SQL'
.mode csv
.import --skip 1 new_attributes.csv attr_staging
SELECT COUNT(*) AS staged FROM attr_staging;
SQL

5. Batch Many Statements into One Transaction #

By default each statement sqlite3 executes commits on its own, and the per-commit fsync dominates runtime when you run thousands of them. Wrapping the batch in BEGIN; and COMMIT; collapses it into a single durable transaction, which is the single biggest lever on scripted write throughput.

# Wrap a large batch of updates in one transaction for a large speedup
sqlite3 field.gpkg <<'SQL'
PRAGMA journal_mode=WAL;
BEGIN;
UPDATE parcels SET status = 'reviewed' WHERE surveyor = 'A. Kaur';
UPDATE parcels SET status = 'reviewed' WHERE surveyor = 'M. Diaz';
DELETE FROM parcels WHERE geometry IS NULL;
COMMIT;
SQL

The interaction between transaction scope and durability is covered in depth in Transaction Scoping & Rollback Strategies; the short rule for scripts is one logical unit of work, one transaction.

6. Pipe Query Output Between Tools #

Because the SQL shells write to standard output, you can stream a query result straight into another command. Here sqlite3 emits a list of feature ids as CSV, and xargs feeds them to a second command — no temporary file required.

# Stream sqlite3 CSV output directly into a downstream command
set -euo pipefail
sqlite3 -csv -noheader field.gpkg \
    "SELECT fid FROM parcels WHERE status = 'flagged';" \
    | while IFS= read -r fid; do
        echo "reprocessing feature $fid"
    done

The -csv and -noheader flags are the command-line equivalents of the .mode csv and .headers off dot-commands, convenient when you only run a single query and do not need a heredoc.

7. Enforce Exit-Code Discipline #

set -euo pipefail makes the shell abort on any command that returns non-zero (-e), on any unset variable (-u), and on a failure anywhere in a pipeline (pipefail). Combine it with an explicit $? check where you need to branch on the specific outcome rather than abort.

#!/usr/bin/env bash
# Abort the whole run if any spatial step fails
set -euo pipefail

DB=field.gpkg
if ! sqlite3 "$DB" "PRAGMA integrity_check;" | grep -q '^ok$'; then
    echo "integrity check failed for $DB" >&2
    exit 1
fi

ogr2ogr -f GPKG -update -append central.gpkg "$DB" parcels
echo "append exit code: $?"

Validation & Verification #

After any scripted mutation, prove the database is still well-formed and the spatial structures are intact before shipping it downstream. Two independent tools give you cross-checked confidence: ogrinfo reads the file through the OGR driver, and sqlite3 PRAGMAs read the raw b-tree.

# ogrinfo summary confirms the driver, layer, geometry type, and feature count
ogrinfo -al -so field.gpkg parcels

# Expected excerpt:
# Layer name: parcels
# Geometry: Polygon
# Feature Count: 5231
# Layer SRS WKT: GEOGCS["WGS 84", ...]

# PRAGMA checks confirm structural integrity and foreign-key soundness
sqlite3 field.gpkg <<'SQL'
PRAGMA integrity_check;
PRAGMA foreign_key_check;
SELECT COUNT(*) AS rtree_rows FROM rtree_parcels_geom;
SQL

An integrity_check that prints anything other than ok, or an rtree_ row count that has drifted away from the layer’s feature count, means the file must not be distributed. For the full OGC conformance assertions beyond these smoke checks, see How to Validate GeoPackage OGC Compliance.

Common Failure Modes & Fixes #

Extension Load Fails in the Plain sqlite3 Shell #

Symptom: Error: unknown command or invalid arguments: "load" or Error: not authorized after .load mod_spatialite.

Cause: The sqlite3 binary was compiled with extension loading disabled (-DSQLITE_OMIT_LOAD_EXTENSION), or mod_spatialite is not on the library search path.

Fix: Use the spatialite shell, which has spatial functions built in and needs no .load. If you must use sqlite3, point the loader at the library directory and confirm the file exists:

# Make the extension discoverable, then load it by bare name
export LD_LIBRARY_PATH="/usr/lib/x86_64-linux-gnu:${LD_LIBRARY_PATH:-}"
sqlite3 field.gpkg ".load mod_spatialite" "SELECT spatialite_version();"

Heredoc Variables Expand Unexpectedly #

Symptom: SQL that references $ or backticks produces empty values or shell errors like bad substitution.

Cause: An unquoted heredoc delimiter (<<SQL) lets the shell expand $var and command substitutions inside the SQL block before sqlite3 ever sees it.

Fix: Quote the delimiter (<<'SQL') to pass the block verbatim. Only leave it unquoted when you deliberately want the shell to interpolate a value, and then interpolate it safely rather than inside a SQL string.

Batch Runs Slowly Despite Few Rows #

Symptom: A script issuing thousands of sqlite3 "$DB" "UPDATE ..." invocations takes minutes for a small table.

Cause: Each invocation starts a new process, opens the file, commits its own transaction, and fsyncs. Process startup and per-statement commits dominate the runtime.

Fix: Feed the whole batch to a single sqlite3 process wrapped in one BEGIN;/COMMIT;. The pattern in step 5 typically turns a multi-minute loop into a sub-second run. The batching approach is expanded in Scripting Batch Spatial Queries with the sqlite3 CLI.

database is locked Under Cron #

Symptom: Error: database is locked when a scheduled script runs, but never when you run it by hand.

Cause: Two invocations overlap — a long run is still writing when the next scheduled run starts — and both contend for the write lock or the -wal sidecar.

Fix: Guard the critical section with an advisory lock so a second run exits immediately:

# Prevent overlapping scheduled runs with an flock guard
exec 9>/var/lock/parcels-sync.lock
flock -n 9 || { echo "sync already running, skipping"; exit 0; }

.import Puts Everything in One Column #

Symptom: After .import data.csv t, every row’s data lands in the first column and the rest are empty.

Cause: The shell was in the default list mode with a pipe separator, so comma-delimited input was never split on commas.

Fix: Set .mode csv before .import so the shell parses commas and quoting per RFC 4180. Add --skip 1 when the file carries a header row you do not want inserted.

Performance Notes #

One Process, One Transaction #

The dominant cost in shell automation is not SQL execution but process startup and per-statement commits. Consolidating a batch into a single sqlite3 process wrapped in one transaction removes both. On a 50,000-row update, moving from per-row invocations to one transactional heredoc routinely cuts wall-clock time by one to two orders of magnitude.

WAL Mode and Checkpoint Cadence #

Set PRAGMA journal_mode=WAL; once at the top of a write session so background readers — an ogrinfo validation, a status dashboard — are not blocked while the script writes. The -wal sidecar grows until checkpointed, so end a heavy sync with an explicit checkpoint rather than leaving it to accumulate:

# Bound the WAL sidecar after a heavy write session
sqlite3 field.gpkg "PRAGMA wal_checkpoint(TRUNCATE);"

Deferring Index Maintenance #

Maintaining the R-tree spatial index on every insert is wasteful during a bulk load. When a script ingests a large batch, drop or disable the spatial index first, load inside one transaction, then rebuild it in a single pass with CreateSpatialIndex. Building the R-tree once over the finished table is markedly faster than maintaining it incrementally row by row.

Child Pages #

• How to Convert Shapefiles with ogr2ogr on the Command Line — the complete ogr2ogr flag set for turning Shapefiles into GeoPackage and SpatiaLite layers with reprojection, spatial indexing, and attribute filtering
• Scripting Batch Spatial Queries with the sqlite3 CLI — running large batches of spatial SQL non-interactively with heredocs, .read, transaction wrapping, and CSV capture

Frequently Asked Questions #

Related #

• CLI Automation & Offline Sync Pipelines — parent section: the full command-line and offline-sync stack this guide sits inside
• How to Convert Shapefiles with ogr2ogr on the Command Line — CLI-only format conversion into GeoPackage and SpatiaLite
• Scripting Batch Spatial Queries with the sqlite3 CLI — non-interactive SQL batching, heredocs, and CSV capture
• Transaction Scoping & Rollback Strategies — WAL mode, transaction boundaries, and retry logic behind safe scripted writes
• Extension Compatibility in Spatial SQLite — loading and version-matching mod_spatialite across platforms