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

SELECT without table

We can use SELECT event without tables to use SQLite as bloated calculator or to demonstrate some of the functions available.

SELECT 19 + 23

$ sqlite3 < examples/add.sql
42

SELECT 'Hello World'

$ sqlite3 < examples/text.sql
Hello World

SELECT date and time

Date And Time Functions

SELECT 'date', date();
SELECT 'time', time();
SELECT 'datetime', datetime();
SELECT 'julianday',julianday();
SELECT 'unixepoch', unixepoch();
SELECT 'strftime', strftime();
SELECT 'strftime with param', strftime('%Y-%m-%d %H:%M:%S');
--SELECT timediff('%Y');

$ sqlite3 < examples/date-and-time.sql

date|2026-05-04
time|13:59:26
datetime|2026-05-04 13:59:26
julianday|2461165.0829416784
unixepoch|1777903166
strftime|
strftime with param|2026-05-04 13:59:26

SELECT functions

Built-In Scalar SQL Functions

SELECT abs(-42);
SELECT random();
SELECT upper('aBcdEf');

42
9185603501169008873
ABCDEF

SELECT mathematical functions

Built-In Mathematical SQL Functions

SELECT ceil(3.1);
SELECT ceil(-3.1);
SELECT floor(3.8);
SELECT floor(-3.8);
SELECT trunc(3.8);
SELECT trunc(-3.8);


$ sqlite3 < examples/math.sql

SELECT ceil(3.1);
SELECT ceil(-3.1);
SELECT floor(3.8);
SELECT floor(-3.8);
SELECT trunc(3.8);
SELECT trunc(-3.8);

Data type enforcement

SQLite employes flexible data types, but allows use to opt-in the STRICT handling of data types.

Flexible typing

The Advantages Of Flexible Typing

Here, although we declared the types of the fields, we can insert any type of value.

CREATE TABLE flexible (
    id INTEGER,
    name TEXT,
    height REAL
);

INSERT INTO flexible (id, name, height) VALUES (1, 'foo', 1.8);
INSERT INTO flexible (id, name, height) VALUES ('id', 23, 'tall');

SELECT * from flexible;


$ sqlite3 < examples/flexible-data.sql

1|foo|1.8
id|23|tall

STRICT Tables

If you really want, you can defined a table to be STRICT, but you need to do that on a per-table basis and the data types you can use are limited.

  • INT
  • INTEGER
  • REAL
  • TEXT (UTF-8)
  • BLOB (for binary)
  • ANY
CREATE TABLE strict_data (
    id INTEGER,
    name TEXT,
    height REAL
) STRICT;

INSERT INTO strict_data (id, name, height) VALUES (1, 'foo', 1.8);
INSERT INTO strict_data (id, name, height) VALUES ('2', 'bar', '2.1');

INSERT INTO strict_data (id, name, height) VALUES ('three', 'Jane', 1.3);
-- Runtime error near line 10: cannot store TEXT value in INTEGER column strict_data.id (19)

SELECT * from strict_data;



$ sqlite < examples/strict-table.out

Error near line 10: cannot store TEXT value in INTEGER column strict_data.id
1|foo|1.8
2|bar|2.1

VARCHAR

You can define a column to be VARCHAR(10), but SQLite will totally disregard it. It can be still useful if your application code can rely on it or if at one point you might want to migrate to another database respects such definitions.

You can’t use this in a STRICT table.

CREATE TABLE people (
    name TEXT,
    phone VARCHAR(10)
);

INSERT INTO people VALUES ('John', '1234567890-123456789');

SELECT * FROM people;

$ sqlite3 < examples/varchar.sql

John|1234567890-123456789

Import data from CSV

We are going to see two ways to import data from CSV.

First we let SQLite create the table based on the first row in the CSV file.

Then we’ll create the table ourselves setting the column types as we prefer and then import the CSV file skipping the first row.

Import planets.csv

Planet name,Distance (AU),Mass
Mercury,0.4,0.055
Venus,0.7,0.815
Earth,1,1
Mars,1.5,0.107
Ceres,2.77,0.00015
Jupiter,5.2,318
Saturn,9.5,95
Uranus,19.6,14
Neptune,30,17
Pluto,39,0.00218
Charon,39,0.000254

.mode batch
.import --csv -v examples/planets.csv planets
SELECT '----------';

.schema
SELECT '----------';

SELECT * FROM planets;
SELECT "Distance (AU)" FROM planets;

$ sqlite3 < examples/import-planets.sql

Column separator ",", row separator "\n"
CREATE TABLE "main"."planets"(
"Planet name" ANY, "Distance (AU)" ANY, "Mass" ANY)

