SQLite Mastery — From Zero to Production

01

Introduction to SQLite

What it is · How it works · Why use it

What is SQLite?

SQLite is a self-contained, serverless, zero-configuration, transactional SQL database engine. Unlike traditional database systems (MySQL, PostgreSQL), SQLite does not run as a separate server process. Instead, it reads and writes directly to ordinary disk files—the entire database lives in a single .db file on your filesystem.

Created by D. Richard Hipp in 2000, SQLite is the most widely deployed database engine in the world—found in every smartphone, browser, operating system, and countless embedded applications. It implements most of SQL-92 and is completely free and open-source.

Key Features

Serverless — no installation, no daemon, no config
Single file database — easy to copy, backup, share
Zero-configuration — just open a file and go
ACID-compliant transactions
Dynamic typing system (type affinity)
Cross-platform — identical file on any OS
Lightweight — library is under 1MB
Embedded in Python, Node.js, Android, iOS
Full SQL support (SELECT, JOIN, triggers, views)
Public domain — no licensing restrictions

Use Cases

SQLite is ideal for local storage, embedded applications, and prototyping. It shines in scenarios where you need a reliable, structured store without server overhead.

1

Mobile Apps (Android & iOS)

Every app on your phone likely uses SQLite for local storage of settings, cache, and user data.

2

Desktop Applications

Browsers (Firefox, Chrome) store history, bookmarks, and cookies in SQLite files.

3

Prototyping & Development

Rapidly prototype data models before migrating to a production server database.

4

Small Business Systems

POS terminals, inventory systems, kiosks that operate offline-first.

5

Data Science & Analytics

Store datasets locally, run SQL analytics without a server.

SQLite vs. Server Databases

Feature	SQLite	MySQL	PostgreSQL
Architecture	File-based, serverless	Client-server	Client-server
Setup Required	None	Installation + config	Installation + config
Concurrent Writes	Single writer at a time	High concurrency	Very high concurrency
Max Database Size	~281TB (theoretical)	Unlimited	Unlimited
Best For	Local/embedded apps	Web apps, mid-scale	Enterprise, complex queries
Network Access	No	Yes	Yes
Footprint	<1MB	200MB+	400MB+

⚠️

When NOT to use SQLite

Avoid SQLite for high-concurrency write applications (e.g., busy web servers), applications requiring multiple simultaneous writers, or when you need stored procedures and advanced user management.

Installation & Setup

CLI Installation

Shell
Install SQLite CLI
# Ubuntu / Debian
sudo apt-get install sqlite3

# macOS (using Homebrew)
brew install sqlite3

# Windows: Download precompiled binary from sqlite.org/download.html
# Verify installation
sqlite3 --version
# → 3.45.1 2024-01-30...

Open Your First Database

Shell
Launch SQLite CLI
# Create (or open) a database file
sqlite3 mystore.db

# You'll see the prompt:
SQLite version 3.45.1
Enter ".help" for usage hints.
sqlite>

# Useful CLI commands
.help         -- List all dot commands
.tables       -- List all tables
.schema       -- Show CREATE statements
.quit         -- Exit the CLI
.mode column  -- Formatted column output
.headers on   -- Show column headers

💡

GUI Tools

For a visual experience, try DB Browser for SQLite (free, cross-platform) or the SQLite Viewer extension for VS Code. These are excellent for exploring databases visually.

02

Database Fundamentals

Tables · Data Types · Schemas

Core Concepts

A database is an organized collection of data. In SQLite, the entire database lives in a single file (e.g., inventory.db). Inside that file you have tables—structured grids of data with columns (attributes) and rows (records).

Concept	Database Analogy	Real World Example
Database (.db file)	The spreadsheet workbook	store.db
Table	A sheet within the workbook	products, customers, orders
Column	A header / field name	product_name, price, quantity
Row / Record	A single data entry	One product: (1, "Laptop", 999.99)
Primary Key	Unique row identifier	product_id: 1, 2, 3…

SQLite Data Types (Type Affinity)

SQLite uses a unique concept called type affinity—columns have a preferred type, but SQLite doesn't strictly enforce it. Any value can be stored in any column. This is different from MySQL or PostgreSQL which are strictly typed.

Storage Class	Description	Example Values
NULL	Null / missing value	NULL
INTEGER	Signed integer (1–8 bytes)	1, -42, 1000000
REAL	Floating-point number (8 bytes)	3.14, -0.001, 999.99
TEXT	UTF-8 / UTF-16 string	"Alice", "Nairobi", "2024-01-01"
BLOB	Binary Large Object (raw bytes)	Images, files, binary data

ℹ️

Common Type Declarations

Although SQLite has flexible typing, it's best practice to declare meaningful type names like VARCHAR(255), BOOLEAN, DATETIME. SQLite maps these to its five storage classes automatically via affinity rules.

Understanding Schemas

A schema is the blueprint of your database—it defines all tables, columns, types, and constraints. SQLite stores the schema in a special internal table called sqlite_master.

