Introduction to SQLite

SQLite supports transactions with full ACID guarantees, including after a crash or power loss. The source code is in the public domain, so there is no licence to comply with for private or commercial use. It needs almost nothing from the operating system, which is why it ships inside phones, browsers and a great many desktop applications.

Installing the command-line shell

You often do not need to install anything. The library is bundled with Python (import sqlite3), built into Android and iOS, and included in the SQLite drivers of most other languages. What you may want to add is the sqlite3 command-line shell for inspecting database files.

• macOS: already installed. Run sqlite3 in Terminal.
• Linux: usually installed. If not, sudo apt install sqlite3 on Debian and Ubuntu or sudo dnf install sqlite on Fedora and RHEL-family systems.
• Windows: go to sqlite.org/download.html and, under "Precompiled Binaries for Windows", download the sqlite-tools-win-x64-...zip bundle. Extract it to a folder such as C:\sqlite and add that folder to your PATH. The bundle contains sqlite3.exe and a few companion tools. The separate sqlite-dll download is the library alone, for developers linking it into their own program; it has no shell.

Check that it works:

sqlite3 --version

Using the shell

sqlite3 notes.db opens the file, creating it if it does not exist. Running sqlite3 with no file name gives you a temporary in-memory database that vanishes on exit, which is an easy way to lose work.

SQL statements end with a semicolon. Commands for the shell itself start with a dot and take no semicolon:

.tables              list tables
.schema notes        show the CREATE statement for a table
.mode column         aligned columns (also: box, table, csv, json)
.headers on          show column names
.import --csv data.csv items    load a CSV file into a table
.dump                print the whole database as SQL
.backup backup.db    write a consistent copy of the database
.quit                exit

Data types work differently

This is the part of SQLite that surprises people coming from other databases. A value, not a column, has a type, and there are five storage classes:

The type you declare on a column is an affinity: a preferred type that SQLite converts to when it can. If it cannot, it stores the value as it is. Nothing is rejected:

CREATE TABLE loose (n INTEGER, t TEXT);
INSERT INTO loose VALUES ('42', 7), ('forty-two', 7.5);
SELECT n, typeof(n), t, typeof(t) FROM loose;

n          typeof(n)  t    typeof(t)
---------  ---------  ---  ---------
42         integer    7    text
forty-two  text       7.5  text

The string '42' became an integer; 'forty-two' stayed text in an INTEGER column. Length limits are not enforced either: VARCHAR(10) accepts a string of any length.

If you want the strict behaviour of other databases, add the STRICT keyword (SQLite 3.37 and later). Column types are then limited to INT, INTEGER, REAL, TEXT, BLOB and ANY, and a value of the wrong type is an error:

CREATE TABLE strict_demo (n INTEGER, t TEXT) STRICT;
INSERT INTO strict_demo VALUES ('forty-two', 'x');

cannot store TEXT value in INTEGER column strict_demo.n

Booleans

There is no boolean storage class. Use an INTEGER holding 0 or 1. The keywords TRUE and FALSE are accepted and are simply other spellings of 1 and 0, as the pinned column in the first example shows.

Dates and times

There is no date type. The built-in date functions understand three representations, and you choose one per column:

ISO 8601 text is the usual choice: it is readable, and it sorts and compares correctly as a plain string. CURRENT_TIMESTAMP and datetime('now') return UTC; use the 'localtime' modifier to convert for display. Date arithmetic uses modifiers:

SELECT date('2026-10-08', '+1 month', 'start of month');   -- 2026-11-01

Primary keys

A column declared exactly INTEGER PRIMARY KEY becomes an alias for the table's internal row id and is filled in automatically when you omit it, as id was above. You do not need AUTOINCREMENT for that. AUTOINCREMENT adds one guarantee, that ids of deleted rows are never reused, at some extra cost per insert.

Settings worth knowing

Foreign keys are off by default. You can declare foreign key constraints, but they are not enforced until you turn enforcement on, and the setting applies per connection, not per database file:

PRAGMA foreign_keys = ON;

Do this every time your application opens a connection.

Write-ahead logging. In the default journal mode a writer blocks readers while it commits. WAL mode lets readers continue during a write and is usually faster:

PRAGMA journal_mode = WAL;

This setting is stored in the file and persists. With WAL, SQLite keeps two extra files next to the database (notes.db-wal and notes.db-shm) while it is in use; they belong to the database and must not be deleted or separated from it.

Busy timeout. Only one connection can write at a time. By default a second writer fails immediately with database is locked. Tell it to wait instead (some drivers set a timeout for you):

PRAGMA busy_timeout = 5000;   -- milliseconds

What SQLite does not have

• Users and permissions. There is no GRANT or REVOKE. Whoever can read the file can read the database.
• A network protocol. Clients cannot connect remotely. Putting the file on a network share and opening it from several machines is unreliable, because file locking on network file systems is often broken; this can corrupt the database.
• Full ALTER TABLE. You can rename a table, rename a column, add a column and drop a column. Other changes, such as altering a column's type or constraints, mean creating a new table and copying the data across.
• Stored procedures. Logic lives in your application, or in triggers.

Most of the rest of SQL is there: joins, subqueries, common table expressions, window functions, views, triggers, upserts (INSERT ... ON CONFLICT), RETURNING, generated columns, JSON functions and full-text search.

When to use it

SQLite is a good fit for:

• Data stored on a device: mobile apps, desktop applications, embedded systems.
• An application file format, in place of a custom binary format or a pile of JSON files.
• Websites and internal tools with modest write volume. Reads scale well; the limit is the single writer.
• Tests, prototypes and local development.
• Analysing a data set: import CSV files and query them with SQL.

Choose a client/server database when many processes or machines need to write at the same time, when the database must be reached over a network, or when you need per-user access control inside the database.

Common mistakes

• Copying the file while the application is writing to it. The copy can be inconsistent. Use .backup in the shell, or VACUUM INTO 'backup.db'; in SQL.
• Assuming column types are enforced. They are not unless the table is STRICT.
• Forgetting PRAGMA foreign_keys = ON and finding orphaned rows later.
• Double quotes around strings. Double quotes are for identifiers; use single quotes for string values. For compatibility with old code, SQLite may treat an unrecognised double-quoted name as a string instead of raising an error, which hides typos in column names.
• One transaction per insert in a bulk load. Each commit waits for the disk. Wrap the batch in BEGIN; ... COMMIT;.

Further reading

• About SQLite
• Appropriate Uses For SQLite
• Datatypes In SQLite
• Command Line Shell For SQLite

How do I fix SQLITE_BUSY ("database is locked") in Python?

Why Python raises "database is locked", how timeout, isolation_level and autocommit interact, and the working pattern for threads, worker processes, SQLAlchemy, Django and aiosqlite.

#concurrency#locking#orm