Added 11 rows with 0 errors using 12 lines of input
----------
CREATE TABLE "planets"(
"Planet name" ANY, "Distance (AU)" ANY, "Mass" ANY);
----------
Mercury|0.4|0.055
Venus|0.7|0.815
Earth|1|1
Mars|1.5|0.107
Ceres|2.77|0.00015
Jupiter|5.2|318
Saturn|9.5|95
Uranus|19.6|14
Neptune|30|17
Pluto|39|0.00218
Charon|39|0.000254
0.4
0.7
1
1.5
2.77
5.2
9.5
19.6
30
39
39

Load schema and import planets.csv

.mode batch

.read examples/planets.sql
.import --csv --skip 1 -v examples/planets.csv planets
SELECT '----------';

.schema
SELECT '----------';

SELECT * FROM planets;
SELECT "Distance (AU)" FROM planets;


$ sqlite3 < exampes/create-and-import-planets.sql

Column separator ",", row separator "\n"
Added 11 rows with 0 errors using 12 lines of input
----------
CREATE TABLE planets (
    "Planet name" TEXT,
    "Distance (AU)" REAL,
    Mass  REAL
);
----------
Mercury|0.4|0.055
Venus|0.7|0.815
Earth|1.0|1.0
Mars|1.5|0.107
Ceres|2.77|0.00015
Jupiter|5.2|318.0
Saturn|9.5|95.0
Uranus|19.6|14.0
Neptune|30.0|17.0
Pluto|39.0|0.00218
Charon|39.0|0.000254
0.4
0.7
1.0
1.5
2.77
5.2
9.5
19.6
30.0
39.0
39.0

Import iris.csv

.mode batch
.import --csv  -v examples/iris.csv iris
SELECT '------';
.schema
SELECT '------';


$ sqlite3 < examples/import-iris.sql

Column separator ",", row separator "\n"
CREATE TABLE "main"."iris"(
"Id" ANY, "SepalLengthCm" ANY, "SepalWidthCm" ANY, "PetalLengthCm" ANY,
 "PetalWidthCm" ANY, "Species" ANY)

Added 150 rows with 0 errors using 151 lines of input
------
CREATE TABLE "iris"(
"Id" ANY, "SepalLengthCm" ANY, "SepalWidthCm" ANY, "PetalLengthCm" ANY,
 "PetalWidthCm" ANY, "Species" ANY);
------
╭──────────╮
│ COUNT(*) │
╞══════════╡
│      150 │
╰──────────╯
╭────────────────────╮
│ AVG(SepalLengthCm) │
╞════════════════════╡
│ 5.8433333333333337 │
╰────────────────────╯
╭────────────────────╮
│ AVG(PetalLengthCm) │
╞════════════════════╡
│ 3.7586666666666662 │
╰────────────────────╯
╭────────────────────┬─────────────────╮
│ AVG(PetalLengthCm) │     Species     │
╞════════════════════╪═════════════════╡
│              1.464 │ Iris-setosa     │
│               4.26 │ Iris-versicolor │
│ 5.5520000000000005 │ Iris-virginica  │
╰────────────────────┴─────────────────╯

Load schema and Import iris.csv

CREATE TABLE iris (
    Id            INTEGER,
    SepalLengthCm REAL,
    SepalWidthCm  REAL,
    PetalLengthCm REAL,
    PetalWidthCm  REAL,
    Species       TEXT
);

sqlite> .schema

sqlite> .read iris.sql

sqlite> .import --csv --skip 1 -v iris.csv iris

Added 150 rows with 0 errors using 151 lines of input

sqlite> .schema
CREATE TABLE iris (
    Id            INTEGER,
    SepalLengthCm REAL,
    SepalWidthCm  REAL,
    PetalLengthCm REAL,
    PetalWidthCm  REAL,
    Species       TEXT
);

LIMIT the number of rows

.import --csv examples/iris.csv iris
SELECT * FROM iris LIMIT 3;


sqlite3 < examples/limit-iris.sql

1|5.1|3.5|1.4|0.2|Iris-setosa
2|4.9|3|1.4|0.2|Iris-setosa
3|4.7|3.2|1.3|0.2|Iris-setosa

Aggregate: COUNT

.mode batch
.import --csv examples/iris.csv iris


SELECT COUNT(*) FROM iris;

SELECT COUNT(*) FROM iris WHERE Species == 'Iris-virginica';

$ sqlite3 < examples/count-iris.sql

150
50

Aggregate: AVG

.mode batch
.import --csv examples/iris.csv iris


SELECT AVG(SepalLengthCm) FROM iris;
SELECT AVG(PetalLengthCm) FROM iris;
SELECT AVG(PetalLengthCm), Species FROM iris GROUP BY Species;

$ sqlite3 < examples/averages-iris.sql

5.8433333333333337
3.7586666666666662
1.464|Iris-setosa
4.26|Iris-versicolor
5.5520000000000005|Iris-virginica

DISTINCT

