Get started

Installation

SynnoDB ships as a Python package with two faces. The light install is the DuckDB drop-in runtime; the factory extra pulls in the agent stack that generates engines. Bring an API key for any litellm-supported provider.

$ pip install synnodb                 # the DuckDB drop-in runtime
$ pip install "synnodb[factory]"      # + the agent engine factory
$ export ANTHROPIC_API_KEY="sk-ant-..."

Everything lives under a single data root - Parquet, generated engines, and the run workspace. Point SYNNO_DATA_DIR at it (env or .env); unset, it defaults to a project-local .synno_data/.

$ export SYNNO_DATA_DIR=/path/to/synno_data   # parquet, engines, workspace

The drop-in

The headline is one import. Swap import duckdb for import synnodb as duckdb and change nothing else: connect, execute, and fetchall behave exactly as DuckDB. With no engines registered, behavior is byte-identical. Point the connection at a directory of published engines and a routing policy, and matching queries auto-route to the bespoke C++ - every routed result cross-checked against DuckDB.

import synnodb as duckdb            # the only import that changes
from synnodb.router import RouterMode, RouterPolicy

con = duckdb.connect(
    "my.duckdb",                          # a path, ":memory:", or a live duckdb connection
    config={"threads": 8},                # same knob DuckDB takes - also fixes the engine's parallelism
    engines="/path/to/synno_data/engines",   # where synthesized engines are published
    policy=RouterPolicy(mode=RouterMode.SAMPLED, cross_check_rate=1.0),
)
con.execute(sql).fetchall()   # bespoke C++ when a matching engine exists, else DuckDB

The first argument is the one duckdb.connect takes - a file path or ":memory:" - so the swap really is one line. It also accepts an already-open DuckDBPyConnection, so you can hand over a live connection you already hold and the router shares it as-is. Warm every engine up front - start its process and ingest your tables - with con.synno_ingest_data() so the first query is served warm. Ask the router why a query went where it did with con.why(sql), pick up engines published mid-session with con.refresh_engines(), and read routed / cross-checked / mismatch counts from con.router_stats(). To get an engine to route to, you synthesize one - the rest of this guide.

Configure the model

The engine factory drives an LLM agent. Model names are litellm-style: a bare OpenAI model, or a provider/model string with credentials read from that provider's environment variable. The default is gpt-5.4 (OPENAI_API_KEY). Set it per run with the model= argument, or globally via SYNNO_MODEL.

from synnodb import SynnoDB

# default: OpenAI gpt-5.4 (OPENAI_API_KEY)
db = SynnoDB(workload="tpch", queries="1-5")

# any litellm provider - credentials from that provider's env var
db = SynnoDB(workload="tpch", queries="1-5",
             model="anthropic/claude-sonnet-5")   # ANTHROPIC_API_KEY
# other examples: "gpt-5.4", "openrouter/z-ai/glm-5.2"

Describe your workload

A workload is a set of parameterized SQL templates over your schema, described by one self-describing JSON file. Each entry carries its SQL template with [PLACEHOLDER] slots and a typed spec for each slot, declaring the value space that is sampled at run time. Scalars are int / float / date / categorical; correlated or distinct placeholders share a param_groups spec.

{
  "6": {
    "sql": "... l_discount between [DISCOUNT] - 0.01 ... l_quantity < [QUANTITY] ...",
    "params": {
      "DATE":     { "type": "date",  "min": "1993-01-01", "max": "1997-01-01" },
      "DISCOUNT": { "type": "float", "min": 0.02, "max": 0.09, "step": 0.01 },
      "QUANTITY": { "type": "int",   "min": 24, "max": 25 }
    }
  },
  "7": {
    "sql": "... n1.n_name = '[NATION1]' ... n2.n_name = '[NATION2]' ...",
    "param_groups": [
      { "type": "sample", "placeholders": ["NATION1", "NATION2"],
        "domain": ["GERMANY", "CHINA", "FRANCE"], "distinct": true }
    ]
  }
}

SynnoDB reads everything else - schema, tables, data - straight from a DuckDB connection you already hold. Hand your live connection (or a .duckdb path) to db.sync_from_duckdb(...): it reads the queries, infers the join graph from their JOINs, freezes a consistent point-in-time snapshot it owns, and derives its own cheap correctness rungs by FK-preserving downscaling of that one connection - anchored on the largest table and following the join graph, so a 2% rung still joins to real rows. There is no pre-scaled data to supply and nothing is ever copied back into your database; because the snapshot is frozen up front, you can keep querying - and even writing - it in parallel.

import duckdb
from synnodb import SynnoDB

duckdb_con = duckdb.connect("tpch.duckdb", read_only=True)   # any DuckDB you already have

db = SynnoDB(
    model="anthropic/claude-sonnet-5",
    queries="1-5",
    db_storage="in_memory",
    data_dir="/path/to/synno_data",
    threads=1,                      # engine is generated, validated & served at this parallelism
)
spec = db.sync_from_duckdb(          # reads schema + queries through your live connection
    duckdb_con,                     # a ".duckdb" path works here too
    name="tpch_byo",
    queries_json="queries.json",
    schema_example_table="lineitem",
)
print(spec.tables, spec.all_query_ids)

