sqlite3 Command Line: Create and Query SQLite Databases

When you need to inspect an application’s database or query a CSV file, you do not always need a database server. SQLite stores a database in a file, with no service to configure, and the sqlite3 command gives you an interactive SQL shell for working with it.

This guide explains how to use the sqlite3 command-line shell to create databases, run queries, import and export data, and make backups.

Installing sqlite3 #

On Ubuntu, Debian, and Derivatives, install the command-line shell with the following command:

sudo apt install sqlite3

On Fedora, RHEL, and Derivatives, the package is named sqlite:

sudo dnf install sqlite

Verify the installation:

sqlite3 --version

The output shows the SQLite version, release date, and source identifier. The installed version depends on your distribution.

Opening and Creating a Database #

The sqlite3 command has the following syntax:

sqlite3 [OPTIONS] [DATABASE_FILE] [SQL_OR_DOT_COMMAND...]

Pass a filename to open a database. If the file does not exist, SQLite creates it when you first write to the database:

sqlite3 tasks.db

You are now in the interactive shell at the sqlite> prompt. Opening a nonexistent database and quitting immediately leaves no database file behind. Running sqlite3 with no filename opens a temporary in-memory database, useful for trying out SQL that you do not want to keep.

Two kinds of input work at the sqlite> prompt: SQL statements, which end with a semicolon, and dot commands like .help and .quit, which start with a dot and control the shell itself. Put each dot command on its own line without a semicolon. Exit with .quit or Ctrl+D.

The examples below use a new tasks.db database. Enter commands marked with sqlite> inside the SQLite shell, and commands marked with $ in your Linux terminal. You do not need to type either prompt.

Creating Tables and Inserting Data #

Create a table and add a few rows:

CREATE TABLE tasks (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    priority INTEGER DEFAULT 3,
    done INTEGER DEFAULT 0
);
INSERT INTO tasks (title, priority) VALUES ('Rotate backup drives', 1);
INSERT INTO tasks (title, priority) VALUES ('Update nginx config', 2);
INSERT INTO tasks (title) VALUES ('Clean up /tmp scripts');

An INTEGER PRIMARY KEY column receives an ID automatically when you omit it from an insert. In this new table, the rows receive IDs 1, 2, and 3. SQLite normally chooses one more than the largest existing ID, so it can reuse an ID after the row with the highest ID is deleted. The separate AUTOINCREMENT keyword prevents reuse of previously committed IDs, but adds overhead and is usually unnecessary.

SQLite uses flexible typing for ordinary columns. For example, the priority column can hold text that cannot be converted to an integer. The INTEGER PRIMARY KEY column is an exception and must contain an integer. If you need stricter type checks, SQLite 3.37.0 and later support tables declared with STRICT.

Check what you have:

.tables
.schema tasks

SQLite lists the table name and its definition:

tasks
CREATE TABLE tasks (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    priority INTEGER DEFAULT 3,
    done INTEGER DEFAULT 0
);

.tables lists all tables in the database, and .schema prints the SQL that defines them, which is the fastest way to orient yourself in an unfamiliar database file.

Querying Data #

To list unfinished tasks in priority order, run a SELECT statement. We explicitly select list mode and turn headers off to get the same pipe-separated output across SQLite versions:

.mode list
.headers off
SELECT * FROM tasks WHERE done = 0 ORDER BY priority;

The output is:

1|Rotate backup drives|1|0
2|Update nginx config|2|0
3|Clean up /tmp scripts|3|0

Each line contains the task ID, title, priority, and completion flag. All three tasks have a done value of 0, so they are included.

SQLite 3.52.0 and later use a boxed table by default in interactive sessions. Earlier versions use pipe-separated list output, which remains the default for batch scripts. Setting the mode explicitly avoids relying on that default. See the SQLite output formatting documentation for details.

For aligned columns with column names, switch to column mode and enable headers:

.mode column
.headers on
SELECT * FROM tasks WHERE done = 0 ORDER BY priority;

The output will look similar to this:

id  title                  priority  done
--  ---------------------  --------  ----
1   Rotate backup drives   1         0
2   Update nginx config    2         0
3   Clean up /tmp scripts  3         0

The rows are unchanged, but the headers and spacing make the columns easier to read. Column spacing and alignment can vary by SQLite version. You can also use .mode box for table borders, .mode json for JSON output, and .mode csv for CSV export. The mode stays in effect until you change it or exit the shell.

Updating and Deleting Rows #

To mark the second task as completed, use UPDATE with a WHERE clause, then check the matching row:

UPDATE tasks SET done = 1 WHERE id = 2;
SELECT * FROM tasks WHERE done = 1;

The query shows task 2 with a done value of 1. Without a WHERE clause, an UPDATE or DELETE statement affects every row in the table.

Before deleting rows from an important database, make a backup. You can try a deletion inside a transaction and undo it with ROLLBACK. The following example temporarily deletes completed tasks, shows the remaining rows, and restores the deleted data:

BEGIN;
DELETE FROM tasks WHERE done = 1;
SELECT * FROM tasks ORDER BY id;
ROLLBACK;

The query shows tasks 1 and 3, but ROLLBACK brings task 2 back. To keep the deletion, run the transaction again and replace ROLLBACK; with COMMIT; after checking the result. Once committed, the deletion cannot be undone with ROLLBACK.

