SQLite

Overview

SQLite claims to be "the most used database engine in the world". It is a relational database implemented in C. It runs on all common operating systems and mobile devices. Its creator, D. Richard Hipp, pronounces it "es queue el ite".

Pros of SQLite include:

Cons of SQLite include:

See Appropriate Uses For SQLite. This contains the following advice and much more:

Installing

SQLite can be downloaded from here. In macOS it can be installed using Homebrew by entering brew install sqlite.

For help, enter sqlite3 --help.

CLI Tool

To start the SQLite CLI, enter sqlite3 optionally followed by a file path to a database file. This displays a sqlite> prompt where you can enter SQL commands (terminated by a semicolon) and "dot commands" that begin with a period.

For example, here is a sample session that creates a pets database that contains tables for dogs and cats.

$ sqlite3 pets.db
sqlite> create table dogs(id integer primary key autoincrement, name string, breed string, age integer);
sqlite> create table cats(id integer primary key autoincrement, name string, breed string, remaining_lives integer);
sqlite> .schema (displays the schema for each table)
sqlite> insert into dogs (name, breed, age) values('Comet', 'Whippet', 4);
sqlite> select * from dogs;
sqlite> .exit
$ ls *.db

The file pets.db exist now as long as at least one table was defined.

File Input

SQL commands can be read from a file. For example, if the file schema.txt commands SQL commands then the command sqlite3 demo.db < schema.txt will execute the commands using the database demo.db.

Dot Commands

The most commonly used dot commands are described in the following table:

ActionCommand
change working directory.cd {directory}
close current database and open new one.open {file-path}.db
attach an additional databaseattach database "{file-path}" as {name};
list names and paths of attached databases.databases
list tables in current database.tables
drop a tabledrop table if exists {table-name}
upsertinsert into table-name (col1, col2) values (val1, val2) on conflict(name) do update set col2 = val2;
show database schema.schema
show table schema.schema {table-name}
configure showing/hiding # of rows changed by SQL.changes {on | off}
export table to CSV file.headers on
.mode csv
.once {file-path}
select * from {table-name}
.system {file-path}
import CSV file into a table.import {file-path} {table-name}
execute shell command.shell {command}
show current configuration settings.show
exit.exit or .quit

When multiple databases are attached, the tables in all of them are accessible and reporting commands such as .tables report on all the databases.

By default, output from select statements uses list mode where each record is output on a single line with | characters separating column values.

Here is an example of list mode output:

Maisey|Treeing Walker Coonhound|11
Ramsay|Native American Indian Dog|8

The .mode {mode} command can be used to change this output. Supported modes include list, csv, line, and column.

Here is an example of csv mode output:

Maisey,"Treeing Walker Coonhound",11
Ramsay,"Native American Indian Dog",8

Here is an example of line mode output:

 name = Maisey
breed = Treeing Walker Coonhound
  age = 11

 name = Ramsay
breed = Native American Indian Dog
  age = 8

Here is an example of column mode output:

id name   breed                    age
-- ------ ------------------------ ---
1  Maisey Treeing Walker Coonhound 11
2  Ramsay Native American Indian D 8

Column Types

SQLite supports a small set of column types that include:

Use the numeric type for boolean values where 0 is false and 1 is true.

Use the text type for ISO date strings.

Use the integer type for storing seconds or milliseconds since the epoch (1970-01-01 00:00:00 UTC).

Write-Ahead Logging (WAL)

By default SQLite performs atomic commits and rollbacks using a "rollback journal". At any point in time there is either one writer or none. In WAL mode, readers block the single writer and the writer block readers.

WAL is a new option that is significantly faster. Readers and the writer no longer block each other.

Some of the disadvantages of WAL include:

To enable WAL mode, start the server as follows:

sqlite3 {name}.db 'PRAGMA journal_mode=WAL;'

For more detail, see Write-Ahead Logging.

JavaScript Support

There are many client libraries in npm for working with SQLite databases. A recommended Node library is better-sqlite3. To install this, enter npm install better-sqlite3. To use TypeScript types, enter npm install @types/better-sqlite3.

No library is needed when using Bun instead of Node. Bun has built-in support for SQLite which claims to be 3 to 6 times faster than better-sqlite3.

The example code below demonstrates using better-sqlite3 with TypeScript.

import sqlite from 'better-sqlite3';
import type {RunResult, Statement} from 'better-sqlite3';

// Open existing database file or creating if missing.
const db = sqlite('gift-track.db');

// This pragma must be enabled in order to
// check foreign key constraints and perform cascading deletes.
db.pragma('foreign_key = true');

// Delete the tables if they already exist.
db.exec('drop table if exists gifts');
db.exec('drop table if exists people');
db.exec('drop table if exists occasions');

// Create tables.

db.exec(
  'create table people (' +
    'id integer primary key autoincrement, ' +
    'name string, ' +
    'month integer, ' +
    'day integer, ' +
    'year integer, ' +
    'unique (name collate nocase))' // case insensitive
);

db.exec(
  'create table occasions (' +
    'id integer primary key autoincrement, ' +
    'name string, ' +
    'month integer, ' +
    'day integer, ' +
    'year integer, ' +
    'unique (name collate nocase))' // case insensitive
);

db.exec(
  'create table gifts (' +
    'id integer primary key autoincrement, ' +
    'description string, ' +
    'location string, ' +
    'name string, ' +
    'occasionId integer, ' +
    'personId integer, ' +
    'price numeric, ' +
    'url string, ' +
    'unique (name collate nocase), ' + // case insensitive
    // If an occasion is deleted, also delete gifts associated with it.
    'constraint fk_occasion foreign key ' +
    '  (occasionId) references occasions(id) on delete cascade ' +
    // If a person is deleted, also delete gifts their gifts.
    'constraint fk_person foreign key ' +
    '  (personId) references people(id) on delete cascade ' +
    ')'
);

// Create prepared statements.

const insertPersonPS = db.prepare(
  'insert into people (name, month, day, year) values (?, ?, ?, ?)'
);
const getAllPeoplePS = db.prepare('select * from people');
const getPersonPS = db.prepare('select * from people where id = ?');
const updatePersonPS = db.prepare(
  'update people set name=?, month=?, day=?, year=? where id = ?'
);
const deletePersonPS = db.prepare('delete from people where id = ?');

// Execute prepared statements.

type Person = {
  id?: number;
  name: string;
  month?: number;
  day?: number;
  year?: number;
};

// Insert a person.
const person: Person = {
  name: 'Mark',
  month: 4,
  day: 16,
  year: 1961
};

try {
  const result: RunResult = insertPersonPS.run( // synchronous
    person.name,
    person.month,
    person.day,
    person.year
  );
  person.id = result.lastInsertRowid as number;
} catch (e) {
  const isDuplicate = e.toString().includes('UNIQUE constraint failed');
  throw isDuplicate
    ? new Error('duplicate person name ' + person.name)
    : e;
}

// Get all the people.
const people = (getAllPeoplePS.all() as unknown) as Person[];

// Update a person.
updatePersonPS.run(
  person.name,
  person.month,
  person.day,
  person.year,
  person.id
);

// Delete a person.
deletePersonPS.run(person.id);

Python Support

Python has built-in support for SQLite. For details, see here.

VS Code Support

See the VS Code extension SQLite. To use this: