sql5 2.4.0

SQLite-compatible database with CJK FTS5 support

sql5

SQLite-compatible database with native CJK FTS5 support. Built with Rust.

v2.0 - Client-Server Architecture

sql5 v2.0 consists of:

• Python package (sql5 on PyPI): Pure Python client
• Rust binary: Server process providing all SQL functionality

The Python client communicates with the Rust server via JSON over stdin/stdout.

Installation

pip install sql5

Python API

import sql5

# In-memory database
db = sql5.connect()

# Or open a file
db = sql5.connect("mydb.db")

# Or open a file
# db = sql5.connect("mydb.db")

# Execute SQL
db.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)")
db.execute("INSERT INTO users VALUES (1, 'Alice', 30)")
db.execute("INSERT INTO users VALUES (2, 'Bob', 25)")
db.execute("INSERT INTO users VALUES (3, 'Charlie', 35)")

# Query with parameters
db.execute("INSERT INTO users VALUES (?, ?, ?)", (4, "David", 28))

# Fetch results
cursor = db.execute("SELECT * FROM users WHERE age > ?", (25,))
for row in cursor:
    print(row)
# (1, 'Alice', 30)
# (2, 'Bob', 25)
# (3, 'Charlie', 35)
# (4, 'David', 28)

# Fetch as list
cursor = db.execute("SELECT name, age FROM users ORDER BY age")
rows = cursor.fetchall()
print(rows)
# [('Bob', 25), ('David', 28), ('Alice', 30), ('Charlie', 35)]

# Fetch one
cursor = db.execute("SELECT * FROM users WHERE id = ?", (1,))
row = cursor.fetchone()
print(row)
# (1, 'Alice', 30)

# Transactions
db.execute("BEGIN")
db.execute("INSERT INTO users VALUES (5, 'Eve', 40)")
db.execute("COMMIT")

# Or rollback
db.execute("BEGIN")
db.execute("INSERT INTO users VALUES (6, 'Frank', 45)")
db.execute("ROLLBACK")

# Full-text search (FTS5)
db.execute("CREATE VIRTUAL TABLE articles USING fts5(title, body)")
db.execute("INSERT INTO articles VALUES ('Hello World', 'The quick brown fox')")
db.execute("INSERT INTO articles VALUES ('Rust Guide', 'Memory safety without GC')")
db.execute("INSERT INTO articles VALUES ('中文測試', '繁體中文全文檢索')")

cursor = db.execute("SELECT * FROM articles WHERE articles MATCH ?", ("rust",))
print(cursor.fetchall())
# [('Rust Guide', 'Memory safety without GC')]

cursor = db.execute("SELECT * FROM articles WHERE articles MATCH ?", ("中文",))
print(cursor.fetchall())
# [('中文測試', '繁體中文全文檢索')]

# Close database
db.close()

CLI Usage

# Run the REPL
sql5

# Open a database file
sql5 /path/to/database.db

# Execute single query
echo "SELECT 1 + 1;" | sql5

Features

• Full SQL support (SELECT, INSERT, UPDATE, DELETE, CREATE, DROP)
• ACID transactions (BEGIN, COMMIT, ROLLBACK)
• WAL mode
• Foreign keys
• Views
• Triggers
• Full-text search (FTS5) with CJK bigram tokenization
• Multiple database attachment (ATTACH DATABASE)
• Window functions (ROW_NUMBER, RANK, LAG, LEAD, etc.)
• String functions (UPPER, LOWER, SUBSTR, REPLACE, etc.)
• Date/time functions (DATE, TIME, DATETIME, STRFTIME)
• JSON functions (JSON, JSON_EXTRACT, JSON_SET, etc.)

Requirements

• Python 3.8+

Development

To use a local Rust binary instead of downloading from GitHub:

export SQL5_BINARY=/path/to/local/sql5
python -c "import sql5; print(sql5.__version__)"

License

MIT