Database Server

SQLite

Overview

SQLite is the world's most widely deployed lightweight and fast file-based SQL database engine. Optimized for embedded use without requiring a server, it's known as a zero-configuration database that works without any setup. It stores a complete database in a single disk file, managing all data, indexes, and metadata. SQLite is standardly adopted as an embedded database for mobile apps, IoT devices, desktop applications, and web applications, with continued demand due to its lightweight nature and reliability.

Details

SQLite 2025 edition is a mature database engine incorporating the latest features of the 3.47 series, providing complete ACID-compliant transactions, robust SQL dialect support, and rich extension capabilities. It's completely free to use under the Public Domain license and is distributed as a single library file written in C. It supports advanced features such as FTS5 full-text search, R-Tree spatial indexing, JSON1 extension, window functions, CTEs (Common Table Expressions), partial indexes, memory-mapped I/O, WAL mode, concurrent reading, auto VACUUM, and foreign key constraints - providing enterprise-level functionality.

Key Features

• Zero Configuration: No server required, no installation needed, ready to use immediately
• Lightweight: Single file format with library size of only a few hundred KB
• ACID Compliant: Complete transaction processing and consistency guarantees
• Rich SQL Features: SQL-92 standard compliance with SQLite-specific extensions
• Cross-Platform: Runs on all major OS and architectures
• Public Domain: Completely free license for commercial use

Pros and Cons

Pros

• Overwhelming market share and high reliability through 20+ years of development track record in embedded use
• Perfect for local development and small-scale applications with no server setup required
• Extremely simple backup and deployment through single file format
• Low memory usage, comfortable operation even in resource-limited environments
• No legal constraints with Public Domain license allowing free use
• Rich programming language bindings and tool ecosystem

Cons

• Limited concurrent writing, unsuitable for high-load web applications
• No network transparency, constraints on simultaneous access from multiple clients
• Performance degradation when database size becomes large (several TB)
• Limited advanced SQL features like stored procedures and some built-in functions
• No replication capabilities, making high availability configurations difficult
• Not suitable for large-scale data analysis or warehouse use

Reference Pages

• SQLite Official Site
• SQLite Documentation
• SQLite Downloads

Code Examples

Installation and Basic Setup

# Ubuntu/Debian installation
sudo apt update
sudo apt install sqlite3 sqlite3-tools

# CentOS/RHEL installation
sudo dnf install sqlite

# macOS (Homebrew)
brew install sqlite

# Windows installation
# Download from https://www.sqlite.org/download.html

# Create database (automatically created if file doesn't exist)
sqlite3 myapp.db

# Inside SQLite shell
.version
.help
.databases
.quit

# Execute SQL from command line
sqlite3 myapp.db "SELECT sqlite_version();"

# Connect to existing database
sqlite3 /path/to/existing/database.db

Basic Database Operations

-- Database creation (file-based)
-- Automatically created with sqlite3 example.db command

-- Table creation
CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT UNIQUE NOT NULL,
    email TEXT NOT NULL,
    first_name TEXT,
    last_name TEXT,
    age INTEGER CHECK (age >= 0),
    is_active BOOLEAN DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Index creation
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_active ON users(is_active);
CREATE UNIQUE INDEX idx_users_username ON users(username);

-- Data insertion
INSERT INTO users (username, email, first_name, last_name, age) VALUES
('john_doe', '[email protected]', 'John', 'Doe', 30),
('jane_smith', '[email protected]', 'Jane', 'Smith', 28),
('bob_wilson', '[email protected]', 'Bob', 'Wilson', 35);

-- Data retrieval
SELECT * FROM users;
SELECT username, email FROM users WHERE age > 25;
SELECT COUNT(*) FROM users WHERE is_active = 1;

-- Data update
UPDATE users SET age = 31 WHERE username = 'john_doe';
UPDATE users SET updated_at = CURRENT_TIMESTAMP WHERE id = 1;

-- Data deletion
DELETE FROM users WHERE is_active = 0;

-- Check table structure
.schema users
PRAGMA table_info(users);

-- Check entire database structure
.tables
.schema

Advanced SQL Features and SQLite Extensions

-- JSON operations (JSON1 extension)
CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    details JSON
);

INSERT INTO products (name, details) VALUES
('Laptop', '{"price": 1200, "specs": {"cpu": "Intel i7", "memory": "16GB"}, "tags": ["laptop", "work"]}'),
('Smartphone', '{"price": 800, "specs": {"os": "Android", "storage": "128GB"}, "tags": ["mobile", "android"]}');

-- JSON value extraction
SELECT name, json_extract(details, '$.price') as price FROM products;
SELECT name, details ->> '$.specs.cpu' as cpu FROM products;

-- JSON array operations
SELECT name, json_each.value as tag 
FROM products, json_each(products.details, '$.tags');

-- JSON updates
UPDATE products 
SET details = json_set(details, '$.price', 1100, '$.updated_at', datetime('now'))
WHERE name = 'Laptop';

-- Window functions
CREATE TABLE sales (
    id INTEGER PRIMARY KEY,
    product_name TEXT,
    sale_date DATE,
    amount INTEGER
);

