SQLite
About SQLite
- SQLite is the most popular embedded relational database and probably the widely deployed software module.
Some features:
- The database is just a single file.
- It has an in-memory version for fast access to temporary data.
- No need for external server process.
- The client directly accesses the file containing the data.
- There is a command-line tool called
sqlite3to access the data. - There are several GUI tools to view and manipulate data.
- Most programming languages have a library to access an SQLite database.
- ACID - Atomic, Consistent, Isolated, and Durable
Documentation
Using SQLite in programming languages
Main features - where to use it
It is very fast for reading, but not so good for concurrent writing.
- Embedded in every web browser
- Embedded in many other applications.
- Cars etc.
Install SQLite command-line tool
Debian/Ubuntu Linux:
$ sudo apt install sqlite3
Windows
- Download
sqlite-tools-win-x64-3530000.zip(or a later version) that includes the “Command-line tools for Windows x64,”
macOS
- Use Homebrew
Version of SQLite
On Ubuntu 25.10:
$ sqlite3 --version
3.46.1 2024-08-13 09:16:08 c9c2ab54ba1f5f46360f1b4f35d849cd3f080e6fc2b6c60e91b16c63f69aalt1 (64-bit)
Downloaded from web site of SQLite
$ ~/bin/sqlite3 --version
3.53.0 2026-04-09 11:41:38 4525003a53a7fc63ca75c59b22c79608659ca12f0131f52c18637f829977f20b (64-bit)
Some of the examples only work on the most recent version.
GUI for SQLite
TODO
There are number of desktop and web-based GUI tools to be used with an SQLite database.
sudo apt install sqlitebrowser
SQLite dialect
- SQLite works according to the SQL92 standard.
- There are some SQLite specific extensions.
Data types
- Data types
- Flexible typing - SQLite does not really care about the types you defined for the columns. You can even create columns without types.
- STRICT tables - In special cases you can ask SQLite to enforce strict type-checking by setting the table to be
STRICT.
Basic types
- NULL
- INTEGER
- REAL
- TEXT
- BLOB
Conventions
SQL is case insensitive, but in order to make it easier to read some people follow the rule to always write every SQL command in capital letters and every user-defined values (e.g. names of the tables, columns etc.) in lower-case or maybe mixed case.
We’ll follow this convention in the examples.
SQLite interactive shell - .help and .quit
The command line tool has a number of internal commands starting with a dot. .help will show the list of the commands.
.quit or .exit will close the shell.
The commands can be given
- interactively
- sent through a pipe
- redirected from a file
$ sqlite3
SQLite version 3.45.1 2024-01-30 16:01:20
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .help
...
sqlite> .quit
$
Help with SQLite CLI using a pipe
Pipe the command to the STDIN of sqlite3. Available on Linux and macOS and in the git-bash on Windows.
echo .help | sqlite3
Help with SQLite CLI using redirect
Redirect from an external file to the Standard Input (STDIN) of the sqlite3 command.
.help
$ sqlite3 < examples/help.sql
SQLite version
This is using the version I installed from SQLite on Ubuntu Linux.
Interactive
$ sqlite3
sqlite> .version
SQLite 3.53.0 2026-04-09 11:41:38 4525003a53a7fc63ca75c59b22c79608659ca12f0131f52c18637f829977f20b
zlib version 1.3.1
gcc-13.3.0 (64-bit)
sqlite> .quit
Piped to STDIN
$ echo .version | sqlite3
SQLite 3.53.0 2026-04-09 11:41:38 4525003a53a7fc63ca75c59b22c79608659ca12f0131f52c18637f829977f20b
zlib version 1.3.1
gcc-13.3.0 (64-bit)
Redirect from file
.version
$ sqlite3 < examples/version.sql
SQLite 3.53.0 2026-04-09 11:41:38 4525003a53a7fc63ca75c59b22c79608659ca12f0131f52c18637f829977f20b
zlib version 1.3.1
gcc-13.3.0 (64-bit)
Command line flag
$ sqlite3 --version
3.53.0 2026-04-09 11:41:38 4525003a53a7fc63ca75c59b22c79608659ca12f0131f52c18637f829977f20b (64-bit)
SQLite CLI - external commands .shell
To run any external (shell) command use .shell.
On Linux / macOS you have ls:
sqlite> .shell ls
On Windows you have dir:
sqlite> .shell dir
SQLite CLI - .schema and .tables
Using the .tables command we can see the list of tables and using the .schema command we can see the whole definition.
.schema
.tables
SQLite CLI - .mode
The .mode controls how results of SELECT statements are displayed.
Show the current mode
sqlite> .mode
.mode qbox --limits on --quote relaxed --sw auto --textjsonb on
List available modes
sqlite> .mode --list
available modes: ascii box c column count csv html insert jatom jobject json
line list markdown off psql qbox quote split table tabs tcl batch tty
Set a mode:
sqlite> .mode column
SQLite in-memory or file-based
- Using in-memory database
$ sqlite3
- Using a persistent database file. If the file does not exist, this command will create it.
$ sqlite3 my.db
DDL - Data Definition Language
- CREATE TABLE
- ALTER TABLE
- DROP TABLE
CREATE TABLE
CREATE TABLE person (
id INTEGER,
name TEXT
);
.schema
$ sqlite < examples/create-table.sql
CREATE TABLE person (
id INTEGER,
name TEXT
);
ALTER TABLE
Using ALTER TABLE we can modify the schema.
CREATE TABLE person (
id INTEGER,
name TEXT
);
.schema
ALTER TABLE person ADD COLUMN phone TEXT;
.schema
$ sqlite3 < examples/alter-table.sql
CREATE TABLE person (
id INTEGER,
name TEXT
);
CREATE TABLE person (
id INTEGER,
name TEXT
, phone TEXT);
DROP TABLE
DROP TABLE will remove the table and all its content. In this example we also used the .schema command to show what is the current schema
and the .mode batch to make the SELECT statement show a simple line.
.mode batch
CREATE TABLE person (
id INTEGER,
name TEXT
);
SELECT '-------';
.schema
DROP TABLE person;
SELECT '-------';
.schema
SELECT '-------';
$ sqlite < examples/drop-table.sql
-------
CREATE TABLE person (
id INTEGER,
name TEXT
);
-------
-------
DML - Data Manipulation Language - Basic Operations
- CREATE
- INSERT
- SELECT
- UPDATE
CREATE - INSERT - SELECT
With CREATE we define the schema.
With INSERT we add a row.
With SELECT we can fetch data from the database.
CREATE TABLE person (
id INTEGER,
name TEXT,
email TEXT
);
INSERT INTO person (id, name, email) VALUES (1, 'Joe', 'joe@example.com');
INSERT INTO person (id, name, email) VALUES (2, 'Jane', 'jane@example.com');
SELECT * FROM person;
In memory:
$ sqlite3 < create-insert-select.sql
1|Joe|joe@example.com
2|Jane|jane@example.com
In file:
$ sqlite3 demo.db < create-insert-select.sql
1|Joe|joe@example.com
2|Jane|jane@example.com
Then, if we don’t need it any more, we should remove the database file:
$ rm -f demo.db
SELECT only some of the columns
Instead of using a * to fetch all the columns, we can define the specific columns we would like to fetch.
CREATE TABLE person (
id INTEGER,
name TEXT,
email TEXT
);
INSERT INTO person (id, name, email) VALUES (1, 'Joe', 'joe@example.com');
INSERT INTO person (id, name, email) VALUES (2, 'Jane', 'jane@example.com');
INSERT INTO person (id, name, email) VALUES (3, 'Mary', 'mary@example.com');
INSERT INTO person (id, name, email) VALUES (4, 'Peter', 'peter@example.com');
SELECT id, name FROM person;
$ sqlite3 < examples/select-some-columns.sql
1|Joe
2|Jane
3|Mary
4|Peter
SELECT only some of the rows - WHERE clause
We can also use the WHERE close to select which rows we would like to see.
CREATE TABLE person (
id INTEGER,
name TEXT,
email TEXT
);
INSERT INTO person (id, name, email) VALUES (1, 'Joe', 'joe@example.com');
INSERT INTO person (id, name, email) VALUES (2, 'Jane', 'jane@example.com');
INSERT INTO person (id, name, email) VALUES (3, 'Mary', 'mary@example.com');
INSERT INTO person (id, name, email) VALUES (4, 'Peter', 'peter@example.com');
SELECT * FROM person WHERE id > 2;
$ sqlite < examples/select-some-rows.sql
3|Mary|mary@example.com
4|Peter|peter@example.com
AUTOINCREMENT
Keeping in mind to increase the id number can be annoying. SQL provides the AUTOINCREMENT option
that can help us maintain that value. However in SQLite it is not necessary to use and it isn’t even recommended.
In any case let’s see an example:
CREATE TABLE person (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT,
email TEXT
);
INSERT INTO person (name, email) VALUES ('Joe', 'joe@example.com');
INSERT INTO person (name, email) VALUES ('Jane', 'jane@example.com');
INSERT INTO person (name, email) VALUES ('Mary', 'mary@example.com');
INSERT INTO person (name, email) VALUES ('Peter', 'peter@example.com');
SELECT * from person;
.schema
$ sqlite3 < examples/autoincrement.sql
1|Joe|joe@example.com
2|Jane|jane@example.com
3|Mary|mary@example.com
4|Peter|peter@example.com
CREATE TABLE person (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT,
email TEXT
);
CREATE TABLE sqlite_sequence(name,seq);
Invisible AUTOINCREMENT (ROWID)
If we have a column that is marked as INTEGER PRIMARY KEY, it will get incremented automatically. See the AUTOINCREMENT description for details.
CREATE TABLE person (
id INTEGER PRIMARY KEY,
name TEXT,
email TEXT
);
INSERT INTO person (name, email) VALUES ('Joe', 'joe@example.com');
INSERT INTO person (name, email) VALUES ('Jane', 'jane@example.com');
INSERT INTO person (name, email) VALUES ('Mary', 'mary@example.com');
INSERT INTO person (name, email) VALUES ('Peter', 'peter@example.com');
SELECT * from person;
.schema
$ sqlite3 < examples/invisible-autoincrement.sql
1|Joe|joe@example.com
2|Jane|jane@example.com
3|Mary|mary@example.com
4|Peter|peter@example.com
CREATE TABLE person (
id INTEGER PRIMARY KEY,
name TEXT,
email TEXT
);
Missing value
When we insert data, some columns (besides the id) might be missing. SQLite will insert a NULL value.
CREATE TABLE qa (
question TEXT,
answer TEXT
);
INSERT INTO qa (question, answer) VALUES ('Language?', 'SQL');
INSERT INTO qa (question, answer) VALUES ('Database?', 'SQLite');
INSERT INTO qa (question) VALUES ('Meaning of life?');
SELECT * from qa;
$ sqlite3 < examples/missing-text-value.sql
Language?|SQL
Database?|SQLite
Meaning of life?|
This is hard to see in the above display, but in the interactive shell or if we use a real terminal it will look much better:
╭──────────────────┬────────╮
│ question │ answer │
╞══════════════════╪════════╡
│ Language? │ SQL │
│ Database? │ SQLite │
│ Meaning of life? │ NULL │
╰──────────────────┴────────╯
Missing numerical value
CREATE TABLE qa (
question TEXT,
answer INTEGER
);
INSERT INTO qa (question, answer) VALUES ('2+2', 4);
INSERT INTO qa (question, answer) VALUES ('2-2', 0);
INSERT INTO qa (question) VALUES ('Meaning of life?');
SELECT * from qa;
$ sqlite3 < examples/missing-integer-value.sql
2+2|4
2-2|0
Meaning of life?|
╭──────────────────┬────────╮
│ question │ answer │
╞══════════════════╪════════╡
│ 2+2 │ 4 │
│ 2-2 │ 0 │
│ Meaning of life? │ NULL │
╰──────────────────┴────────╯
Field with DEFAULT value
We can set up a DEFAULT value to each field and if the INSERT does not set the value then this default value will be set.
However, if the INSERT explicitly sets NULL then that will be the value.
CREATE TABLE qa (
question TEXT,
answer TEXT DEFAULT 42
);
INSERT INTO qa (question, answer) VALUES ('Language?', 'SQL');
INSERT INTO qa (question, answer) VALUES ('Database?', 'SQLite');
INSERT INTO qa (question) VALUES ('Meaning of life?');
INSERT INTO qa (question, answer) VALUES ('What is void?', NULL);
SELECT * from qa;
$ sqlite3 < examples/default-value.sql
Language?|SQL
Database?|SQLite
Meaning of life?|42
What is void?|
╭──────────────────┬────────╮
│ question │ answer │
╞══════════════════╪════════╡
│ Language? │ SQL │
│ Database? │ SQLite │
│ Meaning of life? │ '42' │
│ What is void? │ NULL │
╰──────────────────┴────────╯
Default time
See the SQLite Keywords for DEFAULT_TIMESTAMP and other similar keywords.
CREATE TABLE login (
uid INTEGER NOT NULL,
logged_in DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO login (uid) VALUES (1);
INSERT INTO login (uid, logged_in) VALUES (2, '2026-04-29 08:06:36');
SELECT * from login;
$ sqlite3 < examples/default-time.sql
1|2026-05-05 06:28:37
2|2026-04-29 08:06:36
NOT NULL constraint
Here we have two tables. In the restricted table we set one of the fields to be NOT NULL.
CREATE TABLE plain (
id INTEGER,
name TEXT
);
INSERT INTO plain (id, name) VALUES (1, 'Joe');
INSERT INTO plain (name) VALUES ('Jane');
SELECT * from plain;
CREATE TABLE restricted (
id INTEGER NOT NULL,
name TEXT
);
INSERT INTO restricted (id, name) VALUES (1, 'Joe');
INSERT INTO restricted (name) VALUES ('Jane');
INSERT INTO restricted (id, name) VALUES (NULL, 'Mary');
SELECT * from restricted;
$ sqlite3 < examples/not-null.sql
1|Joe
|Jane
Error near line 18: NOT NULL constraint failed: restricted.id
Error near line 19: NOT NULL constraint failed: restricted.id
1|Joe
UPDATE
With UPDATE we can change one or more fields in one or more rows.
.mode batch
CREATE TABLE people (
name TEXT,
grade INTEGER
);
INSERT INTO people (name, grade) VALUES ('Joe', 40);
INSERT INTO people (name, grade) VALUES ('Jane', 60);
SELECT * from people;
SELECT '-------';
-- SELECT 'Setting the grade of Joe to be 44';
UPDATE people SET grade = 44;
SELECT * from people;
SELECT '-------';
UPDATE people SET grade = 100 WHERE name = 'Joe';
SELECT * from people;
SELECT '-------';
$ sqlite < examples/update.sql
Joe|40
Jane|60
-------
Joe|44
Jane|44
-------
Joe|100
Jane|44
-------