SQLite CLI
Viewing the schema
-- View schema of all tables
.schema

-- View schema of a specific table
.schema products

-- Query schema directly from sqlite_master
SELECT name, sql FROM sqlite_master WHERE type='table';

03

Basic SQL Operations

CREATE · .tables · .schema · DROP

Creating a Database & Tables

When you run sqlite3 mydb.db, the file is created automatically. Use CREATE TABLE to define your tables. Let's build a small inventory system as our running example throughout this course.

SQL
Creating the products table
CREATE TABLE products (
  id        INTEGER PRIMARY KEY AUTOINCREMENT,
  name      TEXT    NOT NULL,
  category  TEXT    DEFAULT 'Uncategorized',
  price     REAL    NOT NULL,
  stock_qty INTEGER DEFAULT 0,
  created_at TEXT   DEFAULT (datetime('now'))
);

CREATE TABLE customers (
  id       INTEGER PRIMARY KEY AUTOINCREMENT,
  name     TEXT    NOT NULL,
  email    TEXT    UNIQUE,
  phone    TEXT,
  city     TEXT
);

CREATE TABLE orders (
  id          INTEGER PRIMARY KEY AUTOINCREMENT,
  customer_id INTEGER REFERENCES customers(id),
  product_id  INTEGER REFERENCES products(id),
  quantity    INTEGER NOT NULL,
  order_date  TEXT    DEFAULT (date('now'))
);

SQLite CLI
Viewing tables and schema
sqlite> .tables
customers  orders  products

sqlite> .schema products
CREATE TABLE products (
  id        INTEGER PRIMARY KEY AUTOINCREMENT,
  name      TEXT    NOT NULL,
  ...
);

SQL
Dropping a table safely
-- Drop only if it exists (safe, no error if missing)
DROP TABLE IF EXISTS products;

-- Recreate it fresh
CREATE TABLE IF NOT EXISTS products (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  price REAL
);

04

CRUD Operations

INSERT · SELECT · UPDATE · DELETE

INSERT — Adding Records

SQL
Insert single and multiple records
-- Insert a single product
INSERT INTO products (name, category, price, stock_qty)
VALUES ('Laptop Pro 15"', 'Electronics', 1299.99, 25);

-- Insert multiple products at once
INSERT INTO products (name, category, price, stock_qty) VALUES
  ('Wireless Mouse',  'Electronics',  29.99,  100),
  ('USB-C Hub',       'Electronics',  49.99,  60),
  ('Notebook A5',     'Stationery',   4.99,   500),
  ('Ballpoint Pens',  'Stationery',   1.99,   1000),
  ('Desk Lamp LED',   'Furniture',    39.99,  45),
  ('Office Chair',    'Furniture',    249.99, 12);

-- Insert customers
INSERT INTO customers (name, email, phone, city) VALUES
  ('Alice Mwangi',   'alice@email.com',  '+254701000001', 'Nairobi'),
  ('Bob Kamau',     'bob@email.com',    '+254701000002', 'Mombasa'),
  ('Clara Osei',    'clara@email.com',  '+233201000003', 'Accra'),
  ('David Nkosi',   'david@email.com',  '+271101000004', 'Johannesburg');

SELECT — Reading Data

SQL
Basic SELECT queries
-- Select all columns from products
SELECT * FROM products;

-- Select specific columns
SELECT name, price, stock_qty FROM products;

-- WHERE: Filter by category
SELECT name, price FROM products
WHERE category = 'Electronics';

-- ORDER BY: Sort by price descending
SELECT name, price FROM products
ORDER BY price DESC;

-- LIMIT: Get only the top 3 results
SELECT name, price FROM products
ORDER BY price DESC
LIMIT 3;

-- OFFSET: Skip first 3, get next 3 (pagination)
SELECT name, price FROM products
ORDER BY id
LIMIT 3 OFFSET 3;

▶ Output: SELECT name, price FROM products ORDER BY price DESC LIMIT 3

name             price
---------------  --------
Office Chair     249.99
Laptop Pro 15"   1299.99
USB-C Hub        49.99

UPDATE — Modifying Records

SQL
UPDATE with conditions
-- Update the price of a specific product
UPDATE products
SET price = 1199.99
WHERE name = 'Laptop Pro 15"';

-- Update multiple columns at once
UPDATE products
SET price = 34.99, stock_qty = 80
WHERE id = 2;

-- Increase stock for all electronics by 10
UPDATE products
SET stock_qty = stock_qty + 10
WHERE category = 'Electronics';

🚨

Always use WHERE with UPDATE and DELETE

Running UPDATE products SET price = 0; without a WHERE clause will update ALL rows. Always verify your WHERE condition with a SELECT first before modifying data.

DELETE — Removing Records

SQL
Safe DELETE operations
-- Delete a specific product by ID (safest approach)
DELETE FROM products WHERE id = 7;