The returned spec draws parameter instantiations with a seeded RNG, so a range becomes a uniform draw, a categorical a choice, and a group one joint row that keeps correlated placeholders aligned. It also becomes this driver's workload, so the same db synthesizes an engine for it next.

Synthesize an engine

The same db that registered the workload synthesizes the engine. Constructing the driver spawned an in-process live dashboard and printed its URL (db.dashboard_url) - open it to watch generation unfold: input tokens, generated code size, per-query speedups, cost, and an activity log, all on one continuous timeline as you chain stages.

print(db.dashboard_url)                 # live-UI dashboard, e.g. http://localhost:8765

plan = db.createStoragePlan()                      # -> StoragePlan
impl = db.createBaseImpl(storage_plan=plan.text)   # -> BaseImplementation, auto-published
print(sorted(impl.files))                          # the generated C++ sources

That single createBaseImpl produces the complete engine: correct and parallel, if lightly tuned - and already enough to beat DuckDB on these queries. When threads > 1, the run ends with a per-query pass that re-runs each query at that thread count and fixes any that were only correct single-threaded. Each stage returns a domain artifact (StoragePlan, BaseImplementation, ...) that carries its git snapshot hash and chains straight into the next stage - no Weights & Biases required. Alternative constructors set sensible defaults: SynnoDB.in_memory(...), .on_ssd(...), .for_tpch(...), .from_env().

Benchmark & compare

Because the drop-in is the same object as DuckDB, an apples-to-apples benchmark is the identical code run twice over the same seeded instantiations - once through vanilla DuckDB, once through the router - both capped to the same thread budget and both reading the same live connection, so only the engine differs. Time like with like with a plain wall-clock timer around execute + result materialization on each side: that is exactly what the router records as engine_ms (and duckdb_ms on fallback), so both columns share one basis. Avoid EXPLAIN ANALYZE here - its server-side latency excludes the fetch the router's timer includes, and its JSON profile is unreliable on a read-only connection. With cross_check_rate=1.0 every routed result is re-run on DuckDB and compared, so any mismatch fails loudly.

import synnodb, random, statistics, time
from synnodb.router import RouterMode, RouterPolicy

NUM_THREADS = 1                               # same budget for both sides (os.cpu_count() for a real run)
duckdb_con.execute(f"PRAGMA threads={NUM_THREADS}")   # the live connection from "Describe your workload"

# Same seeded instantiations feed both runs: {qid: [(name, sql, params), ...]}
gen = spec.query_gen_factory(None)
rng = random.Random(42)
instantiations = {qid: [gen(f"Q{qid}", rng) for _ in range(10)]
                  for qid in spec.all_query_ids}

def wall_ms(execute, sql):                    # perf_counter around execute + Arrow materialization -
    start = time.perf_counter()               # the exact basis the router records as engine_ms / duckdb_ms
    execute(sql).to_arrow_table()
    return (time.perf_counter() - start) * 1_000

# DuckDB baseline - measured on the very connection SynnoDB uses below
baseline = {q: [wall_ms(duckdb_con.execute, s) for _, s, _ in insts]
            for q, insts in instantiations.items()}

# SynnoDB drop-in - hand it the live connection; its tables are already there
con = synnodb.connect(
    duckdb_con,                               # a ".duckdb" path works here too
    config={"threads": NUM_THREADS},
    engines="/path/to/synno_data/engines",
    policy=RouterPolicy(mode=RouterMode.SAMPLED, cross_check_rate=1.0),
)
con.refresh_engines()
con.synno_ingest_data()                       # warm each engine so the first query is served warm
synno = {}
for qid, insts in instantiations.items():
    times = []
    for _, sql, _ in insts:
        con.execute(sql).fetchall()
        last = con._last                      # engine_ms when the bespoke engine served the query,
        times.append(last.get("engine_ms", last.get("duckdb_ms")))   # else duckdb_ms on fallback
    synno[qid] = times

for qid in spec.all_query_ids:                # per-query speedup, same latency basis on both sides
    d, s = statistics.mean(baseline[qid]), statistics.mean(synno[qid])
    print(f"Q{qid}: {d:7.1f} ms -> {s:7.1f} ms  ({d / s:.2f}x)")

stats = con.router_stats()["session"]
assert stats["cross_check_mismatch"] == 0     # every routed result matched DuckDB exactly

On TPC-H, synthesized engines run 11.78× faster than DuckDB across 22 queries at roughly ~$10 of model cost per engine, with 100% of queries faster and every routed result verified.

Optimize further

The base implementation above is the Simple Bespoke Engine - correct and parallel, but only lightly tuned. The same db object carries it further into a Full Bespoke Engine - extra optimization passes for peak performance - every stage restoring the previous one's engine from its git snapshot so the whole pipeline runs without W&B. You can also revalidate the engine you just built at a larger scale factor, restored straight from its local snapshot.

