Feature	GraphQL	CoffeeQL
Defines API contract	✓	,
Queries any database	Resolver (you write)	✓ built in
Cross DB join	Impossible	✓ .mix()
Geo / AI / Time	Manual	✓ .near() .like() .last()
Works as GraphQL resolver	,	✓ they compose

Task	With CoffeeQL
Query PostgreSQL + MongoDB	users[].mix(products{})
Geo proximity search	.where(location.near(28.6, 77.2, 5km))
AI vector similarity	.where(embed.like("query").threshold(0.85))
Switch database backend	Change one adapter config line
Multi backend apps	Unified mental model, same chain everywhere
AI generated queries	One readable grammar: LLMs generate naturally

// getting started

Query everything.
One sip at a time. ☕

CoffeeQL is the query language for your whole stack. SQL tables, document stores, key value caches. One clean chainable syntax, any backend.

users[]

Structured → SQL tables, fixed schema

products{}

Unstructured → documents, flexible fields

session:id{}

Key value → Redis hashes, namespaced

.mix(orders[] ON ...)

Cross type JOIN → SQL + Mongo, one query

COFFEEQL users[].where().give().sort().cup()

-- structured → PostgreSQL, MySQL, SQLite
users[]

.where(city = "Delhi", plan = "pro", active = true)
.give(name, email, balance)
.sort(balance, DESC)
.cup(10)

-- unstructured → MongoDB
products{}

.where(specs.watts > 500, tags.has("espresso"))
.give(name, price, specs.watts)

-- key value → Redis
session:sess_001{}

.give(user_id, plan, city, device)

How it compares

Language	SQL	Docs	KV	Geo	AI	Cross JOIN
SQL	✓	:	:	addon	addon	:
MQL	:	✓	:	index	Atlas	:
GraphQL	resolver	resolver	:	:	:	:
CoffeeQL	✓	✓	✓	✓	✓	✓

Start hereQuick Start →

LearnCollections: [] vs {} →

// getting started

Quick Start

Three steps. Five minutes. Real queries running.

Install

npm, yarn, or pnpm

bash
npm install coffeeql

Parse and validate

Works in Node.js, Deno, Bun, and the browser

javascript
const { coffeeql } = require('coffeeql')

const result = coffeeql(`
  users[]
    .where(age > 18)
    .give(name, email)
`)
result.success  // → true
result.plan     // → physical execution plan
result.error    // → null

Connect your database

Pick your adapter: install only what you use

bash
npm install coffeeql pg          # PostgreSQL
npm install coffeeql mongodb     # MongoDB
npm install coffeeql mysql2      # MySQL
npm install coffeeql ioredis     # Redis

Query your database

One import. Real data returned.

javascript
const { CoffeeQL } = require('coffeeql')
const { PostgresAdapter } = require('coffeeql/adapters/postgres')

const db = new CoffeeQL({
  adapters: {
    'users[]': new PostgresAdapter({ connectionString: 'postgresql://...' })
  }
})

async function run() {
  await db.connect()
  const users = await db.query(`
    users[]
      .where(plan = "pro")
      .give(name, email)
      .cup(10)
  `)
  console.log(users)
  await db.disconnect()
}
run()

Or validate only (WASM, no DB)

Works in browser, Node, Deno, Bun

javascript
const { isValid } = require('coffeeql')

const isFine = isValid(`
  users[]
    .where(plan = "pro")
`)
console.log(isFine) // → true

CoffeeQL parses and plans your query. Execution happens on your database adapter. It translates to native SQL, MQL, or Redis commands automatically.

NextCollections →

Jump toWriting Queries →

// getting started

Installation

One package. No native deps. Works everywhere JS runs.

bash
npm install coffeeql
yarn add coffeeql
pnpm add coffeeql

DB adapter dependencies

Install only what you use: each DB driver is a peer dependency:

bash
npm install pg          # PostgreSQL
npm install mysql2      # MySQL
npm install mongodb     # MongoDB
npm install ioredis     # Redis

Runtime support

Runtime	Version	Notes
Node.js	>= 18	Recommended: full adapter support
Deno	>= 1.30	WASM parse mode via npm: specifier
Bun	>= 1.0	Native WASM support
Browser	Modern	WASM parse/validate mode only

Python (PyPI)

CoffeeQL is also available as a Python package — same Rust engine via PyO3. Parse, validate, and explain queries with zero extra dependencies. Native async adapters available as extras.

bash
pip install coffeeql

Python adapter dependencies

Install only what you use:

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

Python quick usage

python
from coffeeql import parse, is_valid, explain

# Validate
is_valid('users[].where(age > 18).cup(10)')  # → True

# Explain — no DB call
plan = explain('users[].where(plan = "pro").cup(10)')
print(plan.rendered)

Python runtime support

Runtime	Version	Notes
CPython	>= 3.9	Recommended
PyPy	>= 3.9	Experimental
FastAPI	Any	Full async adapter support
Django / Flask	Any	Use with asyncio.run()

// the language

Collections

Two brackets. That's the whole trick. [] for structured. {} for unstructured. Everything else is identical.

Structured → `users[]`

Fixed schema. SQL databases. Use [] for relational, well typed data.

QUERY users[].where().give().sort().cup()

users[]

.where(plan = "pro", active = true)
.give(name, email, balance)
.sort(balance, DESC)
.cup(10)

Unstructured → `products{}`

Flexible schema. Document stores. Each record can have different fields. Nested field access works natively.

QUERY products{}.where(nested.field).give()

products{}

.where(specs.watts > 500, specs.has_grinder = true)
.give(name, price, specs.watts, specs.has_grinder)

events{}

.where(location.city = "Delhi", type = "sale") .give(*)

Key Value → `session:id{}`

Redis hashes. Namespace:id pattern, CoffeeQL translates to HGETALL, HMGET, HSET automatically.

HGETALL session:sess_001{}.give(*)

-- HGETALL session:sess_001
session:sess_001{}

.give(*)

-- HMGET cart:usr_003 items total coupon
cart:usr_003{}

.give(items, total, coupon)

-- HGETALL feature_flags
feature_flags{}

.give(*)

The brackets are the only difference. A query on users[] and a query on products{} look nearly identical, just swap the brackets and you're done.

NextQuerying →

// the language

Querying

