KalamDB SQL Reference

Version: 0.1.3
Last Updated: September 6, 2026

This page documents SQL commands and SQL usage only.

Statement Separator

SELECT 1;
SELECT 2;

Namespace Commands

CREATE NAMESPACE

CREATE NAMESPACE <namespace_name>;
CREATE NAMESPACE IF NOT EXISTS <namespace_name>;

DROP NAMESPACE

DROP NAMESPACE <namespace_name>;
DROP NAMESPACE IF EXISTS <namespace_name>;
DROP NAMESPACE <namespace_name> CASCADE;
DROP NAMESPACE IF EXISTS <namespace_name> CASCADE;

ALTER NAMESPACE

ALTER NAMESPACE <namespace_name>
  SET DESCRIPTION '<description>';

USE / SET NAMESPACE

Changes the default namespace for the current request or multi-statement batch. In the interactive CLI, a successful USE also updates the CLI's local namespace so later requests automatically send namespace_id.

USE <namespace_name>;
USE NAMESPACE <namespace_name>;
SET NAMESPACE <namespace_name>;

SHOW NAMESPACES

SHOW NAMESPACES;

Table DDL

KalamDB supports USER, SHARED, and STREAM tables.

CREATE TABLE (Unified)

CREATE [USER|SHARED|STREAM] TABLE [IF NOT EXISTS] [<namespace>.]<table_name> (
  <column_name> <data_type> [NOT NULL|NULL] [DEFAULT <expr>] [PRIMARY KEY],
  ...,
  [CONSTRAINT <name> PRIMARY KEY (<column_name>)]
)
[WITH (
  TYPE = '<USER|SHARED|STREAM>',
  STORAGE_ID = '<storage_id>',
  USE_USER_STORAGE = <TRUE|FALSE>,
  FLUSH_POLICY = '<rows:N|interval:N|rows:N,interval:N>',
  TTL_SECONDS = <seconds>,
  EVICTION_STRATEGY = '<time_based|size_based|hybrid>',
  MAX_STREAM_SIZE_BYTES = <bytes>,
  COMPRESSION = '<none|snappy|zstd>'
)];

Table options are type-specific:

USER: STORAGE_ID, USE_USER_STORAGE, FLUSH_POLICY, COMPRESSION
SHARED: STORAGE_ID, FLUSH_POLICY, COMPRESSION
STREAM: TTL_SECONDS, EVICTION_STRATEGY, MAX_STREAM_SIZE_BYTES

COMPRESSION accepts only none, snappy, and zstd, and is valid only for USER and SHARED tables. It controls the Parquet codec used when table data is flushed or compacted into cold-storage segments. none writes uncompressed Parquet pages, snappy is the default fast codec, and zstd uses Zstandard level 1 for better density with modest CPU cost. This setting is separate from WebSocket gzip and RocksDB compression. STREAM tables use hot stream log storage and do not accept table Parquet compression.

Examples:

CREATE TABLE app.messages (
  id BIGINT PRIMARY KEY DEFAULT SNOWFLAKE_ID(),
  conversation_id BIGINT NOT NULL,
  sender TEXT NOT NULL,
  role TEXT NOT NULL DEFAULT 'user',
  content TEXT NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT NOW()
) WITH (
  TYPE = 'USER',
  STORAGE_ID = 'local',
  USE_USER_STORAGE = false,
  FLUSH_POLICY = 'rows:1000,interval:60',
  COMPRESSION = 'snappy'
);

CREATE SHARED TABLE app.config (
  key TEXT PRIMARY KEY,
  value TEXT NOT NULL,
  updated_at TIMESTAMP DEFAULT NOW()
) WITH (
  COMPRESSION = 'zstd'
);

CREATE STREAM TABLE app.events (
  event_id TEXT PRIMARY KEY,
  payload TEXT,
  created_at TIMESTAMP DEFAULT NOW()
) WITH (
  TTL_SECONDS = 30,
  EVICTION_STRATEGY = 'hybrid',
  MAX_STREAM_SIZE_BYTES = 1048576
);

ALTER TABLE

ALTER TABLE [<namespace>.]<table_name> ADD COLUMN <name> <type> [NOT NULL|NULL] [DEFAULT <value>];
ALTER TABLE [<namespace>.]<table_name> DROP COLUMN <name>;
ALTER TABLE [<namespace>.]<table_name> MODIFY COLUMN <name> <type> [NOT NULL|NULL];
ALTER TABLE [<namespace>.]<table_name> SET TBLPROPERTIES (<table_option> = <value>, ...);

SET TBLPROPERTIES supports the same type-specific persisted options as CREATE TABLE. Use FLUSH_POLICY = NULL to clear a user/shared flush policy.

Examples:

ALTER TABLE app.config
  SET TBLPROPERTIES (COMPRESSION = 'zstd');

ALTER TABLE app.messages
  SET TBLPROPERTIES (FLUSH_POLICY = 'rows:5000', USE_USER_STORAGE = true);

ALTER TABLE app.events
  SET TBLPROPERTIES (
    TTL_SECONDS = 3600,
    EVICTION_STRATEGY = 'size_based',
    MAX_STREAM_SIZE_BYTES = 1048576
  );

Shared tables always use FORCE row-level security. Creating a shared table without CREATE POLICY is default-deny for User and Service (zero rows on SELECT; writes fail). System and DBA bypass RLS. ACCESS_LEVEL is not a table option; grant access with CREATE POLICY.

DROP TABLE

DROP TABLE [IF EXISTS] [<namespace>.]<table_name>;
DROP USER TABLE [IF EXISTS] [<namespace>.]<table_name>;
DROP SHARED TABLE [IF EXISTS] [<namespace>.]<table_name>;
DROP STREAM TABLE [IF EXISTS] [<namespace>.]<table_name>;

CREATE INDEX / DROP INDEX

Scalar secondary indexes are equality prefix scans on USER and SHARED tables. Vector indexes stay on the no-parentheses USING COSINE|L2|DOT form.

CREATE [UNIQUE] INDEX [IF NOT EXISTS] <index_name>
  ON [<namespace>.]<table_name> (<column> [, <column> ...]);

ALTER TABLE [<namespace>.]<table_name>
  CREATE [UNIQUE] INDEX [IF NOT EXISTS] <index_name> (<column> [, <column> ...]);

ALTER TABLE [<namespace>.]<table_name> DROP INDEX [IF EXISTS] <index_name>;

Chat and membership lookups:

CREATE INDEX idx_messages_conversation ON app.messages (conversation_id);
CREATE INDEX idx_conversation_members_user ON app.conversation_members (user_id);

CREATE / ALTER / DROP POLICY

Row-level security applies to every shared-table scan, write, live event, and file download for User and Service. System and DBA bypass. Policies are permissive (OR); AS RESTRICTIVE is rejected. CURRENT_USER is bound after plan-cache lookup, so the same cached plan can return different rows for Alice and Bob.

TO selects which roles the policy applies to:

TO user — end-user sessions only
TO service — service-account sessions only
TO user, service — both authenticated principals
TO PUBLIC (or omit TO) — every role subject to RLS (user and service)

-- SELECT: end users see only their own documents
CREATE POLICY owner_read ON app.documents
  FOR SELECT TO user
  USING (owner_id = CURRENT_USER);

-- SELECT: membership subquery (same IR as EXISTS)
CREATE POLICY member_read ON app.messages
  FOR SELECT TO user
  USING (
    group_id IN (
      SELECT group_id FROM app.group_members
      WHERE user_id = CURRENT_USER
    )
  );

-- SELECT: service accounts can read every published row
CREATE POLICY service_published_read ON app.documents
  FOR SELECT TO service
  USING (status = 'published');

-- SELECT: both user and service share the same visibility rule
CREATE POLICY tenant_read ON app.events
  FOR SELECT TO user, service
  USING (tenant_id = CURRENT_USER);

-- SELECT: PUBLIC = user and service (same as TO user, service here)
CREATE POLICY public_catalog_read ON app.catalog
  FOR SELECT TO PUBLIC
  USING (is_public = true);

-- DML: separate policies per command, or one FOR ALL
CREATE POLICY owner_insert ON app.documents
  FOR INSERT TO user
  WITH CHECK (owner_id = CURRENT_USER);

CREATE POLICY owner_update ON app.documents
  FOR UPDATE TO user
  USING (owner_id = CURRENT_USER)
  WITH CHECK (owner_id = CURRENT_USER);

CREATE POLICY owner_delete ON app.documents
  FOR DELETE TO user
  USING (owner_id = CURRENT_USER);

CREATE POLICY service_full ON app.documents
  FOR ALL TO service
  USING (true)
  WITH CHECK (true);

ALTER POLICY owner_read ON app.documents
  USING (owner_id = CURRENT_USER);

DROP POLICY owner_read ON app.documents;

EXISTS and IN (SELECT … WHERE principal = CURRENT_USER) compile to the same membership relation. Covering primary keys should be (principal, relation_key) so PointGuard can probe without a full membership scan. Client WHERE clauses, including OR true, cannot bypass RLS: authorized MVCC winners are selected first.

CREATE VIEW

CREATE VIEW [<namespace>.]<view_name> AS <select_query>;
CREATE VIEW [<namespace>.]<view_name> (<column1>, <column2>, ...) AS <select_query>;

SHOW TABLES

SHOW TABLES;
SHOW TABLES IN <namespace>;
SHOW TABLES IN NAMESPACE <namespace>;

DESCRIBE TABLE

DESCRIBE TABLE [<namespace>.]<table_name>;
DESC TABLE [<namespace>.]<table_name>;
DESCRIBE TABLE [<namespace>.]<table_name> HISTORY;

SHOW STATS FOR TABLE

SHOW STATS FOR TABLE [<namespace>.]<table_name>;

Types

Named types are PostgreSQL-style composites, enums, and table row types. They are the same type system used by table columns and procedure signatures. CREATE TYPE, ALTER TYPE, and DROP TYPE require a DBA or System role.

Creating a table also catalogs an implicit row type with the same schema-qualified name as the table (app.users is both the table and the row type). CREATE TYPE ... FROM TABLE adds an optional second name, usually singular, bound to that live row type. Creating a topic catalogs an implicit payload type with the same schema-qualified name as the topic. That payload is a tagged union of the topic's ADD SOURCE tables, discriminated by _table (the wire form namespace:table).

CREATE TYPE

CREATE TYPE [IF NOT EXISTS] [<schema>.]<name> AS (
  <field> <type> [NOT NULL] [NONEMPTY] [, ...]
) [COMMENT ['<text>' | = '<text>']];

CREATE TYPE [IF NOT EXISTS] [<schema>.]<name> AS ENUM ('<label>' [, ...])
  [COMMENT ['<text>' | = '<text>']];

CREATE TYPE [IF NOT EXISTS] [<schema>.]<name> FROM TABLE [<schema>.]<table>
  [COMMENT ['<text>' | = '<text>']];

Examples:

CREATE TYPE app.address AS (
  city TEXT NOT NULL,
  country TEXT NOT NULL
) COMMENT 'Postal address';

CREATE TYPE app.message_status AS ENUM ('sent', 'delivered', 'read');

CREATE SHARED TABLE app.users (
  id TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  home app.address
);

CREATE TYPE app.user FROM TABLE app.users;

Rules:

Unqualified names use the current default namespace.
Fields are nullable unless NOT NULL is present.
NONEMPTY is allowed on TEXT, BYTES, and arrays, and requires NOT NULL.
Arrays are one-dimensional (T[]).
Fields may reference scalars, enums, named composites, table row types, topic payload types, or JSON/JSONB. Named field types must already exist. A composite cannot reference itself.
Enum labels are case-sensitive, unique, non-empty, at most 63 characters, and cannot contain :. 'Active' and 'active' are distinct.
Composite field names must be unique.
FROM TABLE aliases must live in the same schema as the table. A table may have at most one explicit row-type alias.
A standalone type cannot reuse a table's implicit row-type name or a topic's implicit payload-type name.
AS UNION and AS INTERFACE are reserved and rejected. Multi-source topics still produce a tagged union of their source row types (see Topics).
Catalog rows live in system.types and system.type_fields. Optional COMMENT text is stored on system.types.comment and is documentation only (it does not change the contract hash).

Use named types in procedure signatures and as nested table columns:

CREATE PROCEDURE app.get_user(user_id TEXT NOT NULL)
RETURNS ROW TYPE app.users
LANGUAGE JAVASCRIPT
AS $$
  return ctx.db.sql("SELECT * FROM app.users WHERE id = $1", [input]);
$$;

CREATE PROCEDURE chat.on_message(payload chat.ai_inbox NOT NULL)
SECURITY DEFINER;

COMMENT ON

PostgreSQL COMMENT ON updates catalog documentation after create. KalamDB accepts type and procedure targets. There are no procedure overloads, so the procedure name is enough.

COMMENT ON TYPE [<schema>.]<name> IS '<text>';
COMMENT ON TYPE [<schema>.]<name> IS NULL;
COMMENT ON PROCEDURE [<schema>.]<name> IS '<text>';
COMMENT ON PROCEDURE [<schema>.]<name> IS NULL;

COMMENT ON TABLE and other object kinds are rejected. IS NULL clears the comment. Generated TypeScript includes the text as JSDoc so schema can be fed to an agent.

ALTER TYPE

ALTER TYPE [<schema>.]<name> ADD ATTRIBUTE <field> <type> [NOT NULL];
ALTER TYPE [<schema>.]<name> DROP ATTRIBUTE <field>;
ALTER TYPE [<schema>.]<name> RENAME ATTRIBUTE <from> TO <to>;
ALTER TYPE [<schema>.]<name> ALTER ATTRIBUTE <field> TYPE <type>;
ALTER TYPE [<schema>.]<name> ADD VALUE [IF NOT EXISTS] '<label>' [BEFORE | AFTER '<neighbor>'];
ALTER TYPE [<schema>.]<name> SET SCHEMA <schema>;

Attribute operations apply to composite types. DROP ATTRIBUTE CASCADE is not supported; drop dependents first. ADD VALUE applies to enums only and inserts the label at the end, or next to BEFORE / AFTER an existing label. IF NOT EXISTS is a no-op when the label is already present. SET SCHEMA applies to named composites and enums only, not implicit row types or FROM TABLE aliases.

DROP TYPE

DROP TYPE [<schema>.]<name>;
DROP TYPE IF EXISTS [<schema>.]<name>;
DROP TYPE [<schema>.]<name> RESTRICT;

DROP TYPE fails while another type, alias, or procedure still references it. Implicit table row types cannot be dropped; drop the table instead. Implicit topic payload types cannot be dropped; drop the topic instead. DROP TYPE CASCADE is not supported.

Data Manipulation (DML)

INSERT

INSERT INTO [<namespace>.]<table_name> (<column1>, <column2>, ...)
VALUES (<value1>, <value2>, ...);

INSERT INTO [<namespace>.]<table_name> (<column1>, <column2>, ...)
VALUES
  (<value1a>, <value2a>, ...),
  (<value1b>, <value2b>, ...);

UPDATE

UPDATE [<namespace>.]<table_name>
SET <column1> = <value1>, <column2> = <value2>
WHERE <condition>;

DELETE

DELETE FROM [<namespace>.]<table_name>
WHERE <condition>;

SELECT

SELECT <columns>
FROM [<namespace>.]<table_name>
[WHERE <condition>]
[GROUP BY <expr>]
[ORDER BY <expr>]
[LIMIT <n>];

Procedures

