SQLite - Lightweight Embedded Database

SQLite stands as the most widely deployed database engine in the world, powering everything from mobile applications and desktop software to embedded systems and IoT devices. Created by D. Richard Hipp in 2000, SQLite is a self-contained, serverless, zero-configuration, transactional SQL database engine that requires no installation or setup. Unlike traditional client-server database systems, SQLite reads and writes directly to ordinary disk files, making it an ideal choice for applications that need a reliable, embedded database solution without the complexity of a full database server.

Installation and Setup

Installing SQLite

-- Ubuntu/Debian
sudo apt update
sudo apt install sqlite3

-- CentOS/RHEL/Fedora
sudo dnf install sqlite

-- macOS (using Homebrew)
brew install sqlite

-- Windows
-- Download from https://sqlite.org/download.html
-- Or use package managers like Chocolatey: choco install sqlite

-- Verify installation
sqlite3 --version

-- Create/open database
sqlite3 mydatabase.db

-- Open existing database
sqlite3 /path/to/existing/database.db

-- Open in-memory database (temporary)
sqlite3 :memory:

Basic Configuration and Settings

-- Show SQLite version and compile options
SELECT sqlite_version();
SELECT sqlite_source_id();
PRAGMA compile_options;

-- Database settings
PRAGMA database_list;
PRAGMA table_list;
PRAGMA schema_version;

-- Performance settings
PRAGMA cache_size = 10000;        -- Set cache size (pages)
PRAGMA temp_store = MEMORY;       -- Store temp tables in memory
PRAGMA journal_mode = WAL;        -- Write-Ahead Logging mode
PRAGMA synchronous = NORMAL;      -- Synchronization mode
PRAGMA foreign_keys = ON;         -- Enable foreign key constraints

-- Show current settings
PRAGMA cache_size;
PRAGMA journal_mode;
PRAGMA synchronous;
PRAGMA foreign_keys;

-- Database file information
PRAGMA page_size;
PRAGMA page_count;
PRAGMA freelist_count;
PRAGMA integrity_check;

SQLite Command Line Interface

-- Meta-commands (start with .)
.help                    -- Show help
.databases              -- List attached databases
.tables                 -- List tables
.schema                 -- Show schema for all tables
.schema table_name      -- Show schema for specific table
.indices table_name     -- Show indices for table

-- Output formatting
.mode column            -- Column mode
.mode csv              -- CSV mode
.mode html             -- HTML mode
.mode json             -- JSON mode
.mode line             -- Line mode
.mode list             -- List mode
.mode tabs             -- Tab-separated mode

.headers on            -- Show column headers
.width 10 20 15        -- Set column widths

-- File operations
.read script.sql       -- Execute SQL from file
.output results.txt    -- Redirect output to file
.output stdout         -- Reset output to console

-- Import/Export
.import data.csv table_name    -- Import CSV data
.dump                          -- Export entire database
.dump table_name              -- Export specific table

-- Backup and restore
.backup backup.db             -- Backup database
.restore backup.db            -- Restore database

-- System commands
.shell command                -- Execute shell command
.quit or .exit               -- Exit SQLite

Data Types and Storage Classes

SQLite Storage Classes

-- SQLite uses dynamic typing with storage classes
CREATE TABLE data_types_demo (
    id INTEGER PRIMARY KEY,
    null_value NULL,
    integer_value INTEGER,
    real_value REAL,
    text_value TEXT,
    blob_value BLOB
);

-- Insert various data types
INSERT INTO data_types_demo VALUES
(1, NULL, 42, 3.14159, 'Hello World', X'48656C6C6F'),
(2, NULL, -100, 2.71828, 'SQLite', X'53514C697465'),
(3, NULL, 0, 0.0, '', X'');

-- Type affinity examples
CREATE TABLE affinity_demo (
    int_column INTEGER,        -- INTEGER affinity
    text_column TEXT,          -- TEXT affinity
    real_column REAL,          -- REAL affinity
    numeric_column NUMERIC,    -- NUMERIC affinity
    blob_column BLOB           -- BLOB affinity
);

