Run SQL Scripts with .read, Redirection, Batch Mode, and Exit Status

Learning outcomes

Interactive history is useful for exploration, but it is a poor source of truth. Repeatable database work belongs in files that can be reviewed, versioned, rerun, and tested. This lesson turns the sqlite3 shell into a predictable batch tool and explains the operating-system concepts—standard input, standard output, standard error, and exit status—that automation depends on.

Why command history is not a deployment artifact

An interactive session contains invisible context: the database you opened, the order of experiments, output settings, typos you corrected, and perhaps commands loaded by a startup file. A teammate cannot reliably reproduce “the commands I remember typing.” A script makes the intended sequence explicit.

.read temporarily changes the shell's input source

When you are already inside an interactive session, .read FILE tells the shell to read commands from that file. The file may contain ordinary SQL and dot-commands. At end-of-file, the shell returns to keyboard input. This is ideal when you want an interactive diagnostic session around a repeatable setup or verification script.

.bail on.echo onSELECT sqlite_version() AS sqlite_version;SELECT COUNT(*) AS sites FROM site;SELECT COUNT(*) AS devices FROM device;SELECT COUNT(*) AS notes FROM maintenance_note;SELECT d.device_code, d.statusFROM device AS dORDER BY d.device_code;

sqlite3 fieldnotes.dbsqlite> .read verify-fieldnotes.sqlsqlite> .quit

Redirection feeds a script through standard input

You do not need an interactive prompt at all. The operating system can connect a file or pipeline to the process's standard input. Syntax differs by shell, so course examples label the host environment.

sqlite3 -batch -bail fieldnotes.db < verify-fieldnotes.sqlrc=$?echo "sqlite3 exit code: $rc"

Get-Content -Raw .\verify-fieldnotes.sql | sqlite3.exe -batch -bail .\fieldnotes.db$rc = $LASTEXITCODE"sqlite3 exit code: $rc"

Windows cmd.exe also supports input redirection with <. PowerShell's pipeline syntax is shown separately because PowerShell treats native processes and streams differently from Bash.

stdout, stderr, and exit status answer different questions

Standard output (stdout) is where normal query results and requested shell output usually go. Standard error (stderr) is where diagnostics may be written. The exit status is the process-level success/failure signal that a parent shell or CI runner can test. Production automation should not assume that “some text appeared” means success.

-bail and .bail make file processing fail fast

The shell's default is not to bail on every error while processing input files. -bail enables stop-on-error behavior for batch/file input, and .bail on can place the policy inside a script. That prevents a later statement from running under the false assumption that an earlier prerequisite succeeded.

.bail onCREATE TABLE demo(id INTEGER PRIMARY KEY);INSERT INTO demo(id) VALUES (1);INSERT INTO demo(id) VALUES (1);  -- UNIQUE/PRIMARY KEY failure.print This line should not be reached in a fail-fast file run.

Current SQLite has had CLI exit-status fixes across recent releases, which is another reason the course requires a current patched build. For critical automation, test the exact failure path your script cares about rather than assuming every dot-command reports errors identically.

.echo and .timer are diagnostics, not data

.echo on prints input as it is processed, which helps a learner or CI log show which statement preceded an error. .timer on reports timing information. Both are shell diagnostics. They do not change SQL semantics and should not be mixed into machine-readable stdout unless your consumer expects them.

.echo on.timer onSELECT COUNT(*) FROM device;SELECT COUNT(*) FROM maintenance_note;.timer off.echo off

If a script must emit clean CSV or JSON for another process, avoid enabling diagnostic output on the same stream or redirect it separately where your platform allows.

Command-line SQL is convenient for one operation

The shell also accepts SQL or dot-command arguments after the database name. These are processed in order and the CLI exits afterward. This is useful for a concise check, but long multi-step workflows are easier to review in a file.

sqlite3 fieldnotes.db "SELECT COUNT(*) FROM device;"sqlite3 -json fieldnotes.db "SELECT device_code,status FROM device ORDER BY device_code;"sqlite3 fieldnotes.db ".tables"

Be careful with quoting because the operating-system shell processes quotes before sqlite3 sees the argument. Cross-platform quoting becomes complex quickly; that is another reason to move nontrivial work into .sql files or application code.

Build a disposable, rerunnable Chapter 2 database

For automation practice, create chapter02_cli.db rather than risking your main course file. The setup script intentionally starts by dropping only known lab tables inside this disposable database. Never point it at production data.

.bail onPRAGMA foreign_keys = ON;DROP TABLE IF EXISTS maintenance_note;DROP TABLE IF EXISTS device;DROP TABLE IF EXISTS site;CREATE TABLE site (    site_id   INTEGER PRIMARY KEY,    site_name TEXT NOT NULL);CREATE TABLE device (    device_id   INTEGER PRIMARY KEY,    site_id     INTEGER NOT NULL,    device_code TEXT NOT NULL,    device_name TEXT NOT NULL,    status      TEXT NOT NULL,    FOREIGN KEY (site_id) REFERENCES site(site_id));CREATE TABLE maintenance_note (    note_id   INTEGER PRIMARY KEY,    device_id INTEGER NOT NULL,    noted_at  TEXT NOT NULL,    note_text TEXT NOT NULL,    FOREIGN KEY (device_id) REFERENCES device(device_id));INSERT INTO site(site_name) VALUES ('North Plant'),('Harbor Lab');INSERT INTO device(site_id,device_code,device_name,status) VALUES(1,'PUMP-007','Cooling Water Pump 7','active'),(1,'FAN-014','Exhaust Fan 14','inspection_due'),(2,'SENS-003','Vibration Sensor 3','active');SELECT CASE WHEN (SELECT COUNT(*) FROM site)=2             AND (SELECT COUNT(*) FROM device)=3            THEN 'PASS' ELSE 'FAIL' END AS seed_check;

sqlite3 -batch -bail chapter02_cli.db < chapter02_setup.sql# Run the same command again: the disposable lab should rebuild predictably.

Failure lab: prove your automation notices an error

Create a copy of the setup script and deliberately misspell one table name near the end. Run with -batch -bail. Your acceptance criterion is not merely seeing an error message: the process must stop before the final success marker and your shell must report a nonzero exit status.

Production judgment: scripts need contracts

A reliable database script specifies its target, initialization, failure policy, expected outputs, and verification conditions. In CI, pin or record the SQLite CLI version because batch semantics and formatting have evolved. For complex application workflows, move beyond shell scripting to a language driver where errors, parameters, transactions, and rows are structured objects.

Summary and next lesson

.read, redirection, -batch, and fail-fast behavior turn the CLI into a reproducible tool rather than an interactive notebook. The next lesson addresses the most important value-handling rule: data values should be bound as parameters instead of being assembled into SQL text.

References

• SQLite — Command Line Shell For SQLite.
• SQLite — Query Result Formatting In The CLI.
• SQLite — Download Page.
• SQLite — Release History.