Readable Output: .mode, .headers, .width, .nullvalue, and Current CLI Formatting

Learning outcomes

A query result has two layers: the values produced by SQLite and the way the sqlite3 program renders those values. If you do not separate those layers, changing a display mode can look like the database changed. This lesson builds a precise mental model of output modes and explicitly addresses the major formatting changes introduced in SQLite 3.52.0.

Same rows, different renderer

Run one query several times without changing the database. Only the shell's formatter changes. The safest way to prove this is to use a deterministic query and compare values.

SELECT d.device_code,       d.device_name,       d.status,       s.site_nameFROM device AS dJOIN site AS s ON s.site_id = d.site_idORDER BY d.device_code;

Regardless of whether the shell prints boxes, pipes, JSON, or CSV, SQLite has returned the same four columns and the same three rows. Formatting happens after statement execution.

The modern interactive default changed in SQLite 3.52

Before SQLite 3.52.0, many tutorials assumed an interactive shell would start in plain list mode. Current CLI documentation instead defines a richer terminal-oriented default based on qbox, with Unicode box drawing, relaxed SQL-literal quoting, screen-width fitting, and value-length limits. Batch sessions intentionally keep the legacy list-style default for compatibility.

sqlite> .mode.mode qbox --limits on --quote relaxed --sw auto --textjsonb onsqlite> .mode -v-- Prints the full set of current rendering options.sqlite> .mode --list-- Lists modes supported by this CLI build.

Human-oriented table modes

For exploratory work, a person usually wants aligned columns and visible column names. Current SQLite provides several tabular modes. Their purpose overlaps, but their aesthetics and quoting differ.

.mode qboxSELECT device_code, status FROM device ORDER BY device_code;.mode tableSELECT device_code, status FROM device ORDER BY device_code;.mode columnSELECT device_code, status FROM device ORDER BY device_code;.mode markdownSELECT device_code, status FROM device ORDER BY device_code;

Machine-oriented modes are contracts, not decoration

If another program consumes the output, a visually pretty table is usually the wrong contract. Choose a mode designed for interchange, and do not parse decorative borders.

.mode csv --titles onSELECT device_code, status FROM device ORDER BY device_code;.mode jsonSELECT device_code, status FROM device ORDER BY device_code;.mode list --colsep "|"SELECT device_code, status FROM device ORDER BY device_code;

Headers versus modern titles

Older examples often use .headers on. In the modern 3.52+ formatting system, header behavior is integrated into .mode through options such as --titles on or --title. A compatibility build may still accept older header commands, but new course examples prefer mode-local configuration because it keeps the rendering contract together.

-- Current-first style.mode csv --titles onSELECT device_code, status FROM device;-- Older compatibility style you may encounter.headers on.mode csvSELECT device_code, status FROM device;

When maintaining an old script, do not rewrite syntax merely for fashion. First determine its minimum SQLite version and test the replacement output byte-for-byte if downstream tooling depends on it.

.width is now legacy; use .mode --widths

SQLite's current formatting documentation explicitly deprecates the old .width dot-command. It is retained for compatibility, but the supported model is to configure widths through .mode --widths (or --width in examples accepted by the current parser).

.mode column --titles on --widths 12,28,18SELECT device_code, device_name, statusFROM deviceORDER BY device_code;-- Reset to automatic widths.mode column --widths 0

A width controls display space, not the declared column type and not the stored value. A long device_name does not become shorter in the database because a terminal column is narrow.

NULL must not disappear into ambiguity

In some text modes, a SQL NULL can appear as an empty field unless you configure a visible representation. That can make “missing value” look identical to an empty string. For investigation, make NULL conspicuous.

SELECT 'empty string' AS label, '' AS valueUNION ALLSELECT 'sql null', NULL;.nullvalue <NULL>SELECT 'empty string' AS label, '' AS valueUNION ALLSELECT 'sql null', NULL;-- Current mode-local alternative.mode table --null "<NULL>" --titles on

.nullvalue changes shell rendering only. It does not update NULLs in the table and it does not redefine SQL NULL semantics.

Interactive and batch output can intentionally differ

SQLite 3.52+ uses a terminal-friendly default when connected interactively and legacy list output in batch mode. That is sensible for compatibility, but it means a command that “looks fine” at the prompt may produce a different shape when redirected in CI. Automation should choose an explicit mode instead of inheriting a context-sensitive default.

# Human inspectionsqlite3 -box fieldnotes.db "SELECT device_code,status FROM device ORDER BY device_code;"# CSV contractsqlite3 -csv -header fieldnotes.db "SELECT device_code,status FROM device ORDER BY device_code;"# JSON contractsqlite3 -json fieldnotes.db "SELECT device_code,status FROM device ORDER BY device_code;"

Formatting lab: one query, six representations

Use a disposable copy of fieldnotes.db. Run the query below in qbox, table, column, markdown, csv, and json. Record which formats preserve column labels by default on your build, how NULL appears, and which output you would choose for a person versus another program.

SELECT d.device_code,       d.status,       n.noted_at,       n.note_textFROM device AS dLEFT JOIN maintenance_note AS n ON n.device_id = d.device_idORDER BY d.device_code, n.noted_at;

Production judgment: output is an interface

Once a script's stdout is consumed by another tool, formatting is no longer cosmetic—it is an interface. Pin an explicit mode, title/header policy, NULL representation, separators, and minimum CLI version. If you need a durable application API, prefer a language SQLite driver and structured row objects instead of scraping the CLI's human-oriented rendering.

Summary and next lesson

Query values and CLI rendering are separate layers. Current SQLite has a substantially richer result formatter than older releases, and automation should never depend on the interactive default. The next lesson moves from interactive exploration to repeatable scripts, where input sources, error handling, stdout/stderr, and process exit status become part of correctness.

References

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