Chain methods left to right. Each one does exactly what it sounds like.

.where(): filter

Comma = AND. Pipe | = OR. Nest freely.

FILTER.where(conditions)

-- AND (comma)
users[]

.where(city = "Delhi", plan = "pro", active = true)

-- OR (pipe)
users[]

.where(plan = "pro" | plan = "team" | plan = "enterprise")

-- Range
users[]

.where(age >= 18, age <= 35)

-- Nested fields (unstructured)
products{}

.where(specs.watts > 500, specs.has_grinder = true)

-- EXISTS / NOT
products{}

.where(brand EXISTS)

users[]

.where(!active)

.give() / .sort() / .cup()

SELECT.give(fields).sort(field, DIR).cup(n)

users[]

.give(name, email, balance) -- specific fields

events{}

.give(*) -- all fields

products{}

.give(name, specs.watts) -- nested

orders[]

.where(status = "completed")
.sort(total, DESC) ← ASC or DESC
.cup(20) ← first 20 results

Full example

QUERYTop loyal pro users in metro cities

users[]

.where( city = "Delhi" | city = "Mumbai" | city = "Bangalore", plan = "pro" | plan = "team", loyalty_pts > 5000, active = true )
.give(name, city, plan, balance, loyalty_pts)
.sort(loyalty_pts, DESC)
.cup(20)

NextMutations →

Jump toJoins →

// the language

Mutations

Insert with .pour(). Update with .refill(). Delete with .spill(). Yes, the names are intentional.

.pour() → insert

INSERT.pour({ key: value, ... })

users[]

.pour({

name: "Rahul Sharma",
email: "rahul@example.com",
city: "Delhi",
plan: "pro",
balance: 1500.00,
active: true

})

-- Unstructured: any shape
events{}

.pour({

type: "workshop"
, seats: 20
, tags: ["coffee"]

})

-- Redis hash → HSET
session:sess_new{}

.pour({

user_id: "usr_001",
plan: "pro"

})

.refill() → update

UPDATE.where().refill({ key: value })

users[]

.where(email = "rahul@example.com")
.refill({

plan: "team",
balance: 5000.00

})

cart:usr_001{}

.refill({

total: "9497", coupon: "SAVE20"

})

.spill() → delete

DELETE.where().spill()

users[]

.where(email = "test@example.com")
.spill()

session:old{}

.spill() ← DEL session:old

.spill() without .where() deletes everything. Filter first, always.

// language reference

Aggregation

Group records with .blend(), then use aggregate functions inside .give() to compute totals, averages, and more.

How aggregation works

Aggregation is a two step process: first .blend(field) groups your records by a field, then .give() applies functions to each group.

BLEND.blend(groupByField).give(field, FUNC() as alias)

-- Count orders by status
orders[]

.blend(status)
.give(status, COUNT() as total_orders)
.sort(total_orders, DESC)

-- Revenue, avg order, biggest order: all in one query
orders[]

.where(status = "completed")
.blend(city)
.give(

city,
COUNT() as total_orders,
SUM(total) as revenue,
AVG(total) as avg_order_value,
MAX(total) as biggest_order,
MIN(total) as smallest_order

)
.sort(revenue, DESC)

-- User plan distribution
users[]

.blend(plan)
.give(plan, COUNT() as user_count)

-- Works on MongoDB too
products{}

.blend(category)
.give(category, COUNT() as count, AVG(price) as avg_price)
.sort(count, DESC)

All aggregate functions

Function	Returns	Field type	Example
COUNT()	Integer	Any	COUNT() as total
SUM(field)	Number	Numeric	SUM(total) as revenue
AVG(field)	Float	Numeric	AVG(rating) as avg_rating
MAX(field)	Any	Comparable	MAX(balance) as highest
MIN(field)	Any	Comparable	MIN(price) as cheapest

Aggregates must be used inside .give() after .blend(). Using them without .blend() is invalid. Always give them an alias with as.

// the language

Joins → .mix()

Join any two collections, including a SQL table with a MongoDB document. That's the part that doesn't exist anywhere else.

SQL + SQL

JOINusers[].mix(orders[] ON a = b)

users[]

.mix(orders[] ON users[].id = orders[].user_id)
.where(orders[].status = "completed", orders[].total > 5000)
.give(users[].name, orders[].total)
.sort(orders[].total, DESC)
.cup(15)

MongoDB doc + SQL table (cross type)

This is unique to CoffeeQL joining a MongoDB collection with a PostgreSQL table in a single query. No other language does this.

CROSS JOINproducts{}.mix(orders[] ON a = b)

products{}

.mix(orders[] ON products{}.id = orders[].product_id)
.give(products{}.name, orders[].quantity)
.sort(orders[].quantity, DESC)

// the language

Geo, AI & Time

Three things that normally need plugins or separate systems. In CoffeeQL they're just methods on .where().

Geo → .near(lat, lon, distance)

GEO.where(field.near(lat, lon, dist))

cafes[]

.where(location.near(28.6139, 77.2090, 5km))
.give(name, city, rating)
.sort(rating, DESC)
.cup(10)

-- distance units: m, km, mi

AI similarity → .like().threshold()

VECTOR.where(embed.like("query").threshold(n))

products{}

.where(embed.like("dark roast espresso machine").threshold(0.85))
.give(name, price, brand)
.cup(5)

Array contains → .has()

ARRAY.where(field.has(value))

products{}

.where(tags.has("coffee"))
.give(name, price, tags)

Time window → .last()

TIME.where(field.last(Nd))

events{}

.where(created_at.last(7d))
.give(title, type)

logs{}

.where(ts.last(1h), level = "error")
.give(message)

-- units: s m h d w mo y

// the language

Transactions → shot{}

Wrap operations in shot{} for atomicity. All succeed or all rollback. Named after an espresso shot.

ATOMICshot { op1 / op2 / op3 }

shot {

-- debit sender
users[]

.where(id = "usr_001")
.refill({ balance: balance - 500 })

-- credit receiver
users[]

.where(id = "usr_002")
.refill({ balance: balance + 500 })

-- log the transfer
transactions{}

.pour({ from: "usr_001", to: "usr_002", amount: 500, at: now() })

}

If anything inside shot{} fails, all changes rollback. Good things come in short, precise bursts.

// language reference

