Introduction

Welcome to the official documentation for RibbitXDB, the production-ready enterprise database for Python with 100% SQL support, connection pooling, and advanced features.

RibbitXDB combines the simplicity of embedded databases with enterprise capabilities including subqueries, CTEs, window functions, batch operations, and automated backups.

Key Features

• 100% SQL Support: Subqueries, CTEs, window functions, views, triggers
• Production Features: Connection pooling, batch operations, backup/restore
• Enterprise Security: TLS 1.3, BLAKE2, AES-256 encryption, RBAC
• Client-Server: Multi-threaded TCP server with 100+ concurrent connections
• Advanced SQL: JOINs, aggregates, GROUP BY, HAVING, window functions
• High Performance: 100,000+ queries/sec, 70%+ cache hit rate
• Replication Ready: WAL-based master-slave replication
• Secure by Design: BLAKE2b hashing for data integrity
• Lightweight Storage: LZMA compression reduces size by 70%
• Pure Python: Zero external dependencies, works everywhere

Installation

RibbitXDB is available on PyPI and can be installed using pip:

pip install ribbitxdb

Requirements: Python 3.8+

Quick Start

RibbitXDB follows the Python DB-API 2.0 standard (like SQLite3), making it incredibly easy to use.

import ribbitxdb

# Connect to database (creates it if it doesn't exist)
conn = ribbitxdb.connect('myapp.rbx')
cursor = conn.cursor()

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

# Insert data
cursor.execute("INSERT INTO users VALUES (1, 'Alice', 'alice@ribbitx.com')")
conn.commit()

# Query data
cursor.execute("SELECT * FROM users")
print(cursor.fetchall())
# Output: [(1, 'Alice', 'alice@ribbitx.com')]

conn.close()

Architecture

RibbitXDB is built on a sophisticated page-based storage engine designed for reliability and performance.

• Page Structure: 4KB fixed-size pages with header tracking free space and type.
• Storage Engine: Manages page allocation, reading/writing, and caching (default 2000 pages).
• B-Tree Indexing: Uses Order-128 B-Trees for efficient primary key lookups and range scans.

Security (BLAKE2)

RibbitXDB takes unique approach to data integrity by embedding cryptographic verification into the storage layer.

When a row is inserted, a BLAKE2b hash (32-byte digest) is computed from the binary data. This hash is stored alongside the record. Upon retrieval, the engine re-computes the hash and verifies it against the stored signature, ensuring that data hasn't been modified on disk by transactions, migrations, and more.

Python Version Support

Supported Versions: Python 3.8, 3.9, 3.10, 3.11, 3.12, 3.13

RibbitXDB is designed to work across all modern Python versions. Starting with v1.1.6.4, we've ensured full compatibility with Python 3.8+ through proper type hint handling using from __future__ import annotations.

Compression (LZMA)

RibbitXDB integrates LZMA (Lempel-Ziv-Markov chain algorithm) compression transparency into the paging system.

Connection

The connect() function is the entry point to the database.

ribbitxdb.connect(database, **kwargs)

Opens a connection to the SQLite database file database. Returns a Connection object.

• database: Path to the database file.
• compression_level: Integer 0-9 (default 6).

Cursor & Queries

Cursors allow you to execute SQL statements and fetch results.

execute(sql, parameters=())

Executes a SQL statement. Parameters can be provided as a tuple.

fetchone() / fetchall()

Retrieves the next row or all remaining rows of a query result.

Connection Pooling

Production-grade connection pooling for high-concurrency applications.

Basic Usage

from ribbitxdb import ConnectionPool

pool = ConnectionPool(
    database='app.rbx',
    min_connections=5,
    max_connections=20,
    timeout=30
)

with pool.get_connection() as conn:
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM users")
    print(cursor.fetchall())

Pool Statistics

stats = pool.get_stats()
print(f"Active: {stats['active_connections']}")
print(f"Pool size: {stats['pool_size']}")

Batch Operations

10x faster bulk inserts, updates, and deletes with automatic chunking.

Batch Insert

from ribbitxdb import BatchOperations

conn = ribbitxdb.connect('app.rbx')
batch = BatchOperations(conn)

batch.batch_insert('users', [
    {'name': 'Alice', 'age': 30},
    {'name': 'Bob', 'age': 25},
    {'name': 'Charlie', 'age': 35}
])

