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

  • SQLite the most popular embedded relational database.

  • No need for external server process. The client directly accesses the file containing the data.

  • It has an in-memory version.

  • Command-line tool is called sqlite3

Using SQLite in programming languages

Install SQLite command-line tool

Debian/Ubuntu Linux:

$ sudo apt install sqlite3

Version of SQLite

$ echo .version | sqlite3
SQLite 3.45.1 2024-01-30 16:01:20 e876e51a0ed5c5b3126f52e532044363a014bc594cfefa87ffb5b82257ccalt1
zlib version 1.3
gcc-13.2.0 (64-bit)

Help with SQLite

echo .help | sqlite3

SQLite interactive shell

$ 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> .version
SQLite 3.45.1 2024-01-30 16:01:20 e876e51a0ed5c5b3126f52e532044363a014bc594cfefa87ffb5b82257ccalt1
zlib version 1.3
gcc-13.2.0 (64-bit)

sqlite> .help
...

sqlite> .quit

SQLite interactive shell - CREATE-INSERT-SELECT manually

  • Using in-memory database
sqlite3
  • Using a database file
sqlite3 my.db

CREATE INSERT SELECT

  • CREATE
  • INSERT
  • SELECT
CREATE TABLE person (
    name TEXT,
    email TEXT
);

INSERT INTO person (name, email) VALUES ('Foo', 'foo@example.com');
INSERT INTO person (name, email) VALUES ('Bar', 'bar@example.com');
SELECT * from person;
  • In memory:
$ sqlite3 < create-insert-select.sql
Foo|foo@example.com
Bar|bar@example.com
  • In file:
$ sqlite3 demo.db < create-insert-select.sql
Foo|foo@example.com
Bar|bar@example.com

$ rm -f demo.db

AUTOINCREMENT

CREATE TABLE people (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT
);

INSERT INTO people (username) VALUES ('foo');
INSERT INTO people (username) VALUES ('bar');
SELECT * from people;


$ sqlite3 demo.db < autoincrement.sql
1|foo
2|bar

$ rm -f demo.db

Missing 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 demo.db < missing-text-value.sql ; rm -f demo.db
Language?|SQL
Database?|SQLite
Meaning of life?|

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 demo.db < missing-integer-value.sql ; rm -f demo.db
2+2|4
2-2|0
Meaning of life?|

Field with DEFAULT 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?');
SELECT * from qa;


$ sqlite3 demo.db < default-value.sql ; rm -f demo.db
Language?|SQL
Database?|SQLite
Meaning of life?|42

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

UPDATE

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

UPDATE people SET grade = 44 WHERE name = "Joe";
SELECT * from people;
SELECT "";

UPDATE people SET grade = (SELECT grade FROM people WHERE name = "Joe") + 1 WHERE name = "Joe";
SELECT * from people;
SELECT "";

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?