INSERT INTO sales (product_name, sale_date, amount) VALUES
('Product A', '2024-01-15', 1000),
('Product B', '2024-01-16', 1500),
('Product A', '2024-01-17', 1200),
('Product C', '2024-01-18', 800);

-- Window function examples
SELECT 
    product_name,
    sale_date,
    amount,
    SUM(amount) OVER (PARTITION BY product_name ORDER BY sale_date) as running_total,
    ROW_NUMBER() OVER (ORDER BY amount DESC) as rank_by_amount,
    LAG(amount, 1) OVER (PARTITION BY product_name ORDER BY sale_date) as prev_amount
FROM sales;

-- CTE (Common Table Expressions)
WITH monthly_sales AS (
    SELECT 
        strftime('%Y-%m', sale_date) as month,
        product_name,
        SUM(amount) as total_amount
    FROM sales
    GROUP BY strftime('%Y-%m', sale_date), product_name
),
top_products AS (
    SELECT month, product_name, total_amount,
           ROW_NUMBER() OVER (PARTITION BY month ORDER BY total_amount DESC) as rank
    FROM monthly_sales
)
SELECT month, product_name, total_amount
FROM top_products
WHERE rank <= 2;

-- Recursive CTE (hierarchical data processing)
CREATE TABLE categories (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    parent_id INTEGER REFERENCES categories(id)
);

INSERT INTO categories (id, name, parent_id) VALUES
(1, 'Electronics', NULL),
(2, 'Computers', 1),
(3, 'Smartphones', 1),
(4, 'Laptops', 2),
(5, 'Desktops', 2);

-- Get complete path hierarchy
WITH RECURSIVE category_path(id, name, path, level) AS (
    -- Base case: root categories
    SELECT id, name, name as path, 0 as level
    FROM categories
    WHERE parent_id IS NULL
    
    UNION ALL
    
    -- Recursive case: child categories
    SELECT c.id, c.name, cp.path || ' > ' || c.name, cp.level + 1
    FROM categories c
    JOIN category_path cp ON c.parent_id = cp.id
)
SELECT id, name, path, level FROM category_path ORDER BY path;

Full-Text Search (FTS5) and Spatial Indexing

-- FTS5 full-text search table creation
CREATE VIRTUAL TABLE articles_fts USING fts5(
    title,
    content,
    author,
    content='articles',  -- Specify source table
    content_rowid='id'   -- Specify ROWID column
);

