> ## Documentation Index
> Fetch the complete documentation index at: https://docs.wirekite.io/llms.txt
> Use this file to discover all available pages before exploring further.

# What is Sumatra?

> Sumatra is a procedural language for the database your data lives in: PL/SQL-style programs — loops, variables, conditionals, exceptions — compiled to native binaries that run batch-shaped SQL inside the engine. Firebolt is the first supported backend.

Sumatra is a procedural language for the database your data lives in.
You write row-by-row logic — loops, variables, conditionals, exception
handlers — around plain SQL reads and writes, and the toolchain
compiles the program into a native binary that runs it against your
database, batching reads and routing every write to the lightest
correct SQL form automatically.

The language is engine-neutral by design. **Firebolt** is its first
supported backend — and the concrete engine behind every example in
this documentation — but nothing in the language is Firebolt-specific:
the backend is declared per-database by the connection string, and the
program's SQL surface is whatever the connected engine speaks.

Here is a complete Sumatra program. It computes a per-customer running
balance — each row's result depends on the previous row's result, the
kind of logic SQL alone expresses only awkwardly:

```sql balance.sumatra theme={null}
DECLARE
  running       NUMERIC(14,2) := 0;
  last_customer BIGINT        := NULL;
BEGIN
  FOR batch IN (SELECT entry_id, customer_id, delta, running_balance FROM ledger)
      ORDER KEY (customer_id, entry_id)
      BATCH SIZE 50000
  LOOP
    FOR rec IN batch LOOP
      IF rec.customer_id <> last_customer OR last_customer IS NULL THEN
        running       := 0;
        last_customer := rec.customer_id;
      END IF;

      running := GREATEST(running + rec.delta, 0);
      rec.running_balance := running;

      UPDATE ledger SET ROW = rec WHERE entry_id = rec.entry_id;
    END LOOP;
    COMMIT;
  END LOOP;
END;
```

No connection handling, no pagination code, no staging tables, no
`MERGE` statements — the compiler produces all of that. The program
reads in 50,000-row batches, carries `running` across every row and
every batch in order, and commits each batch as a durable checkpoint.

## The problem it solves

Modern analytical engines — Firebolt among them — deliberately ship no
stored procedures and no procedural SQL dialect. Set-based SQL covers
enormous ground, but the moment a pipeline needs a step that **carries
state across rows**, **branches on a value the data just produced**, or
**loops until a computed condition is met**, that step has to leave the
database: a Python script here, a Spark job there, dbt orchestrating
around the gap, and data movement stitching it together.

Sumatra collapses that split. The procedural mid-work is written in one
small language and executes as batch-shaped SQL on the engine itself,
next to the set-based steps it belongs with.

| Approach               | What it gives you                              | Where it stops                                                                               |
| ---------------------- | ---------------------------------------------- | -------------------------------------------------------------------------------------------- |
| The engine's SQL alone | Fast, distributed set transforms               | No carried state, no runtime branching, no loops                                             |
| dbt                    | Orchestrated set steps                         | Control flow is compile-time templating, not runtime data                                    |
| Python / Spark         | Full procedural power                          | Data leaves the platform; connections, batching, staging, and retries are all yours to build |
| Sumatra                | Procedural logic over batched reads and writes | Runs inside the database, in one language — the claim is the combination                     |

The full case — what staying in-database saves you operationally, and
what the batch and merge machinery buys you technically — is on
[Why Sumatra](/sumatra/why).

## The mental model

Every Sumatra program reduces to one shape:

**Read → Compute → Write**

* **Read** — pull rows from the engine in batches into memory. Never a
  per-row server query.
* **Compute** — arbitrary in-memory work: loops, branches, carried
  variables, record and collection types. No server round-trips.
* **Write** — push results back in batches. Never a per-row server
  write.

If you know PL/SQL, the only real shift is this: **the grain is a
batch, not a row**. Cursor-style row-at-a-time server access — natural
on OLTP databases — is pathological on a distributed analytical engine,
so Sumatra makes it inexpressible: the language's grammar has no way to
write a per-row server call. In exchange, the batch machinery
(pagination, ordering, staging, write-back, commit grain) is entirely
the compiler's job, driven by two declarations on the read:
`ORDER KEY` and `BATCH SIZE`.

Beyond that frame, the language is deliberately familiar PL/SQL:
`DECLARE`/`BEGIN`/`END`, `:=` assignment, `IF`/`ELSIF`, `CASE`, `WHILE`,
`FOR` loops, records, `%ROWTYPE`, keyed collections, `EXCEPTION`
handlers, explicit `COMMIT`. A handful of new words cover the batch
boundary: `BATCH SIZE`, `ORDER KEY`, `PARAM`, `STOP`.

## How programs run

The `sumatra` shell compiles a program **against the live catalog** of
the connected database — the database itself is the schema — then
generates native code, builds it with `g++` on your host, and runs it.
The compiled program makes its own connection from the same DSN file
and speaks batch-shaped SQL to the engine.

Writes are routed, not written by hand:

* A write that is already set-shaped (plain `UPDATE`/`DELETE`/`INSERT
  ... SELECT`) runs server-side as-is — rows never leave the engine.
* Per-row writes captured inside a loop are staged and applied as one
  set-based `MERGE` per batch. You never write `MERGE` or manage
  staging tables yourself.

Programs run one-shot from a file (`@prog.sumatra`), or are stored in
the database as [named procedures](/sumatra/procedures) and run with
`EXEC` — compiled once, cached per host, and automatically recompiled
when the source, the toolchain, or the table schemas change.

## Semantics you can trust

Sumatra's answer for what a program *means* is deliberately boring: it
means what the engine underneath means. Values, types, rounding,
overflow, NULL handling — the in-memory evaluator is bit-exact to the
bound engine's own semantics (today, Firebolt's), so moving a
computation between the read SQL and the procedural middle never
changes the answer, and a program never invents behavior the engine
does not have.

Three properties are worth calling out:

* **Determinism.** A compute program's result is declared-order
  deterministic: `ORDER KEY` fixes a strict total order, state carries
  across batch boundaries, and the answer is invariant under
  `BATCH SIZE`. An order tie or a NULL key at runtime is a loud error,
  never a silent skip or duplicate.
* **Loud failure.** Overflow, division by zero, and lossy surprises are
  errors, never silently wrong values. Committed batches always survive
  a fault as a durable prefix; recovery is explicit and yours.
* **Honest rejection.** A program the model cannot run well — a nested
  read, a per-row server op, an unbounded server-dependent loop — is
  rejected at compile time with a named reason. The failure mode is a
  compile error, not a production performance cliff.

## Status at a glance

|                 |                                                                                                                                                                      |
| --------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Current version | 0.2.0                                                                                                                                                                |
| Platform        | Linux x86-64                                                                                                                                                         |
| Backend         | Firebolt — the first supported engine. The DSN scheme declares the backend, and the toolchain is built to bind others; Firebolt is the only one live in this release |
| Toolchain       | `sumatra` (shell) and `sumatra-compile` (offline inspector), plus `g++` on your host                                                                                 |
| Distribution    | A single tarball; no dependencies beyond the C++ toolchain and libcurl                                                                                               |

<Note>
  Sumatra is built by the Wirekite team and documented here, but it is a
  **standalone product**: it is not part of the Wirekite replication
  pipeline, is not invoked by Wirekite, and nothing in this section
  depends on Wirekite in any way.
</Note>

## Reading order

1. [Why Sumatra](/sumatra/why) — the case for in-database
   transformation, and the batch and merge engines in depth.
2. [Quickstart](/sumatra/quickstart) — install, connect, and run your
   first two programs.
3. [Learning by example](/sumatra/examples) — small annotated programs
   covering the everyday shapes.
4. [Programs and batches](/sumatra/programs) — the program skeleton,
   read blocks, write routing, and commit grains.
5. [Types, expressions, and control flow](/sumatra/language) — the
   language reference.
6. [Exceptions and recovery](/sumatra/errors) · [Stored
   procedures](/sumatra/procedures) · [Built-in
   functions](/sumatra/builtins) · [The sumatra shell](/sumatra/shell)
   · [Limitations](/sumatra/limitations)