SQLite

TL;DR

What: A lightweight, serverless, self-contained SQL database engine.

Why: Zero configuration, single file, perfect for embedded and local storage.

Quick Start

Install:

macOS (pre-installed) / Linux:

brew install sqlite  # macOS
sudo apt install sqlite3  # Ubuntu

Create database:

sqlite3 myapp.db

Create table:

CREATE TABLE users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  email TEXT UNIQUE,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

Insert and query:

INSERT INTO users (name, email) VALUES ('John', '[email protected]');
SELECT * FROM users;
.exit

Cheatsheet

Command	Description
sqlite3 file.db	Open/create database
.tables	List tables
.schema table	Show table schema
.headers on	Show column headers
.mode column	Column output mode
.exit	Exit SQLite
.dump	Export database
.read file.sql	Execute SQL file

Gotchas

Type affinity

-- SQLite uses dynamic typing
-- These are all valid:
CREATE TABLE test (
  id INTEGER,      -- INT, BIGINT, etc. → INTEGER
  name TEXT,       -- VARCHAR, CHAR, etc. → TEXT
  price REAL,      -- FLOAT, DOUBLE → REAL
  data BLOB        -- Binary data
);

Auto-increment

-- INTEGER PRIMARY KEY auto-increments automatically
CREATE TABLE users (
  id INTEGER PRIMARY KEY,  -- Auto-increments
  name TEXT
);

-- Or explicitly
CREATE TABLE items (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT
);

Date/time functions

SELECT datetime('now');
SELECT date('now', '-1 day');
SELECT strftime('%Y-%m-%d', 'now');

CREATE TABLE events (
  created_at DATETIME DEFAULT (datetime('now'))
);

Backup database

# Copy the file
cp myapp.db myapp_backup.db

# Or use .dump
sqlite3 myapp.db .dump > backup.sql

Use in code

# Python
import sqlite3
conn = sqlite3.connect('myapp.db')
cursor = conn.execute('SELECT * FROM users')

// Node.js with better-sqlite3
const Database = require('better-sqlite3');
const db = new Database('myapp.db');
const rows = db.prepare('SELECT * FROM users').all();

Next Steps

• SQLite Documentation - Official docs
• SQLite Tutorial - Learn SQLite
• DB Browser for SQLite - GUI tool
• better-sqlite3 - Node.js driver