Skip to Content
We are live but in Staging 🎉
Data EnginesSQLQuickstart

Quickstart

Five minutes from here you’ll have a real table, rows in it, and working SQL. There is nothing to enable — tables are implicit per bucket. Create the bucket, point a Postgres client at it, and write SQL.

Prerequisites

  • dodil CLI installed and dodil auth login done — CLI Basics
  • A bucket — we’ll use kb-prod: 
    dodil data bucket create kb-prod -d "Tables quickstart"
  • A credential — an API key, a service account, or the bearer JWT from dodil auth login. All three work on every wire; see Connect & wire adapters.

0. Connect

The bucket is the database. Connect once and reuse the session for the whole quickstart.

# sslmode=require — the pg door enters on a raw host port (5432) but does
# terminate TLS. Never `prefer`: it falls back to plaintext without telling you,
# and your password here is your API-key/service-account secret.
# dbname is the bare bucket name.
psql "host=pg.uk-lon-1.dodil.io port=5432 dbname=kb-prod \
      user=dk_XXXX password=$DODIL_SECRET sslmode=require"

1. Create a table

A small events table with a composite primary key, a partition column, and one JSON column for arbitrary payload:

CREATE TABLE events (
  id           BIGINT      NOT NULL,
  user_id      VARCHAR     NOT NULL,
  occurred_at  TIMESTAMP   NOT NULL,
  event_type   VARCHAR     NOT NULL,
  payload      JSON,
  PRIMARY KEY (id, user_id)
)
PARTITIONED BY (event_type);

What that creates:

ColumnDeclared asStored asNotes
idBIGINTlongPrimary key (composite, with user_id)
user_idVARCHARstringPrimary key (composite)
occurred_atTIMESTAMPtimestampMicroseconds since epoch, UTC
event_typeVARCHARstringPartition column — rows physically grouped by this
payloadJSONjsonArbitrary JSON — queryable with DuckDB JSON operators

INTEGER is stored as 64-bit. A column declared INTEGER/INT resolves to long, not a 32-bit type — surrogate keys are 64-bit snowflake ids, so a narrower foreign key would silently truncate. Introspection therefore reports BIGINT for a column you declared INTEGER. The full fifteen-type vocabulary — including DECIMAL(p,s), T[], STRUCT(…) and VECTOR(N) — is in SQL Compatibility → Column types.

Check the schema landed. DESCRIBE is the verb; the CLI’s describe (and its alias get) just runs it for you:

SHOW TABLES;
DESCRIBE events;
column       type       nullable  pk     default
id           long       false     true
user_id      string     false     true
occurred_at  timestamp  false     false
event_type   string     false     false
payload      json       true      false

2. Insert rows

INSERT INTO events (id, user_id, occurred_at, event_type, payload) VALUES
  (1, 'u-101', TIMESTAMP '2026-05-27 10:00:00', 'click',    '{"page":"/pricing"}'),
  (2, 'u-101', TIMESTAMP '2026-05-27 10:01:00', 'click',    '{"page":"/signup"}'),
  (3, 'u-102', TIMESTAMP '2026-05-27 10:02:00', 'purchase', '{"sku":"A-12","amount":49.99}');

The response carries the count of rows the statement wrote — WAL appends and tombstones. Because events has a primary key, those rows land in the write-ahead log first and the compactor folds them into Delta on the next drain. A table with no declared primary key has no WAL at all; its writes append straight to Delta.

3. Query

SELECT event_type, COUNT(*) AS n
FROM events
GROUP BY event_type
ORDER BY n DESC;

You will see the rows you just inserted. Reads are read-your-writes by default — there is no freshness flag to set and no “strong mode” to opt into. Before every read the coordinator asks the writer whether the WAL backlog is drained; if that can be proven, the read takes the cheaper analytical path (a pure Delta scan) because at that point eventual and strong are the same answer. If it cannot be proven, the read merges the WAL overlay with Delta. Either way you see your write — once it has committed. The one exception is an open pg-wire transaction: a SELECT inside it does not see rows the same transaction has staged but not yet committed; use RETURNING on the write, or read after COMMIT. See SQL Compatibility → Transactions.

A strong read whose WAL overlay exceeds its cap aborts with FAILED_PRECONDITION rather than returning a partial answer — retry once compaction catches up. Details in Core Concepts → Freshness is not a client knob.

If a result is large, the plane spools it to object storage and returns a manifest of presigned parts instead of inline rows. The CLI tells you so and suggests a LIMIT; see Execute → Spooled results.

4. Query a JSON column

JSON columns are first-class — DuckDB’s native JSON operators work directly:

SELECT user_id,
       payload->>'sku'              AS sku,
       (payload->>'amount')::DOUBLE AS amount
FROM events
WHERE event_type = 'purchase';

->> extracts a JSON field as text; cast to a typed value as needed. ->, json_extract, json_each and the rest of the DuckDB JSON surface work too — see SQL Compatibility → JSON columns.

5. Update and upsert

-- Update by primary key.
UPDATE events SET event_type = 'click_pricing'
WHERE id = 1 AND user_id = 'u-101';
 
-- Upsert a batch.
MERGE INTO events AS t
USING (VALUES
  (1, 'u-101', TIMESTAMP '2026-05-27 10:00:00', 'click_pricing', '{"page":"/pricing","variant":"B"}'),
  (4, 'u-103', TIMESTAMP '2026-05-27 10:03:00', 'signup',        '{"plan":"pro"}')
) AS s(id, user_id, occurred_at, event_type, payload)
ON t.id = s.id AND t.user_id = s.user_id
WHEN MATCHED THEN UPDATE SET event_type = s.event_type, payload = s.payload
WHEN NOT MATCHED THEN INSERT VALUES (s.id, s.user_id, s.occurred_at, s.event_type, s.payload);

UPDATE and DELETE are refused without a WHERE clause. That is deliberate — use TRUNCATE TABLE events to clear a table.

A predicate that doesn’t mention the primary key is fine. The planner synthesises SELECT pk FROM events WHERE <your predicate> and routes the write through the WAL like any other keyed write, so there is no bypass to design around. See Core Concepts → Write routing.

6. Maintenance (optional)

# Force the WAL to drain into Delta now (it also drains on its own).
dodil data table compact events -b kb-prod
 
# Bin-pack small Delta files into larger ones — faster analytical scans.
dodil data table optimize events -b kb-prod

You usually don’t need these — the drain runs in the background. Reach for them after large bulk writes or nightly batches. compact → optimize is the canonical post-batch sequence.

What you just did

StepStatementWhere it ran
1CREATE TABLEExecute → DDL plan → Delta
1SHOW TABLES / DESCRIBEExecute → catalog read
2INSERTkeyed_insert_bulk → WAL → drain MERGE
3SELECT … GROUP BYfrontier check → Delta scan or overlay ∪ Delta
4SELECT with JSON opssame, DuckDB-native JSON
5UPDATE / MERGEkeyed_update / merge_rows
6Compact + OptimizeTableDelta-side maintenance RPCs

Cleanup

dodil data table delete events -b kb-prod
dodil data bucket delete kb-prod   # only if you created it for this quickstart

Next steps

OverviewCore Concepts