.import --csv examples/iris.csv iris
SELECT COUNT(species) FROM iris;
SELECT COUNT(DISTINCT species) FROM iris;


$ sqlite3 < examples/distinct-iris.sql

150
3

Aggregate: COUNT, AVG, MAX, MIN

Aggregare functions

CREATE TABLE person (
    name TEXT,
    subject TEXT,
    grade INTEGER
);

INSERT INTO person (name, subject, grade) VALUES
    ('Jane',  'Chemistry', 62),
    ('Jane',  'Physics', 70),
    ('Jane',  'Math', 23),
    ('Joe',   'Chemistry', 45),
    ('Joe',   'Physics', 85),
    ('Joe',   'Math', 77),
    ('Peter', 'Chemistry', 35),
    ('Peter', 'Physics', 63),
    ('Mary',  'Chemistry', 95),
    ('Dean', 'Physics', 100),
    ('Dana', 'Math', 97),
    ('George', 'Math', 97),
    ('George', 'Chemistry', 45);


SELECT * FROM person;


SELECT COUNT(*) FROM person;
SELECT 'Total', COUNT(*) FROM person;
SELECT 'Physycs', COUNT(*) FROM person WHERE subject == 'Physics';
SELECT 'Failed', COUNT(*) FROM person WHERE grade < 60;

SELECT 'Maximum all', MAX(grade) FROM person;
SELECT 'Maximum Chemistry', MAX(grade) FROM person WHERE subject == 'Chemistry';

SELECT 'Average all', AVG(grade) FROM person;
SELECT 'Average Chemistry', AVG(grade) FROM person WHERE subject == 'Chemistry';
SELECT 'Average Physics', AVG(grade) FROM person WHERE subject == 'Physics';

$ cat examples/grades.sql  examples/aggregate.sql | sqlite3

Jane|Chemistry|62
Jane|Physics|70
Jane|Math|23
Joe|Chemistry|45
Joe|Physics|85
Joe|Math|77
Peter|Chemistry|35
Peter|Physics|63
Mary|Chemistry|95
Dean|Physics|100
Dana|Math|97
George|Math|97
George|Chemistry|45
13
Total|13
Physycs|4
Failed|4
Maximum all|100
Maximum Chemistry|95
Average all|68.769230769230774
Average Chemistry|56.4
Average Physics|79.5

Increment value

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 'Incrementing the grade of Joe by 1';
UPDATE people SET grade = (SELECT grade FROM people WHERE name = 'Joe') + 1 WHERE name = 'Joe';
SELECT * from people;
SELECT '-------';

SELECT 'Incrementing the grade of every person by 5';
UPDATE people SET grade = grade + 5;
SELECT * from people;
SELECT '-------';

sqmlite3 < examples/increment.sql

Joe|40
Jane|60
-------
Incrementing the grade of Joe by 1
Joe|41
Jane|60
-------
Incrementing the grade of every person by 5
Joe|46
Jane|65
-------

Constraints

CHECK, UNIQUE, NOT NULL

This example shows all 3 constraint types.

CREATE TABLE grades (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL UNIQUE,
    value INTEGER,
    CHECK (value IS NULL OR (value BETWEEN 0 AND 100))
);

INSERT INTO grades (name, value) VALUES ('Joe', 100);
INSERT INTO grades (name, value) VALUES ('Jane', 200);
INSERT INTO grades (name, value) VALUES ('Mary', NULL);
INSERT INTO grades (name) VALUES ('Peter');
INSERT INTO grades (name, value) VALUES ('Zorg', 'hello');

SELECT * FROM grades;

$sqlite3 < examples/range-constraint.sql

Error near line 9: CHECK constraint failed: value IS NULL OR (value BETWEEN 0 AND 100)
Error near line 12: CHECK constraint failed: value IS NULL OR (value BETWEEN 0 AND 100)
1|Joe|100
2|Mary|
3|Peter|

Multi-column UNIQUE-ness constraint

Sometimes the each column can have the same value more than once, but 2 (or more column) put together must be unique. For example in this table we are storing coordinates of points. Both x and y can be any value and the same value can appear multiple times in either x or y.

However the (x, y) pair must be unique.

Here is how we can accomplish that.

CREATE TABLE points (
    x INTEGER,
    y INTEGER
);
CREATE UNIQUE INDEX xy_index ON points (x, y);


INSERT INTO points (x, y) VALUES (2, 3);
INSERT INTO points (x, y) VALUES (4, 5);
INSERT INTO points (x, y) VALUES (2, 3);

SELECT * FROM points;

$ sqlite3 < examples/points.sql

Error near line 10: UNIQUE constraint failed: points.x, points.y
2|3
4|5

Database normalization

Database normalization

  • First Normal Form (1NF) - every column should store only one value.
  • Second Normal Form (2NF) - the same value should not be repeated in the column.
  • Third Normal Form (3NF)
  • BCNF