Schema: grind & menu

Define your collections with grind. Inspect them with menu(). CoffeeQL generates the correct DDL for your backend automatically.

grind: create structured collection

GRINDgrind name[] ( fields ) [FLEX]

-- Full schema with all options
grind users[] (

id UUID PRIMARY, ← unique key, auto indexed
name TEXT NOT NULL, ← required, no null
email TEXT UNIQUE NOT NULL, ← unique across table + required
age INT,
city TEXT,
plan TEXT,
balance FLOAT,
active BOOL,
joined_at DATETIME,
location GEOPOINT, ← for .near() queries
embedding VECTOR ← for .like() AI queries

)

-- FLEX: fixed core fields + accept any extras
grind orders[] (

id UUID PRIMARY,
user_id TEXT NOT NULL,
total FLOAT NOT NULL

) FLEX

-- Unstructured: no schema at all
grind events{}
grind products{}
grind logs{}

Constraints

Constraint	Meaning	Example
PRIMARY	Primary key: unique, not null, indexed	id UUID PRIMARY
NOT NULL	Field must always have a value	name TEXT NOT NULL
UNIQUE	No two records can share this value	email TEXT UNIQUE

Data types

Type	Description	PostgreSQL	MySQL	SQLite
UUID	Unique identifier	UUID	VARCHAR(36)	TEXT
TEXT	Variable string	TEXT	TEXT	TEXT
INT	Integer	INTEGER	INT	INTEGER
FLOAT	Decimal number	NUMERIC	DECIMAL(10,2)	REAL
BOOL	true / false	BOOLEAN	TINYINT(1)	INTEGER
DATETIME	Timestamp with tz	TIMESTAMPTZ	DATETIME	INTEGER
GEOPOINT	Lat/lon coordinate	POINT	POINT	TEXT
VECTOR	AI embedding array	vector[]	JSON	TEXT

menu(): inspect collections

MENUmenu() / menu(collection)

menu() ← list all collections in current database
menu(users[]) ← show full schema for users
menu(orders[]) ← show full schema for orders

// adapters

PostgreSQL

Native SQL translation. Drop CoffeeQL on any Postgres 14+: Supabase, Neon, RDS, local. No schema changes. No migrations. Zero overhead.

Why CoffeeQL beats raw SQL

SQL is powerful: but it's verbose, error prone to compose dynamically, and completely different from MQL, Redis, or any other store. CoffeeQL gives you the same expressive power with a consistent chainable syntax.

BEFORERaw PostgreSQL SQL

SELECT name, email, balance FROM users WHERE city = $1 AND plan = $2 AND balance > $3 AND active = true ORDER BY balance DESC LIMIT 10;

AFTERCoffeeQL: same query, any backend

users[]

.where(city = "Delhi", plan = "pro", balance > 1000, active = true)
.give(name, email, balance)
.sort(balance, DESC)
.cup(10)

What CoffeeQL gives you on Postgres

Feature	Raw SQL	CoffeeQL
Chainable reads	Manual WHERE + ORDER + LIMIT	✓ .where().sort().cup()
Cross DB JOIN	Impossible	✓ .mix(orders{} ON ...)
Geo proximity	PostGIS extension required	✓ .near() built in
AI similarity	pgvector extension required	✓ .like() built in
CRUD mutations	INSERT/UPDATE/DELETE syntax	✓ .pour()/.refill()/.spill()
Transactions	BEGIN / COMMIT / ROLLBACK	✓ shot{ } block

// adapters

MySQL

Full MySQL 8.0 support. PlanetScale, AWS RDS, self hosted: drop CoffeeQL on top and write one syntax for your entire stack.

Why CoffeeQL beats raw MySQL

MySQL SQL is nearly identical to PostgreSQL but differs just enough to cause bugs when you switch. CoffeeQL abstracts the differences: write once, run on both.

BEFORERaw MySQL: subtle differences from Postgres

SELECT name, email, loyalty_pts FROM users WHERE active = 1 -- not TRUE like Postgres AND plan IN ('pro','team') ORDER BY loyalty_pts DESC LIMIT 20;

AFTERCoffeeQL: identical to your Postgres query

users[]

.where(plan = "pro" | plan = "team", active = true)
.give(name, email, loyalty_pts)
.sort(loyalty_pts, DESC)
.cup(20)

Schema creation

Define once with grind: CoffeeQL generates the right DDL for MySQL automatically (VARCHAR(36) for UUID, TINYINT for BOOL, etc).

DDLgrind → MySQL CREATE TABLE

grind users[] (

id UUID PRIMARY, → VARCHAR(36) PRIMARY KEY
name TEXT NOT NULL, → TEXT NOT NULL
active BOOL, → TINYINT(1)
balance FLOAT → DECIMAL(10,2)

)

What's different in MySQL

Feature	MySQL native	CoffeeQL handles it
Booleans	1 / 0	Write true/false: translated auto
Insert conflict	INSERT IGNORE	Handled by .pour()
UUID	UUID() function	Use UUID type in grind

// adapters

MongoDB

Translates to native MongoDB aggregation pipelines. Atlas, self hosted, Docker. Write CoffeeQL: get MQL.

Why CoffeeQL beats raw MQL

MongoDB query language is JSON pipelines. They're powerful but verbose, hard to read, and completely incompatible with SQL. CoffeeQL gives MongoDB the same readable syntax as your relational queries.

BEFORERaw MongoDB aggregation pipeline

db.products.aggregate([ { $match: { "specs.watts": { $gt: 500 }, "tags": { $elemMatch: { $eq: "espresso" } } }}, { $project: { name:1, price:1, "specs.watts":1 }}, { $sort: { price: 1 } }, { $limit: 5 } ])

AFTERCoffeeQL: reads like a sentence

products{}

.where(specs.watts > 500, tags.has("espresso"))
.give(name, price, specs.watts)
.sort(price, ASC)
.cup(5)

Unique advantage: cross type JOIN

MongoDB can't join with a PostgreSQL table. CoffeeQL can. Use .mix() to query across your SQL and Mongo collections in a single statement.

CROSS JOINMongoDB doc + PostgreSQL table

products{} ← MongoDB

.mix(orders[] ON products{}.id = orders[].product_id) ← PostgreSQL
.give(products{}.name, orders[].quantity)
.sort(orders[].quantity, DESC)

