SQLite Shell Basics

The SQLite shell is an interactive program where you type SQL commands and see results instantly. It behaves like a conversation — you ask a question, the shell answers. Knowing the shell's built-in commands saves significant time and confusion when working with databases.

Opening the Shell

Type sqlite3 followed by a filename to open or create a database. If the file does not exist, SQLite creates it the moment you first write data to it.

$ sqlite3 school.db

SQLite version 3.46.0
Enter ".help" for usage hints.
sqlite>

The sqlite> prompt signals the shell is waiting for your input. SQL statements end with a semicolon (;). Dot commands do not.

Two Types of Commands

COMMAND TYPE       EXAMPLE              ENDS WITH
───────────────────────────────────────────────────
SQL statement      SELECT * FROM t;     semicolon ;
Dot command        .tables              nothing

Dot commands are special instructions to the shell itself, not to the database. They control display formatting, help, import/export, and other shell behaviors.

Essential Dot Commands

.help — List All Commands

sqlite> .help
.open FILE           Open a database file
.tables              List all tables
.schema TABLE        Show CREATE statement
.quit                Exit the shell
...

.tables — List All Tables

sqlite> .tables
courses   students   teachers

.schema — See Table Structure

sqlite> .schema students
CREATE TABLE students (
  id    INTEGER PRIMARY KEY,
  name  TEXT NOT NULL,
  age   INTEGER
);

.open — Open Another Database

sqlite> .open library.db

.quit — Exit the Shell

sqlite> .quit
$

Formatting Output

By default, SQLite outputs columns separated by a pipe character. Change the display mode to make results easier to read.

DEFAULT OUTPUT (pipe-separated)
──────────────────────────────────
sqlite> SELECT * FROM students;
1|Alice|20
2|Bob|22

COLUMN MODE (aligned columns)
──────────────────────────────────
sqlite> .mode column
sqlite> .headers on
sqlite> SELECT * FROM students;
id  name   age
──  ─────  ───
1   Alice  20
2   Bob    22

TABLE MODE (box-drawn table)
──────────────────────────────────
sqlite> .mode table
sqlite> SELECT * FROM students;
+────+───────+─────+
| id | name  | age |
+────+───────+─────+
| 1  | Alice | 20  |
| 2  | Bob   | 22  |
+────+───────+─────+

Available Display Modes

Importing and Exporting Data

Export to CSV

sqlite> .mode csv
sqlite> .output students_export.csv
sqlite> SELECT * FROM students;
sqlite> .output stdout

Import from CSV

sqlite> .mode csv
sqlite> .import students_data.csv students

Running a SQL File

You can write multiple SQL statements in a .sql file and run the entire file at once.

-- setup.sql
CREATE TABLE products (id INTEGER, name TEXT, price REAL);
INSERT INTO products VALUES (1, 'Pen', 0.99);
INSERT INTO products VALUES (2, 'Notebook', 3.49);

$ sqlite3 store.db < setup.sql

Or from inside the shell:

sqlite> .read setup.sql

Checking the Current Database

sqlite> .databases
main: /home/user/school.db  r/w

Multiline SQL in the Shell

You can press Enter before typing the semicolon. The shell waits for a semicolon before executing.

sqlite> SELECT id,
   ...>        name,
   ...>        age
   ...> FROM students
   ...> WHERE age > 20;

Shell Interaction Diagram

You type SQL
     │
     ▼
sqlite> SELECT * FROM students;
     │
     ▼
Shell sends to SQLite engine
     │
     ▼
SQLite engine reads school.db
     │
     ▼
Result rows returned to shell
     │
     ▼
Shell displays rows on screen

Key Takeaways

• SQL statements end with ;; dot commands do not
• .tables lists all tables; .schema shows how a table was created
• .mode column and .headers on make output readable
• .read file.sql runs a SQL script file from inside the shell
• .quit exits the shell safely without data loss