rep   = db.checkSfCorrectness(source=impl, target_sf=50)   # revalidate at a larger scale factor
opt   = db.runOptimLoop(base_impl=impl)                    # SIMD / cache-locality tuning
multi = db.addMultiThreading(optimized=opt)                # push parallel scaling further

To chain across machines - or to persist the run - turn on Weights & Biases, covered next.

Track runs with Weights & Biases

Everything so far runs with no external tracking: the live dashboard is in-process, and stages chain locally through git snapshots. Weights & Biases is an opt-in layer on top. It stays off unless you set a project or an entity - nothing logs in, initializes, or requires credentials otherwise - so there is no W&B dependency in the default path.

Set wandb_project (and optionally wandb_entity) on SynnoDB(...), or the WANDB_PROJECT / WANDB_ENTITY environment variables. The presence of either one enables it; the project defaults to SynnoDB and the entity to your own default W&B entity. Credentials are read from WANDB_API_KEY in the environment or .env.

from synnodb import SynnoDB

db = SynnoDB(
    queries="1-5",
    wandb_project="my-engines",   # presence of a project (or entity) turns tracking on
    wandb_entity="my-team",       # optional; defaults to your own W&B entity
)                                 # reads WANDB_API_KEY from the environment / .env
db.sync_from_duckdb(duckdb_con, name="tpch_byo", queries_json="queries.json")

plan = db.createStoragePlan()
impl = db.createBaseImpl(storage_plan=plan.text)
print(impl.run_id)   # the W&B run id - each stage logs its own run

Each stage becomes a logged run capturing input tokens, model cost, generated code size, per-query speedups, and the storage plan and generated C++ as artifacts - the same timeline the live dashboard shows, persisted and shareable. The main reason to reach for it, though, is chaining across machines or sessions: pass a previous stage's run id via *_wandb_id= instead of the in-process artifact, and SynnoDB restores that engine from its logged snapshot.

# on a different machine / session, resume from a logged run instead of an artifact
opt = db.runOptimLoop(base_impl_wandb_id="q45vm9fz")
mt  = db.addMultiThreading(optimized_wandb_id="0br4bjqb")
rep = db.checkSfCorrectness(source_wandb_id="0br4bjqb", target_sf=50)

Chaining by run id requires the producing run to have logged to W&B (a project or entity was set on it). Within a single session or machine you do not need any of this - pass the artifact (or its snapshot_hash) and the whole pipeline runs W&B-free.

Define your own conversation

Every built-in stage is an ordinary ConversationPlan, and you can assemble your own from the same primitives. A plan names the run (for logging and caching), states what the prepared workspace must provide, and supplies a stages callable that turns a context into a flat list of stage items - PromptStage, PerQueryLoop, and markers like Benchmark or AssertCorrect. db.run_synthesis(plan, start=...) is the single entry point every stage goes through.

from synnodb import (
    AssertCorrect, Benchmark, ConversationPlan, ConvContext,
    PerQueryLoop, PrepareFeatures, PromptStage,
)

def my_stages(ctx: ConvContext):
    return [
        AssertCorrect(),
        PerQueryLoop(lambda qid, ctx: [
            PromptStage(
                descriptor=f"tune {qid}",
                get_prompt_with_tracing=lambda _cfg, rt, trace: (
                    f"Query {qid} runs in {rt:.0f} ms.\nTrace:\n{trace}\nOptimize it."),
                max_turns=125,
            ),
        ]),
        Benchmark(),
    ]

plan = ConversationPlan(
    name="myTuningPass",
    prepare=PrepareFeatures(tracing=True),   # the workspace needs tracing instrumentation
    stages=my_stages,
)
tuned = db.run_synthesis(plan, start=impl)   # start: artifact | snapshot hash | None

A single stage, with automatic revert

A PromptStage is one declarative LLM task. Its get_prompt callback receives the freshly measured runtime of the current engine, so the model always optimizes against real numbers. By default the engine is re-measured after the stage and the change is kept only if it made the query faster - auto_revert_on_regression discards a regression back to the previous git snapshot. That safety net means a stage can never leave the engine slower than it found it.

from synnodb import PromptStage

vectorize = PromptStage(
    descriptor="vectorize the aggregation loop",
    get_prompt=lambda _cfg, rt: (
        f"The engine currently runs this query in {rt:.0f} ms.\n"
        "Rewrite the hot aggregation loop to use SIMD intrinsics."),
    max_turns=125,
    measure_performance_after_stage=True,    # re-measure the engine once the stage finishes
    auto_revert_on_regression=True,          # keep the change only if it got faster (both are the defaults)
)

Drop it into any stages list - on its own, or inside a PerQueryLoop to run one revert-guarded pass per query. Set measure_performance_after_stage=False for exploratory stages (profiling, note-taking) that should never be reverted, and pair it with get_prompt_with_tracing when the prompt should also see the profiler trace.

Next steps

You have installed SynnoDB, described a workload, synthesized and benchmarked an engine, and dropped it in behind one import. From here: