Execute — API Reference

Package: dodil.tables.v1 · Service: Tables

The canonical SQL surface. Send one DuckDB SQL statement; the plane’s planner parses it, picks the dispatch route (keyed write through the WAL, non-keyed write straight to Delta, DDL, CTAS, read), and runs it. Everything the typed RPCs cover is reachable here, plus everything they don’t — JOINs, CTEs, window functions, CTAS, ALTER TABLE, CREATE INDEX, TRUNCATE, RESTORE.

This is a data-plane call, not a control-plane one. api.data.dodil.io does not serve table data — every /:bucket/tables/_execute route was removed in the pillar split. The /v1/databases/{db}/… form is canonical (db is a path segment); the header-routed /v1/sql/execute + x-db-id form is the legacy path-prefix style. Source: dodil-k3/crates/tables-gateway/src/http.rs:3-11,154-166, dodil-tables/crates/adapter/src/rest.rs:1073-1096.

Sending a statement

psql "postgresql://$USER:$TOKEN@pg.uk-lon-1.dodil.io:5432/kb-prod?sslmode=require" \
  -c "SELECT event_type, COUNT(*) AS n FROM events GROUP BY event_type ORDER BY n DESC"

# `dodil data sql` goes over the tables-gateway gRPC door (Tables.Execute).
dodil data sql -b kb-prod "SELECT event_type, COUNT(*) AS n FROM events GROUP BY event_type"
 
# `dodil data pg` sends the same SQL over the Postgres wire instead.
dodil data pg  -b kb-prod "SELECT event_type, COUNT(*) AS n FROM events GROUP BY event_type"

import psycopg
 
with psycopg.connect(
    "host=pg.uk-lon-1.dodil.io port=5432 dbname=kb-prod "
    f"user={user} password={token} sslmode=require"
) as conn, conn.cursor() as cur:
    cur.execute(
        "SELECT event_type, COUNT(*) AS n FROM events GROUP BY event_type ORDER BY n DESC"
    )
    for row in cur:
        print(row)

curl -sS -X POST "https://table.uk-lon-1.dodil.io/v1/databases/kb-prod/sql/execute" \
  -H "Authorization: Bearer $DODIL_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"sql":"SELECT event_type, COUNT(*) AS n FROM events GROUP BY event_type"}'

Request / response

rpc Execute(ExecuteRequest) returns (ExecuteResponse);
 
message ExecuteRequest {
  string db_id = 1;
  // ANY single DuckDB-flavored SQL statement (SELECT / INSERT / UPSERT /
  // UPDATE / DELETE / MERGE / CREATE|ALTER|DROP TABLE / CREATE INDEX /
  // CTAS / CREATE DATABASE / SHOW / DESCRIBE / EXPLAIN).
  string sql = 2;
  map<string, Value> params = 3;              // @name placeholders
  map<string, Type> param_types = 4;          // disambiguates string-carried types
  optional string min_ulid = 5;               // read-your-writes session watermark
  ResultEncoding result_encoding = 6;
  bool preview = 7;                           // lower a txn block without writing
  repeated uint64 expected_statement_rows = 8;// optimistic-concurrency guard
  optional string guard_observed_ulid = 9;    // SELECT … FOR UPDATE conflict guard
}
 
message ExecuteResponse {
  oneof result {
    RowSet rows = 1;          // SELECT / SHOW / DESCRIBE / EXPLAIN
    uint64 rows_affected = 2; // INSERT / UPSERT / UPDATE / DELETE / MERGE
    DdlResult ddl = 3;        // DDL summary
  }
  // "select"|"insert"|"upsert"|"update"|"delete"|"merge"|"create_table"|
  // "create_index"|"alter_table"|"drop_table"|"create_database"|
  // "show_databases"|"show_tables"|"describe_table"|"describe_database"|
  // "explain"
  string statement_kind = 4;
  string max_wal_ulid = 5;    // session watermark after a write ("" when nothing written)
  ServedBy served_by = 6;     // which tier served the rows
  ResultManifest manifest = 7;// spooled result — `rows` empty, pull the presigned parts
  repeated string warnings = 8;
}
 
message DdlResult {
  string table_name = 1;
  int64 version = 2;
  uint32 column_count = 3;    // TOTAL columns after the statement
  repeated string columns_added = 4;
  repeated string pk_columns = 5;
  bool already_existed = 6;
  bool replaced = 7;
  uint64 objects_deleted = 8; // DROP TABLE
  uint64 rows_backfilled = 9; // CTAS / CREATE INDEX
}

