(opens in a new tab)

SQLite Commands Cheat Sheet: CLI and CREATE TABLE

September 19, 2026 by Chat2DB Team

SQLite has two command languages that people tend to mix up. The first is the set of dot-commands understood by the sqlite3 command-line shell: .tables, .schema, .mode, .import and friends. They are instructions to the shell program, never reach the database engine, and do not end with a semicolon. The second is SQL itself, where SQLite has a few habits of its own: flexible typing, a hidden rowid, foreign keys that are switched off by default, and an ALTER TABLE that can do less than you might expect. This cheat sheet covers both, in the order you usually need them: opening and inspecting a database, formatting output, moving data in and out, creating tables properly, and then the SQL features and PRAGMAs that come up in day-to-day work. Every example is copy-paste ready for a recent sqlite3 build.

Version notes are included where a feature is newer than the SQLite that some Linux distributions and older macOS releases still ship. Check yours with sqlite3 --version or, inside the shell, SELECT sqlite_version();.

Starting the sqlite3 shell

Run sqlite3 with a file name to open (or create) a database. If the file does not exist, SQLite creates it lazily the first time you write something.

sqlite3 app.db                       # open or create app.db
sqlite3                              # in-memory database, gone when you quit
sqlite3 -readonly app.db             # open without write access
sqlite3 app.db "SELECT count(*) FROM users;"          # run one statement and exit
sqlite3 -header -csv app.db "SELECT * FROM users;" > users.csv
sqlite3 -json app.db "SELECT id, email FROM users LIMIT 3;"
sqlite3 app.db < schema.sql          # execute a script file

Command-line flags such as -header, -csv, -json, -box and -column mirror the .headers and .mode dot-commands, so anything you can do interactively you can also do in a shell script or a cron job.

SQLite dot-commands cheat sheet

Dot-commands must start at the beginning of a line, take whitespace-separated arguments, and are not terminated with a semicolon. Type .help for the full list or .help import for help on a single command.

Inspecting a database you have never seen

A good first minute with an unfamiliar SQLite file looks like this:

sqlite> .databases
main: /home/dev/app.db r/w
sqlite> .tables
orders    products  users
sqlite> .schema users
CREATE TABLE users (
  id         INTEGER PRIMARY KEY,
  email      TEXT NOT NULL UNIQUE,
  created_at TEXT NOT NULL DEFAULT (datetime('now'))
);
sqlite> .indexes orders
idx_orders_user_id
sqlite> PRAGMA table_info(orders);

.schema prints the stored DDL exactly as it was written, including comments, which often tells you more about intent than a column list does. PRAGMA table_info gives you a structured view with column position, declared type, NOT NULL flag, default value and primary-key position.

Output modes

The default list mode separates columns with a pipe character and prints no headers, which is fine for scripts and unreadable for humans. Switch to a better mode at the start of each session, or put your preferences in ~/.sqliterc, which the shell reads on startup.

A minimal ~/.sqliterc that makes the shell pleasant:

.headers on
.mode box
.nullvalue NULL
.timer on

box, markdown, json and table arrived in SQLite 3.33. On an older shell, .headers on plus .mode column is the closest equivalent.

Importing CSV

.import --csv reads a CSV file into a table. If the table does not exist, the shell creates it and uses the first row as column names, with every column declared as TEXT. If the table already exists, every row, including the header, is treated as data, so add --skip 1.

sqlite> .import --csv customers.csv customers
sqlite> .schema customers
CREATE TABLE IF NOT EXISTS "customers"(
"id" TEXT, "name" TEXT, "country" TEXT, "signup_date" TEXT);

sqlite> CREATE TABLE products (sku TEXT PRIMARY KEY, name TEXT NOT NULL, price REAL);
sqlite> .import --csv --skip 1 products.csv products

For real work, create the table first with proper types and constraints and then import with --skip 1. Importing into an auto-created all-TEXT table means numbers sort as strings: '10' comes before '9'.

Exporting with output and once

.output redirects everything until you reset it; .once redirects only the next statement. Combine them with a mode:

sqlite> .headers on
sqlite> .mode csv
sqlite> .once orders_2026.csv
sqlite> SELECT * FROM orders WHERE created_at >= '2026-01-01';

sqlite> .mode json
sqlite> .once users.json
sqlite> SELECT id, email FROM users;

