SQLite Trends 2025-2026: New Features, Vector Search, and Emerging Use Cases

-- Enhanced ALTER TABLE support
-- Now supports adding/removing NOT NULL and CHECK constraints

-- Before 3.52:
ALTER TABLE users ADD COLUMN email TEXT;

-- In 3.52+:
ALTER TABLE users ADD COLUMN email TEXT NOT NULL;
ALTER TABLE products ADD CONSTRAINT positive_price CHECK(price > 0);

-- Remove constraints
ALTER TABLE users ALTER COLUMN email DROP NOT NULL;
ALTER TABLE products DROP CONSTRAINT positive_price;

# New CLI output formatting
sqlite3 database.db "SELECT * FROM users LIMIT 5;"

-- Output now displays in formatted boxes:
+----+-----------+-------------------+
| id | name      | email             |
+----+-----------+-------------------+
| 1  | John Doe  | [email protected]  |
| 2  | Jane Doe  | [email protected]  |
+----+-----------+-------------------+

-- Numeric values now right-justified in tabular mode
CREATE TABLE numbers (
    id INTEGER PRIMARY KEY,
    value REAL,
    computed REAL
);

INSERT INTO numbers VALUES (1, 123.45, 246.90);
INSERT INTO numbers VALUES (2, 9999.99, 19999.98);

SELECT * FROM numbers;

-- Output:
+----+----------+-----------+
| id |    value |  computed |
+----+----------+-----------+
|  1 |   123.45 |    246.90 |
|  2 |  9999.99 |  19999.98 |
+----+----------+-----------+

# Install sqlite-vec (load extension)
.load ./vec0

-- Or build with loadable extension:
-- https://github.com/asg017/sqlite-vec

-- Load extension
SELECT load_extension('./vec0');

-- Create virtual table for vectors
CREATE VIRTUAL TABLE embeddings USING vec0(
    id INTEGER PRIMARY KEY,
    embedding float[1536],  -- 1536-dimensional (OpenAI ada-002)
    content TEXT
);

-- Alternative: store vectors as blob
CREATE VIRTUAL TABLE doc_vectors USING vec0(
    doc_id INTEGER PRIMARY KEY,
    embedding float[384],   -- 384-dimensional (sentence-transformers)
    metadata TEXT
);

-- Basic similarity search (KNN)
SELECT id, distance 
FROM embeddings 
WHERE embedding MATCH '[-0.1, 0.2, ...]' 
LIMIT 10;

-- Using parameter binding (in Python)
cursor.execute("""
    SELECT id, distance 
    FROM embeddings 
    WHERE embedding MATCH ?
    LIMIT 10
""", (query_vector,))

-- Using LIMIT syntax (SQLite 3.41+)
SELECT id, distance 
FROM doc_vectors 
WHERE embedding MATCH ?
LIMIT 10;

-- Distance metrics supported:
-- cosine (default), euclidean, manhattan, hamming

-- Cosine distance (for normalized vectors)
SELECT id, distance 
FROM embeddings 
WHERE embedding MATCH ? 
LIMIT 10;

-- Euclidean distance
SELECT id, distance 
FROM embeddings 
WHERE embedding MATCH ?
ORDER BY distance 
LIMIT 10;

-- Filtered vector search
SELECT id, distance 
FROM embeddings 
WHERE embedding MATCH ? 
AND id > 1000
LIMIT 10;

-- With distance threshold
SELECT id, distance 
FROM embeddings 
WHERE embedding MATCH ?
AND k = 15
AND distance < 0.5;

import sqlite3
import numpy as np

# Connect and enable vec extension
conn = sqlite3.connect('vectors.db')
conn.enable_load_extension(True)
conn.load_extension('./vec0')

# Create table
conn.execute("""
    CREATE VIRTUAL TABLE articles USING vec0(
        id INTEGER PRIMARY KEY,
        embedding float[768],
        title TEXT,
        content TEXT
    )
""")

# Generate embeddings (example using numpy)
def get_embedding(text):
    # In production, use sentence-transformers or OpenAI API
    return np.random.randn(768).astype(np.float32)

# Insert vectors
articles = [
    ("Python Tutorial", "Learn Python programming"),
    ("JavaScript Guide", "Master JavaScript"),
    ("Database Design", "Design efficient databases"),
]

for title, content in articles:
    embedding = get_embedding(content)
    conn.execute(
        "INSERT INTO articles (embedding, title, content) VALUES (?, ?, ?)",
        (embedding.tobytes(), title, content)
    )

conn.commit()

# Search similar articles
query = "programming language"
query_embedding = get_embedding(query)

results = conn.execute("""
    SELECT id, title, distance 
    FROM articles 
    WHERE embedding MATCH ?
    LIMIT 3
""", (query_embedding.tobytes(),)).fetchall()