Multivalue fields

CREATE TABLE grades (
  student TEXT,
  subjects TEXT,
  grades  TEXT
);

INSERT INTO grades (student, subjects, grades) VALUES ('Joe', 'Math,Chemistry,Programming', '27,89,32');
INSERT INTO grades (student, subjects, grades) VALUES ('Jane', 'Math,Literature,Physics', '99,100,97');

SELECT * FROM grades;

$ sqlite < examples/multivalue-fields.sql

Joe|Math,Chemistry,Programming|27,89,32
Jane|Math,Literature,Physics|99,100,97

╭─────────┬────────────────────────────┬───────────╮
│ student │          subjects          │  grades   │
╞═════════╪════════════════════════════╪═══════════╡
│ Joe     │ Math,Chemistry,Programming │ 27,89,32  │
│ Jane    │ Math,Literature,Physics    │ 99,100,97 │
╰─────────┴────────────────────────────┴───────────╯

Flat table

CREATE TABLE person (
    name TEXT,
    subject TEXT,
    grade INTEGER
);

INSERT INTO person (name, subject, grade) VALUES
    ('Jane',  'Chemistry', 62),
    ('Jane',  'Physics', 70),
    ('Jane',  'Math', 23),
    ('Joe',   'Chemistry', 45),
    ('Joe',   'Physics', 85),
    ('Joe',   'Math', 77),
    ('Peter', 'Chemistry', 35),
    ('Peter', 'Physics', 63),
    ('Mary',  'Chemistry', 95),
    ('Dean', 'Physics', 100),
    ('Dana', 'Math', 97),
    ('George', 'Math', 97),
    ('George', 'Chemistry', 45);


SELECT * FROM person;

Two tables


-- enforce foreignk key integrity
PRAGMA foreign_keys = ON;

CREATE TABLE subject (
    id INTEGER PRIMARY KEY,
    name TEXT UNIQUE NOT NULL
);

CREATE TABLE person (
    name TEXT,
    subject_id INTEGER REFERENCES subject(id),
    grade INTEGER
);

INSERT INTO subject (id, name) VALUES
    (1, 'Chemistry'),
    (2, 'Physics'),
    (3, 'Math');

INSERT INTO person (name, subject_id, grade) VALUES
    ('Jane', 1, 62),
    ('Jane', 2, 70),
    ('Jane', 3, 23),
    ('Joe', 1, 45),
    ('Joe', 2, 85),
    ('Joe', 3, 77),
    ('Peter', 1, 35),
    ('Peter', 2, 63),
    ('Mary', 1, 95),
    ('Dean', 2, 100),
    ('Dana', 3, 97),
    ('Gerorge', 3, 97),
    ('Gerorge', 1, 45);

SELECT * FROM subject;
SELECT * FROM person;

SELECT person.name, subject.name AS subject, person.grade
  FROM person, subject
  WHERE person.subject_id = subject.id;

SELECT person.name, subject.name AS subject, person.grade
  FROM person JOIN subject
  WHERE person.subject_id = subject.id;

SELECT person.name, subject.name AS subject, person.grade
  FROM person INNER JOIN subject
  WHERE person.subject_id = subject.id;

FOREIGN KEY

-- turn on FOREIGN KEY checking
PRAGMA foreign_keys = ON;

CREATE TABLE people (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT

);

CREATE TABLE groups (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT,
    manager INTEGER,
    FOREIGN KEY(manager) REFERENCES people(id)
);

INSERT INTO people (name) VALUES ('Natalie Portman ');
INSERT INTO people (name) VALUES ('Gal Gadot');
INSERT INTO people (name) VALUES ('Cole Lawrence');
INSERT INTO people (name) VALUES ('Lior Raz');
INSERT INTO people (name) VALUES ('Erick Tryzelaar');
INSERT INTO people (name) VALUES ('Ernest Kissiedu');
SELECT * FROM people;
SELECT "-----";

INSERT INTO groups (name, manager) VALUES ('Rust London', (SELECT id FROM people WHERE name = 'Ernest Kissiedu'));
INSERT INTO groups (name, manager) VALUES ('Rust NYC', (SELECT id FROM people WHERE name = 'Cole Lawrence'));
INSERT INTO groups (name, manager) VALUES ('Rust Bay Area', (SELECT id FROM people WHERE name = 'Erick Tryzelaar'));
INSERT INTO groups (name, manager) VALUES ('Other Group', 42);
SELECT * FROM people;
SELECT "";
SELECT * FROM groups;
SELECT "-----";

DELETE FROM people WHERE name = 'Cole Lawrence';
SELECT * FROM people;
SELECT "";
SELECT * FROM groups;
SELECT "-----";

PRAGMA

PRAGMA

Family

PRAGMA foreign_keys = ON;