Over HTTP the tables door flattens the oneof into a tagged envelope:

// SELECT / SHOW / DESCRIBE
{ "kind": "query",
  "columns": [{ "name": "event_type", "type": "string", "vectorDimension": 0 }],
  "rows": [{ "event_type": "click", "n": 184729 }],
  "rowCount": 1,
  "warnings": [] }
 
// INSERT / UPDATE / DELETE / MERGE
{ "kind": "write", "rowsAffected": 3 }
 
// DDL
{ "kind": "ddl" }

Rows come back as JSON objects keyed by column name — not the JSON-encoded string arrays older drafts of this page showed (rest.rs:1002-1023).

One statement per call

Multi-statement batches

Execute takes exactly one statement. A semicolon-separated batch is refused:

Execute takes ONE statement per call — split the batch and issue the statements in order

Source: dodil-tables/crates/tables-sql/src/lib.rs:280-287. There is no statement_results[], no failed_statement_index, no failed_sql — those fields do not exist.

Transaction blocks

The one exception is a transaction block. If the input opens with BEGIN / START TRANSACTION, the whole block is lowered as a single atomic commit:

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

Rules the block parser enforces (tables-sql/src/lib.rs:150-215):

On the Postgres wire you get real buffered sessions with savepoints instead; Execute is what the adapter lowers them onto.

How the planner routes a write

The planner classifies every DML statement internally, from (1) whether the target declares merge_keys, (2) whether the predicate matches them, (3) the statement shape. These classifications are not wire values — ExecuteResponse carries no strategy field. They are the vocabulary the rest of these pages use to explain routing (dodil-tables/crates/htap-planner/src/ir.rs:108-300).

A non-PK predicate on a keyed table no longer bypasses the WAL. The planner synthesises SELECT <pk> FROM <target> WHERE <predicate> and routes through KeyedFromSubquery, so every write on a keyed table goes through the WAL and cannot be clobbered by a later drain. It costs one extra read proportional to predicate selectivity. NonKeyedUpdate / NonKeyedDelete survives for exactly one case — a table with no declared pk_columns, which has no WAL prefix at all and therefore nothing to race. Source: dodil-tables/crates/htap-planner/src/dml.rs:786-925.

Reads — the freshness contract

There is no Freshness request field and no QueryStrategy enum. Reads are read-your-writes by default. The cross-process mechanism is a session watermark: a write returns ExecuteResponse.max_wal_ulid, and you thread it back as min_ulid on the next read. Setting min_ulid forces the strong path — the read observes at least that ULID or fails over to the S3 WAL, never a silently-stale overlay (proto/api/tables.proto:132-140,232-241). The CLI’s --freshness flag is a deprecated no-op (cli-shell/cli-k3/cmd/table.go:692-693).

ExecuteResponse.served_by reports which tier answered:

Source: dodil-tables/proto/api/types.proto:177-185.

Sub-pages — by SQL shape

• DDL — CREATE TABLE (PRIMARY KEY + PARTITIONED BY), CTAS, CREATE OR REPLACE, ALTER TABLE, CREATE INDEX, DROP TABLE, TRUNCATE, RESTORE. Start here.
• SELECT — reads, the min_ulid watermark, JSON column operators, joins / CTEs / windows.
• INSERT — keyed and non-keyed inserts, INSERT … SELECT.
• UPDATE — the WHERE requirement and every keyed route.
• DELETE — the DELETE counterparts plus TRUNCATE TABLE.
• MERGE — MERGE INTO … USING …, WHEN MATCHED THEN DELETE, pre/post-drain counting.

Refusals

Valid DuckDB SQL the plane refuses, with the real reason:

Sources: tables-sql/src/lib.rs:280-287, htap-planner/src/dml.rs:159-172,240-253, htap-planner/src/ddl.rs:214-215, tables-sql/src/ddl.rs:174-186.

ALTER TABLE DROP COLUMN is supported (and so is ALTER COLUMN … TYPE) — older drafts of this page listed both as refused. BEGIN / COMMIT / ROLLBACK are supported too, per the block rules above.

For the dialect itself — types, JSON ops, statement shapes — see SQL Compatibility.

See also

• Data — the typed RPC shortcuts (Query, Upsert, Delete, WriteStream)
• Tables — table lifecycle (CreateTable, AlterTable, DropTable)
• Maintenance — OptimizeTable · VacuumTable · Compact, and the RESTORE statement
• SQL Compatibility — DuckDB dialect specifics
• Recipes → Manual table + CTAS — runnable end-to-end flows