-- SQLite will try to convert values based on affinity
INSERT INTO affinity_demo VALUES
('123', 456, '78.9', '100.5', 'text data'),
(789, 'text', 123, 'abc', X'424C4F42');

-- Check actual storage classes
SELECT 
    int_column, typeof(int_column),
    text_column, typeof(text_column),
    real_column, typeof(real_column),
    numeric_column, typeof(numeric_column),
    blob_column, typeof(blob_column)
FROM affinity_demo;

-- Date and time handling (stored as TEXT, REAL, or INTEGER)
CREATE TABLE datetime_demo (
    id INTEGER PRIMARY KEY,
    date_text TEXT,           -- ISO 8601 format
    date_real REAL,           -- Julian day number
    date_integer INTEGER      -- Unix timestamp
);

INSERT INTO datetime_demo VALUES
(1, '2024-01-15 14:30:00', julianday('2024-01-15 14:30:00'), strftime('%s', '2024-01-15 14:30:00')),
(2, datetime('now'), julianday('now'), strftime('%s', 'now'));

-- Date/time functions
SELECT 
    datetime('now') AS current_datetime,
    date('now') AS current_date,
    time('now') AS current_time,
    strftime('%Y-%m-%d %H:%M:%S', 'now') AS formatted_datetime,
    julianday('now') AS julian_day,
    strftime('%s', 'now') AS unix_timestamp;

Boolean and JSON Support

-- Boolean values (stored as INTEGER 0 or 1)
CREATE TABLE boolean_demo (
    id INTEGER PRIMARY KEY,
    is_active BOOLEAN,
    is_verified INTEGER CHECK (is_verified IN (0, 1))
);

INSERT INTO boolean_demo VALUES
(1, TRUE, 1),
(2, FALSE, 0),
(3, 1, 1),
(4, 0, 0);

-- JSON support (SQLite 3.38+)
CREATE TABLE json_demo (
    id INTEGER PRIMARY KEY,
    data JSON,
    metadata TEXT
);

-- Insert JSON data
INSERT INTO json_demo VALUES
(1, '{"name": "John", "age": 30, "skills": ["SQL", "Python"]}', '{"created": "2024-01-15"}'),
(2, json('{"name": "Jane", "age": 25, "active": true}'), json('{"updated": "2024-01-16"}'));

-- JSON functions (if available)
SELECT 
    json_extract(data, '$.name') AS name,
    json_extract(data, '$.age') AS age,
    json_extract(data, '$.skills[0]') AS first_skill,
    json_type(data, '$.age') AS age_type
FROM json_demo;

-- JSON aggregation
SELECT json_group_array(json_extract(data, '$.name')) AS all_names
FROM json_demo;

Database Operations

Creating and Managing Databases

-- Attach additional databases
ATTACH DATABASE 'secondary.db' AS secondary;
ATTACH DATABASE ':memory:' AS temp_db;

-- List attached databases
PRAGMA database_list;

-- Create table in specific database
CREATE TABLE secondary.users (
    id INTEGER PRIMARY KEY,
    username TEXT UNIQUE,
    email TEXT
);

-- Copy data between databases
INSERT INTO secondary.users SELECT * FROM main.users;

-- Detach database
DETACH DATABASE secondary;

-- Vacuum database (reclaim space)
VACUUM;

-- Analyze database (update statistics)
ANALYZE;

-- Check database integrity
PRAGMA integrity_check;
PRAGMA quick_check;

-- Database size information
SELECT 
    page_count * page_size AS database_size_bytes,
    page_count,
    page_size,
    freelist_count
FROM pragma_page_count(), pragma_page_size(), pragma_freelist_count();

Table Creation and Management

