SQLite Can Do More Than You Think — Full-Text Search, JSON, Window Functions, and 281TB Databases

SQLite ships with every Python, every iPhone, every Android, every Mac, and every Linux distro. Yet most developers reach for PostgreSQL or MySQL by default.

Here are 5 things SQLite does that might surprise you.

1. Full-Text Search (Built-In)

-- Create a full-text search table
CREATE VIRTUAL TABLE articles USING fts5(title, body);

-- Insert data
INSERT INTO articles VALUES('Web Scraping Guide', 'Learn how to scrape websites using Python...');
INSERT INTO articles VALUES('API Tutorial', 'Build REST APIs with FastAPI and Python...');

-- Search with ranking
SELECT title, rank FROM articles WHERE articles MATCH 'python scraping' ORDER BY rank;

No Elasticsearch. No Algolia. No external service. Full-text search with ranking, snippets, and boolean operators — built into a 1MB file.

2. JSON Support

-- Store and query JSON directly
CREATE TABLE events (data JSON);
INSERT INTO events VALUES('{"user": "john", "action": "click", "page": "/pricing"}');

-- Query nested JSON fields
SELECT json_extract(data, '$.user') as user,
       json_extract(data, '$.action') as action
FROM events
WHERE json_extract(data, '$.page') = '/pricing';

SQLite handles JSON natively since version 3.38. You can index JSON fields too.

3. Window Functions

-- Running average, rank, lead/lag — all supported
SELECT
  date,
  revenue,
  AVG(revenue) OVER (ORDER BY date ROWS 6 PRECEDING) as rolling_7day_avg,
  RANK() OVER (ORDER BY revenue DESC) as revenue_rank
FROM daily_sales;

Every window function PostgreSQL has, SQLite has too.

4. 281 TB Max Database Size

SQLite's theoretical max is 281 terabytes. In practice, people run multi-GB databases without issues.

import sqlite3

# This is your entire database setup
conn = sqlite3.connect('app.db')
conn.execute('CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, email TEXT UNIQUE)')
conn.execute('INSERT INTO users (email) VALUES (?)', ('[e-mail]',))
conn.commit()

No server. No config. No connection pooling. No Docker container. One file.

5. 35% Faster Than the Filesystem

The SQLite team benchmarked it: reading small blobs from SQLite is 35% faster than reading individual files from the filesystem.

That's why:

• Chrome stores your history in SQLite
• iOS stores your messages in SQLite
• Android stores your contacts in SQLite
• macOS stores your photos metadata in SQLite

When SQLite Is NOT Enough

Be honest about limits:

• High write concurrency: >100 concurrent writers = use PostgreSQL
• Network access: Multiple servers need the same DB = use PostgreSQL
• Replication: Need read replicas = use PostgreSQL

For everything else — side projects, prototypes, analytics, mobile apps, single-server web apps — SQLite is not just "enough." It's often better.

The One-Liner That Replaces Your Dev Database

# Instead of docker-compose up postgres...
python3 -c "import sqlite3; sqlite3.connect('dev.db').execute('SELECT 1')"
# Done. Your database is ready.

---

I find simpler alternatives to complex developer tools. Follow for more.

Need data extraction or custom API integrations? Email me at [e-mail]