CREATE TABLE people (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT,
    father INTEGER,
    mother INTEGER,
    FOREIGN KEY (father) REFERENCES people(id),
    FOREIGN KEY (mother) REFERENCES people(id)
);

INSERT INTO people (name) VALUES
    ('Abraham'),
    ('Hagar'),
    ('Sarah'),
    ('Keturah'),
    ('Ishmael'),
    ('Isaac'),
    ('Rebecca'),
    ('Jacob');

UPDATE people SET
    father = (SELECT id FROM people WHERE name = 'Abraham'),
    mother =(SELECT id FROM people WHERE name = 'Sarah')
    WHERE name = 'Isaac';

UPDATE people SET
    father = (SELECT id FROM people WHERE name = 'Abraham'),
    mother =(SELECT id FROM people WHERE name = 'Hagar')
    WHERE name = 'Ishmael';

UPDATE people SET
    father = (SELECT id FROM people WHERE name = 'Isaac'),
    mother =(SELECT id FROM people WHERE name = 'Rebecca')
    WHERE name = 'Jacob';

UPDATE people SET father = 32;

SELECT child.name AS Name, father.name AS Father, mother.name AS Mother
    FROM people child, people father, people mother
    WHERE father.id = child.father AND mother.id = child.mother
--    ORDER BY child.id;
    ORDER BY child.name;

-- SELECT child.name AS Name,
--        IFNULL(father.name, 'NULL') AS Father,
--        IFNULL(mother.name, 'NULL') AS Mother
--     FROM people AS child
--     LEFT JOIN people AS father ON father.id = child.father
--     LEFT JOIN people AS mother ON mother.id = child.mother
--     ORDER BY child.id;
--
-- CREATE VIEW family AS
--   SELECT child.name AS Name,
--          IFNULL(father.name, 'NULL') AS Father,
--          IFNULL(mother.name, 'NULL') AS Mother
--       FROM people AS child
--       LEFT JOIN people AS father ON father.id = child.father
--       LEFT JOIN people AS mother ON mother.id = child.mother
--       ORDER BY child.id;
--
-- SELECT * FROM family;
--
-- INSERT INTO people (name) VALUES ('Gabor');
-- SELECT * FROM family;

Flat grades

Student,Math,Chemistry,Biology,Physics,Literature,Sport,Drawing
Joe,100,80,,30,,,
Jane,90,92,100,,,,
Mary,,70,,48,51,97,20

CREATE TABLE IF NOT EXISTS grades (
    student TEXT NOT NULL UNIQUE,
    math INTEGER,
    chemistry INTEGER,
    biology INTEGER,
    physics INTEGER,
    literature INTEGER,
    sport INTEGER,
    drawing INTEGER,
    id INTEGER PRIMARY KEY
);
.import --csv --skip 1 examples/flat-grades.csv grades

SELECT * FROM grades;


---------------------------

CREATE TABLE students (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL UNIQUE
);

SELECT id, student from grades ORDER BY id;

INSERT INTO students SELECT id, student FROM grades;

SELECT * FROM students ORDER BY id;

CREATE TABLE subjects (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL UNIQUE
);

SELECT name FROM pragma_table_info('grades') WHERE name != 'id' AND name != 'student';

INSERT INTO subjects (name) SELECT name FROM pragma_table_info('grades') WHERE name != 'id' AND name != 'student';

SELECT * FROM subjects;

CREATE TABLE new_grades (
    student INTEGER REFERENCES students(id),
    subject INTEGER REFERENCES subjects(id),
    grade INTEGER
);
CREATE UNIQUE INDEX new_grades_index ON new_grades (student, subject);

INSERT INTO new_grades (student, subject, grade)
SELECT students.id, subjects.id, flat_grades.grade
FROM (
    SELECT student, 'math' AS subject, math AS grade FROM grades
    UNION ALL
    SELECT student, 'chemistry' AS subject, chemistry AS grade FROM grades
    UNION ALL
    SELECT student, 'biology' AS subject, biology AS grade FROM grades
    UNION ALL
    SELECT student, 'physics' AS subject, physics AS grade FROM grades
    UNION ALL
    SELECT student, 'literature' AS subject, literature AS grade FROM grades
    UNION ALL
    SELECT student, 'sport' AS subject, sport AS grade FROM grades
    UNION ALL
    SELECT student, 'drawing' AS subject, drawing AS grade FROM grades
) AS flat_grades
JOIN students ON students.name = flat_grades.student
JOIN subjects ON subjects.name = flat_grades.subject
WHERE flat_grades.grade IS NOT NULL AND flat_grades.grade != '';

SELECT students.name AS student, subjects.name AS subject, new_grades.grade
FROM new_grades
JOIN students ON students.id = new_grades.student
JOIN subjects ON subjects.id = new_grades.subject
ORDER BY students.id, subjects.id;

Transactions

SQLite Transaction - in a bank

  • Setup Bank accounts with some initial money.
  • Move some money from one account to another - two separate steps. Worked.
  • Move some money from one account to another - two separate steps - stop in the middle. Failed.
  • Move some money from one account to another - Transaction - stop in the middle. Worked. (money stayed where it was)
  • Remove bank
CREATE TABLE bank (
    name TEXT PRIMARY KEY,
    balance INTEGER NOT NULL
);

INSERT INTO bank (name, balance) VALUES ("Jane", 0);
INSERT INTO bank (name, balance) VALUES ("Mary", 1000);
INSERT INTO bank (name, balance) VALUES ("Ann", 1000);



SELECT * FROM bank;
SELECT "Total", SUM(balance) FROM bank;
SELECT "-----";

UPDATE bank SET balance = (SELECT balance FROM bank WHERE name = "Mary") - 100 WHERE name = "Mary";
UPDATE bank SET balance = (SELECT balance FROM bank WHERE name = "Jane") + 100 WHERE name = "Jane";


UPDATE bank SET balance = (SELECT balance FROM bank WHERE name = "Mary") - 100 WHERE name = "Mary";
.exit
UPDATE bank SET balance = (SELECT balance FROM bank WHERE name = "Jane") + 100 WHERE name = "Jane";


BEGIN TRANSACTION;
UPDATE bank SET balance = (SELECT balance FROM bank WHERE name = "Mary") - 100 WHERE name = "Mary";
.exit
UPDATE bank SET balance = (SELECT balance FROM bank WHERE name = "Jane") + 100 WHERE name = "Jane";
COMMIT;

sqlite3 bank.db < setup_bank.sql
sqlite3 bank.db < show.sql

sqlite3 bank.db < transfer.sql
sqlite3 bank.db < show.sql

sqlite3 bank.db < without_transaction.sql
sqlite3 bank.db < show.sql

sqlite3 bank.db < with_transaction.sql
sqlite3 bank.db < show.sql

rm -f bank.db

Jane|0
Mary|1000
Ann|1000
Total|2000
-----
Jane|100
Mary|900
Ann|1000
Total|2000
-----
Jane|100
Mary|800
Ann|1000
Total|1900
-----
Jane|100
Mary|800
Ann|1000
Total|1900
-----
  • TODO: loading a large CSV file into the database and running queries.
  • TODO: creating a multi-tabe database, dumping it and then loading it and running queries against it.
  • TODO: FOREIGN KEY - cascading deletition?

ALTER TABLE

As we make progress with our application we might need to change the schema. For example we might need to add a new field to a table.

ALTER TABLE

ALTER TABLE after adding data

Here we have a table with two fields (id and name) and with some data in it. Then we inserted some data.

Then we execute ALTER TABLE to add another column.

After that we can add new rows, this time already supplying the new field as well.

The old rows will have NULL in the new field.

We can also update the old rows to have some value in the field using the UPDATE command.

CREATE TABLE person (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT
);

INSERT INTO person (name) VALUES ('Joe');
INSERT INTO person (name) VALUES ('Jane');

SELECT * FROM person;


ALTER TABLE person ADD COLUMN phone TEXT;

SELECT * FROM person;

INSERT INTO person (name, phone) VALUES ('Marcus', '1234565');

SELECT * FROM person;


sqlite3 < exmples/alter-table-after-adding-data.sql

1|Joe
2|Jane
1|Joe|
2|Jane|
1|Joe|
2|Jane|
3|Marcus|1234565

╭────┬──────╮
│ id │ name │
╞════╪══════╡
│  1 │ Joe  │
│  2 │ Jane │
╰────┴──────╯
╭────┬──────┬───────╮
│ id │ name │ phone │
╞════╪══════╪═══════╡
│  1 │ Joe  │ NULL  │
│  2 │ Jane │ NULL  │
╰────┴──────┴───────╯
╭────┬────────┬───────────╮
│ id │  name  │   phone   │
╞════╪════════╪═══════════╡
│  1 │ Joe    │ NULL      │
│  2 │ Jane   │ NULL      │
│  3 │ Marcus │ '1234565' │
╰────┴────────┴───────────╯

ALTER TABLE not NULL

What if we would like to add a column that should be NOT NULL?

We cannot add a column that is NOT NULL as it would immediately violate the integriry of the table.

We can first add the column. Then update the existing rows, then change the column definition.

CREATE TABLE person (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT
);

INSERT INTO person (name) VALUES ('Joe');
INSERT INTO person (name) VALUES ('Jane');

SELECT * FROM person;

-- ALTER TABLE person ADD COLUMN phone TEXT NOT NULL;
-- Error near line 11: Cannot add a NOT NULL column with default value NULL

ALTER TABLE person ADD COLUMN phone TEXT;
UPDATE person SET phone = '123' WHERE name = 'Joe';
UPDATE person SET phone = '567' WHERE name = 'Jane';
SELECT * FROM person;

ALTER TABLE person ALTER phone SET NOT NULL;

-- INSERT INTO person (name) VALUES ('Mary');
-- Error near line 21: NOT NULL constraint failed: person.phone

INSERT INTO person (name, phone) VALUES ('Mary', '890');
SELECT * FROM person;


sqlite3 < examples/alter-table-not-null.sql

1|Joe
2|Jane
1|Joe|123
2|Jane|567
1|Joe|123
2|Jane|567
3|Mary|890

ALTER TABLE in transaction

Even better, do all the changes inside a transaction. That way they are either all implemented, or nothing changes.

CREATE TABLE person (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT
);

INSERT INTO person (name) VALUES ('Joe');
INSERT INTO person (name) VALUES ('Jane');

SELECT * FROM person;

-- ALTER TABLE person ADD COLUMN phone TEXT NOT NULL;
-- Error near line 11: Cannot add a NOT NULL column with default value NULL


BEGIN TRANSACTION;
ALTER TABLE person ADD COLUMN phone TEXT;
UPDATE person SET phone = '123' WHERE name = 'Joe';
UPDATE person SET phone = '567' WHERE name = 'Jane';
SELECT * FROM person;

ALTER TABLE person ALTER phone SET NOT NULL;
COMMIT;

-- INSERT INTO person (name) VALUES ('Mary');
-- Error near line 21: NOT NULL constraint failed: person.phone

INSERT INTO person (name, phone) VALUES ('Mary', '890');
SELECT * FROM person;


$ sqlite examples/alter-table-in-transaction.sql

1|Joe
2|Jane
1|Joe|123
2|Jane|567
1|Joe|123
2|Jane|567
3|Mary|890

Optimization

We have a Perl script that will generated a file called examples/big-data.csv with lots of rows. We’ll use that data next.

perl generate.pl

INDEX

Adding INDEX to a table might speed up certain request.

.timer on
.import --csv -v examples/big-data.csv pairs

SELECT * from pairs WHERE name = 'aaaafryc'; -- 'aaaaaaaz';

CREATE INDEX names ON pairs (name);

SELECT * from pairs WHERE name = 'aaaafryc'; -- 'aaaaaaaz';


$ sqlite3 < examples/big-data-time.sql

Column separator ",", row separator "\n"
CREATE TABLE "main"."pairs"(
"name" ANY, "value" ANY)

Added 100000 rows with 0 errors using 100001 lines of input
aaaafryc|99999
Run Time: real 0.005019 user 0.005032 sys 0.000000
Run Time: real 0.027671 user 0.022654 sys 0.005020
aaaafryc|99999
Run Time: real 0.000018 user 0.000018 sys 0.000002

  • The 1st SELECT takes 0.005019 seconds. That’s going over all the rows.

  • The 3rd SELECT takes 0.000018 seconds. That’s because having and index allows SQLite to go directly to the right row.

  • The CREATE INDEX statement takes 0.027671 seconds. So it is very time consuming and also uses extra memory. It starts to be worth doing it if you run the select at least 7 times. (In this specific case.)

EXPLAIN

.import --csv -v examples/big-data.csv pairs

EXPLAIN SELECT * from pairs WHERE name = 'aaaaaaaz';

CREATE INDEX names ON pairs (name);

SELECT '--------------------------------';
EXPLAIN SELECT * from pairs WHERE name = 'aaaaaaaz';


$ sqlite < examples/big-data-explain.sql

Column separator ",", row separator "\n"
CREATE TABLE "main"."pairs"(
"name" ANY, "value" ANY)

Added 100000 rows with 0 errors using 100001 lines of input
addr  opcode         p1    p2    p3    p4             p5  comment      
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     10    0                    0   Start at 10
1     OpenRead       0     2     0     2              0   root=2 iDb=0; pairs
2     Rewind         0     9     0                    0   
3       Column         0     0     1                    0   r[1]= cursor 0 column 0
4       Ne             2     8     1     BINARY-8       83  if r[1]!=r[2] goto 8
5       Column         0     0     3                    0   r[3]= cursor 0 column 0
6       Column         0     1     4                    0   r[4]= cursor 0 column 1
7       ResultRow      3     2     0                    0   output=r[3..4]
8     Next           0     3     0                    1   
9     Halt           0     0     0                    0   
10    Transaction    0     0     1     0              1   usesStmtJournal=0
11    String8        0     2     0     aaaaaaaz       0   r[2]='aaaaaaaz'
12    Goto           0     1     0                    0   
--------------------------------
addr  opcode         p1    p2    p3    p4             p5  comment      
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     13    0                    0   Start at 13
1     OpenRead       0     2     0     2              0   root=2 iDb=0; pairs
2     OpenRead       1     483   0     k(2,,)         2   root=483 iDb=0; names
3     String8        0     1     0     aaaaaaaz       0   r[1]='aaaaaaaz'
4     Affinity       1     1     0     C              0   affinity(r[1])
5     SeekGE         1     12    1     1              0   key=r[1]
6       IdxGT          1     12    1     1              0   key=r[1]
7       DeferredSeek   1     0     0                    0   Move 0 to 1.rowid if needed
8       Column         1     0     2                    0   r[2]= cursor 1 column 0
9       Column         0     1     3                    0   r[3]= cursor 0 column 1
10      ResultRow      2     2     0                    0   output=r[2..3]
11    Next           1     6     1                    0   
12    Halt           0     0     0                    0   
13    Transaction    0     0     2     0              1   usesStmtJournal=0
14    Goto           0     1     0                    0