-- Delete products with zero stock
DELETE FROM products WHERE stock_qty = 0;

-- Best practice: SELECT first to preview what will be deleted
SELECT * FROM products WHERE stock_qty = 0;
-- If results look correct, then run DELETE

-- Delete ALL rows (table structure stays intact)
DELETE FROM products;

-- vs. DROP TABLE (removes structure entirely)
DROP TABLE products;

05

Filtering & Querying

WHERE · Operators · LIKE · IN · Logical

WHERE Clause Operators

SQL
Comparison and range operators
-- Equality and comparison
SELECT * FROM products WHERE price = 29.99;
SELECT * FROM products WHERE price > 100;
SELECT * FROM products WHERE price <= 50;
SELECT * FROM products WHERE stock_qty != 0;

-- BETWEEN: inclusive range
SELECT name, price FROM products
WHERE price BETWEEN 20 AND 100;

-- LIKE: pattern matching (% = any chars, _ = one char)
SELECT * FROM products WHERE name LIKE '%USB%';
SELECT * FROM products WHERE name LIKE 'Desk%';   -- starts with "Desk"
SELECT * FROM customers WHERE email LIKE '%@gmail.com';

-- IN: match a set of values
SELECT * FROM products
WHERE category IN ('Electronics', 'Furniture');

-- NOT IN: exclude a set
SELECT * FROM products
WHERE category NOT IN ('Stationery');

-- IS NULL / IS NOT NULL
SELECT * FROM customers WHERE phone IS NULL;
SELECT * FROM customers WHERE email IS NOT NULL;

Logical Operators: AND, OR, NOT

SQL
Combining conditions
-- AND: both conditions must be true
SELECT name, price, stock_qty FROM products
WHERE category = 'Electronics'
  AND price < 100;

-- OR: at least one condition must be true
SELECT name, city FROM customers
WHERE city = 'Nairobi'
   OR city = 'Accra';

-- NOT: negate a condition
SELECT * FROM products
WHERE NOT category = 'Stationery';

-- Combining AND / OR with parentheses (important!)
SELECT name, price, category FROM products
WHERE (category = 'Electronics' OR category = 'Furniture')
  AND price > 50
ORDER BY price;

▶ Output

name          price    category
------------  -------  -----------
USB-C Hub     49.99    Electronics
Desk Lamp LED 39.99    Furniture
Office Chair  249.99   Furniture
Laptop Pro    1199.99  Electronics

06

Joins & Relationships

Primary Keys · Foreign Keys · INNER JOIN · LEFT JOIN

Relational Design Principles

Good databases separate data into logical tables and link them using keys. A primary key uniquely identifies each row in a table. A foreign key in one table references the primary key of another—establishing a relationship.

SQL
Insert sample orders (linking customers to products)
INSERT INTO orders (customer_id, product_id, quantity) VALUES
  (1, 1, 1),  -- Alice bought a Laptop
  (1, 2, 2),  -- Alice bought 2 Wireless Mice
  (2, 3, 1),  -- Bob bought a USB-C Hub
  (3, 6, 1),  -- Clara bought an Office Chair
  (4, 4, 5);  -- David bought 5 Notebooks

INNER JOIN

An INNER JOIN returns only rows where there is a match in both tables. Rows with no match are excluded.

SQL
INNER JOIN — orders with customer and product info
SELECT
  o.id         AS order_id,
  c.name       AS customer_name,
  p.name       AS product_name,
  p.price,
  o.quantity,
  (p.price * o.quantity) AS total_amount,
  o.order_date
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
INNER JOIN products  p ON o.product_id  = p.id
ORDER BY o.id;

▶ Output

order_id  customer_name  product_name     price    qty  total_amount  order_date
--------  -------------  ---------------  -------  ---  ------------  ----------
1         Alice Mwangi   Laptop Pro 15"   1199.99  1    1199.99       2024-01-15
2         Alice Mwangi   Wireless Mouse   34.99    2    69.98         2024-01-15
3         Bob Kamau      USB-C Hub        49.99    1    49.99         2024-01-16
4         Clara Osei     Office Chair     249.99   1    249.99        2024-01-17
5         David Nkosi    Notebook A5      4.99     5    24.95         2024-01-17

LEFT JOIN

A LEFT JOIN returns all rows from the left table and the matching rows from the right table. If there's no match, NULL is returned for right-side columns. Useful for finding customers who have never placed an order.

SQL
LEFT JOIN — customers with or without orders
-- All customers, even those with no orders
SELECT
  c.name        AS customer,
  c.city,
  COUNT(o.id)  AS total_orders
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name, c.city
ORDER BY total_orders DESC;

-- Find customers who have NEVER ordered
SELECT c.name, c.email
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.id IS NULL;

07

Aggregate Functions

COUNT · SUM · AVG · MAX · MIN · GROUP BY · HAVING

Core Aggregate Functions

