Alex Spinov

Posted on Mar 26

SQLite Is Enough for Your Side Project — Full-Text Search, JSON, and WAL Mode Included

#python #tutorial #database #webdev

Every developer tutorial starts with "first, install PostgreSQL" or "set up MongoDB." For most side projects, SQLite is all you need.

Why SQLite

Zero setup — it is a file. No server, no config, no Docker.
Built into Python — import sqlite3 works everywhere.
Handles millions of rows — tested up to 281 TB databases.
ACID compliant — yes, even for concurrent reads.
Production-ready — Airbus, Apple, and every Android phone use it.

Getting Started

import sqlite3

# Create database (or open existing)
conn = sqlite3.connect("app.db")

# Create table
conn.execute("""
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        email TEXT UNIQUE,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )
""")

# Insert
conn.execute("INSERT INTO users (name, email) VALUES (?, ?)", ("Alice", "alice@example.com"))
conn.commit()

# Query
users = conn.execute("SELECT * FROM users").fetchall()

5 Things SQLite Does That Surprise People

1. Full-Text Search

conn.execute("CREATE VIRTUAL TABLE articles USING fts5(title, body)")
conn.execute("INSERT INTO articles VALUES ('Web Scraping Guide', 'Learn how to scrape...')")

# Fast full-text search
results = conn.execute("""
    SELECT * FROM articles WHERE articles MATCH 'scraping'
    ORDER BY rank
""").fetchall()

2. JSON Support

conn.execute("""
    CREATE TABLE events (
        id INTEGER PRIMARY KEY,
        data JSON
    )
""")

conn.execute("INSERT INTO events (data) VALUES (?)", ['{"type": "click", "page": "/home"}'])

# Query JSON fields
conn.execute("""
    SELECT json_extract(data, '$.type') as event_type, COUNT(*)
    FROM events
    GROUP BY event_type
""")

3. Window Functions

conn.execute("""
    SELECT name, score,
        RANK() OVER (ORDER BY score DESC) as rank,
        AVG(score) OVER () as avg_score
    FROM players
""")

4. Common Table Expressions (WITH)

conn.execute("""
    WITH monthly_sales AS (
        SELECT strftime('%Y-%m', date) as month, SUM(amount) as total
        FROM orders
        GROUP BY month
    )
    SELECT month, total,
        total - LAG(total) OVER (ORDER BY month) as growth
    FROM monthly_sales
""")

5. WAL Mode (Concurrent Reads)

conn.execute("PRAGMA journal_mode=WAL")
# Now multiple processes can READ simultaneously
# Only writes are serialized

When NOT to Use SQLite

Multiple servers writing to the same database
More than ~100 concurrent write operations per second
Database larger than the server's disk
You need real-time replication

For everything else — side projects, prototypes, data analysis, CLI tools, mobile apps, embedded systems — SQLite is the right choice.

The Pattern

Every project I build starts the same way:

import sqlite3
from pathlib import Path

DB_PATH = Path("data/app.db")
DB_PATH.parent.mkdir(exist_ok=True)

def get_db():
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row  # Access columns by name
    conn.execute("PRAGMA journal_mode=WAL")
    conn.execute("PRAGMA foreign_keys=ON")
    return conn

No ORM. No migration tool. Just SQL.

📧 spinov001@gmail.com — I build data tools and web scrapers.

DEV Community