Developer's Catalog

Database

SQLite

Overview

SQLite is a self-contained, serverless SQL database engine that requires no server process. It manages the entire database in a single file and is extremely lightweight while supporting many SQL features. It is one of the most widely deployed database engines in the world.

Details

SQLite was developed by Dwayne Richard Hipp in 2000. Despite its "lite" name, it is a full-featured SQL database engine that completely supports ACID properties. Since it requires no server process and is embedded directly into applications, it is widely used in mobile applications, desktop applications, IoT devices, and more.

Key features of SQLite:

• Zero-configuration (no setup required)
• Serverless and self-contained
• Single-file database management
• Complete ACID properties support
• Cross-platform compatibility
• Rich SQL feature set
• Small memory footprint
• High reliability
• Public domain license
• File-level locking

Advantages and Disadvantages

Advantages

• Lightweight: Very small footprint (hundreds of KB)
• Simple: No configuration or maintenance required
• Fast: High performance for small to medium-sized data
• Reliable: Thoroughly tested stability
• Portable: Self-contained in a single file
• Free: Completely free under public domain
• Embeddable: Direct integration into applications

Disadvantages

• Concurrent write limitations: Only one write process at a time
• Large data: Not suitable for very large databases
• Network: Limited network access capabilities
• User management: No built-in user authentication features
• Replication: No built-in replication features

Key Links

• Official Website
• Documentation
• Tutorial
• Command Line Shell

Code Examples

Installation & Setup

# Ubuntu/Debian
sudo apt update
sudo apt install sqlite3

# Red Hat/CentOS
sudo yum install sqlite

# macOS (Homebrew)
brew install sqlite

# Windows (Chocolatey)
choco install sqlite

# Python built-in (standard library)
# Automatically available with Python installation
python3 -c "import sqlite3; print(sqlite3.version)"

Basic Operations (CRUD)

-- Create/connect to database
sqlite3 myapp.db

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

-- Insert data (Create)
INSERT INTO users (name, email) VALUES 
('John Doe', '[email protected]'),
('Jane Smith', '[email protected]');

-- Read data (Read)
SELECT * FROM users;
SELECT * FROM users WHERE name LIKE 'John%';

-- Update data (Update)
UPDATE users SET email = '[email protected]' 
WHERE name = 'John Doe';

-- Delete data (Delete)
DELETE FROM users WHERE id = 1;

-- Check database information
.tables
.schema users
.exit

Data Modeling

-- Foreign key constraints (need to be enabled)
PRAGMA foreign_keys = ON;

CREATE TABLE orders (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER,
    amount REAL NOT NULL,
    order_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE ON UPDATE CASCADE
);

-- Create indexes
CREATE INDEX idx_user_email ON users(email);
CREATE INDEX idx_order_date ON orders(order_date);

-- Create view
CREATE VIEW user_orders AS
SELECT u.name, u.email, o.amount, o.order_date
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

Indexing & Optimization

-- Composite index
CREATE INDEX idx_user_date ON orders(user_id, order_date);

-- Partial index
CREATE INDEX idx_active_users ON users(id) WHERE active = 1;

-- Check execution plan
EXPLAIN QUERY PLAN SELECT * FROM users WHERE email = '[email protected]';

-- Update statistics
ANALYZE;

-- Database integrity check
PRAGMA integrity_check;

-- Database optimization
VACUUM;

Practical Examples

-- Using JSON type (SQLite 3.45+)
CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT,
    attributes JSON
);

INSERT INTO products (name, attributes) VALUES 
('Laptop', json('{"brand": "Dell", "cpu": "Intel i7", "ram": "16GB"}'));

SELECT * FROM products 
WHERE json_extract(attributes, '$.brand') = 'Dell';

-- Trigger
CREATE TRIGGER update_user_timestamp 
AFTER UPDATE ON users
FOR EACH ROW 
BEGIN
    UPDATE users SET updated_at = CURRENT_TIMESTAMP WHERE id = NEW.id;
END;

-- Full-text search (FTS5)
CREATE VIRTUAL TABLE articles_fts USING fts5(title, content);

INSERT INTO articles_fts VALUES ('SQLite Tutorial', 'SQLite is a lightweight database');

SELECT * FROM articles_fts WHERE articles_fts MATCH 'SQLite';

Best Practices

-- Transaction management
BEGIN TRANSACTION;
INSERT INTO users (name, email) VALUES ('New User', '[email protected]');
INSERT INTO orders (user_id, amount) VALUES (last_insert_rowid(), 1000.00);
COMMIT;

-- WAL mode setting (improved concurrent reads)
PRAGMA journal_mode = WAL;

-- Performance settings
PRAGMA synchronous = NORMAL;
PRAGMA cache_size = 10000;
PRAGMA temp_store = MEMORY;

-- Check database configuration
.dbconfig

-- Create backup
.backup backup.db
-- or
VACUUM INTO 'backup.db';

-- Optimization settings example
PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA cache_size = -1000;  -- 1MB
PRAGMA foreign_keys = ON;
PRAGMA recursive_triggers = ON;

Python Usage Example

import sqlite3

# Database connection
conn = sqlite3.connect('myapp.db')
cursor = conn.cursor()

# Create table
cursor.execute('''
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        email TEXT UNIQUE
    )
''')

# Insert data
cursor.execute('INSERT INTO users (name, email) VALUES (?, ?)', 
               ('John Doe', '[email protected]'))

# Read data
cursor.execute('SELECT * FROM users WHERE name = ?', ('John Doe',))
user = cursor.fetchone()
print(user)

# Commit changes
conn.commit()

# Close connection
conn.close()