for row in results:
    print(f"ID: {row[0]}, Title: {row[1]}, Distance: {row[2]:.4f}")

-- Enhanced JSON functions in recent versions

-- JSON path operations
SELECT json_extract('{"a":[1,2,3]}', '$.a[0]');  -- 1

-- JSON with table-valued function
CREATE TABLE configs (id INTEGER, data TEXT);
INSERT INTO configs VALUES (1, '{"theme":"dark","lang":"en"}');

SELECT key, value
FROM json_each(configs.data, '$.settings')
WHERE configs.id = 1;

-- JSON validation
SELECT json_valid('{"key":"value"}');  -- 1
SELECT json_valid('not json');          -- 0

-- Convert JSON array to rows
SELECT value
FROM json_each('[1,2,3,4,5]');

-- With object arrays
SELECT user->>'$.name' as name, user->>'$.age' as age
FROM json_each('[{"name":"John","age":30},{"name":"Jane","age":25}]');

-- Better cost estimation for complex queries

-- Improved index utilization
EXPLAIN QUERY PLAN
SELECT * FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE c.status = 'vip'
ORDER BY o.created_at DESC;

-- Should now use covering indexes more efficiently

-- Not true parallel processing (SQLite is single-threaded)
-- But improved I/O handling and caching

-- Use PRAGMA for better I/O
PRAGMA cache_size = -128000;    -- 128MB cache
PRAGMA mmap_size = 1073741824; -- 1GB mmap
PRAGMA synchronous = NORMAL;   -- Balanced safety
PRAGMA journal_mode = WAL;    -- Better concurrency

-- Better handling of multi-GB databases

-- Incremental vacuum for auto-vacuum databases
PRAGMA auto_vacuum = INCREMENTAL;
PRAGMA incremental_vacuum(10000);

-- Improved index-only scans
CREATE INDEX idx_covering ON table(col1, col2, col3);

-- Use covering index
SELECT col1, col2 FROM table WHERE col1 = 'value';

# Edge deployment with SQLite
# IoT devices, mobile, embedded systems

# Lightweight configuration
import sqlite3

def init_edge_db(db_path):
    """Initialize database for edge device."""
    conn = sqlite3.connect(db_path)
    
    # Minimal settings for embedded
    conn.execute("PRAGMA journal_mode = DELETE")
    conn.execute("PRAGMA synchronous = OFF")
    conn.execute("PRAGMA cache_size = -1000")  # 1MB
    conn.execute("PRAGMA temp_store = MEMORY")
    
    # Create tables
    conn.execute("""
        CREATE TABLE IF NOT EXISTS sensor_data (
            id INTEGER PRIMARY KEY,
            timestamp INTEGER NOT NULL,
            sensor_id TEXT NOT NULL,
            value REAL NOT NULL,
            metadata TEXT
        )
    """)
    
    conn.execute("""
        CREATE INDEX IF NOT EXISTS idx_sensor_time 
        ON sensor_data(sensor_id, timestamp)
    """)
    
    conn.commit()
    return conn

// Rust with SQLite (rusqlite)
// Embedded in desktop applications

use rusqlite::{Connection, Result};

fn main() -> Result<()> {
    let conn = Connection::open("app.db")?;
    
    conn.execute(
        "CREATE TABLE IF NOT EXISTS tasks (
            id INTEGER PRIMARY KEY,
            title TEXT NOT NULL,
            completed INTEGER DEFAULT 0
        )",
        [],
    )?;
    
    conn.execute(
        "INSERT INTO tasks (title) VALUES (?1)",
        ["Learn Rust"],
    )?;
    
    let mut stmt = conn.prepare("SELECT id, title, completed FROM tasks")?;
    let task_iter = stmt.query_map([], |row| {
        Ok((
            row.get::<_, i32>(0)?,
            row.get::<_, String>(1)?,
            row.get::<_, i32>(2)?,
        ))
    })?;
    
    for task in task_iter {
        println!("{:?}", task?);
    }
    
    Ok(())
}

# AWS Lambda with SQLite (ephemeral storage)
# Note: Not for production with concurrent writes

import sqlite3
import os

def lambda_handler(event, context):
    # Use /tmp or /var/task (ephemeral)
    db_path = "/tmp/app.db"
    
    # Initialize on cold start
    conn = sqlite3.connect(db_path)
    conn.execute("PRAGMA journal_mode=WAL")
    
    # Process request
    result = conn.execute(
        "SELECT * FROM items WHERE id = ?",
        (event['id'],)
    ).fetchone()
    
    conn.close()
    
    return {"statusCode": 200, "body": str(result)}

# Better: Use SQLite in container with persistent volume
# Or S3 + SQLite pattern (r2, duckdb, etc.)

# Hybrid approach: SQLite + S3/R2
# Download database snapshot, query, upload changes

import boto3
import sqlite3
import io