Server procedures are transactional business operations invoked with CALL. They are not SQL expression functions: SNOWFLAKE_ID(), NOW(), and similar built-ins stay in SELECT lists. A procedure runs in a V8 isolate, can read and write tables, publish topics, call other procedures, and return a typed value.

There is one procedure type (CREATE PROCEDURE) and three invocation origins:

Origin	How it runs	`ctx.http`	`ctx.source.kind`
SQL / PGWire	`CALL schema.name(...)`	`null`	`"call"`
HTTP	`POST /v1/functions/{namespace}/{procedure}`	present	`"call"`
Topic trigger	`CREATE TRIGGER ... ON TOPIC ... EXECUTE PROCEDURE`	`null`	`"topic"`

The same procedure can be called from SQL and HTTP. Topic handlers usually take the topic PAYLOAD. Table AFTER ROW triggers and scheduled/cron procedures are not in this release; react to writes by routing the table into a topic.

CREATE PROCEDURE, DROP PROCEDURE, GRANT EXECUTE, and REVOKE EXECUTE require a DBA or System role. CALL is allowed for any authenticated role that holds EXECUTE on that procedure.

CREATE PROCEDURE

CREATE [OR REPLACE] PROCEDURE [<namespace>.]<name> (
  <arg> <type> [NOT NULL] [NONEMPTY] [, ...]
)
[RETURNS [ROW TYPE] <type>]
[LANGUAGE <JAVASCRIPT|JS|TYPESCRIPT|TS>]
[SECURITY INVOKER | SECURITY DEFINER]
[COMMENT ['<text>' | = '<text>']]
[AS $$
  <javascript_body>
$$];

Rules:

One procedure per namespace.name. Overloads are rejected.
Unqualified names use the current default namespace (USE / session namespace).
The namespace must already exist (CREATE NAMESPACE). Creating a procedure in a missing namespace fails.
Parameters are IN only. They are nullable unless NOT NULL is present. Named parameter and RETURNS types must already exist.
SECURITY INVOKER is the default. The procedure runs as the caller; RLS and CURRENT_USER use that principal.
SECURITY DEFINER runs as the procedure owner for that frame. The original actor is preserved for audit. Table privileges and RLS use the owner.
LANGUAGE is required only when a body is present. Project-backed procedures omit LANGUAGE and AS. LANGUAGE JAVASCRIPT and JS are parsed in V8 at CREATE time (syntax + host-API lint) and stored as an inline artifact. LANGUAGE TYPESCRIPT and TS are stored for introspection and are not executed until a project deployment supplies compiled JavaScript. Dollar-quoted ($$ ... $$) or string-literal bodies are accepted.
LANGUAGE SQL catalogs the routine but CALL is not supported.
CREATE OR REPLACE replaces an existing procedure. Without OR REPLACE, a duplicate name fails. The success message says created or replaced and includes the inline source hash / artifact id. This statement does not create a function module revision; those come from kalam deploy. Omitting COMMENT on CREATE OR REPLACE keeps the previous comment.
Source-file mapping (AS 'src/api/orders.ts', 'createOrder') is rejected. Project-backed procedures are bound by generated procedure.<schema>.<method> builders and named exports, one scaffold file per procedure.
COMMENT / COMMENT = is optional documentation stored on system.routines.comment and exposed on system.procedures.comment. It does not change the contract hash. COMMENT ON PROCEDURE can set or clear it later. The clause may appear among RETURNS / LANGUAGE / SECURITY / AS, including after the body.

The body is wrapped as (ctx, input) => { ... } unless it already defines function kalamInvoke(name, args). input is always a named object matching the procedure parameters ({ x } for inc(x INT), { first, last } for greet(first TEXT, last TEXT)). A one-argument SQL CALL is packed into that object. Nested ctx.functions.<ns>.<method>(input) already passes the object and is not wrapped again.

Host objects injected into ctx:

Host	Purpose
`ctx.source.kind`	Always `"call"` for SQL, REST, and PGWire invocation. Clients cannot supply this.
`ctx.db.query(sql, params?)` / `ctx.db.execute(sql, params?)`	Nested SQL on the same request transaction. `query` returns rows; `execute` returns a result. `params` is an optional array bound as `$1`..`$n`. STREAM `INSERT`/`UPDATE`/`DELETE` autocommit outside that transaction so live progress rows are visible before the procedure commits. A client `BEGIN` still rejects STREAM DML.
`ctx.sleep(ms)`	Pause up to 60s (also bounded by the procedure deadline). Honors cancellation.
`ctx.functions.call(name, args)`	Nested procedure call. `name` may be `namespace.name` or unqualified.
`ctx.topics.publish(topic, payload)`	Stage a typed topic publish. Commit flushes it; rollback drops it.
`ctx.log.info/debug/warn/error(...)`	Structured logs to `system.procedure_logs` (`outcome=log`, `channel=ctx.log`) and the process logger (`target: kalamdb::functions`). `ctx.log` is an object, not a function.
`console.log/info/debug/warn/error(...)`	Same destinations with `channel=console`. `console.log` maps to info. Uncaught exceptions and unhandled Promise rejections are written as `outcome=error` with the V8 message/stack.
`ctx.http.request.method/path/headers.get/query.get`	HTTP-root only. `Authorization`, `Proxy-Authorization`, and `Cookie` are not readable. SQL/`CALL` and topic origins set `ctx.http` to null.
`ctx.http.response.status/header/contentType`	HTTP-root only; nested procedures cannot mutate the response. `Connection`, `Transfer-Encoding`, `Content-Length`, and `Host` are rejected.

Examples:

CREATE OR REPLACE PROCEDURE app.echo(msg TEXT)
LANGUAGE JAVASCRIPT
AS $$
  ctx.log.info('echo', { msg: input.msg });
  return input.msg;
$$;

CREATE OR REPLACE PROCEDURE app.inc(x INT)
LANGUAGE JAVASCRIPT
AS $$
  return input.x + 1;
$$;

CREATE OR REPLACE PROCEDURE app.plus_one(x INT)
LANGUAGE JAVASCRIPT
AS $$
  return ctx.functions.call('app.inc', [input]);
$$;

CREATE OR REPLACE PROCEDURE app.place_order(p_id INT)
LANGUAGE JAVASCRIPT
SECURITY DEFINER
AS $$
  ctx.db.execute("INSERT INTO app.orders (id, status) VALUES ($1, 'ok')", [input.p_id]);
  ctx.topics.publish('app.events', { id: input, status: 'ok' });
  return { id: input, status: 'ok' };
$$;

DROP PROCEDURE

DROP PROCEDURE [<namespace>.]<name>;
DROP PROCEDURE IF EXISTS [<namespace>.]<name>;

GRANT / REVOKE EXECUTE

EXECUTE is independent of table grants and row-level security. It only decides whether a principal may enter the procedure. Nested ctx.db.sql still uses the effective principal's table privileges and RLS.

GRANT EXECUTE ON PROCEDURE [<namespace>.]<name> TO <PUBLIC|user|service|<role>|anonymous>;
REVOKE EXECUTE ON PROCEDURE [<namespace>.]<name> FROM <PUBLIC|user|service|<role>|anonymous>;

Rules:

New procedures have no PUBLIC execute privilege. Grant access deliberately.
The owner, DBA, and System roles may always CALL a procedure they own or administer.
TO user allows end-user sessions. TO service allows service accounts. TO PUBLIC allows every authenticated role except anonymous.
Anonymous sessions cannot execute procedures unless GRANT EXECUTE ... TO anonymous is explicit. PUBLIC does not include anonymous. REST POST /v1/functions/{namespace}/{procedure} uses a named JSON object, a positional JSON array, or empty; the success body is the procedure return value (not {status,result}).
A user can CALL a SECURITY DEFINER API without holding INSERT on the underlying table, as long as they have EXECUTE and the owner does.

REVOKE EXECUTE ON PROCEDURE app.echo FROM PUBLIC;
GRANT EXECUTE ON PROCEDURE app.echo TO user;
CALL app.echo('ok');
REVOKE EXECUTE ON PROCEDURE app.echo FROM user;

CALL

CALL [<namespace>.]<name>();
CALL [<namespace>.]<name>(<arg> [, ...]);
CALL [<namespace>.]<name>($1, $2);

SQL CALL arguments are positional literals or 1-based placeholders:

NULL, TRUE, FALSE
integers and floats
single-quoted strings
$1, $2, ... bound from the prepared-statement parameter list

Named composite arguments belong on the REST body, not in SQL CALL. Unqualified CALL ping() uses the current default namespace.

CALL checks the procedure contract before V8 runs:

Argument count must match the signature. A single JSON object that contains every parameter name (nested ctx.functions.call(name, [input])) is accepted and is not wrapped again.
NOT NULL parameters and return values reject JSON/SQL null.
Enum arguments and returns must be one of the type's labels (case-sensitive).
Composite arguments must be objects. Missing NOT NULL fields fail; extra fields are ignored.
Array NONEMPTY rejects []. Invalid arguments return INVALID_ARGUMENTS (HTTP 400).

The result is one column named result. A root CALL starts a request transaction when none is open. Nested ctx.functions.call and ctx.db.sql share that transaction. BEGIN; CALL ...; ROLLBACK; drops nested inserts and staged topic publishes together.

CALL app.echo('hello');
CALL app.plus_one(41);
CALL app.place_order(7);

BEGIN;
CALL app.place_order(99);
ROLLBACK;

PGWire and the SQL HTTP API run the same CALL statement through SqlExecutor.

REST invocation

Every executable procedure is also available over HTTP. This is the same runtime as SQL CALL, not a second controller contract.

POST /v1/functions/{namespace}/{procedure}
Authorization: Bearer <token>
Content-Type: application/json

The body is a JSON object of named parameters, a JSON array of positional values, or empty/null for a procedure with no arguments. A procedure with exactly one JSON parameter also accepts the raw JSON object as the argument when the object does not contain that parameter name:

POST /v1/functions/app/send_message
Content-Type: application/json

{ "conversationId": "c1", "text": "hello" }

That is equivalent to { "body": { "conversationId": "c1", "text": "hello" } } when the signature is send_message(body JSON). Named wrap and positional [{ ... }] still work.

Success is the procedure return value itself (not {status, result}). A RETURNS JSON procedure returns a JSON object or array:

{ "ok": true, "conversationId": "c1", "text": "hello" }

A RETURNS TEXT procedure such as echo returns the scalar:

"rest"

Clients must not send context, ctx, source, actor, or tx. The host builds those from the authenticated session. HTTP-root procedures may set ctx.http.status and ctx.http.header; those apply only to this REST response.

Catalog rows live in system.routines, system.routine_parameters, and system.routine_grants. Operators can read the joined catalog from system.procedures (signature, inline/module/missing implementation, current module_id/revision_id, and comment). Deployed modules are stored in system.function_modules / system.function_revisions / system.function_artifacts; the operator views are system.modules and system.module_revisions (is_current is derived from the module pointer). Live runtime is system.module_instances, system.active_procedure_runs, and system.procedure_logs. Invocation and V8 console.*/ctx.log.* records are stored under {data_path}/functions/runtime/<procedure_id>/logs/ (default ./data/functions/runtime/<procedure_id>/logs/procedures.jsonl). The system.procedure_logs view tails those files from EOF; it does not load a full log into memory. Compiled module bytes live under {data_path}/functions/artifacts/.

Scheduled invocations set origin = 'schedule' and schedule_id to the scheduler identity, and reuse the schedule run_id as request_id / execution_id so nested CALL and SQL can correlate later. Manual SQL and HTTP calls of the same procedure omit schedule_id.

system.procedure_logs is also the runtime audit trail (channel = 'lifecycle', actor = 'system'): deployed when an active function set is published, and per isolate created (cold start), dropped (with the reason and total calls served), plus reused/idle at most once per minute per isolate so steady-state warm traffic does not flood the log.

SELECT * FROM system.procedures;
SELECT * FROM system.module_revisions WHERE is_current;
SELECT * FROM system.module_instances;
SELECT * FROM system.procedure_logs
WHERE procedure_id = 'chat.send_message'
ORDER BY timestamp DESC;
SELECT timestamp, outcome, message FROM system.procedure_logs
WHERE channel = 'lifecycle' AND module_id = 'backend'
ORDER BY timestamp DESC;
SELECT timestamp, outcome, duration_ms, execution_id
FROM system.procedure_logs
WHERE origin = 'schedule' AND schedule_id = 'reports.daily_summary'
ORDER BY timestamp DESC;

Topic triggers

Durable topic delivery is CREATE TRIGGER … ON TOPIC … EXECUTE PROCEDURE, not table AFTER ROW triggers.

CREATE TRIGGER chat.process_message
  ON TOPIC chat.message_created
  EXECUTE PROCEDURE chat.on_message_created(PAYLOAD)
  WITH (
    principal = 'system',
    start = 'latest',
    retries = 5,
    retry_backoff = '1s',
    concurrency = 1
  );

ALTER TRIGGER chat.process_message DISABLE;
ALTER TRIGGER chat.process_message ENABLE;
DROP TRIGGER IF EXISTS chat.process_message;

start is latest (default) or earliest and is captured when the trigger is created. The dispatcher consumes each partition in order, ACKs after a successful commit, retries with backoff, then writes system.trigger_attempts status dlq. Nested ctx.functions.call keeps ctx.source.kind = "topic" and sets ctx.parent to the trigger procedure. Disabling or dropping a trigger keeps committed offsets.

Catalog rows live in system.triggers and system.trigger_attempts. Consumer group id is trigger:{trigger_id}.

Execute As

EXECUTE AS syntax is wrapper-only. It switches USER-table or STREAM-table execution to a target user ID only when the authenticated actor role is allowed to target that ID's cached role class.

EXECUTE AS '<user_id>' (
  <single_statement>
);

Examples:

EXECUTE AS 'user_123' (
  SELECT * FROM app.messages WHERE conversation_id = 42
);

Rules:

The wrapper must contain exactly one SQL statement.
The target user ID must be single-quoted.
System users may target system, dba, service, and user accounts.
DBA users may target dba, service, and user accounts.
Service users may target service and user accounts.
Regular users may only use self-targeted EXECUTE AS '<user_id>' as a no-op identity boundary.
The wrapper is valid for USER and STREAM tables; shared tables use their table policy directly.
Target role checks are hot-path cached: service, DBA, and system user IDs are tracked in memory from system.users; soft-deleted privileged IDs stay classified by their persisted role, and target IDs not present in that privileged cache are treated as regular users.
Legacy inline ... AS USER 'name' syntax is not supported.

User Management

CREATE USER

CREATE USER '<username>'
  WITH <PASSWORD '<password>' | OIDC '<oidc_json>'>
  ROLE <user|service|dba|system>
  [EMAIL '<email>']
  [STORAGE_MODE <table|region>]
  [STORAGE_ID '<storage_id>'];

WITH OIDC creates an external OIDC user. The payload must contain the OIDC issuer and subject. WITH OAUTH is still accepted as a compatibility alias for older scripts.

CREATE USER 'provider-subject'
  WITH OIDC '{"issuer": "https://idp.example.com/realms/kalamdb", "subject": "provider-subject"}'
  ROLE user
  EMAIL 'alice@example.com';

For OIDC users, the CREATE USER id must match the OIDC subject. KalamDB uses that subject directly as the authenticated user id.

ALTER USER