sqlite> .excel
sqlite> SELECT country, count(*) AS customers FROM customers GROUP BY country;

.excel writes the next result to a temporary CSV and opens it with whatever application your system associates with CSV files.

Dump, read, backup and restore

sqlite3 app.db .dump > app.sql            # full SQL dump
sqlite3 app.db ".dump users" > users.sql  # one table
sqlite3 restored.db < app.sql             # rebuild from the dump
sqlite3 app.db ".backup app-2026-09-19.db"

Use .dump when you need a text artifact you can diff, edit, or load into another engine after some cleanup. Use .backup for fast, consistent binary copies of a database that other processes may be writing to; it uses SQLite's online backup API rather than copying the file byte by byte, which is the safe way to back up a live database. .restore goes the other direction and replaces the current database's contents with the backup file, so be sure you are connected to the right file before running it.

.read executes a file of SQL and dot-commands, which makes it the natural way to run migrations or seed scripts from inside an interactive session:

sqlite> .read migrations/004_add_orders.sql

SQLite CREATE TABLE

The core syntax is the same as in any SQL database, but the details matter more in SQLite because the engine is permissive by default. Here is a realistic starting point:

CREATE TABLE IF NOT EXISTS users (
  id          INTEGER PRIMARY KEY,
  email       TEXT    NOT NULL UNIQUE COLLATE NOCASE,
  full_name   TEXT    NOT NULL,
  status      TEXT    NOT NULL DEFAULT 'active'
                      CHECK (status IN ('active', 'suspended', 'deleted')),
  credit      INTEGER NOT NULL DEFAULT 0 CHECK (credit >= 0),
  created_at  TEXT    NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))
);
 
CREATE TABLE IF NOT EXISTS orders (
  id          INTEGER PRIMARY KEY,
  user_id     INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  total_cents INTEGER NOT NULL CHECK (total_cents >= 0),
  placed_at   TEXT    NOT NULL DEFAULT (datetime('now')),
  UNIQUE (user_id, placed_at)
);

Note that a default value that is an expression, like datetime('now'), must be wrapped in parentheses; a plain literal like 'active' or 0 does not need them.

Types and type affinity

SQLite stores each value with one of five storage classes: NULL, INTEGER, REAL, TEXT or BLOB. The type you declare on a column does not restrict what you can store; it only gives the column an affinity, a preference SQLite uses to convert incoming values when the conversion is lossless. The affinity is chosen from the declared type name with a simple set of rules applied in order:

Two consequences surprise people. VARCHAR(255) does not limit length; SQLite ignores the number. And a column declared INTEGER will happily store the string 'abc', because that value cannot be converted to an integer without loss, so SQLite keeps it as TEXT:

CREATE TABLE t (n INTEGER);
INSERT INTO t VALUES (42), ('42'), ('abc'), (4.0);
SELECT n, typeof(n) FROM t;
-- 42   integer
-- 42   integer   ('42' was converted)
-- abc  text      (kept as text)
-- 4    integer   (4.0 converted because it is lossless)

There is no native boolean or date type. Booleans are stored as 0 and 1 (the keywords TRUE and FALSE are accepted as aliases), and dates are usually stored as ISO-8601 TEXT, Unix-epoch INTEGER, or Julian-day REAL.

INTEGER PRIMARY KEY and the rowid

Every ordinary SQLite table has a hidden 64-bit integer key called rowid. If you declare a column as exactly INTEGER PRIMARY KEY, that column becomes an alias for the rowid: it costs no extra storage, lookups by it are the fastest possible, and if you insert NULL (or omit it) SQLite assigns the next value automatically.

INSERT INTO users (email, full_name) VALUES ('ana@example.com', 'Ana Silva');
SELECT id, rowid FROM users;   -- the same number twice
SELECT last_insert_rowid();

The alias only happens with the exact type name INTEGER. INT PRIMARY KEY or BIGINT PRIMARY KEY creates an ordinary column plus a separate unique index, which is slower and does not auto-assign values.

The AUTOINCREMENT caveat

Adding AUTOINCREMENT does not turn on auto-increment; INTEGER PRIMARY KEY already does that. What it changes is reuse: without it, SQLite normally picks one more than the current largest rowid, so if you delete the row with the largest id, that id can be handed out again. With AUTOINCREMENT, SQLite tracks the highest value ever used in the internal sqlite_sequence table and never reuses it.