// adapters

Redis

CoffeeQL maps to Redis commands automatically. Use the namespace:id{} pattern: no need to remember HGETALL vs HMGET vs ZREVRANGE.

Why CoffeeQL beats raw Redis commands

Redis has a different command for every data structure: HGETALL, HMGET, GET, ZREVRANGE, ZADD, DEL. CoffeeQL unifies them into the same chainable syntax you use for SQL and MongoDB.

BEFORERaw Redis commands: different syntax for everything

HGETALL session:sess_001 HMGET cart:usr_003 items total coupon DEL session:old ZREVRANGE leaderboard:top 0 9 WITHSCORES

AFTERCoffeeQL: same syntax as everything else

session:sess_001{}

.give(*) → HGETALL

cart:usr_003{}

.give(items, total) → HMGET

session:old{}

.spill() → DEL

leaderboard:top{} → ZREVRANGE WITHSCORES

.give(*)
.sort(score, DESC)
.cup(10)

CRUD on hashes

WRITE.pour() / .refill() / .spill() on Redis hashes

-- HSET (create)
session:new{}

.pour({ user_id: "u1", plan: "pro" })

-- HSET (update fields)
cart:usr_001{}

.refill({ total: "9497", coupon: "SAVE20" })

-- DEL
session:expired{}

.spill()

Command auto mapping

CoffeeQL	Redis command
session:s{}.give(*)	→ HGETALL
cart:u{}.give(items, total)	→ HMGET
session:old{}.spill()	→ DEL
leaderboard:top{}.give(*).cup(10)	→ ZREVRANGE WITHSCORES
hash{}.pour({ key: "val" })	→ HSET

// adapters

SQLite

Zero config. File based. Bundled. Same CoffeeQL queries as PostgreSQL: no changes needed.

Build locally with SQLite, deploy on PostgreSQL. Your CoffeeQL queries don't change. One syntax, all adapters.

// language reference

All Keywords

Every keyword, method, and operator in CoffeeQL.

Collection syntax

Syntax	Backend	Meaning
name[]	PostgreSQL · MySQL · SQLite	Structured: fixed schema SQL table
name{}	MongoDB	Unstructured: flexible document store
ns:id{}	Redis	Key value hash, namespaced

Read methods

Method	Signature	What it does
.where()	.where(cond, cond \| cond)	Filter: comma=AND, pipe=OR
.give()	.give(f1, f2) / .give(*)	Select fields to return
.sort()	.sort(field, ASC\|DESC)	Order results
.cup()	.cup(n)	Limit to n results
.blend()	.blend(field)	Group by field
.mix()	.mix(col ON a = b)	Join two collections

Write methods

Method	Signature	What it does
.pour()	.pour({ key: val })	Insert a new record
.refill()	.where().refill({ key: val })	Update matching records
.spill()	.where().spill()	Delete matching records

Top level statements

Statement	What it does
grind name[] (schema)	Create structured collection with schema
grind name{}	Create unstructured collection
shot { ... }	Atomic transaction block
menu()	List all collections
menu(name[])	Show schema of a collection

Operators

Operator	Meaning	Example
=	Equal	plan = "pro"
!=	Not equal	status != "cancelled"
>	Greater than	balance > 1000
<	Less than	price < 500
>=	Greater than or equal	age >= 18
<=	Less than or equal	age <= 65
,	AND: inside .where()	active = true, age > 18
\|	OR: inside .where()	city = "Delhi" \| city = "Mumbai"
!	NOT: prefix	!active
EXISTS	Field is not null	brand EXISTS

Special where methods

Method	Signature	What it does
.near()	field.near(lat, lon, dist)	Geo proximity: m, km, mi
.like()	field.like("q").threshold(n)	AI vector similarity: 0 to 1
.has()	arrayField.has(value)	Array contains this value
.last()	field.last(7d \| 1h \| 30m)	Within last N time units

// language reference

Built in Functions

Functions you can use inside CoffeeQL queries: value generators for .pour(), aggregates for .give() after .blend().

Value generators

Use inside .pour() to auto generate values at insert time:

GENERATORSuuid() · now() · today()

users[]

.pour({

id: uuid(), ← generates UUID v4: "a3f7c2d1-..."
name: "Rahul",
joined_at: now(), ← Unix timestamp in ms: 1716892800000
date_only: today() ← date string: "2026-05-21"

})

Function	Returns	Example value
uuid()	UUID v4 string	"a3f7c2d1-4e5b-6f7a-8b9c-0d1e2f3a4b5c"
now()	Unix timestamp ms	1716892800000
today()	Date string	"2026-05-21"

Aggregate functions

Use inside .give() after .blend(). Always needs an alias with as:

AGGREGATESCOUNT · SUM · AVG · MAX · MIN

orders[]

.blend(city)
.give(

city,
COUNT() as order_count,
SUM(total) as total_revenue,
AVG(total) as average_order,
MAX(total) as largest_order,
MIN(total) as smallest_order

)

Time duration units for .last()

Unit	Meaning	Example
s	Seconds	field.last(30s)
m	Minutes	field.last(15m)
h	Hours	field.last(1h)
d	Days	field.last(7d)
w	Weeks	field.last(2w)
mo	Months	field.last(1mo)
y	Years	field.last(1y)

// reference

Data Types

Types in grind schemas. CoffeeQL maps to the right native type per backend.

CoffeeQL	PostgreSQL	MySQL	SQLite
UUID	UUID	VARCHAR(36)	TEXT
TEXT	TEXT	TEXT	TEXT
INT	INTEGER	INT	INTEGER
FLOAT	NUMERIC	DECIMAL(10,2)	REAL
BOOL	BOOLEAN	TINYINT(1)	INTEGER
DATETIME	TIMESTAMPTZ	DATETIME	INTEGER
GEOPOINT	POINT	POINT	TEXT
VECTOR	vector[]	JSON	TEXT

// reference

Errors

CoffeeQL errors tell you what went wrong and hint at the fix.

error messages
-- Missing brackets
☕ users needs a type!
   Hint: users[] for a table, users{} for a document

-- Wrong chain order
☕ Wrong chain order!
   .give() cannot come after .cup()