ALTER USER '<username>' SET PASSWORD '<new_password>';
ALTER USER '<username>' SET ROLE <user|service|dba|system>;
ALTER USER '<username>' SET EMAIL '<new_email>';
ALTER USER '<username>' SET STORAGE_MODE <table|region>;
ALTER USER '<username>' SET STORAGE_ID '<storage_id>';
ALTER USER '<username>' SET STORAGE_ID NULL;

DROP USER

DROP USER '<username>';
DROP USER IF EXISTS '<username>';

Storage Commands

CREATE STORAGE

CREATE STORAGE <storage_id>
  TYPE '<filesystem|s3|gcs|azure>'
  [NAME '<storage_name>']
  [DESCRIPTION '<description>']
  [PATH '<path>']
  [BUCKET '<bucket_or_s3_url>']
  [REGION '<region>']
  [BASE_DIRECTORY '<path_or_url>']
  [SHARED_TABLES_TEMPLATE '<template>']
  [USER_TABLES_TEMPLATE '<template>']
  [CREDENTIALS '<json_credentials>']
  [CONFIG '<json_config>'];

Examples:

CREATE STORAGE local
  TYPE 'filesystem'
  PATH './data';

CREATE STORAGE s3_prod
  TYPE 's3'
  BUCKET 'my-bucket'
  REGION 'us-west-2'
  CREDENTIALS '{"access_key_id":"...","secret_access_key":"..."}';

ALTER STORAGE

ALTER STORAGE <storage_id>
  [SET NAME '<new_name>']
  [SET DESCRIPTION '<new_description>']
  [SET SHARED_TABLES_TEMPLATE '<new_template>']
  [SET USER_TABLES_TEMPLATE '<new_template>']
  [SET CONFIG '<json_config>'];

DROP STORAGE

DROP STORAGE <storage_id>;
DROP STORAGE IF EXISTS <storage_id>;

SHOW STORAGES

SHOW STORAGES;

STORAGE CHECK

STORAGE CHECK <storage_id>;
STORAGE CHECK <storage_id> EXTENDED;

STORAGE FLUSH

STORAGE FLUSH TABLE <namespace>.<table_name>;
STORAGE FLUSH ALL IN <namespace>;
STORAGE FLUSH ALL IN NAMESPACE <namespace>;
STORAGE FLUSH ALL;

STORAGE COMPACT

STORAGE COMPACT TABLE <namespace>.<table_name>;
STORAGE COMPACT ALL IN <namespace>;
STORAGE COMPACT ALL IN NAMESPACE <namespace>;
STORAGE COMPACT ALL;

SHOW MANIFEST

SHOW MANIFEST;

Job Commands

KILL JOB

KILL JOB '<job_id>';

Live Query Commands

SUBSCRIBE TO

SUBSCRIBE TO <namespace>.<table_name>
[WHERE <condition>]
[OPTIONS (last_rows=<n>, batch_size=<n>, from_seq_id=<n>)];

KILL LIVE QUERY

KILL LIVE QUERY '<subscription_id>';

Topic / Consume Commands

CREATE TOPIC

CREATE TOPIC <topic_name>;
CREATE TOPIC <topic_name> PARTITIONS <count>;

DROP TOPIC

DROP TOPIC [IF EXISTS] <topic_name>;

CLEAR TOPIC

CLEAR TOPIC <topic_name>;

ALTER TOPIC ADD SOURCE

ALTER TOPIC <topic_name>
ADD SOURCE <table_name_or_namespace.table_name>
ON <INSERT|UPDATE|DELETE>
[WHERE <filter_expression>]
[WITH (payload = '<key|full|diff>')];

WHERE is evaluated against the row routed for the selected operation. That lets you publish only a subset of inserts or updates into a worker topic.

Creating a topic catalogs an implicit payload type with the same name. Each ADD SOURCE table (default payload = 'full') is one arm of that type. Full payloads include the source row plus _table (namespace:table) so a trigger procedure can narrow the union. Declare the procedure argument as the topic name instead of JSON:

CREATE TOPIC chat.ai_inbox;
ALTER TOPIC chat.ai_inbox ADD SOURCE chat.messages ON INSERT;
ALTER TOPIC chat.ai_inbox ADD SOURCE chat.direct_messages ON INSERT;

CREATE PROCEDURE chat.on_message(payload chat.ai_inbox NOT NULL);
CREATE TRIGGER chat.process_message
  ON TOPIC chat.ai_inbox
  EXECUTE PROCEDURE chat.on_message(PAYLOAD);

Generated TypeScript for that topic:

type ChatAiInbox =
  | ({ _table: "chat:direct_messages" } & ChatDirectMessages)
  | ({ _table: "chat:messages" } & ChatMessages);

A procedure may also RETURNS the topic payload type when it inserts into one of those source tables and returns the tagged row.

Example: publish task-cancellation work only when a task is already cancelled on insert, or becomes cancelled on update.

ALTER TOPIC app.task_cancellations
ADD SOURCE app.tasks
ON INSERT
WHERE cancelled = true
WITH (payload = 'full');

ALTER TOPIC app.task_cancellations
ADD SOURCE app.tasks
ON UPDATE
WHERE cancelled = true
WITH (payload = 'full');

CONSUME FROM

CONSUME FROM <topic_name>
[GROUP '<group_id>']
[FROM <LATEST|EARLIEST|offset>]
[LIMIT <count>];