CREATE TABLE audit_log (
  id      INTEGER PRIMARY KEY AUTOINCREMENT,
  message TEXT NOT NULL
);
SELECT * FROM sqlite_sequence;

That bookkeeping costs an extra table write on every insert, and the SQLite documentation recommends avoiding it unless you genuinely need ids that are never reused, for example when ids are exposed to external systems that must not see a recycled one.

STRICT tables

Since SQLite 3.37 you can opt out of flexible typing per table with the STRICT keyword. Strict tables only accept the types INT, INTEGER, REAL, TEXT, BLOB and ANY, and reject values that cannot be losslessly converted to the declared type.

CREATE TABLE payments (
  id          INTEGER PRIMARY KEY,
  order_id    INTEGER NOT NULL,
  amount      INTEGER NOT NULL,
  meta        ANY
) STRICT;
 
INSERT INTO payments (order_id, amount) VALUES (1, 'lots');
-- Runtime error: cannot store TEXT value in INTEGER column payments.amount

For new applications, STRICT catches a whole class of bugs that would otherwise surface much later as odd sorting or failed comparisons. The trade-off is that older SQLite versions cannot open a database containing strict tables for writing.

WITHOUT ROWID tables

A WITHOUT ROWID table stores rows in a B-tree keyed by the declared primary key instead of the hidden rowid. It must have an explicit PRIMARY KEY. It suits tables with a non-integer or composite key and small rows, such as lookup and mapping tables:

CREATE TABLE user_roles (
  user_id INTEGER NOT NULL,
  role    TEXT    NOT NULL,
  PRIMARY KEY (user_id, role)
) WITHOUT ROWID;

With a regular table, this schema would store the key twice: once in the table and once in the automatic index that backs the primary key. WITHOUT ROWID stores it once. It is not a good fit for tables with large rows, and last_insert_rowid() does not apply to it.

CHECK, UNIQUE and FOREIGN KEY constraints

SQLite enforces NOT NULL, UNIQUE, CHECK and PRIMARY KEY constraints out of the box. Foreign keys are parsed and stored but not enforced until you turn enforcement on, and the setting is per connection, so every application connection must run it:

PRAGMA foreign_keys = ON;
 
INSERT INTO orders (user_id, total_cents) VALUES (9999, 500);
-- Runtime error: FOREIGN KEY constraint failed
 
PRAGMA foreign_key_check;   -- list existing rows that violate a foreign key

If you enable foreign keys on an existing database, run PRAGMA foreign_key_check; first to find orphaned rows written while enforcement was off. Also note that SQLite allows NULL in a PRIMARY KEY column of an ordinary table for historical reasons; add NOT NULL explicitly on non-integer primary keys, or use a STRICT or WITHOUT ROWID table, which reject it.

Generated columns

Generated columns (SQLite 3.31+) compute their value from other columns. VIRTUAL columns are computed when read; STORED columns are computed on write and take space on disk. Both can be indexed.

CREATE TABLE line_items (
  id          INTEGER PRIMARY KEY,
  qty         INTEGER NOT NULL,
  unit_cents  INTEGER NOT NULL,
  total_cents INTEGER GENERATED ALWAYS AS (qty * unit_cents) STORED,
  email       TEXT,
  email_lower TEXT GENERATED ALWAYS AS (lower(email)) VIRTUAL
);
CREATE INDEX idx_line_items_email_lower ON line_items(email_lower);

ALTER TABLE and its limits

SQLite's ALTER TABLE supports a short list of operations:

ADD COLUMN cannot add a PRIMARY KEY or UNIQUE column, cannot use a non-constant default such as CURRENT_TIMESTAMP, and a NOT NULL column needs a non-NULL default. DROP COLUMN fails if the column is part of a primary key, a unique constraint, an index, a foreign key or a generated column expression.

Anything else, such as changing a column's type, adding a CHECK constraint, or changing a primary key, requires rebuilding the table:

PRAGMA foreign_keys = OFF;
BEGIN;
CREATE TABLE users_new (
  id         INTEGER PRIMARY KEY,
  email      TEXT NOT NULL UNIQUE,
  full_name  TEXT NOT NULL,
  credit     INTEGER NOT NULL DEFAULT 0 CHECK (credit >= 0)
) STRICT;
INSERT INTO users_new (id, email, full_name, credit)
  SELECT id, email, full_name, credit FROM users;
