Keyboard shortcuts

Press ← or → to navigate between chapters

Press S or / to search in the book

Press ? to show this help

Press Esc to hide this help

SQLite

About SQLite

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 sqlite3 to 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

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.

DB Browser for SQLite

sudo apt install sqlitebrowser

Beekeeper Studio

DBeaver

Sqlime

SQLite Viewer

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.

Command Line Shell For SQLite

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
-------