Bulk Upsert

stats = batch.bulk_upsert('users', data, key_columns=['id'])
print(f"Inserted: {stats['inserted']}, Updated: {stats['updated']}")

Backup & Restore

Automated backups with compression, encryption, and metadata tracking.

Create Backup

from ribbitxdb import DatabaseBackup

backup = DatabaseBackup('app.rbx')
backup_path = backup.create_backup(
    compress=True,
    encrypt=True,
    encryption_key=b'your-32-byte-key-here'
)

Restore from Backup

from ribbitxdb import DatabaseRestore

restore = DatabaseRestore('app.rbx')
restore.restore_from_backup(
    backup_path,
    decryption_key=b'your-32-byte-key-here',
    verify=True
)

Subqueries

Full support for scalar, correlated, EXISTS, and IN subqueries.

Scalar Subquery

cursor.execute("""
    SELECT name, 
           (SELECT COUNT(*) FROM orders WHERE user_id = users.id) as order_count
    FROM users
""")

IN Subquery

cursor.execute("""
    SELECT * FROM users
    WHERE id IN (SELECT user_id FROM orders WHERE total > 1000)
""")

CTEs (Common Table Expressions)

WITH clause support for complex queries and recursive operations.

cursor.execute("""
    WITH high_value_users AS (
        SELECT user_id, SUM(total) as lifetime_value
        FROM orders
        GROUP BY user_id
        HAVING SUM(total) > 10000
    )
    SELECT u.name, hvu.lifetime_value
    FROM users u
    JOIN high_value_users hvu ON u.id = hvu.user_id
    ORDER BY hvu.lifetime_value DESC
""")

Window Functions

Advanced analytics with ROW_NUMBER, RANK, LAG, LEAD, and more.

ROW_NUMBER & RANK

cursor.execute("""
    SELECT 
        name,
        salary,
        ROW_NUMBER() OVER (ORDER BY salary DESC) as row_num,
        RANK() OVER (ORDER BY salary DESC) as rank
    FROM employees
""")

LAG & LEAD

cursor.execute("""
    SELECT 
        date,
        revenue,
        LAG(revenue) OVER (ORDER BY date) as prev_revenue,
        LEAD(revenue) OVER (ORDER BY date) as next_revenue
    FROM sales
""")

Views

Virtual tables based on the result-set of an SQL statement. Views are persistent and can be queried like regular tables.

cursor.execute("""
    CREATE VIEW high_spenders AS
    SELECT * FROM users WHERE total_spend > 1000
""")

cursor.execute("SELECT * FROM high_spenders")

Unions

Combine result sets of two or more SELECT statements using UNION (distinct) or UNION ALL.

cursor.execute("""
    SELECT name FROM employees
    UNION
    SELECT name FROM contractors
""")

Alter Table

Modify existing table structures dynamically. Supports renaming tables and adding new columns with default values.

# Rename table
cursor.execute("ALTER TABLE old_users RENAME TO users")

# Add new column
cursor.execute("ALTER TABLE users ADD COLUMN age INTEGER DEFAULT 18")

Client-Server Architecture

RibbitXDB v1.1.6 provides full client-server capabilities with TLS encryption and multi-user support.

Starting the Server

ribbitxdb-server \
  --host 0.0.0.0 \
  --port 5432 \
  --database /var/lib/ribbitxdb/main.rbx \
  --tls-cert server.crt \
  --tls-key server.key

Connecting from Client

import ribbitxdb

# Connect to remote server
conn = ribbitxdb.connect_network(
    host='db.example.com',
    port=5432,
    user='alice',
    password='secure_password',
    tls_verify=True
)

cursor = conn.cursor()
cursor.execute("SELECT * FROM users WHERE age > 25")
print(cursor.fetchall())
conn.close()

Authentication & Authorization

RibbitXDB includes a complete user management system with BLAKE2 password hashing and role-based access control.

User Management

from ribbitxdb.auth import UserManager

# Create user manager
um = UserManager('server.rbx')

# Create user
um.create_user('alice', 'password123')

# Grant permissions
um.grant_permission('alice', 'main', 'users', 'SELECT')
um.grant_permission('alice', 'main', 'users', 'INSERT')