-- Unknown type in grind
☕ Expected data type (UUID, TEXT, INT...) but found 'VARCHAR'
   Hint: Use TEXT for strings

-- Empty query
☕ Cup is empty — no statements found.

-- Slow query
☕ Too hot! Query took too long.
   Hint: Add .cup() to limit results

// reference

Changelog

v0.3.0: 29 May 2026

db.explain(cql): human-readable execution plan — steps, adapters, strategy, concurrent flag
.timeout(ms): fail fast if adapter doesn't respond
.retry(n): automatic retry on failure
.partial(): return partial results when one federation adapter fails
Python package on PyPI: pip install coffeeql — parse, validate, explain via PyO3
Native Python adapters: PostgresAdapter (asyncpg), MongoAdapter (motor), MySQLAdapter (aiomysql), RedisAdapter (redis-py)

v0.2.0: 14 May 2026

Real adapter execution + Cross adapter federation ☕

Real adapters: actual DB execution

PostgresAdapter: tokio postgres, parameterized queries, RETURNING *
MongoAdapter: native aggregation pipelines, nested fields, .has()
MySQLAdapter: mysql_async, boolean translation, JSON support
RedisAdapter: HGETALL / HMGET / HSET / DEL / ZREVRANGE auto detected

Cross adapter federation

PostgreSQL[] .mix(MongoDB{}): in memory hash join across live databases
PostgreSQL[] .mix(MySQL[]): two structured DBs federated
Concurrent fetch: both sides queried via tokio::try_join!
Sort + limit applied post join

CoffeeQL class

new CoffeeQL({ adapters: { 'users[]': new PostgresAdapter(...) } })
db.connect(): ping all adapters
db.query(cql): parse → plan → execute → real rows
db.disconnect(): clean shutdown

v0.1.0: 1 May 2026

First public release ☕

Language

[] structured and {} unstructured collection syntax
.where(): AND, OR, NOT, EXISTS, nested fields
.give() / .sort() / .cup(): query, order, limit
.blend(): GROUP BY with COUNT, SUM, AVG, MAX, MIN
.mix(): cross type JOINs
.pour() / .refill() / .spill(): full CRUD
shot{}: atomic transactions
grind + menu(): schema definition and inspection
.near() / .like() / .has() / .last(): special methods

npm package

Published as coffeeql on npmjs.com
JavaScript + TypeScript API
CLI via npx coffeeql
WASM: runs in browser and Node.js

// the brew guide

What is CoffeeQL? ☕

CoffeeQL is a query language. It's also a love letter to coffee. Every keyword, every method, every error message: named after something you'd find in a coffee shop. This guide teaches you the language the fun way.

The Menu Analogy

Think of your database like a coffee shop. You walk in and ask for what you want. CoffeeQL is how you order.

The cup: the collection you're querying (users[], products{})
The filter: .where(): what goes in, what stays out
The pour: .give(): what you actually get served
The limit: .cup(10): how much fits in your cup
The grind: defining the schema, like setting up the beans
The shot: shot{}: a quick, precise, atomic transaction
The spill: .spill(): delete something (careful)

The Golden Rule

CoffeeQL has exactly one rule: tell it what collection, then chain what you want. That's it. Everything else follows.

ANATOMYA complete CoffeeQL statement

collection[] ← WHAT are you querying? (structured)

.where(conditions) ← FILTER: who gets through?
.give(fields) ← SELECT: what do you want back?
.sort(field, DIR) ← ORDER: how do you want it sorted?
.cup(10) ← LIMIT: how much fits in your cup?

Two brackets, two worlds

The entire difference between SQL databases and document stores is captured in two characters:

[]

Structured

Fixed schema · SQL databases

Like a printed menu. Every item has the same format. You always know exactly what fields exist.

users[] orders[] payments[]

{}

Unstructured

Flexible schema · Document stores

Like a chalkboard menu. Each item can have different properties. Fields vary per document.

products{} events{} logs{}

Next in Brew GuideCollections: the Cup →

ReferenceCollections (full) →

// the brew guide

Collections: The Cup ☕

Before you can order, you need to know what's on the menu. Collections are your tables, your document stores, your Redis namespaces. They're where your data lives.

☕ Coffee analogy

A collection is like a type of coffee. espresso[], latte[], cold_brew{}. Each one has a slightly different structure. You pick the one you want, then specify how you want it prepared.

Defining a structured collection

Use grind to define a structured collection. Think of grind as setting up the grinder before you brew: you configure the schema once, then queries flow through it.

GRINDDefine structured collection schema

grind users[] (

id UUID PRIMARY, ← unique identifier, auto indexed
name TEXT NOT NULL, ← string, required
email TEXT UNIQUE, ← must be unique across collection
age INT, ← integer
city TEXT,
plan TEXT, ← e.g. "free", "pro", "team"
balance FLOAT, ← decimal number
active BOOL, ← true / false
joined_at DATETIME ← timestamp

)

Querying structured data

Once you've ground the beans, brew your query. Use collection[] to start:

QUERYusers[]: SQL table

-- Most basic: give me everything from users
users[]

-- With a filter
users[].where(active = true)

-- Full query
users[]

.where(plan = "pro")
.give(name, email)
.cup(10)

Defining an unstructured collection

No schema needed for {}. Every document can have different fields. Just grind the name:

GRINDDefine unstructured collection

grind products{} ← no schema. any shape. any fields.

-- Now query it: nested fields work natively
products{}

.where(category = "appliances", specs.watts > 500)
.give(name, price, specs.watts, specs.has_grinder)

Redis key value collections

Redis hashes use a namespace:id{} pattern. CoffeeQL maps this to HGETALL, HMGET, HSET automatically.

REDISnamespace:key{} pattern

-- Get entire hash
session:sess_001{}

.give(*)

-- Get specific fields
cart:usr_003{}

.give(items, total, coupon)

-- Config / feature flags
feature_flags{}

.give(new_checkout, dark_mode)

Inspect with menu()

MENUList collections and inspect schemas

menu() ← list all collections
menu(users[]) ← show schema of users

NextChaining: the Pour →

// the brew guide

Chaining: The Pour ☕

CoffeeQL queries are chains. You start with the collection, then add methods one by one: like steps in a brewing process. Each step refines the result.

☕ Coffee analogy