DROP TABLE users;
ALTER TABLE users_new RENAME TO users;
-- recreate indexes, triggers and views that referenced users here
PRAGMA foreign_key_check;
COMMIT;
PRAGMA foreign_keys = ON;

Indexes

CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE UNIQUE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_user_date ON orders(user_id, placed_at DESC);
 
-- partial index: only index the rows you actually query
CREATE INDEX idx_users_active ON users(email) WHERE status = 'active';
 
-- expression index
CREATE INDEX idx_users_email_lower ON users(lower(email));
 
DROP INDEX IF EXISTS idx_users_active;

A partial index is used only when the query's WHERE clause implies the index's WHERE clause. An expression index is used only when the query contains the same expression, so WHERE lower(email) = ? can use idx_users_email_lower but WHERE email = ? cannot. Run ANALYZE; (or PRAGMA optimize; before closing long-lived connections) so the query planner has statistics to choose between indexes.

INSERT, UPSERT and RETURNING

-- multi-row insert
INSERT INTO users (email, full_name) VALUES
  ('bo@example.com',  'Bo Chen'),
  ('cy@example.com',  'Cy Okafor');
 
-- skip rows that would violate a constraint
INSERT OR IGNORE INTO users (email, full_name) VALUES ('bo@example.com', 'Bo Chen');
 
-- UPSERT (3.24+): insert, or update the existing row on conflict
INSERT INTO users (email, full_name, credit)
VALUES ('bo@example.com', 'Bo Chen', 50)
ON CONFLICT(email) DO UPDATE SET
  full_name = excluded.full_name,
  credit    = users.credit + excluded.credit;
 
-- RETURNING (3.35+): get generated values back without a second query
INSERT INTO orders (user_id, total_cents) VALUES (1, 2599)
RETURNING id, placed_at;
 
UPDATE users SET status = 'suspended' WHERE credit = 0 RETURNING id, email;
DELETE FROM orders WHERE placed_at < '2025-01-01' RETURNING id;

excluded refers to the row that would have been inserted. Prefer UPSERT over INSERT OR REPLACE: REPLACE deletes the conflicting row and inserts a new one, which fires delete triggers, cascades foreign-key deletes, and resets any columns you did not supply to their defaults.

One parsing quirk: when the source is a SELECT, add WHERE true before ON CONFLICT so the parser does not read ON as a join condition.

INSERT INTO products (sku, name, price)
SELECT sku, name, price FROM staging_products WHERE true
ON CONFLICT(sku) DO UPDATE SET price = excluded.price;

Date and time functions

SQLite has no date type, but it has a solid set of functions that work on ISO-8601 strings, Unix timestamps and Julian days, all in UTC unless you add the localtime modifier.

SELECT strftime('%Y-%m', placed_at) AS month,
       count(*)                     AS orders,
       sum(total_cents) / 100.0     AS revenue
FROM orders
WHERE placed_at >= date('now', 'start of year')
GROUP BY month
ORDER BY month;

Store timestamps in one consistent format per column. ISO-8601 TEXT sorts correctly as a string and is readable; INTEGER epoch seconds are compact and fast to compare.

JSON functions

JSON support is built in by default since SQLite 3.38. JSON is stored as TEXT (or as the binary JSONB format in newer versions) and queried with functions and operators:

CREATE TABLE events (
  id      INTEGER PRIMARY KEY,
  payload TEXT NOT NULL CHECK (json_valid(payload))
);
INSERT INTO events (payload) VALUES
  ('{"type":"signup","user":{"id":7,"plan":"pro"},"tags":["web","eu"]}');
 
SELECT json_extract(payload, '$.user.plan') FROM events;   -- pro
SELECT payload ->> '$.user.id'  FROM events;              -- 7 (SQL value)
SELECT payload ->  '$.user'     FROM events;              -- {"id":7,"plan":"pro"} (JSON text)
 
-- expand an array into rows
SELECT e.id, t.value AS tag
FROM events e, json_each(e.payload, '$.tags') t;
 
-- modify JSON
UPDATE events SET payload = json_set(payload, '$.user.plan', 'team') WHERE id = 1;
 
-- aggregate rows into JSON
SELECT json_group_array(json_object('id', id, 'email', email)) FROM users;
 
-- index a JSON field
CREATE INDEX idx_events_type ON events(payload ->> '$.type');

The -> operator returns JSON text; the ->> operator returns a plain SQL value (text, integer, real or NULL), which is what you usually want in WHERE clauses and comparisons. Both operators were added in 3.38; on older versions use json_extract.

Useful PRAGMAs

PRAGMA statements read or change engine settings and expose metadata. The ones worth memorizing:

user_version is a simple, dependency-free way to track migrations: read it on startup, apply every migration with a higher number inside a transaction, and set the new value in the same transaction.

PRAGMA journal_mode = WAL;     -- returns: wal
PRAGMA integrity_check;        -- returns: ok
PRAGMA user_version;           -- returns: 0 on a fresh database

ATTACH DATABASE

ATTACH opens a second database file on the same connection under a schema name, which lets you query and copy across files in a single statement:

ATTACH DATABASE 'archive.db' AS archive;
 
.databases
-- main:    /home/dev/app.db r/w
-- archive: /home/dev/archive.db r/w
 
CREATE TABLE IF NOT EXISTS archive.orders AS SELECT * FROM main.orders WHERE 0;
INSERT INTO archive.orders SELECT * FROM main.orders WHERE placed_at < '2025-01-01';
DELETE FROM main.orders WHERE placed_at < '2025-01-01';
 
DETACH DATABASE archive;

Transactions that touch several attached databases are atomic across all of them, except when the main database is in WAL mode, where each file commits atomically on its own.

VACUUM

Deleting rows frees pages inside the file but does not shrink it. VACUUM rebuilds the database into a compact copy:

VACUUM;                          -- rewrite and shrink in place
VACUUM INTO 'app-compact.db';    -- write a compacted copy to a new file (3.27+)

VACUUM needs free disk space of roughly the size of the database and takes an exclusive lock while it runs. VACUUM INTO is also a convenient way to produce a clean, defragmented backup.

EXPLAIN QUERY PLAN

EXPLAIN QUERY PLAN shows whether SQLite will scan a table or search it through an index:

sqlite> EXPLAIN QUERY PLAN SELECT * FROM orders WHERE user_id = 7;
QUERY PLAN
`--SEARCH orders USING INDEX idx_orders_user_id (user_id=?)

sqlite> EXPLAIN QUERY PLAN SELECT * FROM users WHERE full_name LIKE '%silva%';
QUERY PLAN
`--SCAN users

SEARCH with an index is what you want for selective lookups; SCAN means every row is read. USING COVERING INDEX means the index alone satisfied the query without touching the table. Turn on .eqp on in the shell to print the plan automatically before every query, and .expert to have the shell suggest indexes for the next query.

Working with SQLite outside the terminal

The sqlite3 shell is the fastest way to inspect a file on a server, but when you are designing tables or comparing data from several databases at once, a GUI saves time. Chat2DB (opens in a new tab) opens SQLite files alongside MySQL, PostgreSQL and other connections, shows the schema in a sidebar, and can generate SQL such as the CREATE TABLE and UPSERT statements above from a plain-language description, which is useful when you switch between engines and want to avoid syntax that only works in one of them.

FAQ

How do I list all tables in SQLite?

In the sqlite3 shell, run .tables. From SQL or any client, query the schema table: SELECT name FROM sqlite_schema WHERE type = 'table' AND name NOT LIKE 'sqlite_%'; (use sqlite_master on versions older than 3.33).

How do I show a table's structure in SQLite?

Use .schema tablename to see the original CREATE statement, or PRAGMA table_info(tablename); for a row per column with type, NOT NULL, default and primary-key information.

Why do dot-commands not work in my application code?

Dot-commands are implemented by the sqlite3 shell program, not by the SQLite library. Drivers in Python, Node.js, Go or Java only understand SQL and PRAGMAs, so use sqlite_schema queries and PRAGMAs instead of .tables and .schema.

Should I use AUTOINCREMENT in SQLite?

Usually not. INTEGER PRIMARY KEY already assigns ids automatically. Add AUTOINCREMENT only when ids must never be reused after deletion, and accept the extra write to sqlite_sequence on every insert.

How do I enforce column types in SQLite?

Create the table with the STRICT keyword (SQLite 3.37+). On older versions, add CHECK constraints such as CHECK (typeof(amount) = 'integer').

How do I exit the sqlite3 shell?

Type .quit or .exit, or press Ctrl+D. If the prompt shows ...>, the shell is waiting for the rest of an unfinished statement; type a semicolon and press Enter first.