Skip to Content
SQL ReferenceData Manipulation

Data Manipulation (DML)

Standard SQL data manipulation commands for reading and writing data.

DML permission and routing behavior depends on table type. For USER, SHARED, STREAM, and SYSTEM access rules, see /docs/server/architecture/table-types.

INSERT

Insert one or more rows into a table:

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

Batch Insert

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

Examples

SQL
-- Single insert with auto-generated IDINSERT INTO chat.messages (conversation_id, sender, content)VALUES (1, 'alice', 'Hello!'); -- Batch insertINSERT INTO chat.messages (conversation_id, sender, role, content)VALUES  (1, 'alice', 'user', 'What is KalamDB?'),  (1, 'assistant', 'assistant', 'KalamDB is a SQL-first realtime database.');

UPDATE

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

Example

SQL
UPDATE chat.messagesSET content = 'Updated message content'WHERE id = 42;

DELETE

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

Example

SQL
DELETE FROM chat.messagesWHERE conversation_id = 1 AND sender = 'bot';

Explicit Transactions

KalamDB stays in autocommit mode by default. Use explicit transaction blocks when you need PostgreSQL-style atomic multi-statement writes.

Syntax

SQL
BEGIN;START TRANSACTION; COMMIT;COMMIT WORK; ROLLBACK;ROLLBACK WORK;

Commit example

SQL
BEGIN; INSERT INTO chat.messages (conversation_id, sender, content)VALUES (7, 'alice', 'draft'); UPDATE chat.messagesSET content = 'published'WHERE conversation_id = 7 AND sender = 'alice'; COMMIT;

Rollback example

SQL
BEGIN; INSERT INTO chat.messages (conversation_id, sender, content)VALUES (8, 'alice', 'temporary'); SELECT *FROM chat.messagesWHERE conversation_id = 8; ROLLBACK;

Within the open transaction, reads see the staged writes from that same transaction. After ROLLBACK, those staged changes are discarded.

Rules and limits

  • Explicit transactions currently support USER and SHARED tables.
  • STREAM tables and system.* tables are rejected inside explicit transactions.
  • DDL statements are not supported inside explicit transactions.
  • Nested BEGIN and savepoints are not supported in this phase.
  • A single /v1/api/sql request can contain multiple sequential BEGIN ... COMMIT or BEGIN ... ROLLBACK blocks.
  • If a /v1/api/sql request ends with an open transaction, KalamDB rolls it back automatically.

To inspect live transaction state, query system.transactions. For pg-extension-specific session state, use system.sessions. See /docs/server/sql-reference/system-views.

For deeper read-query patterns (joins, CTEs, and DataFusion-compatible advanced SELECT usage), see /docs/server/sql-reference/query-data.

Last updated on
Table DDLQuery Data (SELECT & JOIN)