SQL
Aggregate function examples
-- COUNT: total number of products
SELECT COUNT(*) AS total_products FROM products;
-- → 6

-- SUM: total inventory value
SELECT SUM(price * stock_qty) AS total_inventory_value
FROM products;

-- AVG: average product price
SELECT ROUND(AVG(price), 2) AS avg_price FROM products;

-- MAX and MIN: price extremes
SELECT
  MAX(price) AS most_expensive,
  MIN(price) AS cheapest
FROM products;

GROUP BY & HAVING

SQL
Grouping and filtering groups
-- Sales summary by category
SELECT
  category,
  COUNT(*)           AS product_count,
  ROUND(AVG(price), 2) AS avg_price,
  SUM(stock_qty)     AS total_stock
FROM products
GROUP BY category
ORDER BY avg_price DESC;

-- HAVING: filter groups (not individual rows)
-- Show categories with avg price > 50
SELECT
  category,
  ROUND(AVG(price), 2) AS avg_price
FROM products
GROUP BY category
HAVING AVG(price) > 50;

▶ Output: Category summary

category     product_count  avg_price   total_stock
-----------  -------------  ----------  -----------
Furniture    2              144.99      57
Electronics  3              428.32      185
Stationery   2              3.49        1500

ℹ️

WHERE vs HAVING

Use WHERE to filter individual rows before grouping. Use HAVING to filter groups after aggregation. You cannot use aggregate functions in a WHERE clause.

08

Indexes & Performance

CREATE INDEX · Query Optimization · EXPLAIN QUERY PLAN

What is an Index?