# Check permission
can_select = um.check_permission('alice', 'main', 'users', 'SELECT')
print(f"Alice can SELECT: {can_select}")

Permission Types

• SELECT: Read data from tables
• INSERT: Add new records
• UPDATE: Modify existing records
• DELETE: Remove records
• CREATE: Create new tables
• DROP: Drop tables

Replication

Write-Ahead Log (WAL) support enables master-slave replication for high availability.

WAL Usage

from ribbitxdb.replication import WriteAheadLog

# Initialize WAL
wal = WriteAheadLog('replication.wal')

# Append operations
lsn = wal.append("INSERT INTO users VALUES (1, 'Alice')")

# Read from specific LSN
entries = wal.read_from(lsn)

# Get current LSN
current_lsn = wal.get_current_lsn()

Advanced SQL

RibbitXDB supports enterprise-grade SQL features including JOINs, aggregates, and complex queries.

JOINs

cursor.execute("""
    SELECT users.name, orders.total
    FROM users
    INNER JOIN orders ON users.id = orders.user_id
    WHERE orders.total > 100
""")

Aggregates with GROUP BY

cursor.execute("""
    SELECT category, COUNT(*) as count, AVG(price) as avg_price
    FROM products
    GROUP BY category
    HAVING COUNT(*) > 5
    ORDER BY avg_price DESC
""")

Advanced Filtering

cursor.execute("""
    SELECT * FROM users
    WHERE name LIKE 'A%'
      AND age IN (25, 30, 35)
      AND salary BETWEEN 50000 AND 100000
""")

Pagination

cursor.execute("""
    SELECT * FROM products
    ORDER BY price DESC
    LIMIT 20 OFFSET 40
""")

Transactions

RibbitXDB supports ACID transactions with commit and rollback functionality.

try:
    with ribbitxdb.connect('bank.rbx') as conn:
        cursor = conn.cursor()
        cursor.execute("UPDATE accounts SET balance = balance - 100 WHERE id = 1")
        cursor.execute("UPDATE accounts SET balance = balance + 100 WHERE id = 2")
        # Implicit commit on exit
except Exception:
    # Implicit rollback on error
    print("Transaction cancelled")

Benchmarks

Performance metrics for RibbitXDB v1.1.6 (10,000 rows):

Write Performance

25,000+ inserts/sec with compression and hashing enabled.

Read Performance

100,000+ selects/sec with LRU caching (70%+ cache hit rate).

Network Performance

<10% overhead for client-server vs embedded mode.

Comparison with SQLite

Storage Efficiency

Reduces storage footprint by approximately 70% with LZMA compression.

Examples

Storing Binary Data (BLOBs)

conn = ribbitxdb.connect('images.rbx', compression_level=9)
cursor = conn.cursor()
cursor.execute("CREATE TABLE images (id INTEGER PRIMARY KEY, data BLOB)")

with open('photo.jpg', 'rb') as f:
    blob = f.read()

cursor.execute("INSERT INTO images VALUES (1, ?)", (blob,))
conn.commit()

Changelog

v1.1.6.5 Latest - Production/Stable ✅

• Documentation: Added Contributors section acknowledging community contributions.

v1.1.6.4

• Python 3.8 Compatibility: Fixed type hint incompatibility using from __future__ import annotations.
• Metadata: Clarified supported Python versions (3.8-3.13) in package metadata.

v1.1.6

• Critical Fix: Resolved `DataType` serialization bug causing validation failures on INSERT.
• Improved: Enhanced error messages for constraint violations with specific column details.

v1.1.5

• New SQL Features: Views, Alter Table, Unions, Constraints
• Views: full support for CREATE/DROP VIEW and recursive selection
• Schema Management: ALTER TABLE RENAME and ADD COLUMN with schema evolution
• Union: UNION and UNION ALL operators supported
• Constraints: Persistence for CHECK and FOREIGN KEY constraints
• 100% SQL Support: Subqueries, CTEs, window functions
• Connection Pooling with automatic management
• Batch Operations (10x faster bulk inserts)
• Backup & Restore with compression and encryption

v1.0.0

• Initial Release
• Core storage engine with LZMA compression.
• BLAKE2b hashing for row-level security.
• B-Tree indexing implementation.
• Basic SQL support (CREATE, INSERT, SELECT, UPDATE, DELETE).