-- Create source table
CREATE TABLE articles (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    content TEXT NOT NULL,
    author TEXT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Data insertion
INSERT INTO articles (title, content, author) VALUES
('SQLite Basics', 'SQLite is a lightweight and fast database engine. It operates file-based and requires no server setup.', 'John Smith'),
('Database Design', 'Good database design requires balance between normalization and performance. The same principles apply to SQLite.', 'Jane Doe'),
('Web Development with SQLite', 'SQLite is perfect for development environments and prototyping in web development. It''s supported by Django and Flask.', 'Bob Johnson');

-- Sync to FTS table
INSERT INTO articles_fts(rowid, title, content, author)
SELECT id, title, content, author FROM articles;

-- Execute full-text search
SELECT * FROM articles_fts WHERE articles_fts MATCH 'SQLite';
SELECT * FROM articles_fts WHERE articles_fts MATCH 'database AND design';
SELECT * FROM articles_fts WHERE articles_fts MATCH 'title:Web OR content:development';

-- Search result highlighting
SELECT 
    highlight(articles_fts, 0, '<mark>', '</mark>') as highlighted_title,
    snippet(articles_fts, 1, '<b>', '</b>', '...', 32) as content_snippet
FROM articles_fts 
WHERE articles_fts MATCH 'SQLite';

-- R-Tree spatial indexing (geospatial data)
CREATE VIRTUAL TABLE locations USING rtree(
    id,              -- Primary key
    min_x, max_x,    -- X coordinate range
    min_y, max_y     -- Y coordinate range
);

-- Location data insertion
INSERT INTO locations VALUES
(1, 139.7, 139.7, 35.68, 35.68),  -- Tokyo
(2, 135.5, 135.5, 34.69, 34.69),  -- Osaka  
(3, 136.9, 136.9, 35.18, 35.18);  -- Nagoya

-- Range search
SELECT id FROM locations 
WHERE min_x >= 135.0 AND max_x <= 140.0 
AND min_y >= 34.0 AND max_y <= 36.0;

-- Nearest neighbor search
SELECT id FROM locations
ORDER BY (min_x - 139.7) * (min_x - 139.7) + (min_y - 35.68) * (min_y - 35.68)
LIMIT 3;

Transaction Control and PRAGMA Settings

-- Transaction control
BEGIN TRANSACTION;

INSERT INTO users (username, email, first_name, last_name, age) 
VALUES ('test_user', '[email protected]', 'Test', 'User', 25);

UPDATE users SET age = age + 1 WHERE id = 1;

-- Conditional rollback
SELECT changes(); -- Check affected row count
-- If problems: ROLLBACK, if ok: COMMIT
COMMIT;

-- Using savepoints
BEGIN TRANSACTION;
    INSERT INTO users (username, email) VALUES ('user1', '[email protected]');
    SAVEPOINT sp1;
    
    INSERT INTO users (username, email) VALUES ('user2', '[email protected]');
    SAVEPOINT sp2;
    
    -- Cancel only user2 insertion
    ROLLBACK TO SAVEPOINT sp2;
    
    -- Keep user1 and commit
COMMIT;

-- Important PRAGMA settings
-- Enable foreign key constraints
PRAGMA foreign_keys = ON;

-- Enable WAL mode (improved concurrent reading)
PRAGMA journal_mode = WAL;

-- Set synchronous mode (performance vs safety)
PRAGMA synchronous = NORMAL;  -- FULL, NORMAL, OFF

-- Set cache size (MB units)
PRAGMA cache_size = 10000;  -- About 40MB

-- Enable auto VACUUM
PRAGMA auto_vacuum = INCREMENTAL;

-- Set memory-mapped I/O (performance improvement)
PRAGMA mmap_size = 268435456;  -- 256MB

-- Database integrity check
PRAGMA integrity_check;
PRAGMA quick_check;

-- Update table statistics
ANALYZE;

-- Database optimization
VACUUM;
PRAGMA optimize;

-- Check database information
PRAGMA database_list;
PRAGMA table_info(users);
PRAGMA index_list(users);
PRAGMA compile_options;

File Operations and Backup

# Create database dump
sqlite3 myapp.db .dump > backup.sql

# Dump specific table
sqlite3 myapp.db "SELECT sql FROM sqlite_master WHERE name='users';" > users_schema.sql
sqlite3 myapp.db ".dump users" > users_data.sql

# CSV export
sqlite3 -header -csv myapp.db "SELECT * FROM users;" > users.csv

# CSV import
sqlite3 myapp.db << EOF
.mode csv
.import users.csv users
EOF

# Restore from SQL file
sqlite3 new_database.db < backup.sql

# Database file copy (simplest backup)
cp myapp.db myapp_backup_$(date +%Y%m%d).db

# WAL mode backup (possible while online)
sqlite3 myapp.db "VACUUM INTO 'backup_$(date +%Y%m%d).db';"

# Display database statistics
sqlite3 myapp.db << EOF
.schema
.tables
SELECT name, sql FROM sqlite_master WHERE type='table';
SELECT COUNT(*) as table_count FROM sqlite_master WHERE type='table';
PRAGMA page_count;
PRAGMA page_size;
PRAGMA freelist_count;
EOF

Programming Language Integration Examples

# Python SQLite3 library usage example
import sqlite3
from datetime import datetime

# Database connection
conn = sqlite3.connect('example.db')
conn.row_factory = sqlite3.Row  # Dictionary-like access

# Create cursor
cursor = conn.cursor()

# Table creation
cursor.execute('''
CREATE TABLE IF NOT EXISTS logs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    message TEXT NOT NULL,
    level TEXT NOT NULL,
    timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
)
''')

# Data insertion (prepared statement)
cursor.execute(
    "INSERT INTO logs (message, level) VALUES (?, ?)",
    ("Application started", "INFO")
)

# Multiple data insertion
log_data = [
    ("User login", "INFO"),
    ("Processing data", "DEBUG"),
    ("Error occurred", "ERROR")
]
cursor.executemany(
    "INSERT INTO logs (message, level) VALUES (?, ?)",
    log_data
)

# Transaction management
try:
    cursor.execute("BEGIN TRANSACTION")
    cursor.execute("INSERT INTO logs (message, level) VALUES (?, ?)", 
                  ("Important process", "INFO"))
    # Some processing...
    cursor.execute("COMMIT")
except Exception as e:
    cursor.execute("ROLLBACK")
    print(f"Error: {e}")

# Data retrieval
cursor.execute("SELECT * FROM logs WHERE level = ? ORDER BY timestamp DESC", ("INFO",))
rows = cursor.fetchall()

for row in rows:
    print(f"[{row['timestamp']}] {row['level']}: {row['message']}")

# Close connection
conn.close()

// Node.js SQLite3 usage example
const sqlite3 = require('sqlite3').verbose();

// Database connection
const db = new sqlite3.Database('example.db');

// Table creation
db.serialize(() => {
    db.run(`CREATE TABLE IF NOT EXISTS tasks (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        title TEXT NOT NULL,
        completed BOOLEAN DEFAULT 0,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP
    )`);

    // Data insertion
    const stmt = db.prepare("INSERT INTO tasks (title) VALUES (?)");
    ["Learn SQLite", "Build app", "Write tests"].forEach(task => {
        stmt.run(task);
    });
    stmt.finalize();

    // Data retrieval
    db.all("SELECT * FROM tasks ORDER BY created_at", (err, rows) => {
        if (err) {
            console.error(err);
        } else {
            console.log('Task list:');
            rows.forEach(row => {
                console.log(`${row.id}: ${row.title} [${row.completed ? 'Completed' : 'Pending'}]`);
            });
        }
    });
});

// Close connection
db.close();