An index is a separate data structure (like a book's index) that allows SQLite to find rows without scanning every row in a table. Without an index, every WHERE query does a full table scan—fine for small tables, catastrophic for millions of rows.

SQL
Creating and using indexes
-- Create a single-column index on category
CREATE INDEX idx_products_category
ON products(category);

-- Create an index on email for fast lookups
CREATE UNIQUE INDEX idx_customers_email
ON customers(email);

-- Composite index (multiple columns)
CREATE INDEX idx_products_cat_price
ON products(category, price);

-- Partial index: only index low-stock items
CREATE INDEX idx_low_stock
ON products(stock_qty)
WHERE stock_qty < 20;

-- See all indexes on a table
PRAGMA index_list(products);

-- Analyze query performance (before index)
EXPLAIN QUERY PLAN
SELECT * FROM products WHERE category = 'Electronics';
-- → SCAN TABLE products (full scan — slow on large data)
-- After creating idx_products_category:
-- → SEARCH TABLE products USING INDEX idx_products_category

-- Drop an index
DROP INDEX IF EXISTS idx_products_category;

⚠️

Index Trade-offs

Indexes speed up reads but slow down writes (INSERT/UPDATE/DELETE must update the index too). Don't index every column—focus on columns used in WHERE clauses, JOIN conditions, and ORDER BY on large tables.

09

Constraints & Data Integrity

PRIMARY KEY · NOT NULL · UNIQUE · FOREIGN KEY · CHECK

Comprehensive Constraints Example

Constraints are rules enforced by SQLite to maintain the accuracy and consistency of your data. They prevent bad data from ever entering the database.

SQL
Table with all major constraints
PRAGMA foreign_keys = ON; -- Must enable FK enforcement!

CREATE TABLE products_v2 (
  id          INTEGER PRIMARY KEY AUTOINCREMENT,
               -- PRIMARY KEY: unique, not null, auto-incremented

  name        TEXT    NOT NULL,
               -- NOT NULL: column must always have a value

  sku         TEXT    UNIQUE NOT NULL,
               -- UNIQUE: no two rows can have the same SKU

  price       REAL    NOT NULL
              CHECK (price >= 0),
               -- CHECK: price must be non-negative

  stock_qty   INTEGER NOT NULL
              DEFAULT 0
              CHECK (stock_qty >= 0),
               -- DEFAULT + CHECK combined

  category_id INTEGER
              REFERENCES categories(id)
              ON DELETE SET NULL
               -- FOREIGN KEY: links to categories table
               -- ON DELETE SET NULL: if category removed, set to NULL
);

-- ON DELETE options for foreign keys:
--   RESTRICT   → prevent delete if child rows exist (default)
--   CASCADE    → delete child rows when parent deleted
--   SET NULL   → set FK to NULL when parent deleted
--   SET DEFAULT → set FK to default value

SQL
Seeing constraints in action
-- This will FAIL — price is negative
INSERT INTO products_v2 (name, sku, price) VALUES
  ('Bad Product', 'SKU-999', -5.00);
-- Error: CHECK constraint failed: price >= 0

-- This will FAIL — duplicate SKU
INSERT INTO products_v2 (name, sku, price) VALUES
  ('Another Laptop', 'SKU-001', 999.00);
-- Error: UNIQUE constraint failed: products_v2.sku

10

Transactions

BEGIN · COMMIT · ROLLBACK · ACID Guarantees

Why Transactions Matter

Imagine processing a sale: you need to (1) decrease stock, (2) create an order, and (3) record payment. If step 2 fails after step 1 completes, your data is inconsistent. Transactions group operations so they either all succeed or all fail together—guaranteeing data integrity.

SQL
Transaction for processing a sale
BEGIN TRANSACTION;

-- Step 1: Create the order record
INSERT INTO orders (customer_id, product_id, quantity)
VALUES (1, 1, 2);

-- Step 2: Reduce stock quantity
UPDATE products
SET stock_qty = stock_qty - 2
WHERE id = 1;

-- Step 3: Verify stock didn't go negative (safety check)
-- If the above UPDATE caused stock to go below 0,
-- a CHECK constraint would trigger and we ROLLBACK

COMMIT; -- All operations saved permanently

-- ── Error handling pattern ──────────────────────
BEGIN TRANSACTION;

INSERT INTO orders (customer_id, product_id, quantity)
VALUES (99, 1, 1); -- customer 99 doesn't exist!

-- Oops, something went wrong
ROLLBACK; -- Undo ALL changes in this transaction

-- SAVEPOINT: partial rollback within a transaction
BEGIN TRANSACTION;
  INSERT INTO orders (customer_id, product_id, quantity) VALUES (1, 2, 1);
  SAVEPOINT after_order;
  UPDATE products SET stock_qty = stock_qty - 1 WHERE id = 2;
  -- If update fails:
  ROLLBACK TO after_order; -- Keeps INSERT, undoes UPDATE
COMMIT;

💡

Performance Tip: Batch Inserts

Wrapping thousands of INSERTs in a single transaction is dramatically faster—SQLite writes to disk only once per transaction, not once per statement. Expect 10–100× speedup for bulk operations.

11

SQLite with Applications

Python · Node.js · Parameterized Queries · Security

Python: sqlite3 (Built-in)

Python ships with the sqlite3 module in its standard library—no installation needed.

Python
Full Python SQLite workflow
import sqlite3
from contextlib import contextmanager

# Connect to (or create) the database
conn = sqlite3.connect('store.db')
conn.row_factory = sqlite3.Row  # Access cols by name: row['name']
cursor = conn.cursor()

# Create table
cursor.execute("""
    CREATE TABLE IF NOT EXISTS products (
        id        INTEGER PRIMARY KEY AUTOINCREMENT,
        name      TEXT    NOT NULL,
        price     REAL    NOT NULL,
        stock_qty INTEGER DEFAULT 0
    )
""")
conn.commit()

# ── PARAMETERIZED INSERT (safe from SQL injection!) ──
def add_product(name, price, qty):
    cursor.execute(
        "INSERT INTO products (name, price, stock_qty) VALUES (?, ?, ?)",
        (name, price, qty)   # ? placeholders — NEVER use f-strings for SQL!
    )
    conn.commit()
    return cursor.lastrowid

product_id = add_product("Wireless Keyboard", 49.99, 75)
print(f"Added product with ID: {product_id}")

# ── PARAMETERIZED SELECT ──
def get_products_by_category(max_price):
    cursor.execute(
        "SELECT * FROM products WHERE price <= ? ORDER BY price",
        (max_price,)
    )
    return cursor.fetchall()

products = get_products_by_category(100)
for p in products:
    print(f"{p['name']:20} ${p['price']:.2f}")

# ── TRANSACTION CONTEXT MANAGER ──
def process_sale(product_id, qty):
    try:
        with conn:  # auto-commit or rollback
            conn.execute(
                "INSERT INTO orders (product_id, quantity) VALUES (?, ?)",
                (product_id, qty)
            )
            conn.execute(
                "UPDATE products SET stock_qty = stock_qty - ? WHERE id = ?",
                (qty, product_id)
            )
    except sqlite3.Error as e:
        print(f"Transaction failed: {e}")

conn.close()

🚨

NEVER use string formatting for SQL values

Using f"WHERE name = '{user_input}'" creates a SQL injection vulnerability. Always use parameterized queries with ? placeholders. This is the most important security rule in database programming.

Node.js: better-sqlite3

Shell
Install better-sqlite3
npm install better-sqlite3

JavaScript / Node.js
Full Node.js SQLite workflow
const Database = require('better-sqlite3');

// Open database (creates file if doesn't exist)
const db = new Database('store.db');

// Enable WAL mode for better performance
db.pragma('journal_mode = WAL');
db.pragma('foreign_keys = ON');

// Create table
db.exec(`
  CREATE TABLE IF NOT EXISTS products (
    id        INTEGER PRIMARY KEY AUTOINCREMENT,
    name      TEXT    NOT NULL,
    price     REAL    NOT NULL,
    stock_qty INTEGER DEFAULT 0
  )
`);

// Prepared statements (compiled once, reused)
const insertProduct = db.prepare(
  'INSERT INTO products (name, price, stock_qty) VALUES (?, ?, ?)'
);

const getByMaxPrice = db.prepare(
  'SELECT * FROM products WHERE price <= ? ORDER BY price'
);

// Insert products
insertProduct.run('Mechanical Keyboard', 129.99, 30);
insertProduct.run('Monitor 27"', 399.99, 15);

// Query and iterate
const affordable = getByMaxPrice.all(200);
affordable.forEach(p => {
  console.log(`${p.name}: $${p.price}`);
});

// Transactions in Node.js (synchronous, fast)
const processSale = db.transaction((productId, qty) => {
  db.prepare('INSERT INTO orders (product_id, quantity) VALUES (?, ?)')
    .run(productId, qty);
  db.prepare('UPDATE products SET stock_qty = stock_qty - ? WHERE id = ?')
    .run(qty, productId);
});

try {
  processSale(1, 2);  // Atomic: both run or neither runs
  console.log('Sale processed!');
} catch (err) {
  console.error('Sale failed:', err.message);
}

db.close();

12

Advanced SQLite Features

Views · Triggers · Subqueries · FTS · JSON

Views — Saved Queries

A view is a virtual table defined by a stored query. It simplifies complex queries and provides an abstraction layer—users query the view as if it were a real table.

SQL
Creating and using views
-- Create a view for the order summary report
CREATE VIEW order_summary AS
  SELECT
    o.id           AS order_id,
    c.name         AS customer,
    p.name         AS product,
    o.quantity,
    p.price,
    (o.quantity * p.price) AS total,
    o.order_date
  FROM orders o
  JOIN customers c ON o.customer_id = c.id
  JOIN products  p ON o.product_id  = p.id;

-- Now query it like a regular table
SELECT * FROM order_summary WHERE total > 100;
SELECT customer, SUM(total) FROM order_summary GROUP BY customer;

DROP VIEW IF EXISTS order_summary;

Triggers — Automatic Reactions

A trigger is a procedure that automatically runs before or after INSERT, UPDATE, or DELETE events. Use triggers for auditing, maintaining derived data, or enforcing business rules.

SQL
Trigger: auto-update stock on order insert
-- Create an audit log table first
CREATE TABLE stock_audit (
  id          INTEGER PRIMARY KEY AUTOINCREMENT,
  product_id  INTEGER,
  old_qty     INTEGER,
  new_qty     INTEGER,
  changed_at  TEXT DEFAULT (datetime('now'))
);

-- Trigger: reduce stock when an order is placed
CREATE TRIGGER reduce_stock_on_order
AFTER INSERT ON orders
BEGIN
  UPDATE products
  SET stock_qty = stock_qty - NEW.quantity
  WHERE id = NEW.product_id;

  -- Also log the stock change
  INSERT INTO stock_audit (product_id, old_qty, new_qty)
  SELECT
    id,
    stock_qty + NEW.quantity,  -- what it was before
    stock_qty                  -- what it is now
  FROM products WHERE id = NEW.product_id;
END;

-- Now inserting an order automatically updates stock!
INSERT INTO orders (customer_id, product_id, quantity) VALUES (1, 2, 3);
SELECT * FROM stock_audit; -- See the audit trail

Subqueries

SQL
Correlated and nested subqueries
-- Scalar subquery: products priced above average
SELECT name, price
FROM products
WHERE price > (
  SELECT AVG(price) FROM products
);

-- EXISTS subquery: customers who have placed an order
SELECT name, email
FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.id
);

-- Subquery in FROM clause (derived table)
SELECT category, total_revenue
FROM (
  SELECT
    p.category,
    SUM(o.quantity * p.price) AS total_revenue
  FROM orders o JOIN products p ON o.product_id = p.id
  GROUP BY p.category
) revenue_by_cat
ORDER BY total_revenue DESC;

Full-Text Search (FTS5)

SQL
Full-text search on product descriptions
-- Create a virtual FTS5 table
CREATE VIRTUAL TABLE products_fts
USING fts5(name, category, description);

-- Populate it
INSERT INTO products_fts SELECT name, category,
  'High performance laptop with 16GB RAM' FROM products WHERE id=1;

-- Search for "laptop" across all text columns
SELECT * FROM products_fts WHERE products_fts MATCH 'laptop';

-- Ranked results (BM25 ranking)
SELECT name, category, rank
FROM products_fts
WHERE products_fts MATCH 'wireless OR keyboard'
ORDER BY rank;

JSON Support

SQL
Storing and querying JSON data
CREATE TABLE product_specs (
  id      INTEGER PRIMARY KEY,
  name    TEXT,
  specs   TEXT  -- JSON stored as TEXT
);

INSERT INTO product_specs (name, specs) VALUES
  ('Laptop', '{"ram":"16GB","cpu":"i7","storage":"512GB SSD"}'),
  ('Phone',  '{"ram":"8GB","cpu":"Snapdragon","storage":"256GB"}');

