SQL & JSON
Tables, indexes, parameterized queries, JSON and transactions.
Tables & indexes
Start with familiar relational structures. CobaltDB supports SQL tables, primary keys, constraints and indexes. Use explicit IDs in this example so you can run each statement in sequence.
CREATE TABLE projects (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
language TEXT,
stars INTEGER,
metadata JSON
);
CREATE INDEX idx_language ON projects(language);
INSERT INTO projects VALUES
(1, 'cobaltdb', 'Go', 1280, '{"region":"eu-west"}'),
(2, 'atlas', 'Go', 864, '{"region":"us-east"}');The names and numbers in these examples are illustrative data, not repository statistics.
Queries & parameters
SELECT name, language, stars
FROM projects
WHERE language = 'Go'
ORDER BY stars DESC
LIMIT 10;In Go, bind values using ? placeholders. Keep user input out of SQL string concatenation.
rows, err := db.Query(ctx,
"SELECT name FROM projects WHERE stars > ?", 100)
if err != nil { log.Fatal(err) }
defer rows.Close()
for rows.Next() {
var name string
if err := rows.Scan(&name); err != nil { log.Fatal(err) }
fmt.Println(name)
}JSON, alongside your tables
Use a JSON column for fields that vary by record. Extract nested values while keeping the rest of the query relational.
SELECT name,
JSON_EXTRACT(metadata, '$.region') AS region
FROM projects;
UPDATE projects
SET metadata = JSON_SET(metadata, '$.active', true)
WHERE id = 1;Transactions
Group related statements with BEGIN and COMMIT. Use ROLLBACK when you need to discard the transaction.
CREATE TABLE accounts (id INTEGER PRIMARY KEY, balance INTEGER);
INSERT INTO accounts VALUES (1, 1000), (2, 1000);
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;For application code, the engine also exposes db.Begin(), tx.Exec(), tx.Commit() and tx.Rollback(). Review current concurrency semantics for your workload before deployment.
Keep the reference close
Read the upstream SQL reference for types and basic statements, and the compatibility matrix for tested SQL forms and protocol behavior.