Data Manipulation (DML)
Standard SQL data manipulation commands for reading and writing data.
INSERT
Insert one or more rows into a table:
INSERT INTO [<namespace>.]<table_name> (<column1>, <column2>, ...)VALUES (<value1>, <value2>, ...);Batch Insert
INSERT INTO [<namespace>.]<table_name> (<column1>, <column2>, ...)VALUES (<value1a>, <value2a>, ...), (<value1b>, <value2b>, ...);Examples
-- 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
UPDATE [<namespace>.]<table_name>SET <column1> = <value1>, <column2> = <value2>WHERE <condition>;Example
UPDATE chat.messagesSET content = 'Updated message content'WHERE id = 42;DELETE
DELETE FROM [<namespace>.]<table_name>WHERE <condition>;Example
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
BEGIN;START TRANSACTION; COMMIT;COMMIT WORK; ROLLBACK;ROLLBACK WORK;Commit example
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
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
USERandSHAREDtables. STREAMtables andsystem.*tables are rejected inside explicit transactions.- DDL statements are not supported inside explicit transactions.
- Nested
BEGINand savepoints are not supported in this phase. - A single
/v1/api/sqlrequest can contain multiple sequentialBEGIN ... COMMITorBEGIN ... ROLLBACKblocks. - If a
/v1/api/sqlrequest 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 System Views.
For deeper read-query patterns (joins, CTEs, and DataFusion-compatible advanced SELECT usage), see Query Data (SELECT & JOIN).
Last updated on