Skip to Content
SQL Workflows

SQL Workflows

Many CLI workflows are plain SQL through -c, -f, or the interactive shell. Backslash commands cover CLI-managed operations; this page covers SQL-first workflows.

Follower write forwarding

KalamDB uses Multi-Raft groups — a client can send a write to any reachable node.

BASH
kalam --url http://node-2:2900 --command "INSERT INTO app.messages (id, body) VALUES (101, 'hello')"

Assume effective user user-42 hashes to DataUserShard(7):

  1. Request lands on node 2 even if it is a follower for that shard.
  2. KalamDB classifies SQL and derives the target Raft group from table type and user_id.
  3. User/stream tables route by hashing user_id into cluster.user_shards groups.
  4. Non-leader nodes forward SQL, params, auth, and request id over gRPC to the group leader.
  5. Leader executes, replicates, commits, and returns the result.

The client does not need to discover the leader first.

Multi-Raft routing today

  • One metadata Raft group plus multiple user data groups.
  • User/stream data routes by user_id for write-path locality.
  • Shared tables route to a single shared data group today; partition-by-key is planned.

See clustering and table types.

Impersonation

SQL
EXECUTE AS 'user_123' (  SELECT * FROM app.messages ORDER BY id DESC LIMIT 20);

Or the \as shortcut — see Interactive Commands.

Backup and restore

SQL
BACKUP DATABASE TO '/var/backups/kalamdb-nightly.tar.gz';RESTORE DATABASE FROM '/var/backups/kalamdb-nightly.tar.gz';
  • Paths resolve on the server filesystem, not the CLI machine.
  • .tar.gz / .tgz → single archive; otherwise directory layout.
  • Requires DBA or System role.
  • Track status: SELECT * FROM system.jobs WHERE job_id = 'backupjob';
BASH
kalam -c "BACKUP DATABASE TO '/var/backups/kalamdb-nightly.tar.gz'"kalam -c "RESTORE DATABASE FROM '/var/backups/kalamdb-nightly.tar.gz'"

Export user data

SQL
EXPORT USER DATA;SHOW EXPORT;

SHOW EXPORT returns download_url paths like /v1/exports/<user_id>/<export_id>. Prefix with your server base URL when downloading.

BASH
kalam -c "EXPORT USER DATA;"kalam -c "SHOW EXPORT;" --json

Table export/import (meta-commands)

BASH
kalam -c "\\export app.orders --output ./orders-backup.zip"kalam -c "\\import app.orders_restored ./orders-backup.zip"

Live queries

Same SELECT you would run normally — no separate live-query keyword:

BASH
kalam --subscribe "SELECT * FROM app.messages WHERE conversation_id = 7"

Topic pub/sub SQL

SQL
CREATE TOPIC app.new_messages PARTITIONS 4;ALTER TOPIC app.new_messages ADD SOURCE app.messages ON INSERT WITH (payload = 'full');CONSUME FROM app.new_messages GROUP 'worker-1' FROM EARLIEST LIMIT 100;ACK app.new_messages GROUP 'worker-1' UPTO OFFSET 99;DROP TOPIC app.new_messages;

Use --consume / \consume for terminal convenience; use SQL when scripting explicit offset control.

Last updated on
Interactive CommandsOptions & Timeouts