-- Extract a JSON field using json_extract()
SELECT
  name,
  json_extract(specs, '$.ram')     AS ram,
  json_extract(specs, '$.cpu')     AS cpu,
  json_extract(specs, '$.storage') AS storage
FROM product_specs;

-- Filter by JSON value
SELECT name FROM product_specs
WHERE json_extract(specs, '$.ram') = '16GB';

13

Backup & Export

.dump · .restore · CSV · Database Management

Exporting & Importing

Shell / SQLite CLI
Backup and restore operations
# ── EXPORT: Dump entire database to SQL text ──
sqlite3 store.db ".dump" > store_backup.sql

# Dump a single table
sqlite3 store.db ".dump products" > products_backup.sql

# ── IMPORT: Restore from SQL dump ──
sqlite3 new_store.db < store_backup.sql

# Or from inside CLI:
sqlite3 new_store.db
sqlite> .read store_backup.sql

# ── CSV EXPORT ──
sqlite3 store.db
sqlite> .mode csv
sqlite> .output products_export.csv
sqlite> SELECT * FROM products;
sqlite> .output stdout   # Reset output to screen

# ── CSV IMPORT ──
sqlite> .mode csv
sqlite> .import products_export.csv products

# ── Online Backup (while DB is in use!) ──
sqlite3 store.db ".backup backup_$(date +%Y%m%d).db"

# ── VACUUM: Reclaim space after deletes ──
sqlite3 store.db "VACUUM;"
Python
Programmatic backup in Python
import sqlite3, shutil
from datetime import datetime

def backup_database(source_path, backup_dir):
    timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
    backup_path = f"{backup_dir}/store_{timestamp}.db"

    source = sqlite3.connect(source_path)
    dest   = sqlite3.connect(backup_path)

    with dest:
        source.backup(dest)   # Safe live backup

    source.close()
    dest.close()
    print(f"Backup saved to {backup_path}")

backup_database('store.db', './backups')

14

Mini Projects — Hands-On

Beginner · Intermediate · Advanced

◆ Beginner

📒 Notes Database

Build a simple personal notes app with tags, search, and timestamps. Practice CREATE, INSERT, SELECT, and LIKE queries.

◆ Intermediate

✅ To-Do List

A persistent task manager with priorities, due dates, completion tracking, and category filtering. Uses transactions and indexes.

◆ Advanced

🏪 Inventory / POS System

A full point-of-sale system with products, customers, orders, stock management, triggers, and sales reports.

Project 1: Notes Database

SQL
Notes app schema + queries
-- Schema
CREATE TABLE notes (
  id         INTEGER PRIMARY KEY AUTOINCREMENT,
  title      TEXT NOT NULL,
  content    TEXT,
  tag        TEXT DEFAULT 'general',
  created_at TEXT DEFAULT (datetime('now')),
  updated_at TEXT DEFAULT (datetime('now'))
);

-- Add notes
INSERT INTO notes (title, content, tag) VALUES
  ('SQLite Tips', 'Always use parameterized queries!', 'tech'),
  ('Meeting Notes', 'Q4 planning: focus on mobile...', 'work'),
  ('Ideas', 'Build an offline-first POS system', 'ideas');

-- Search notes by keyword
SELECT title, tag, created_at
FROM notes
WHERE content LIKE '%offline%'
   OR title  LIKE '%offline%';

-- List by tag
SELECT * FROM notes WHERE tag = 'tech' ORDER BY created_at DESC;

-- Update note + refresh timestamp
UPDATE notes
SET content = 'Always use parameterized queries and transactions!',
    updated_at = datetime('now')
WHERE id = 1;

Project 2: To-Do List with Persistence

SQL
To-do list schema + queries
CREATE TABLE tasks (
  id          INTEGER PRIMARY KEY AUTOINCREMENT,
  title       TEXT    NOT NULL,
  description TEXT,
  priority    TEXT    CHECK(priority IN ('low','medium','high')) DEFAULT 'medium',
  due_date    TEXT,
  is_done     INTEGER DEFAULT 0,  -- 0 = pending, 1 = done
  created_at  TEXT    DEFAULT (datetime('now'))
);

CREATE INDEX idx_tasks_priority ON tasks(priority);
CREATE INDEX idx_tasks_due      ON tasks(due_date);

-- Insert tasks
INSERT INTO tasks (title, priority, due_date) VALUES
  ('Set up SQLite project', 'high',   '2024-01-20'),
  ('Write unit tests',     'medium', '2024-01-25'),
  ('Deploy to production', 'high',   '2024-01-30'),
  ('Update documentation', 'low',    '2024-02-05');

-- Get all pending high-priority tasks
SELECT title, due_date
FROM tasks
WHERE is_done = 0 AND priority = 'high'
ORDER BY due_date;

-- Mark a task as done
UPDATE tasks SET is_done = 1 WHERE id = 1;

-- Summary: task completion report
SELECT
  priority,
  COUNT(*)                                              AS total,
  SUM(is_done)                                          AS done,
  COUNT(*) - SUM(is_done)                              AS pending,
  ROUND(100.0 * SUM(is_done) / COUNT(*), 1) || '%'    AS completion_rate
FROM tasks
GROUP BY priority
ORDER BY CASE priority WHEN 'high' THEN 1
                        WHEN 'medium' THEN 2
                        ELSE 3 END;

Project 3: Inventory / POS System (Advanced)

SQL
Complete POS schema with triggers and reports
PRAGMA foreign_keys = ON;

-- Core tables
CREATE TABLE categories (
  id   INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT UNIQUE NOT NULL
);

CREATE TABLE products (
  id          INTEGER PRIMARY KEY AUTOINCREMENT,
  sku         TEXT    UNIQUE NOT NULL,
  name        TEXT    NOT NULL,
  category_id INTEGER REFERENCES categories(id),
  cost_price  REAL    NOT NULL CHECK(cost_price >= 0),
  sell_price  REAL    NOT NULL CHECK(sell_price >= 0),
  stock_qty   INTEGER DEFAULT 0 CHECK(stock_qty >= 0)
);

CREATE TABLE sales (
  id           INTEGER PRIMARY KEY AUTOINCREMENT,
  sale_date    TEXT    DEFAULT (datetime('now')),
  cashier      TEXT    NOT NULL,
  total_amount REAL    DEFAULT 0
);

CREATE TABLE sale_items (
  id         INTEGER PRIMARY KEY AUTOINCREMENT,
  sale_id    INTEGER REFERENCES sales(id) ON DELETE CASCADE,
  product_id INTEGER REFERENCES products(id),
  quantity   INTEGER NOT NULL CHECK(quantity > 0),
  unit_price REAL    NOT NULL,  -- snapshot price at time of sale
  subtotal   REAL    GENERATED ALWAYS AS (quantity * unit_price)
);

-- Trigger: auto-deduct stock when item is sold
CREATE TRIGGER deduct_stock
AFTER INSERT ON sale_items
BEGIN
  UPDATE products
  SET stock_qty = stock_qty - NEW.quantity
  WHERE id = NEW.product_id;

  UPDATE sales
  SET total_amount = total_amount + NEW.subtotal
  WHERE id = NEW.sale_id;
END;

-- ── REPORTS ──────────────────────────────────────

-- Daily sales report
SELECT
  date(sale_date)           AS sale_day,
  COUNT(DISTINCT id)        AS transactions,
  ROUND(SUM(total_amount),2) AS revenue
FROM sales
GROUP BY date(sale_date)
ORDER BY sale_day DESC;

-- Top 5 best-selling products by quantity
SELECT
  p.name,
  p.category_id,
  SUM(si.quantity)               AS units_sold,
  ROUND(SUM(si.subtotal), 2)    AS revenue,
  ROUND(SUM((si.unit_price - p.cost_price) * si.quantity), 2) AS profit
FROM sale_items si
JOIN products p ON si.product_id = p.id
GROUP BY p.id
ORDER BY units_sold DESC
LIMIT 5;

-- Low stock alert: items below reorder threshold
SELECT sku, name, stock_qty,
  CASE
    WHEN stock_qty = 0 THEN 'OUT OF STOCK'
    WHEN stock_qty < 10 THEN 'CRITICAL'
    ELSE 'LOW'
  END AS status
FROM products
WHERE stock_qty < 20
ORDER BY stock_qty;

SQLite Mastery:Zero to Production

Introduction to SQLite

What is SQLite?

Key Features

Use Cases

SQLite vs. Server Databases

Installation & Setup

CLI Installation

Open Your First Database

Database Fundamentals

Core Concepts

SQLite Data Types (Type Affinity)

Understanding Schemas

Basic SQL Operations

Creating a Database & Tables

CRUD Operations

INSERT — Adding Records

SELECT — Reading Data

UPDATE — Modifying Records

DELETE — Removing Records

Filtering & Querying

WHERE Clause Operators

Logical Operators: AND, OR, NOT

Joins & Relationships

Relational Design Principles

INNER JOIN

LEFT JOIN

Aggregate Functions

Core Aggregate Functions

GROUP BY & HAVING

Indexes & Performance

What is an Index?

Constraints & Data Integrity

Comprehensive Constraints Example

Transactions

Why Transactions Matter

SQLite with Applications

Python: sqlite3 (Built-in)

Node.js: better-sqlite3

Advanced SQLite Features

Views — Saved Queries

Triggers — Automatic Reactions

Subqueries

Full-Text Search (FTS5)

JSON Support

Backup & Export

Exporting & Importing

Mini Projects — Hands-On

📒 Notes Database

✅ To-Do List

🏪 Inventory / POS System

Project 1: Notes Database

Project 2: To-Do List with Persistence

Project 3: Inventory / POS System (Advanced)

SQLite Mastery:
Zero to Production