Examples:

CONSUME FROM app.new_messages;
CONSUME FROM app.new_messages GROUP 'worker-1' FROM EARLIEST LIMIT 100;
CONSUME FROM app.new_messages GROUP 'worker-1' FROM 250;

CONSUME FROM ... GROUP ... reserves a delivery range for the group but does not commit progress. After processing the returned rows, commit progress with ACK. If the caller does not ACK before the configured topic visibility timeout, the unacked range can be delivered again to the same group.

ACK

ACK <topic_name>
GROUP '<group_id>'
[PARTITION <partition_id>]
UPTO OFFSET <offset>;

RESET CONSUMER GROUP

RESET CONSUMER GROUP '<group_id>'
ON <topic_name>
[PARTITION <partition_id>]
TO <next_offset>;

Examples:

RESET CONSUMER GROUP 'worker-1' ON app.new_messages TO 0;
RESET CONSUMER GROUP 'worker-1' ON app.new_messages PARTITION 0 TO 250;

RESET CONSUMER GROUP is admin-only and moves one consumer-group partition to the next offset you specify. It also clears pending in-memory claims for that group partition so the reset takes effect immediately.

Cluster Commands

CLUSTER LIST;
CLUSTER STATUS;
CLUSTER SNAPSHOT;
CLUSTER PURGE --UPTO <index>;
CLUSTER PURGE <index>;
CLUSTER TRIGGER ELECTION;
CLUSTER TRIGGER-ELECTION;
CLUSTER TRANSFER LEADER <node_id>;
CLUSTER TRANSFER-LEADER <node_id>;
CLUSTER STEPDOWN;
CLUSTER STEP-DOWN;
CLUSTER CLEAR;

Backup / Restore Commands

EXPORT USER DATA

EXPORT USER DATA;

SHOW EXPORT

SHOW EXPORT;

SHOW EXPORT returns a download_url URI path such as /v1/exports/<user_id>/<export_id>. Prefix it with your KalamDB server base URL when downloading the finished ZIP over HTTP.

The Admin UI table editor also supports scoped table data transfer for user and shared tables. A user-table export requires a user_id; shared-table export omits the user scope. Table export ZIPs contain committed Parquet segments plus KalamDB manifest metadata, and table import accepts that ZIP format through the Admin UI when the target table already exists with matching columns.

BACKUP DATABASE

BACKUP DATABASE TO '<backup_path>';

<backup_path> is a path on the server filesystem. If it ends with .tar.gz or .tgz, KalamDB writes a single archive file there. Otherwise it writes the backup directory layout directly under that path. BACKUP DATABASE requires a DBA or System role.

RESTORE DATABASE

RESTORE DATABASE FROM '<backup_path>';

<backup_path> is a path on the server filesystem and may point to either a backup directory or a .tar.gz / .tgz archive created by BACKUP DATABASE. The restore job copies Parquet and stream files in place and stages RocksDB into a sibling rocksdb_restore_pending_* directory. A server restart promotes the newest complete staged copy onto the live RocksDB path and deletes leftover staging directories. Incomplete or older unmarked staging dirs are discarded without replacing the live database. RESTORE DATABASE requires a DBA or System role.

Built-in Functions (Common)

These are SQL expression functions for SELECT lists, defaults, and predicates. They are not procedures. Application logic that writes tables or publishes topics uses CALL, not these names.

SELECT SNOWFLAKE_ID();
SELECT UUID_V7();
SELECT ULID();
SELECT CURRENT_USER();
SELECT NOW();

FilesExpand file tree

sql.md

Latest commit

History

sql.md

File metadata and controls

KalamDB SQL Reference

Statement Separator

Namespace Commands

CREATE NAMESPACE

DROP NAMESPACE

ALTER NAMESPACE

USE / SET NAMESPACE

SHOW NAMESPACES

Table DDL

CREATE TABLE (Unified)

ALTER TABLE

DROP TABLE

CREATE INDEX / DROP INDEX

CREATE / ALTER / DROP POLICY

CREATE VIEW

SHOW TABLES

DESCRIBE TABLE

SHOW STATS FOR TABLE

Types

CREATE TYPE

COMMENT ON

ALTER TYPE

DROP TYPE

Data Manipulation (DML)

INSERT

UPDATE

DELETE

SELECT

Procedures

CREATE PROCEDURE

DROP PROCEDURE

GRANT / REVOKE EXECUTE

CALL

REST invocation

Topic triggers

Execute As

User Management

CREATE USER

ALTER USER

DROP USER

Storage Commands

CREATE STORAGE

ALTER STORAGE

DROP STORAGE

SHOW STORAGES

STORAGE CHECK

STORAGE FLUSH

STORAGE COMPACT

SHOW MANIFEST

Job Commands

KILL JOB

Live Query Commands

SUBSCRIBE TO

KILL LIVE QUERY

Topic / Consume Commands

CREATE TOPIC

DROP TOPIC

CLEAR TOPIC

ALTER TOPIC ADD SOURCE

CONSUME FROM

ACK

RESET CONSUMER GROUP

Cluster Commands

Backup / Restore Commands

EXPORT USER DATA

SHOW EXPORT

BACKUP DATABASE

RESTORE DATABASE

Built-in Functions (Common)