-- Create table with constraints
CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT NOT NULL UNIQUE,
    email TEXT NOT NULL UNIQUE,
    password_hash TEXT NOT NULL,
    first_name TEXT,
    last_name TEXT,
    date_of_birth DATE,
    is_active BOOLEAN DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    CHECK (length(username) >= 3),
    CHECK (email LIKE '%@%.%')
);

-- Create table with foreign keys
CREATE TABLE posts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    title TEXT NOT NULL,
    content TEXT,
    published_at DATETIME,
    is_published BOOLEAN DEFAULT 0,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Create index
CREATE INDEX idx_users_username ON users(username);
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_posts_user_id ON posts(user_id);
CREATE INDEX idx_posts_published ON posts(is_published, published_at);

-- Unique index
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);

-- Partial index
CREATE INDEX idx_active_users ON users(username) WHERE is_active = 1;

-- Expression index
CREATE INDEX idx_users_lower_email ON users(lower(email));

-- Show table schema
.schema users
PRAGMA table_info(users);

-- Show indexes
PRAGMA index_list(users);
PRAGMA index_info(idx_users_username);

-- Alter table (limited in SQLite)
ALTER TABLE users ADD COLUMN phone TEXT;
ALTER TABLE users RENAME TO customers;
ALTER TABLE customers RENAME COLUMN phone TO phone_number;
ALTER TABLE customers DROP COLUMN phone_number;  -- SQLite 3.35+

-- Drop table
DROP TABLE IF EXISTS old_table;

CRUD Operations

INSERT Operations

-- Basic insert
INSERT INTO users (username, email, first_name, last_name) 
VALUES ('john_doe', 'john@example.com', 'John', 'Doe');

-- Multiple row insert
INSERT INTO users (username, email, first_name, last_name) VALUES
('jane_smith', 'jane@example.com', 'Jane', 'Smith'),
('bob_wilson', 'bob@example.com', 'Bob', 'Wilson'),
('alice_brown', 'alice@example.com', 'Alice', 'Brown');

-- Insert with SELECT
INSERT INTO users (username, email, first_name, last_name)
SELECT 'user_' || id, 'user' || id || '@example.com', 'Test', 'User' || id
FROM (SELECT 1 AS id UNION SELECT 2 UNION SELECT 3);

-- Insert or replace
INSERT OR REPLACE INTO users (id, username, email, first_name, last_name) 
VALUES (1, 'john_doe_updated', 'john.doe@example.com', 'John', 'Doe');

-- Insert or ignore (skip if conflict)
INSERT OR IGNORE INTO users (username, email, first_name, last_name) 
VALUES ('john_doe', 'john@example.com', 'John', 'Doe');

-- Get last inserted row ID
SELECT last_insert_rowid();

-- Insert with UPSERT (SQLite 3.24+)
INSERT INTO users (username, email, first_name, last_name) 
VALUES ('new_user', 'new@example.com', 'New', 'User')
ON CONFLICT(email) DO UPDATE SET 
    username = excluded.username,
    updated_at = CURRENT_TIMESTAMP;

SELECT Operations

-- Basic select
SELECT * FROM users;

-- Select specific columns
SELECT id, username, email, created_at FROM users;

-- Select with WHERE clause
SELECT * FROM users 
WHERE is_active = 1 
AND created_at > '2024-01-01';

-- Pattern matching
SELECT * FROM users 
WHERE username LIKE 'john%' 
OR email GLOB '*@gmail.com';

-- Select with LIMIT and OFFSET
SELECT * FROM users 
ORDER BY created_at DESC 
LIMIT 10 OFFSET 20;

-- Aggregate functions
SELECT 
    COUNT(*) AS total_users,
    COUNT(DISTINCT email) AS unique_emails,
    MIN(created_at) AS first_user_date,
    MAX(created_at) AS latest_user_date,
    AVG(julianday('now') - julianday(date_of_birth)) / 365.25 AS avg_age
FROM users 
WHERE is_active = 1;

