How to Use SQLite as a Local Store for Shell Scripts

Scripts accumulate state: which files were processed, when something last ran, counters, a queue of work. Text files serve for a while, then grow into a mess of grep and sort with races and duplicates. SQLite is the step up: a real database in a single file that every machine already has, with transactions and indexes. This post shows the pattern, with the pitfalls for scripts. It leads into SQLite for log analysis and uses the sqlite3 shell. Examples use SQLite 3.46 and Bash 5.2.

Set up once

db=/var/lib/myjob/state.db
mkdir -p "$(dirname "$db")"
sqlite3 "$db" <<'SQL'
pragma journal_mode = wal;
create table if not exists processed (
  path  text primary key,
  sha   text not null,
  seen  text not null default (datetime('now'))
);
create index if not exists processed_seen on processed(seen);
SQL

Every statement is written to be repeatable, so the setup can run at the start of every execution. wal (write-ahead logging) is persistent: it lets readers and a writer work at the same time, and is the right mode for a script that may run twice. The default value for seen uses SQLite's own clock in UTC.

Write and read

q() { sqlite3 -batch -cmd '.timeout 5000' "$db" "$@"; }

sha=$(sha256sum "$file" | cut -d' ' -f1)
esc=${file//\'/\'\'}
q "insert into processed(path, sha) values('$esc', '$sha')
   on conflict(path) do update set sha=excluded.sha, seen=datetime('now')"

known=$(q "select sha from processed where path='$esc'")
count=$(q "select count(*) from processed where seen > datetime('now','-1 day')")
echo "$count files today"

Wrap the call in a function so every use gets -batch (no prompts) and a busy timeout. Query results come back as plain text, one row per line with | between columns; add -separator $'\t' or -csv for other formats, and -header when you want names. The upsert clause makes inserting an existing key an update, not an error.

Quote values safely

A file name with a single quote breaks the SQL above and, with untrusted names, opens SQL injection. The rule for SQL strings is to double every single quote, which the line esc=${file//\'/\'\'} does with parameter expansion. For numbers, validate first ([[ $n =~ ^[0-9]+$ ]]). A tidier route is the shell's parameter binding: .parameter set :path "'$esc'" then select ... where path = :path, which keeps value and statement apart. For text from the outside world, use a language with real bound parameters, such as Python's sqlite3 module, instead of the shell.

Concurrency

q "begin immediate; update queue set state='running' where id=7; commit;"

SQLite allows one writer at a time. A second script trying to write gets database is locked unless it waits: .timeout 5000 (or pragma busy_timeout=5000) retries for five seconds. For read-then-write sequences, begin with begin immediate to take the write lock up front, avoiding the deadlock where two readers both try to upgrade. Heavy parallel writing from many processes does not suit SQLite; a handful of scripts is fine.

Backup and inspection

sqlite3 "$db" ".backup '/backup/state-$(date +%F).db'"
sqlite3 "$db" "pragma integrity_check; select count(*) from processed;"
sqlite3 -header -column "$db" "select * from processed order by seen desc limit 5"

.backup uses the online backup API and gives a consistent copy even while another process writes; copying the file with cp while it is being written, or without the -wal file, can produce a corrupt backup. -header -column gives readable output for a human.

Where this does not apply

For a counter or two, a plain file with a lock is simpler. Data shared across machines needs a network database; SQLite files on NFS are unsafe because file locking is unreliable. For large analytical queries on text data, see DuckDB. Dump to text when you need a diffable record: sqlite3 state.db .dump.

Why SQLite fits shell scripts and where it stops fitting

SQLite is a library that stores a whole database in one ordinary file and needs no server, which makes it a good home for state that a script keeps between runs: a list of processed items, a history of measurements, a cache with expiry, a queue of work. Compared with text files, you get types, indexes, transactions and queries, so asking for the ten newest failures or the count per day is one statement and not a pipeline of sort and awk. Compared with a client-server database, there is nothing to install, secure or keep running, and backup is copying a file with the right command. The limits are real. Only one writer can hold the database at a time, so many processes writing at once will queue or fail, and the database must live on a local file system, since file locking on network file systems is unreliable and corruption is a known risk there. It suits a single machine and modest write rates.

The version matters for some features: strict tables arrived in 3.37, the arrow operators for JSON in 3.38 and parameters in the shell in 3.32. Check sqlite3 --version on the machine that runs the script.

Pragmas that change behavior per connection

Several settings are per connection, and a script that opens a new connection for each command must set them each time. PRAGMA foreign_keys = ON enables foreign key enforcement, which is off by default for compatibility, so constraints you declared do nothing without it. PRAGMA busy_timeout = 5000, or the shell's .timeout 5000, makes a connection wait up to five seconds for a lock and not fail immediately with "database is locked", which is the usual error when two scripts overlap. PRAGMA journal_mode = WAL is stored in the database and persists, and write-ahead logging lets readers continue while a writer works, which removes most locking trouble for read-heavy scripts. In WAL mode there are extra files beside the database, and copying only the main file while it is in use can give a copy that is missing recent changes. Put the pragmas in an initialization file that every invocation reads, with -init, so the settings are in one place and never forgotten.

Wrap related statements in a transaction with BEGIN IMMEDIATE when you will write, which takes the write lock up front and avoids a deadlock-like failure that happens when two readers both try to upgrade.

Putting values into queries safely

String-building SQL in the shell is where scripts break and become insecure. A value containing a single quote ends the string early, and a crafted value can inject statements. In the SQLite shell, set a named parameter with .parameter set :name "value" and refer to it in the statement, which binds the value instead of splicing it into text. Where that is unavailable, double every single quote in the value and wrap it in quotes, which a short shell expansion does, ${v//\'/\'\'}, though a numeric value is better validated with a pattern and used bare. Better still, validate input at the edge: accept only the characters you expect and reject the rest with a clear message. For reads, set -header -column or -json for output you can parse, and use -batch so the shell never prompts. Test the script with names that contain quotes, percent signs and Unicode, since those are the values that break naive code.

For backup, use .backup in the shell or VACUUM INTO, which produce a consistent copy while the database is in use, and run PRAGMA integrity_check on the copy to confirm it opens.

See the sqlite3 command line shell, write-ahead logging and upsert.