Making a latte is a chain: grind beans → pull shot → steam milk → pour milk → add flavor. Skip a step or change the order and you get something different. CoffeeQL chains work the same way: each method builds on the last.

The chain order

Methods can be chained in this order. Not all are required: only the collection name is mandatory:

#	Method	Required?	What it does
1	collection[]/{}	✓ Always	Pick what you're querying
2	.where()	Optional	Filter records
3	.blend()	Optional	Group records (before give)
4	.give()	Optional	Select which fields to return
5	.sort()	Optional	Order the results
6	.cup()	Optional	Limit how many come back

.where(): the filter

The barista's filter. Only what matches gets through.

WHERE.where(condition, condition, ...)

-- Comma = AND. All must be true.
users[]

.where(city = "Delhi", plan = "pro", active = true)

-- Pipe | = OR. Any can be true.
users[]

.where(plan = "pro" | plan = "team")

-- Mix AND and OR
users[]

.where(

city = "Delhi" | city = "Mumbai",
plan = "pro",
age >= 18

)

-- Operators: = != > < >= <=
orders[]

.where(total >= 1000, total <= 5000)

-- EXISTS: field must not be null
products{}

.where(brand EXISTS)

-- NOT: prefix with !
users[]

.where(!active)

-- Nested fields (unstructured only)
products{}

.where(specs.watts > 500)

.give(): what comes out of the cup

GIVE.give(field1, field2, ...) or .give(*)

users[]

.give(name, email, balance) ← specific fields

events{}

.give(*) ← all fields

products{}

.give(name, specs.watts) ← nested fields

-- With aggregates after .blend()
orders[]

.blend(city)
.give(city, COUNT() as total, SUM(total) as revenue)

.sort(): order matters

SORT.sort(field, ASC | DESC)

users[]

.sort(balance, DESC) ← highest balance first

products{}

.sort(price, ASC) ← cheapest first

events{}

.sort(created_at, DESC) ← newest first

.cup(): how much fits in your cup

CUP.cup(n): limit to n results

users[]

.cup(10) ← first 10 only

products{}

.cup(1) ← just the top result

orders[]

.cup(100) ← up to 100

Full brew: real example

FULL CHAINFind top pro users in metro cities

users[] ← collection

.where( ← filter

city = "Delhi" | city = "Mumbai",
plan = "pro" | plan = "team",
balance > 2000,
active = true

)
.give(name, city, plan, balance) ← select fields
.sort(balance, DESC) ← order
.cup(20) ← limit

NextMutations: the Refill →

// the brew guide

Mutations: The Refill ☕

Reading data is brewing. Writing data is the refill. CoffeeQL gives you three mutation methods: pour (insert), refill (update), spill (delete).

☕ Coffee analogy

Pour: you pour fresh coffee into an empty cup. New record, new data.
Refill: you top up an existing cup. Update what's already there.
Spill: you knocked it over. The record is gone. Be careful.

.pour(): insert a new record

POURcollection.pour({ field: value, ... })

-- Structured insert (PostgreSQL / MySQL)
users[]

.pour({

name: "Khushvi",
email: "khushvi@example.com",
city: "Rajkot",
plan: "pro",
balance: 2500.00,
active: true

})

-- Unstructured insert (MongoDB): any shape
products{}

.pour({

name: "Espresso Machine Pro",
brand: "BrewMaster",
price: 12999,
in_stock: true,
specs: {

watts: 1450,
has_grinder: true,
color: "matte black"

},
tags: ["coffee", "espresso", "premium"]

})

-- Redis hash insert → HSET
session:sess_abc{}

.pour({ user_id: "usr_001", plan: "pro", device: "mobile" })

.refill(): update existing records

REFILL.where(condition).refill({ field: newvalue })

-- Always .where() before .refill() (or you update everything)
users[]

.where(email = "khushvi@example.com")
.refill({ plan: "team", balance: 5000.00 })

-- Update a product's stock and price
products{}

.where(_id = "prd_001")
.refill({ in_stock: true, price: 11999 })

-- Update Redis hash fields → HSET (partial)
cart:usr_001{}

.refill({ total: "8999", coupon: "SAVE20" })

.spill(): delete records

SPILL.where(condition).spill()

-- Delete with filter (recommended)
users[]

.where(email = "test@example.com")
.spill()

-- Delete a Redis key → DEL
session:expired_001{}

.spill()

-- Delete all inactive users (use with caution)
users[]

.where(!active)
.spill()

.spill() without .where() deletes every record in the collection. There is no undo. Always filter unless you mean to wipe everything.

NextGeo, AI, Time: the Extras →

// the brew guide

Geo, AI & Time: The Extras ☕

These are CoffeeQL's secret menu items: things most databases require extensions or separate systems for. In CoffeeQL, they're just methods.

☕ Coffee analogy

Geo, AI, and time queries are like asking for oat milk, an extra shot, or a specific roast. Special orders that a great barista handles without blinking: because they've built the system to handle it natively.

Geo: .near(lat, lon, distance)

Find records within a geographic radius. Works anywhere you store coordinates. No PostGIS, no 2dsphere index setup required.

GEO.where(location.near(lat, lon, distance))

-- Cafes within 5km of Delhi centre
cafes[]

.where(location.near(28.6139, 77.2090, 5km))
.give(name, city, rating)
.sort(rating, DESC)
.cup(10)

-- Combine with other filters
stores{}

.where(

location.near(19.0760, 72.8777, 2mi),
open = true,
rating > 4.0

)
.give(name, address, rating)

-- Distance units: m (metres), km (kilometres), mi (miles)

AI Similarity: .like("query").threshold(n)

Semantic vector search. Find documents that mean the same thing as your query, not just exact keyword matches. Threshold is cosine similarity from 0 to 1: 0.85 is a good starting point.

AI.where(embed.like("query").threshold(0.85))

-- Semantic product search
products{}

.where(embed.like("dark roast espresso machine").threshold(0.85))
.give(name, price, brand)
.cup(5)

-- Combine with price filter
products{}

.where(

embed.like("budget coffee maker").threshold(0.80),
price < 2000

)
.give(name, price)
.cup(3)

Array Contains: .has(value)

Check if an array field contains a specific value. Works natively on MongoDB arrays.

HAS.where(arrayField.has(value))

products{}

.where(tags.has("espresso"))

events{}

