---
slug: 33-sql-mongo-redis
title: SQL, Mongo & Redis
description: One universal db API over SQLite/Postgres/MySQL, plus mongo_* (documents) and redis_* (key-value) — all pure-Rust, all gated by the db capability.
example_ids: [sql]
---

# SQL, Mongo & Redis

All databases open with `db_open` and are gated by `require db(scope)`. Three families: **SQL** (SQLite/Postgres/MySQL), **documents** (MongoDB, `mongo_*`), **key-value** (Redis, `redis_*`). All drivers are pure-Rust (single static binary).

```synsema
-- Doc example: SQL over SQLite in-memory (real, no server). Same API for Postgres/MySQL.
intent: "doc example: SQL"
require db(":memory:")

db_open(":memory:", "memory")
sql_exec("CREATE TABLE demo (n TEXT)")
sql_exec("INSERT INTO demo VALUES (?)", ["it works"])
print("sql demo → " + (sql("SELECT n FROM demo")[0])["n"])
db_close()

test "create, parameterized insert, query"
    db_open(":memory:", "memory")
    sql_exec("CREATE TABLE items (name TEXT, price REAL)")
    sql_exec("INSERT INTO items VALUES (?, ?)", ["Laptop", 999])
    sql_exec("INSERT INTO items VALUES (?, ?)", ["Mouse", 25])
    let rows be sql("SELECT name, price FROM items WHERE price > ? ORDER BY price DESC", [50])
    assert_eq(length(rows), 1)
    assert_eq(name of (rows[0]), "Laptop")
    db_close()
```

## SQL (SQLite / Postgres / MySQL)

```synsema
require db("./store.db")
db_open("./store.db")                 -- file → SQLite. ":memory:" with mode "memory". postgres:// / mysql://
sql_exec("INSERT INTO users (name) VALUES (?)", ["Ada"])   -- parameterized (safe)
let rows be sql("SELECT * FROM users WHERE id = ?", [1])
give paged("SELECT * FROM users ORDER BY id")              -- pagination for a route handler
```

Placeholders are always `?` (Postgres rewrites to `$n` internally; MySQL uses `?` natively). `bytes` columns round-trip to BLOB/BYTEA byte-exactly.

## MongoDB (documents)

```synsema
require db("mongodb://localhost/appdb")
db_open("mongodb://host:27017/appdb")
let id be mongo_insert("users", {"name": "Ana", "age": 30})
let adults be mongo_find("users", {"age": {"$gte": 18}}, {"sort": {"age": -1}})
```

All of them: `mongo_find(coll, filter?, opts?)` (opts `{limit, skip, sort: {f: 1/-1}, fields: {f: 1}}`), `mongo_find_one(coll, filter?)` → doc or `nothing`, `mongo_insert(coll, doc)` → `_id`, `mongo_insert_many(coll, docs)` → ids, `mongo_update(coll, filter, update)` → `{matched, modified}` (`update` uses operators, `{"$set": …}`), `mongo_delete(coll, filter)` → `{deleted}`, `mongo_count(coll, filter?)`, `mongo_aggregate(coll, pipeline)`, `mongo_collections()`. Documents/filters are maps ↔ BSON; `_id` reads as hex text and a 24-hex string under `_id` in a filter is coerced to an ObjectId.

## Redis (key-value / cache / structures)

```synsema
require db("redis://localhost")
db_open("redis://localhost:6379")
redis_set("session:42", token, 3600)             -- with TTL
let v be redis_get("session:42")
redis_set("cfg", json_encode({"theme": "dark"})) -- structured data is explicit
```

Values are byte-strings (`text` if UTF-8, else `bytes`; integers → `number`); arguments accept text/bytes/number (anything else: `json_encode` it). All of them:

- strings / keys: `redis_get(key)` → value or `nothing`, `redis_set(key, val, ttl_secs?)`, `redis_del(key…)`, `redis_exists(key…)`, `redis_mget(keys)`, `redis_mset(map)`, `redis_keys(pattern)` (O(N)), `redis_type(key)`.
- counters: `redis_incr`, `redis_decr`, `redis_incrby(key, n)` (atomic).
- TTL: `redis_expire(key, secs)`, `redis_ttl(key)` (`-1` no TTL, `-2` absent), `redis_persist(key)`.
- hashes: `redis_hget(key, field)`, `redis_hset(key, map)` → new fields, `redis_hdel(key, field…)`, `redis_hgetall(key)` → map, `redis_hincrby(key, field, n)`.
- lists: `redis_lpush`/`redis_rpush(key, val…)` → length, `redis_lpop`/`redis_rpop(key)`, `redis_lrange(key, start, stop)`, `redis_llen(key)`.
- sets: `redis_sadd`/`redis_srem(key, member…)`, `redis_smembers(key)`, `redis_sismember(key, member)`.
- distributed lock: `redis_lock(key, ttl_ms?)` → token or `nothing` if held (SET NX PX, default 30000 ms), `redis_unlock(key, token)` → bool (atomic, only the token holder frees it).

`db_close(path?)` closes a connection of any family.

> Redis db-index gotcha: `redis://host:6379` → scope `redis://host` (no `/0`); `…/0` is a *different* scope.