coffeeql 0.3.1

CoffeeQL — Universal query orchestration. One syntax. Any database.

☕ CoffeeQL

One syntax. Any database.

CoffeeQL is a universal query orchestration layer. Write one expressive, chain-based query and run it seamlessly across PostgreSQL, MongoDB, MySQL, and Redis.
Powered by a blazing-fast Rust parse-and-plan engine, executed natively via Python async adapters.

Website • npm • pip (PyPI)

---

✨ Why CoffeeQL?

Most modern applications talk to three or more databases. You end up writing raw SQL for Postgres, pymongo pipelines for MongoDB, and redis-py commands for Redis battling different APIs, error handling paradigms, and mental models.

CoffeeQL abstracts this friction. It provides one unified syntax that compiles down to the exact query required for each underlying database, with a zero-copy Rust engine handling the heavy lifting of parsing and query planning.

📦 Installation

Install the core package via pip or include specific database adapters:

pip install coffeeql
pip install coffeeql[postgres]   # asyncpg
pip install coffeeql[mongodb]    # motor
pip install coffeeql[mysql]      # aiomysql
pip install coffeeql[redis]      # redis-py

# Or install everything at once
pip install coffeeql[all]        

📖 The Syntax

CoffeeQL uses a highly readable, chain-based syntax designed to feel natural.

Syntax	Meaning	Target Database
users[]	Table	PostgreSQL / MySQL
products{}	Collection	MongoDB
session:id{}	Hash Key	Redis
.where(...)	Filter / Condition	All
.give(...)	Select specific fields	All
.sort(field, DESC)	Order by	All
.cup(10)	Limit results	All
.blend(field)	Group by	All
.mix(... ON ...)	Join / Cross-DB Federation	All
.pour({...})	Insert	All
.refill({...})	Update	All
.spill()	Delete	All

---

🚀 Quick Start (FastAPI Example)

CoffeeQL integrates beautifully into asynchronous Python stacks like FastAPI.

from fastapi import FastAPI
from coffeeql import CoffeeQL, PostgresAdapter, MongoAdapter

app = FastAPI()

# Initialize unified orchestration
db = CoffeeQL({
    'users[]':    PostgresAdapter(uri='postgresql://user:pass@localhost/mydb'),
    'products{}': MongoAdapter(uri='mongodb://localhost:27017', db='shop'),
})

@app.on_event('startup')
async def startup():
    await db.connect()

@app.get('/users')
async def get_users():
    result = await db.query('''
        users[]
            .where(plan = "pro", active = true)
            .give(name, email, balance)
            .sort(balance, DESC)
            .cup(20)
    ''').run()
    return result.rows

🐳 Recommended Development Stack

Because CoffeeQL queries across multiple architectures, testing locally is easiest with a unified container stack. You can spin up Postgres, Mongo, and Redis instantly using a standard docker-compose.yml to test your cross-database joins natively before pushing to production via pip.

---

🧠 Advanced Features

Cross-Database Federation (Experimental)

Join a PostgreSQL table with a MongoDB collection in memory, automatically handled by the engine.

@app.get('/dashboard')
async def dashboard():
    result = await db.query('''
        users[]
            .where(active = true)
            .mix(products{} ON users[].id = products{}.user_id)
            .give(users[].name, products[].title, products[].price)
            .sort(products[].price, DESC)
            .cup(10)
    ''').run()
    return result.rows

Resiliency: Timeout, Retry, & Partial States

Built for the real world, where networks drop and databases stall.

# Fail fast if a DB is slow
await db.query('users[].cup(100)').timeout(3000).run()

# Retry on flaky connections
await db.query('products{}.where(active = true)').retry(3).run()

# Accept partial results if a federated node goes down
await db.query('users[].mix(products{} ON ...)').partial().run()

Explain & Validate (Zero Dependencies)

Inspect execution plans without making a single database call.

from coffeeql import explain

plan = explain('''
    users[]
        .where(plan = "pro")
        .mix(products{} ON users[].id = products{}.user_id)
        .cup(10)
''')
print(plan.rendered)

Output:

# CoffeeQL Execution Plan
#   Step 1 → PostgreSQL   SCAN    users[]       WHERE plan='pro'
#   Step 2 → MongoDB      SCAN    products{}
#   Step 3 → Memory       HASH JOIN              ON users[].id = products{}.user_id
#   Step 4 → Memory       LIMIT   10
#
#   Adapters: 2  ·  Strategy: in-memory hash join  ·  Concurrent fetch: yes

---

🏗️ Architecture Layer

The Rust core is the exact same engine that powers the npm package guaranteeing 100% parity across languages for parsing, planning, and explain outputs.

graph TD;
    A[Your CoffeeQL Query] -->|String| B(Rust Engine via PyO3);
    B -->|Parse & Plan - Fast, Zero-Copy| C(Python Execution Layer);
    C -->|Reads Plan| D{Native Async Adapters};
    D -->|asyncpg| E[(PostgreSQL)];
    D -->|motor| F[(MongoDB)];
    D -->|aiomysql| G[(MySQL)];
    D -->|redis-py| H[(Redis)];
    E & F & G & H --> I[Real Rows Returned];

---

📦 What's in v0.3.0

• coffeeql.parse() → Parse and validate any CoffeeQL query.
• coffeeql.is_valid() → Boolean validation.
• coffeeql.explain() → Human-readable execution plan.
• CoffeeQL class → Core client for connect, query, and explain.
• All 4 adapters stable: PostgreSQL, MongoDB, MySQL, Redis.
• .timeout(), .retry(), .partial() resilience modifiers.
• Cross-adapter federation (Experimental).

---

🤝 Contributing & issues

CoffeeQL is an open-source initiative released under the MIT License. We welcome contributions from the community to help expand our adapter ecosystem and refine the engine.

• GitHub: KhushviB/coffeeql
• pip (PyPI): pypi.org/project/coffeeql