Running Queries from the Shell #

Run the commands in this section from your Linux terminal. If you are still at the sqlite> prompt, leave the SQLite shell first:

.quit

For scripting, skip the interactive prompt entirely by passing SQL as an argument:

sqlite3 tasks.db "SELECT title FROM tasks WHERE priority = 1;"

The output is:

Rotate backup drives

The query returns the title of the task with priority 1, then the command exits.

Formatting options give you the same control as the dot commands. For a readable report that also opens the database read-only, run:

sqlite3 -readonly -header -column tasks.db "SELECT * FROM tasks ORDER BY id;"

The options used here are:

• -readonly - Open the database without allowing writes. The file must already exist.
• -header - Include column names in the output.
• -column - Align the output in columns.

Use -json or -csv instead of -column when another program needs to read the results. This one-shot form is useful in cron jobs and shell pipelines: the command opens a file, runs the query, and exits.

Importing and Exporting CSV #

For this example, save the following contents as contacts.csv in the same directory as tasks.db:

name,email
Alice,alice@example.com
Bob,bob@example.com

Reopen the database from your terminal:

sqlite3 tasks.db

To load the CSV file, use .import --csv inside the SQLite shell. If the target table does not exist, SQLite creates it and uses the first row as column names:

.import --csv contacts.csv contacts
.mode column
.headers on
SELECT * FROM contacts;

The output will look similar to this:

name   email
-----  -----------------
Alice  alice@example.com
Bob    bob@example.com

The first CSV row supplied the name and email column names. Only Alice and Bob were imported as data.

If the target table already exists, the first row is imported as data too. Use --skip 1 when the CSV file has a header. For example, we can create a separate table and import the same file into it:

CREATE TABLE mailing_list (name TEXT, email TEXT);
.import --csv --skip 1 contacts.csv mailing_list

This imports the two data rows without adding a row containing the column labels. Importing the same file again appends more rows; it does not replace existing data.

To export unfinished tasks, set CSV mode, enable headers, and send the next query to a file with .once. Choose an unused filename because an existing output file will be overwritten:

.mode csv
.headers on
.once report.csv
SELECT title, priority FROM tasks WHERE done = 0 ORDER BY priority;

The result lands in report.csv with a header row. Subsequent queries print to the terminal again, still in CSV mode. Use .output report.csv instead of .once when several queries should go to the same file, and .output alone to return to the terminal.

Backing Up and Restoring #

Run the backup and restore commands below from your Linux terminal. Use .quit first if you are still in the SQLite shell.

The .backup command creates a consistent copy of the database, including committed data that is still in a write-ahead log (WAL). Choose an unused backup filename because .backup can overwrite an existing database:

sqlite3 -readonly tasks.db ".backup tasks-backup.db"

The backup is a database file you can open directly with sqlite3. To check its structure, run:

sqlite3 -readonly tasks-backup.db "PRAGMA integrity_check;"

For a database that passes the check, the output is:

ok

This checks the database structure. It does not confirm that the backup contains every row you expected, so also check important tables before relying on it.

For a plain-text SQL backup, use .dump. The shell redirection also overwrites an existing file, so choose an unused filename:

sqlite3 -readonly tasks.db .dump > tasks-backup.sql

Restore the SQL into a new database file. Make sure restored.db does not already exist. The dump contains statements to recreate the tables and insert the rows:

sqlite3 -bail restored.db < tasks-backup.sql

The -bail option stops processing if a statement fails. The SQL dump is plain text, so you can inspect or compress it before restoring. If you also work with MySQL, see our guide to backing up and restoring databases with mysqldump .

Use .backup for databases that an application is using. Copying only the .db file can miss committed data stored in a -wal file, and copying files during a write can produce an inconsistent backup. See SQLite’s backup guidance for the conditions needed to make a safe file copy.

Quick Reference #

For a printable quick reference, see the SQLite cheatsheet .

For a printable quick reference, see the SQLite cheatsheet .

Troubleshooting #

Database is locked
Another connection may be holding a write transaction. Finish it with COMMIT or ROLLBACK, or wait for the application to finish. Inside your SQLite session, .timeout 5000 makes operations wait up to five seconds for a lock before failing. It does not release another connection’s lock.

Unable to open the database file
Check the path and permissions. SQLite can create a database file, but it cannot create a missing parent directory. For writes, you also need permission to create journal or WAL files in the database directory. With -readonly, the database file must already exist.

No such table
Check .databases to see which file is open and .tables to see its tables. A relative path is resolved from your current directory, so a typo can open a new, empty database. Use an absolute path or -readonly when inspecting an existing database.

SQLite keeps showing the continuation prompt
A SQL statement is incomplete. Add the missing semicolon or close an unmatched quote or parenthesis. Press Ctrl+C to cancel the current input, then enter the statement again. Dot commands must start on their own line and cannot be entered in the middle of an unfinished SQL statement.

Conclusion #

When inspecting an existing application database, start with -readonly and .tables. Before changing its data, make a .backup copy and check that you can open it.

About the authors

Dejan Panovski

Dejan Panovski is the founder of Linuxize, an RHCSA-certified Linux system administrator and DevOps engineer based in Skopje, Macedonia. Author of 1000+ Linux tutorials with 20+ years of experience turning complex Linux tasks into clear, reliable guides.

View author page