-- Group by with having
SELECT 
    strftime('%Y', created_at) AS registration_year,
    COUNT(*) AS user_count
FROM users 
GROUP BY strftime('%Y', created_at)
HAVING user_count > 5
ORDER BY registration_year DESC;

-- Subqueries
SELECT * FROM users 
WHERE id IN (
    SELECT DISTINCT user_id 
    FROM posts 
    WHERE is_published = 1
);

-- Common Table Expressions (CTE)
WITH active_users AS (
    SELECT id, username, email 
    FROM users 
    WHERE is_active = 1
),
user_post_counts AS (
    SELECT 
        u.id,
        u.username,
        COUNT(p.id) AS post_count
    FROM active_users u
    LEFT JOIN posts p ON u.id = p.user_id
    GROUP BY u.id, u.username
)
SELECT * FROM user_post_counts
WHERE post_count > 0
ORDER BY post_count DESC;

UPDATE and DELETE Operations

-- Basic update
UPDATE users 
SET first_name = 'Jonathan' 
WHERE username = 'john_doe';

-- Update multiple columns
UPDATE users 
SET 
    first_name = 'Jane',
    last_name = 'Johnson',
    updated_at = CURRENT_TIMESTAMP
WHERE id = 2;

-- Update with calculation
UPDATE users 
SET username = lower(first_name || '_' || last_name)
WHERE username IS NULL;

-- Update with JOIN (using subquery in SQLite)
UPDATE users 
SET last_post_date = (
    SELECT MAX(published_at) 
    FROM posts 
    WHERE posts.user_id = users.id 
    AND is_published = 1
)
WHERE EXISTS (
    SELECT 1 FROM posts 
    WHERE posts.user_id = users.id 
    AND is_published = 1
);

-- Conditional update with CASE
UPDATE users 
SET status = CASE 
    WHEN julianday('now') - julianday(created_at) > 365 THEN 'veteran'
    WHEN julianday('now') - julianday(created_at) > 30 THEN 'regular'
    ELSE 'new'
END;

-- Delete operations
DELETE FROM users 
WHERE username = 'test_user';

-- Delete with conditions
DELETE FROM users 
WHERE is_active = 0 
AND created_at < date('now', '-2 years');

-- Delete with subquery
DELETE FROM users 
WHERE id NOT IN (
    SELECT DISTINCT user_id 
    FROM posts 
    WHERE user_id IS NOT NULL
) 
AND created_at < date('now', '-1 year');

Advanced Queries and Functions

String Functions

-- String manipulation functions
SELECT 
    username,
    length(username) AS username_length,
    upper(username) AS uppercase,
    lower(username) AS lowercase,
    substr(username, 1, 3) AS first_three_chars,
    replace(email, '@', ' AT ') AS masked_email,
    trim('  ' || username || '  ') AS trimmed,
    ltrim(username, 'j') AS left_trimmed,
    rtrim(username, 'e') AS right_trimmed
FROM users;

-- String concatenation
SELECT 
    first_name || ' ' || last_name AS full_name,
    printf('%s <%s>', first_name || ' ' || last_name, email) AS formatted_contact
FROM users;

-- Pattern matching
SELECT * FROM users 
WHERE email LIKE '%@gmail.com'
OR username GLOB 'j*'
OR email REGEXP '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$';

-- String aggregation
SELECT group_concat(username, ', ') AS all_usernames
FROM users
WHERE is_active = 1;

SELECT group_concat(DISTINCT substr(email, instr(email, '@') + 1), ', ') AS email_domains
FROM users;

Date and Time Functions

-- Current date/time functions
SELECT 
    datetime('now') AS current_datetime,
    date('now') AS current_date,
    time('now') AS current_time,
    strftime('%Y-%m-%d %H:%M:%S', 'now') AS formatted_now,
    strftime('%w', 'now') AS day_of_week,
    strftime('%j', 'now') AS day_of_year,
    strftime('%W', 'now') AS week_of_year;

