Build and Inspect a SQLite Database with sqlite3

You will create a small SQLite database, insert records, query them in a readable format, inspect the schema, and write a separate backup. The examples use the installed sqlite3 package version 3.45.1-1ubuntu2.8. Its executable reports SQLite 3.53.4.

Allow about fifteen minutes. You need a shell and a directory where you can create a test file. The normal commands are unprivileged. Do not use sudo to open a database unless the file permissions genuinely require it: changing ownership or running the CLI as root can create a later access problem.

1. Check the installed command

Confirm which executable will run and record its version:

$ command -v sqlite3
/usr/bin/sqlite3
$ sqlite3 --version
3.53.4 2026-07-24 19:02:57 ... (64-bit)

The timestamp and source hash vary by build. The useful check is that the command exists and reports a version. The package version and SQLite library version are related but not identical labels, so keep both when reporting a host.

Checkpoint: if command -v finds nothing, stop here and install the distribution package through your normal software-management process. Nothing in this guide needs an elevated shell after the command is installed.

2. Create a disposable database

Choose a path that is safe to replace. This example uses /tmp; use a project path for data you intend to keep:

$ DB=/tmp/sqlite3-guide.db
$ rm -f "$DB"
$ sqlite3 "$DB" <<'SQL'
CREATE TABLE tasks (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    done INTEGER NOT NULL DEFAULT 0
);
INSERT INTO tasks (title) VALUES ('write the report');
INSERT INTO tasks (title, done) VALUES ('check the backup', 1);
SQL

The database file is created when it does not exist. The here-document sends several SQL statements to a non-interactive session, and the terminating SQL line is shell syntax, not SQL. The rm -f line is destructive: use it only for this disposable path. If the path contains useful data, omit that line and make a backup first.

Verify that the file exists and that SQLite can read its table:

$ ls -l "$DB"
$ sqlite3 "$DB" 'SELECT id, title, done FROM tasks;'
1|write the report|0
2|check the backup|1

3. Query interactively without losing your place

Start an interactive session against the same file:

$ sqlite3 "$DB"
SQLite version 3.53.4 ...
Enter ".help" for usage hints.
sqlite> .tables
tasks
sqlite> SELECT title FROM tasks WHERE done = 0;
write the report
sqlite> .quit

SQL statements end with a semicolon. Commands beginning with a dot are sqlite3 meta-commands, handled by the command-line program rather than by the SQLite engine. Use .help when you forget a command. The default output mode is list-style text with a | separator, which is compact but not ideal for every query.

Checkpoint: .tables should show tasks. If it does not, check the database path before running any write statement. Opening the wrong filename can quietly create a second, empty database.

4. Make query output easier to read

For a human-readable result, select column mode and headers before the query. The -header and -column options apply before the SQL argument:

$ sqlite3 -header -column "$DB" 'SELECT id, title, done FROM tasks ORDER BY id;'
id  title              done
--  -----------------  ----
1   write the report   0
2   check the backup   1

For scripts or data exchange, choose the output format deliberately. CSV is useful for spreadsheet-compatible output, while JSON is useful when another program will parse the result:

$ sqlite3 -json "$DB" 'SELECT id, title, done FROM tasks ORDER BY id;'
[{"id":1,"title":"write the report","done":0},{"id":2,"title":"check the backup","done":1}]

Do not parse the default pipe-separated output if values may contain the separator or newlines. Select -csv or -json according to the consumer, and test with representative data.

5. Inspect the schema and attached files

Use meta-commands when you need to understand the database before changing it:

$ sqlite3 "$DB" <<'SQL'
.schema tasks
.databases
SQL
CREATE TABLE tasks (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    done INTEGER NOT NULL DEFAULT 0
);
main: /tmp/sqlite3-guide.db r/w

.schema tasks prints the table definition. .databases lists the attached database names and file paths, which is a useful guard against editing the wrong file. The exact formatting of paths and schema output can vary with the installed build.

For a read-only inspection of an existing database, add -readonly:

$ sqlite3 -readonly "$DB" 'SELECT count(*) AS task_count FROM tasks;'
2

This is a useful safety boundary when you are exploring an unfamiliar file. It does not grant access to a file that your account cannot read, and it does not replace a backup.

6. Make and verify a separate backup

Use the CLI's .backup command to write a copy to a new path. Do not overwrite the only known-good copy:

$ BACKUP=/tmp/sqlite3-guide-backup.db
$ rm -f "$BACKUP"
$ sqlite3 "$DB" ".backup '$BACKUP'"
$ sqlite3 -readonly "$BACKUP" 'PRAGMA integrity_check;'
ok

The two rm -f commands above are only safe because both paths are disposable examples. For a real backup destination, use a new filename or remove an old copy only after checking it is the intended file. Keep the original database until the backup has passed an integrity check and, for important data, a restore test.

There is no undo for an accidental overwrite of a backup file. Recovery means restoring from another known-good copy. If a write operation fails halfway through a larger workflow, stop and inspect the database rather than automatically repeating every statement.

7. Avoid the common traps

A missing database filename is not an error: the CLI uses an in-memory database when no filename is supplied. That is useful for experiments, but the data disappears when the process exits. Always include the intended path when persistence matters.

The initialisation file can also change an interactive session. sqlite3 reads the first existing file from ${XDG_CONFIG_HOME}/sqlite3/sqliterc or ~/.sqliterc, then processes a file named by -init. These files can alter modes, prompts and other settings. For a reproducible script, use -batch and consider -noinit so a personal configuration cannot change its output.

If a script should stop at the first SQL error, add -bail. Without it, the installed CLI defaults to continuing after an error. Capture the shell status and inspect output before treating a batch run as successful.