Increment or Insert (UPSERT)

The ON CONFLICT clause is a non-standard extension specific to SQLite.

This way of using ON CONFLICT is also referred to as UPSERT (which is not a keyword in SQLite).

CREATE TABLE counters (
    name TEXT UNIQUE NOT NULL,
    number INTEGER NOT NULL
);

INSERT INTO counters (name, number)
  VALUES ('apple', 1)
  ON CONFLICT(name) DO UPDATE SET number = number + 1;

SELECT * FROM counters;

INSERT INTO counters (name, number)
  VALUES ('apple', 1)
  ON CONFLICT(name) DO UPDATE SET number = number + 1;

SELECT * FROM counters;


$ sqlite < examples/increment-or-insert.sql

apple|1
apple|2

INSERT or UPDATE (replace)

ON CONFLICT

This statement will try to INSERT a row, but if the name is already in the database then it will update the recent field. The exclude.recent is the value we tried to insert. It can be used instead of duplicating the value of the recent field.

The first statement will INSERT a row. (because ‘Joe’ was not in the database yet)

The second statement will UPDATE the recent field of Joe.

CREATE TABLE score (
    name TEXT UNIQUE NOT NULL,
    recent INTEGER NOT NULL
);

INSERT INTO score (name, recent) VALUES('Joe', 23)
  ON CONFLICT(name) DO UPDATE SET recent=excluded.recent;

SELECT * FROM score;

INSERT INTO score (name, recent) VALUES('Joe', 42)
  ON CONFLICT(name) DO UPDATE SET recent=excluded.recent;

SELECT * FROM score;


CREATE TABLE score (
    name TEXT UNIQUE NOT NULL,
    recent INTEGER NOT NULL
);

INSERT INTO score (name, recent) VALUES('Joe', 23)
  ON CONFLICT(name) DO UPDATE SET recent=excluded.recent;

SELECT * FROM score;

INSERT INTO score (name, recent) VALUES('Joe', 42)
  ON CONFLICT(name) DO UPDATE SET recent=excluded.recent;

SELECT * FROM score;


Joe|23
Joe|42

Conditional CREATE

IF NOT EXISTS can help us especially with migration scripts. Both when they are external to the application and when they are part of the startup code.

In this example the first statement succeeds creating the table.

The second statement fails with an error.

The third statement fails but there is no error.

CREATE TABLE IF NOT EXISTS counters (
    name TEXT UNIQUE NOT NULL,
    number INTEGER NOT NULL
);

CREATE TABLE counters (
    name TEXT UNIQUE NOT NULL,
    number INTEGER NOT NULL
);


CREATE TABLE IF NOT EXISTS counters (
    name TEXT UNIQUE NOT NULL,
    number INTEGER NOT NULL
);

.schema


$ sqlite3 < examples/conditional-create.sql

Parse error near line 6: table counters already exists
  CREATE TABLE counters (     name TEXT UNIQUE NOT NULL,     number INTEGER NOT 
               ^--- error here
CREATE TABLE counters (
    name TEXT UNIQUE NOT NULL,
    number INTEGER NOT NULL
);

sqldiff

The sqldiff program comes with the installation of SQLite. It can help creating migration scripts by showing the differences between two databases.

We have a before and after schema:

CREATE TABLE person (
    id INTEGER PRIMARY KEY,
    name TEXT
);


CREATE TABLE person (
    id INTEGER PRIMARY KEY,
    name TEXT UNIQUE NOT NULL,
    phone TEXT NOT NULL
);

We create two db files and run the sqldiff command on these files.

rm -f /tmp/before.db /tmp/after.db
~/bin/sqlite3 /tmp/before.db < examples/before.sql
~/bin/sqlite3 /tmp/after.db < examples/after.sql
~/bin/sqldiff /tmp/before.db /tmp/after.db

This is the output we get:

ALTER TABLE person ADD COLUMN phone;

SQLite in Python

Python comes with the sqlite3 module that wraps (embeds) the C library implementing SQLite.

See SQLite in Python pages

SQLite in Rust

SQLite in Perl