-- Date arithmetic
SELECT 
    created_at,
    date(created_at, '+1 day') AS tomorrow,
    date(created_at, '+1 month') AS next_month,
    date(created_at, '+1 year') AS next_year,
    date(created_at, '-7 days') AS week_ago,
    datetime(created_at, '+2 hours') AS two_hours_later
FROM users;

-- Date calculations
SELECT 
    username,
    date_of_birth,
    (julianday('now') - julianday(date_of_birth)) / 365.25 AS age_years,
    julianday('now') - julianday(created_at) AS days_since_registration,
    strftime('%Y', created_at) AS registration_year,
    strftime('%m', created_at) AS registration_month
FROM users
WHERE date_of_birth IS NOT NULL;

-- Date formatting
SELECT 
    created_at,
    strftime('%Y-%m-%d', created_at) AS date_only,
    strftime('%H:%M:%S', created_at) AS time_only,
    strftime('%d/%m/%Y', created_at) AS uk_format,
    strftime('%m/%d/%Y', created_at) AS us_format,
    strftime('%B %d, %Y', created_at) AS long_format,
    strftime('%a, %b %d %Y', created_at) AS short_format
FROM users;

Mathematical Functions

-- Basic math functions
SELECT 
    abs(-42) AS absolute_value,
    round(3.14159, 2) AS rounded,
    max(10, 20, 5) AS maximum,
    min(10, 20, 5) AS minimum,
    random() AS random_number,
    abs(random() % 100) AS random_0_to_99;

-- Aggregate math functions
SELECT 
    COUNT(*) AS total_users,
    SUM(CASE WHEN is_active = 1 THEN 1 ELSE 0 END) AS active_users,
    AVG(julianday('now') - julianday(created_at)) AS avg_days_registered,
    MIN(created_at) AS first_registration,
    MAX(created_at) AS latest_registration
FROM users;

-- Statistical functions (if available)
CREATE TABLE scores (
    id INTEGER PRIMARY KEY,
    student_name TEXT,
    score REAL
);

INSERT INTO scores VALUES
(1, 'Alice', 85.5),
(2, 'Bob', 92.0),
(3, 'Charlie', 78.5),
(4, 'Diana', 96.5),
(5, 'Eve', 88.0);

-- Calculate statistics
WITH stats AS (
    SELECT 
        AVG(score) AS mean_score,
        COUNT(*) AS count_scores
    FROM scores
)
SELECT 
    s.student_name,
    s.score,
    st.mean_score,
    s.score - st.mean_score AS deviation_from_mean,
    RANK() OVER (ORDER BY s.score DESC) AS rank
FROM scores s, stats st
ORDER BY s.score DESC;

Window Functions (SQLite 3.25+)

-- Basic window functions
SELECT 
    username,
    created_at,
    ROW_NUMBER() OVER (ORDER BY created_at) AS row_num,
    RANK() OVER (ORDER BY created_at) AS rank,
    DENSE_RANK() OVER (ORDER BY created_at) AS dense_rank,
    NTILE(4) OVER (ORDER BY created_at) AS quartile
FROM users;

