Data Manipulation (DML)

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

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

-- Single insert with auto-generated ID
INSERT INTO chat.messages (conversation_id, sender, content)
VALUES (1, 'alice', 'Hello!');
 
-- Batch insert
INSERT 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 [<namespace>.]<table_name>
SET <column1> = <value1>, <column2> = <value2>
WHERE <condition>;

UPDATE chat.messages
SET content = 'Updated message content'
WHERE id = 42;

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

DELETE FROM chat.messages
WHERE conversation_id = 1 AND sender = 'bot';

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

-- Basic query
SELECT * FROM chat.messages WHERE conversation_id = 1;
 
-- With ordering and limit
SELECT id, sender, content, created_at
FROM chat.messages
WHERE conversation_id = 1
ORDER BY created_at DESC
LIMIT 50;
 
-- Aggregation
SELECT sender, COUNT(*) as message_count
FROM chat.messages
WHERE conversation_id = 1
GROUP BY sender
ORDER BY message_count DESC;