.where(categories.has("workshop"), tags.has("beginner"))

Time Window: .last(duration)

Filter by recency without writing timestamp comparisons. Duration units: s m h d w mo y

TIME.where(field.last(Nd / Nh / Nm))

events{}

.where(created_at.last(7d)) ← last 7 days

logs{}

.where(ts.last(1h), level = "error") ← last hour, errors only

orders[]

.where(placed_at.last(30d)) ← last 30 days

NextErrors: the Spill →

Also learnTransactions →

// the brew guide

Errors: The Spill ☕

When something goes wrong, CoffeeQL tells you exactly what and exactly how to fix it. Every error starts with ☕ and ends with a Hint.

☕ Coffee analogy

A spill is recoverable. CoffeeQL errors are the barista telling you "that's not how we make that here": clearly, helpfully, with a suggestion for what to do instead.

Common errors and fixes

Missing collection type

error
☕ users needs a type!
   Hint: Use users[] for a table, users{} for a document

You wrote users without brackets. CoffeeQL doesn't know if it's a SQL table or a document store.

fix
-- Wrong
users
  .where(active = true)

-- Right
users[]
  .where(active = true)   ← SQL table

users{}
  .where(active = true)   ← document store

Wrong chain order

error
☕ Wrong chain order!
   .give() cannot come after .cup()
   Hint: Give comes before Cup — order matters.

fix
-- Wrong
users[]
  .where(active = true)
  .cup(10)
  .give(name)

-- Right
users[]
  .where(active = true)
  .give(name)
  .cup(10)

Unknown data type in grind

error
☕ Expected data type (UUID, TEXT, INT...) but found 'VARCHAR'
   Hint: Use TEXT for strings, INT for integers

fix
-- Wrong: SQL types
grind users[] ( id VARCHAR(36) PRIMARY )

-- Right: CoffeeQL types
grind users[] ( id UUID PRIMARY )

Empty query

error
☕ Cup is empty — no statements found.

You submitted an empty string or just comments. Add a collection name.

Slow query warning

warning
☕ Too hot! Query took too long.
   Hint: Add .cup() to limit results, or add an index

Your query scanned too many records. Add .cup() to limit, or make sure the field you're filtering on is indexed.

// the brew guide

Adapters: The Bridge ☕

An adapter is the bridge between CoffeeQL and your actual database. CoffeeQL parses and plans: the adapter executes. Without an adapter, you get a plan. With an adapter, you get real data.

☕ Coffee analogy

The CoffeeQL engine is like a recipe card. It tells you exactly what to do, in what order. The adapter is the barista: it reads the recipe and actually makes the drink, using the specific equipment in front of it (PostgreSQL, MongoDB, Redis).

How adapters connect: the developer code

This is what you actually write in your application. One import, four adapters available, all from the same package:

SETUPRegister adapters, connect, query

import { CoffeeQL, PostgresAdapter, MongoAdapter } from 'coffeeql'

// 1. Register: tell CoffeeQL which adapter owns each collection
const db = new CoffeeQL({

adapters: {

'users[]': new PostgresAdapter({ uri: process.env.PG_URI }),
'products{}': new MongoAdapter({ uri: process.env.MONGO_URI, db: 'shop' }),

}

})

// 2. Connect: pings all adapters to verify connections
await db.connect()

// 3. Query: CoffeeQL routes to the right adapter automatically
const result = await db.query(` users[] .where(plan = "pro", active = true) .give(name, email) .cup(10) `)

console.log(result.rows) // real rows from PostgreSQL

// 4. Disconnect: clean shutdown
await db.disconnect()

Cross adapter federation: two DBs, one query

Register adapters for different collections, and .mix() works across them automatically. CoffeeQL fetches both sides concurrently and joins in memory:

FEDERATIONPostgreSQL users[] + MongoDB products{}

// Same setup as above: just register both collections
const db = new CoffeeQL({

adapters: {

'users[]': new PostgresAdapter({ uri: PG_URI }), // structured
'products{}': new MongoAdapter({ uri: MONGO_URI, db: 'shop' }), // unstructured

}

})
await db.connect()

// CoffeeQL detects two different adapters → runs federation
const result = await db.query(` users[] .where(plan = "pro") .mix(products{} ON users[].id = products{}.user_id) .give(users[].name, products{}.title, products{}.price) .sort(products{}.price, DESC) .cup(10) `)

// What happens internally:
// 1. PostgreSQL → SELECT id, name FROM users WHERE plan='pro'
// 2. MongoDB → db.products.find({ user_id: { $in: [...ids] } })
// 3. CoffeeQL → hash join in memory → sort → limit → return

console.log(result.rows) // merged rows from both databases

What each adapter actually does

Adapter	Translates to	DB driver used	Collection type
PostgresAdapter	Parameterized SQL: SELECT / INSERT / UPDATE / DELETE RETURNING *	tokio postgres	[] only
MongoAdapter	Aggregation pipelines: find() / insertOne() / updateMany() / deleteMany()	mongodb crate	{} only
MySQLAdapter	MySQL SQL: handles bool as 1/0, UUID as VARCHAR(36) auto	mysql_async	[] only
RedisAdapter	Auto detects key type → HGETALL / HMGET / HSET / DEL / ZREVRANGE	redis crate	ns:id{} only

What's implemented today (v0.3.0)

Every item below is real, tested, and ships in v0.3.0. 265 / 265 tests passing.

Feature	Status
PostgresAdapter: scan, insert, update, delete	✓ v0.2.0
MongoAdapter: find, insert, updateMany, deleteMany, nested fields, .has()	✓ v0.2.0
MySQLAdapter: full CRUD, bool translation, JSON support	✓ v0.2.0
RedisAdapter: HGETALL, HMGET, HSET, DEL, ZREVRANGE auto detected	✓ v0.2.0
Cross adapter federation: PG + Mongo, PG + MySQL	✓ v0.2.0 (experimental)
db.connect(): ping all adapters	✓ v0.2.0
db.query(cql): parse → plan → execute → real rows	✓ v0.2.0
db.disconnect(): clean shutdown	✓ v0.2.0
WASM parse/validate: browser + Node, zero native deps	✓ v0.2.0
db.explain(cql): human-readable execution plan	✓ v0.3.0
.timeout(ms) / .retry(n) / .partial(): query failure semantics	✓ v0.3.0
Python package (PyPI): pip install coffeeql	✓ v0.3.0
Native Python adapters: asyncpg / motor / aiomysql / redis-py	✓ v0.3.0