class S3SQLite:
    def __init__(self, bucket, key):
        self.s3 = boto3.client('s3')
        self.bucket = bucket
        self.key = key
        self.db_path = '/tmp/cache.db'
    
    def download(self):
        """Download database from S3."""
        self.s3.download_file(self.bucket, self.key, self.db_path)
        return sqlite3.connect(self.db_path)
    
    def upload(self):
        """Upload database to S3."""
        # Vacuum before upload for smaller size
        conn = sqlite3.connect(self.db_path)
        conn.execute("VACUUM")
        conn.close()
        
        self.s3.upload_file(self.db_path, self.bucket, self.key)

# Improved CLI in version 3.52+

# Better table formatting (QRF)
sqlite3 db.sqlite "SELECT * FROM users LIMIT 5;"

# JSON output
sqlite3 db.sqlite "SELECT json_object('users', json_grouparray(
    json_object('name', name, 'email', email)
)) FROM users;"

# Mode options
sqlite3 db.sqlite ".mode line"   # One line per value
sqlite3 db.sqlite ".mode column" # Column alignment
sqlite3 db.sqlite ".mode box"    # Box characters
sqlite3 db.sqlite ".mode json"   # JSON output
sqlite3 db.sqlite ".mode table"  # ASCII table

# Import/Export
sqlite3 db.sqlite ".import data.csv users"
sqlite3 db.sqlite ".export users users.csv"

# Enhanced autocomplete
sqlite3 db.sqlite

-- Tab completion for:
-- - SQL keywords
-- - Table names
-- - Column names

-- Command history
-- Up/Down arrows for command history

-- .tables with pattern
.tables pattern%
-- Lists tables matching pattern

-- Compile-time security options
-- From SQLite 3.41+:

-- Read-only database
PRAGMA query_only = ON;

-- Encrypted database (requires compilation with SEE)
-- Or use SQLCipher

-- Access control via VFS
-- Custom VFS can enforce:
-- - Read-only files
-- - Limited directory access
-- - Audit logging

# Using SQLCipher for encryption

# Install: pip install sqlcipher (requires OpenSSL)

from sqlcipher import sqlcipher

# Load SQLCipher
sqlcipher.load_libsqlcipher()

# Connect with encryption key
conn = sqlcipher.connect('encrypted.db')
conn.set_key('your-secure-key')

# Or use key derivation
conn.set_key('password')
conn.derive_key(4000)  # PBKDF2 iterations

# Operations work normally
conn.execute("CREATE TABLE secrets (id, data)")

# Python: Multiple options

# Standard library (built-in)
import sqlite3

# With type hints
from sqlite3 import Cursor, Connection

# Alternative: aiosqlite (async)
import aiosqlite

async def main():
    async with aiosqlite.connect('app.db') as db:
        await db.execute("CREATE TABLE ...")
        async with db.execute("SELECT * FROM table") as cursor:
            rows = await cursor.fetchall()

# Alternative: SQLAlchemy ORM
from sqlalchemy import create_engine
engine = create_engine('sqlite:///app.db')

// Node.js: better-sqlite3 (synchronous, fast)
const Database = require('better-sqlite3');
const db = new Database('app.db');

const stmt = db.prepare('SELECT * FROM users WHERE id = ?');
const user = stmt.get(userId);

// Or sql.js (WebAssembly)
import initSqlJs from 'sql.js';
const SQL = await initSqlJs();
const db = new SQL.Database();

# SQLAlchemy with SQLite
from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    email = Column(String)

engine = create_engine('sqlite:///app.db')
Base.metadata.create_all(engine)

Session = sessionmaker(bind=engine)
session = Session()

# Add
user = User(name='John', email='[email protected]')
session.add(user)
session.commit()

# Query
user = session.query(User).filter_by(name='John').first()

-- Modern production settings

-- WAL mode for concurrency
PRAGMA journal_mode = WAL;

-- Balanced durability
PRAGMA synchronous = NORMAL;

-- Adequate cache
PRAGMA cache_size = -128000;  -- 128MB

-- Enable auto-vacuum for large databases
PRAGMA auto_vacuum = INCREMENTAL;

-- Memory-mapped I/O (where supported)
PRAGMA mmap_size = 1073741824;  -- 1GB

-- Foreign keys
PRAGMA foreign_keys = ON;

-- Extended error codes
PRAGMA extended_result_codes = ON;

-- Use CTE for complex queries
WITH RECURSIVE hierarchy AS (...)
SELECT * FROM hierarchy;

-- Use window functions for analytics
SELECT *,
    ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) as rank
FROM employees;

-- Use JSON for flexible schema
CREATE TABLE entities (
    id INTEGER PRIMARY KEY,
    data TEXT  -- JSON
);

-- Consider vector search for AI applications
CREATE VIRTUAL TABLE vectors USING vec0(id, embedding float[1536]);