SQL Statements Versus Dot-Commands, Help, and Shell State

Learning outcomes

Chapter 1 established the architectural boundary between the sqlite3 shell and the SQLite library. This lesson turns that boundary into a practical debugging habit. You will learn to tell whether a line is being interpreted by the shell or by the SQL engine, inspect the database without guessing, recover from incomplete input, and make hidden shell state visible before it surprises you.

One prompt, two interpreters

When you type into sqlite3, not every line goes to the same interpreter. Most input is accumulated as SQL and eventually passed to the SQLite library. A line beginning with a dot at the left margin is intercepted by the command-line program itself. This is why SELECT works from Python, Java, C, or Node SQLite APIs while .tables does not: .tables is not part of the SQL language.

sqlite> .tablessite  device  maintenance_notesqlite> SELECT name FROM sqlite_schema WHERE type='table' ORDER BY name;devicemaintenance_notesite

Both inputs can answer a similar question, but they travel through different paths. The first is implemented by the shell as a convenience command. The second is ordinary SQL against SQLite's schema table.

Dot-command rules are deliberately different from SQL rules

SQL is free-form: it can span lines, include whitespace and SQL comments, and normally finishes when the shell recognizes a complete statement terminator. Dot-commands are line-oriented. Current SQLite documentation requires the dot to begin at the left margin, requires the command to fit on one input line, and does not allow a dot-command in the middle of an unfinished SQL statement.

SQLite 3.52.0 and later also tolerate a bare trailing semicolon on a dot-command, but that compatibility behavior should not blur the mental model. Write dot-commands without semicolons in course scripts; write SQL with explicit semicolon terminators.

The continuation prompt is information, not an error

If you enter SQL without finishing it, the shell changes from its main prompt to a continuation prompt. That means “I am still collecting SQL,” not “SQLite is hung.” Beginners often type a dot-command at this point and receive confusing SQL parse behavior because dot-commands are not allowed inside the unfinished statement.

sqlite> SELECT device_code, device_name   ...> FROM device   ...> WHERE status = 'active'   ...> ORDER BY device_code;PUMP-007|Cooling Water Pump 7SENS-003|Vibration Sensor 3sqlite> SELECT *   ...> FROM device   ...> .tables-- Wrong place: the shell is still collecting SQL.

If you simply forgot the terminator and the SQL is otherwise correct, finish it with ;. If the partially typed statement is wrong, use the terminal interrupt key (commonly Ctrl+C) to cancel the pending input, then start again. Do not keep adding random punctuation until the prompt changes; that makes diagnosis harder.

Use .help as live documentation for your actual build

The course records SQLite 3.53.4 as the current upstream release at generation time, but your shell can differ by version and compile options. Therefore, the highest-value CLI habit is not memorization: it is asking the executable what it supports.

sqlite> .versionsqlite> .helpsqlite> .help modesqlite> .help importsqlite> .help parametersqlite> .mode --list

.help without arguments lists documented commands. .help PATTERN narrows the output. If a command from an old article is absent, investigate before assuming your installation is broken.

Inspect the open database before touching it

Chapter 1 introduced a verification habit because a misspelled filename can create a new database. In Chapter 2 we formalize that habit into a short preflight sequence. Start every unfamiliar session by checking the active file and schema rather than trusting the terminal directory or command history.

sqlite> .databasessqlite> .tablessqlite> .schemasqlite> .indexessqlite> SELECT sqlite_version();

.databases tells you which logical database names are attached and which file backs each one. .tables gives a quick inventory of tables and views. .schema reveals the DDL. .indexes lists indexes. Together they answer the practical questions “what file is this?”, “what objects exist?”, and “does this look like the database I intended to edit?”

.schema, .fullschema, and .indexes answer different questions

.schema is usually the first choice when you want to understand object definitions. .fullschema goes further: it includes the schema plus the contents of SQLite's statistics tables when such statistics exist. Those statistics influence query planning and are not normally useful in a first glance. .indexes focuses only on index names and can be filtered to a table.

sqlite> .schema deviceCREATE TABLE device (...);sqlite> .indexes device-- Output depends on indexes currently present.sqlite> .fullschema-- More complete diagnostic output; may include sqlite_stat* content.

Do not confuse .fullschema with “a better .schema that should always be used.” More output is not automatically more useful. Start narrow and expand only when you need the additional planner metadata.

What happened to .show?

Older SQLite shell tutorials often use .show to print several shell settings at once. The current documented command list for the 3.52-era CLI does not advertise .show, although SQLite notes that deprecated compatibility commands may remain available. Because this course targets current patched builds, .show is treated as a compatibility probe, not as a required dependency.

sqlite> .help show-- If help is displayed, use that build's documented behavior.-- If no documented help exists, do not make automation depend on .show.sqlite> .modesqlite> .mode -vsqlite> .databasessqlite> .version

The current .mode -v output is particularly useful because modern result formatting contains many settings that older .show examples could not describe.

Useful command-line options before the prompt appears

The shell accepts options before it opens the database. Options are excellent for making a session reproducible because they establish behavior before interactive commands or scripts run. Run sqlite3 --help on your installation before using a version-sensitive option in automation.

Startup configuration is convenient—and a hidden dependency

A personal CLI startup file can make interactive work pleasant: for example, you might prefer a particular mode or timer setting. But that convenience becomes dangerous when a script silently inherits settings you forgot existed. SQLite provides the explicit -init FILENAME option, so reproducible labs and automation should prefer an initialization file that is part of the lab rather than depending on a user's private shell preferences.

# cli-init.txt.mode table --titles on --null NULL.timer off.echo off# Launch with an explicit init filesqlite3 -init cli-init.txt fieldnotes.db

Some platforms/builds also look for a per-user resource file such as .sqliterc. Treat that as user environment, not application configuration. If output or behavior differs unexpectedly between machines, startup configuration is one of the first hidden dependencies to investigate.

Discovery lab: identify the database without being told the schema

Use the Chapter 1 fieldnotes.db file or a disposable copy. Do not begin by opening the lesson's schema listing. Instead, act as if a teammate handed you an unfamiliar SQLite file.

sqlite3 -ifexists fieldnotes.db.databases.tables.schema.indexes.help schema.help indexesSELECT COUNT(*) AS site_count FROM site;SELECT COUNT(*) AS device_count FROM device;SELECT COUNT(*) AS note_count FROM maintenance_note;.quit

Expected logical state from Chapter 1 is two sites, three devices, and three maintenance notes. If your counts differ, that is not automatically wrong—the learner may have extended the lab—but you should be able to explain the difference before continuing.

Summary and next lesson

The sqlite3 prompt is a mixed language environment: SQL goes to the SQLite engine, while left-margin dot-commands are intercepted by the shell. Strong CLI users make state visible with .help, .databases, schema inspection, version checks, and explicit startup options. In the next lesson, we focus on another source of confusion: the result you see on screen is a presentation chosen by the shell, not the underlying data itself.

References

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