What's in v0.3.0

Feature	What it means
db.explain(cql)	Human readable step by step plan: adapter routing, pushdown decisions, join strategy, estimated rows
Partial failure handling	If MongoDB times out, return PostgreSQL results + error flag. FailureMode::PartialResults / FailureMode::StrictBoth
Retry semantics	db.query(cql).retry(3): retry failed adapter N times before failing
Query timeout	db.query(cql).timeout(5000): fail fast if adapter doesn't respond
Benchmarks	Federation latency vs manual, pushdown savings, memory usage at 100 / 1000 / 10000 rows
SQLite adapter	Zero config, file based, same queries as PostgreSQL

NextPostgreSQL Adapter →

ReferenceCore Concepts →

// getting started

Core Concepts

Everything you need to understand CoffeeQL before writing your first query.

How translation works

CoffeeQL sits between your application code and your databases. It parses CoffeeQL queries into a unified AST, plans optimizations, and compiles them to native query statements.

PIPELINEQuery Compilation & Orchestration Flow

Your CoffeeQL Query String (e.g. users[].where(active))

↓
Parser ← tokenizes query string, constructs abstract syntax tree (AST)
↓
Planner ← validates schema, rewrites filters, plans cross DB joins
↓
Adapter ← translates plan steps into SQL, MQL, or Redis statements
↓
Database ← executes native queries and returns results

What the planner produces today

The planner is real and ships in v0.2.0. Every query goes through lexer → parser → logical plan → physical plan. The physical plan is returned as a debug string in result.plan. A clean human readable db.explain() API ships in v0.3.0.

TODAY result.plan: what coffeeql() returns right now

const result = coffeeql(` users[] .where(plan = "pro") .mix(orders{} ON users[].id = orders{}.user_id) .sort(orders{}.total, DESC) .cup(10) `)

result.success // true
result.error // null
result.plan // PhysicalPlan::HashJoin {
// left: FullScan { collection: "users", kind: Structured,
// filter: Some(Binary { field: plan, op: Eq, val: "pro" }) },
// right: FullScan { collection: "orders", kind: Unstructured },
// left_field: "id", right_field: "user_id",
// sort: Some((total, Desc)), limit: Some(10)
// }

v0.3.0: shipped. db.explain(cql) returns a structured object with step-by-step human readable plan, adapter routing decisions, and join strategy details.

What coffeeql() returns

RETURN{ success, plan, error }

const result = coffeeql(`

users[]

.where(age > 18)
.give(name)

`)

result.success // boolean: did it parse?
result.plan // string: the physical execution plan
result.error // string | null: error message if failed
result.ast // string: the abstract syntax tree

isValid(): fast validation

Use isValid() when you only need to know if a query is valid: it skips the full plan and is faster.

VALIDATEisValid(query) → boolean

isValid(`

users[]

.where(age > 18)
.cup(10)

`) // → true

isValid(`

users

.where(age > 18)

`) // → false (no brackets)
isValid('') // → false (empty)

The adapter model

CoffeeQL itself only parses and plans. Your adapter (PostgreSQL, MongoDB, etc.) handles execution. This means CoffeeQL is database agnostic by design: change the adapter, keep the queries.

Adapter	Collection type	Generates
PostgreSQL	[] only	SELECT / INSERT / UPDATE / DELETE
MySQL	[] only	SELECT / INSERT IGNORE / UPDATE / DELETE
MongoDB	{} only	find() / insertOne() / updateOne() / deleteOne()
Redis	namespace:id{}	HGETALL / HSET / DEL / ZREVRANGE
SQLite	[] only	SELECT / INSERT / UPDATE / DELETE

One query layer. Any database. Any paradigm.

Three brackets.Every storage paradigm.

What CoffeeQLorchestrates for you.

Already using SQL, GraphQL,or Prisma? Good.

SQL is the best query language ever built. For one database.

GraphQL solves API design. Not database orchestration.

ORMs abstract one database beautifully. Then you add a second one.

CoffeeQL is not trying to replace SQL, GraphQL, or Prisma.

The problem isn'tthe databases. It's the friction.

Drop in on yourexisting stack.

Stay updated on CoffeeQL

One query layer.Any backend.

Query everything.One sip at a time. ☕

How it compares

Quick Start

Install

Parse and validate

Connect your database

Query your database

Or validate only (WASM, no DB)

Installation

DB adapter dependencies

Runtime support

Python (PyPI)

Python adapter dependencies

Python quick usage

Python runtime support

Collections

Structured → users[]

Unstructured → products{}

Key Value → session:id{}

Querying

.where(): filter

.give() / .sort() / .cup()

Full example

Mutations

.pour() → insert

.refill() → update

.spill() → delete

Aggregation

How aggregation works

All aggregate functions

Joins → .mix()

SQL + SQL

MongoDB doc + SQL table (cross type)

Geo, AI & Time

Geo → .near(lat, lon, distance)

AI similarity → .like().threshold()

Array contains → .has()

Time window → .last()

Transactions → shot{}

Schema: grind & menu

grind: create structured collection

Constraints

Data types

menu(): inspect collections

PostgreSQL

Why CoffeeQL beats raw SQL

What CoffeeQL gives you on Postgres

MySQL

Why CoffeeQL beats raw MySQL

Schema creation

What's different in MySQL

MongoDB

Why CoffeeQL beats raw MQL

Unique advantage: cross type JOIN

Redis

Why CoffeeQL beats raw Redis commands

CRUD on hashes

Command auto mapping

SQLite

All Keywords

Collection syntax

Read methods

Write methods

Top level statements

Operators

Special where methods

Built in Functions

Value generators

One query layer.
Any database.
Any paradigm.

Three brackets.
Every storage paradigm.

What CoffeeQL
orchestrates for you.

Already using SQL, GraphQL,
or Prisma? Good.

The problem isn't
the databases. It's the friction.

Drop in on your
existing stack.

One query layer.
Any backend.

Query everything.
One sip at a time. ☕

Structured → `users[]`

Unstructured → `products{}`

Key Value → `session:id{}`