-- Partition by groups
SELECT 
    username,
    is_active,
    created_at,
    ROW_NUMBER() OVER (PARTITION BY is_active ORDER BY created_at) AS group_row_num,
    COUNT(*) OVER (PARTITION BY is_active) AS group_total,
    FIRST_VALUE(username) OVER (PARTITION BY is_active ORDER BY created_at) AS first_in_group,
    LAST_VALUE(username) OVER (PARTITION BY is_active ORDER BY created_at 
                               ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_in_group
FROM users;

-- Running totals and moving averages
WITH daily_registrations AS (
    SELECT 
        date(created_at) AS reg_date,
        COUNT(*) AS daily_count
    FROM users
    GROUP BY date(created_at)
)
SELECT 
    reg_date,
    daily_count,
    SUM(daily_count) OVER (ORDER BY reg_date) AS running_total,
    AVG(daily_count) OVER (ORDER BY reg_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg_3day,
    LAG(daily_count, 1) OVER (ORDER BY reg_date) AS previous_day,
    LEAD(daily_count, 1) OVER (ORDER BY reg_date) AS next_day
FROM daily_registrations
ORDER BY reg_date;

Transactions and Concurrency

Transaction Management

-- Basic transaction
BEGIN TRANSACTION;
INSERT INTO users (username, email, first_name, last_name) 
VALUES ('new_user', 'new@example.com', 'New', 'User');
INSERT INTO posts (user_id, title, content) 
VALUES (last_insert_rowid(), 'First Post', 'Hello World!');
COMMIT;

-- Transaction with rollback
BEGIN TRANSACTION;
UPDATE users SET email = 'updated@example.com' WHERE id = 1;
-- Check the change
SELECT * FROM users WHERE id = 1;
-- Rollback if not satisfied
ROLLBACK;

-- Savepoints
BEGIN TRANSACTION;
INSERT INTO users (username, email) VALUES ('user1', 'user1@example.com');
SAVEPOINT sp1;

INSERT INTO users (username, email) VALUES ('user2', 'user2@example.com');
SAVEPOINT sp2;

INSERT INTO users (username, email) VALUES ('user3', 'user3@example.com');

-- Rollback to savepoint
ROLLBACK TO SAVEPOINT sp2;
-- user3 is rolled back, but user1 and user2 remain

COMMIT;

-- Deferred transactions
BEGIN DEFERRED TRANSACTION;  -- Default
BEGIN IMMEDIATE TRANSACTION; -- Lock immediately
BEGIN EXCLUSIVE TRANSACTION; -- Exclusive lock

Concurrency and Locking

-- WAL mode for better concurrency
PRAGMA journal_mode = WAL;

-- Check current journal mode
PRAGMA journal_mode;

-- Busy timeout (wait for locks)
PRAGMA busy_timeout = 30000;  -- 30 seconds

-- Show database locks
PRAGMA locking_mode;
PRAGMA locking_mode = EXCLUSIVE;  -- Exclusive locking
PRAGMA locking_mode = NORMAL;     -- Normal locking

-- Synchronous modes
PRAGMA synchronous = OFF;     -- Fastest, least safe
PRAGMA synchronous = NORMAL;  -- Good balance (default for WAL)
PRAGMA synchronous = FULL;    -- Safest, slowest

-- Check for database locks
SELECT name FROM pragma_database_list() WHERE name = 'main';

-- Handle busy database
.timeout 30000  -- Set timeout in CLI

Error Handling and Constraints

-- Foreign key constraints
PRAGMA foreign_keys = ON;

-- Check constraint violations
PRAGMA foreign_key_check;
PRAGMA foreign_key_check(posts);

-- Constraint examples
CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    user_id INTEGER NOT NULL,
    total_amount REAL NOT NULL CHECK (total_amount > 0),
    order_date DATE NOT NULL DEFAULT CURRENT_DATE,
    status TEXT NOT NULL DEFAULT 'pending' 
        CHECK (status IN ('pending', 'processing', 'shipped', 'delivered', 'cancelled')),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Insert with constraint handling
INSERT OR IGNORE INTO orders (user_id, total_amount, status) 
VALUES (999, -10, 'invalid');  -- Will be ignored due to constraints

-- Error handling in application code (pseudo-code)
-- try:
--     execute("INSERT INTO orders ...")
-- except sqlite3.IntegrityError as e:
--     handle_constraint_violation(e)
-- except sqlite3.Error as e:
--     handle_general_error(e)

Performance Optimization

Indexing Strategies

-- Analyze query performance
EXPLAIN QUERY PLAN SELECT * FROM users WHERE email = 'john@example.com';

-- Create covering index
CREATE INDEX idx_users_email_covering ON users(email, username, first_name, last_name);

-- Composite index for multiple columns
CREATE INDEX idx_users_active_created ON users(is_active, created_at);

-- Partial index for specific conditions
CREATE INDEX idx_active_users_username ON users(username) WHERE is_active = 1;

-- Expression index
CREATE INDEX idx_users_email_domain ON users(substr(email, instr(email, '@') + 1));

-- Show index usage
EXPLAIN QUERY PLAN 
SELECT username, first_name, last_name 
FROM users 
WHERE email = 'john@example.com';

-- Drop unused indexes
DROP INDEX IF EXISTS old_index_name;

-- Analyze tables for better query planning
ANALYZE;
ANALYZE users;

-- Show table statistics
SELECT * FROM sqlite_stat1;

Query Optimization

-- Use appropriate data types
-- Use INTEGER PRIMARY KEY for auto-increment
-- Use TEXT for strings, REAL for floating point, INTEGER for whole numbers

-- Optimize WHERE clauses
-- Good: Use indexed columns
SELECT * FROM users WHERE id = 123;
SELECT * FROM users WHERE email = 'john@example.com';

-- Avoid: Functions in WHERE clause
-- Bad: SELECT * FROM users WHERE upper(username) = 'JOHN';
-- Good: SELECT * FROM users WHERE username = 'john';

-- Use LIMIT when appropriate
SELECT * FROM users ORDER BY created_at DESC LIMIT 10;

-- Use EXISTS instead of IN for better performance
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM posts p 
    WHERE p.user_id = u.id 
    AND p.is_published = 1
);

-- Avoid SELECT * when possible
SELECT id, username, email FROM users WHERE is_active = 1;

-- Use prepared statements (in application code)
-- stmt = db.prepare("SELECT * FROM users WHERE email = ?")
-- stmt.bind(1, email)
-- result = stmt.execute()

Database Optimization

-- Vacuum database to reclaim space
VACUUM;

-- Incremental vacuum (if auto_vacuum is enabled)
PRAGMA auto_vacuum = INCREMENTAL;
PRAGMA incremental_vacuum(1000);  -- Vacuum 1000 pages

-- Analyze database for query optimization
ANALYZE;

-- Optimize database settings
PRAGMA cache_size = 10000;        -- Increase cache size
PRAGMA temp_store = MEMORY;       -- Use memory for temp tables
PRAGMA mmap_size = 268435456;     -- Use memory mapping (256MB)

-- Check database size and statistics
SELECT 
    page_count * page_size AS size_bytes,
    page_count,
    page_size,
    freelist_count,
    (freelist_count * 100.0 / page_count) AS fragmentation_percent
FROM pragma_page_count(), pragma_page_size(), pragma_freelist_count();

-- Show compile-time options
PRAGMA compile_options;

-- Performance monitoring
.timer ON  -- Show query execution time in CLI

Backup and Recovery

Backup Strategies

-- SQLite backup using .backup command
.backup backup.db

-- Backup specific database
.backup main backup_main.db

-- Online backup using SQL
VACUUM INTO 'backup.db';

-- Copy database file (when not in use)
-- cp database.db backup.db

-- Export as SQL dump
.dump > backup.sql

-- Export specific tables
.dump users posts > tables_backup.sql

-- Export schema only
.schema > schema_backup.sql

Restore Operations

-- Restore from backup file
.restore backup.db

-- Restore from SQL dump
.read backup.sql

-- Import into new database
sqlite3 new_database.db < backup.sql

-- Attach and copy data
ATTACH DATABASE 'backup.db' AS backup;
INSERT INTO users SELECT * FROM backup.users;
DETACH DATABASE backup;

-- Verify backup integrity
PRAGMA integrity_check;
PRAGMA foreign_key_check;

Point-in-Time Recovery

-- Enable WAL mode for better backup consistency
PRAGMA journal_mode = WAL;

-- Create consistent backup while database is in use
-- 1. Start backup process
-- 2. Copy main database file
-- 3. Copy WAL file
-- 4. Copy SHM file (if exists)

-- Checkpoint WAL file
PRAGMA wal_checkpoint;
PRAGMA wal_checkpoint(FULL);
PRAGMA wal_checkpoint(RESTART);
PRAGMA wal_checkpoint(TRUNCATE);

-- Show WAL information
PRAGMA wal_autocheckpoint;
PRAGMA wal_autocheckpoint = 1000;  -- Checkpoint every 1000 pages

-- Manual checkpoint
PRAGMA wal_checkpoint(FULL);

SQLite Extensions and Advanced Features

Full-Text Search (FTS)

-- Create FTS table
CREATE VIRTUAL TABLE documents USING fts5(title, content);

-- Insert documents
INSERT INTO documents VALUES
('SQLite Tutorial', 'Learn SQLite database management and SQL queries'),
('Python Programming', 'Introduction to Python programming language'),
('Web Development', 'HTML, CSS, JavaScript and web frameworks');

-- Search documents
SELECT * FROM documents WHERE documents MATCH 'sqlite';
SELECT * FROM documents WHERE documents MATCH 'python OR javascript';
SELECT * FROM documents WHERE documents MATCH 'web AND development';

-- Highlight search results
SELECT highlight(documents, 0, '<b>', '</b>') AS highlighted_title,
       highlight(documents, 1, '<mark>', '</mark>') AS highlighted_content
FROM documents 
WHERE documents MATCH 'programming';

-- Snippet extraction
SELECT snippet(documents, 1, '<b>', '</b>', '...', 10) AS snippet
FROM documents 
WHERE documents MATCH 'database';

-- Ranking results
SELECT *, rank FROM documents 
WHERE documents MATCH 'sqlite' 
ORDER BY rank;

R-Tree Spatial Index

-- Create R-Tree index for spatial data
CREATE VIRTUAL TABLE spatial_index USING rtree(
    id,              -- Primary key
    minX, maxX,      -- X coordinate range
    minY, maxY       -- Y coordinate range
);

-- Insert spatial data
INSERT INTO spatial_index VALUES
(1, 0, 10, 0, 10),    -- Rectangle from (0,0) to (10,10)
(2, 5, 15, 5, 15),    -- Rectangle from (5,5) to (15,15)
(3, 20, 30, 20, 30);  -- Rectangle from (20,20) to (30,30)

-- Spatial queries
-- Find rectangles that intersect with (7,7) to (12,12)
SELECT * FROM spatial_index 
WHERE minX <= 12 AND maxX >= 7 AND minY <= 12 AND maxY >= 7;

-- Find rectangles within a bounding box
SELECT * FROM spatial_index 
WHERE minX >= 0 AND maxX <= 20 AND minY >= 0 AND maxY <= 20;

Custom Functions (Application Level)

-- Example of custom function registration (pseudo-code)
-- In Python with sqlite3:
-- 
-- def calculate_distance(lat1, lon1, lat2, lon2):
--     # Haversine formula implementation
--     return distance
-- 
-- conn.create_function("distance", 4, calculate_distance)
-- 
-- Then use in SQL:
-- SELECT *, distance(lat1, lon1, lat2, lon2) AS dist 
-- FROM locations 
-- WHERE distance(lat1, lon1, 40.7128, -74.0060) < 10;

-- Common custom functions to implement:
-- - String functions (regex, advanced formatting)
-- - Mathematical functions (trigonometry, statistics)
-- - Date/time functions (business days, holidays)
-- - Encryption/hashing functions
-- - Data validation functions

SQLite's simplicity, reliability, and zero-configuration nature make it an excellent choice for embedded applications, mobile development, desktop software, and prototyping. Its ACID compliance, full SQL support, and small footprint provide a robust database solution without the complexity of client-server database systems. Understanding SQLite's unique characteristics and optimization techniques enables developers to build efficient, reliable applications that can scale from simple scripts to complex desktop applications and mobile apps.