SQL Reference¶

Complete SQL syntax reference for CommitDB.

Data Definition¶

Databases¶

CREATE DATABASE mydb;
DROP DATABASE mydb;
DROP DATABASE IF EXISTS mydb;  -- No error if database doesn't exist
SHOW DATABASES;

Tables¶

CREATE TABLE mydb.users (
    id INT PRIMARY KEY,
    name STRING,
    email STRING,
    age INT,
    score FLOAT,
    bio TEXT,
    active BOOL,
    birth_date DATE,       -- Date only (YYYY-MM-DD)
    created TIMESTAMP,     -- Date + time (YYYY-MM-DD HH:MM:SS)
    metadata JSON          -- JSON object or array
);

DROP TABLE mydb.users;
DROP TABLE IF EXISTS mydb.users;  -- No error if table doesn't exist
SHOW TABLES IN mydb;
DESCRIBE mydb.users;

Indexes¶

CREATE INDEX idx_name ON mydb.users(name);
CREATE UNIQUE INDEX idx_email ON mydb.users(email);
DROP INDEX idx_name ON mydb.users;
SHOW INDEXES ON mydb.users;

Alter Table¶

ALTER TABLE mydb.users ADD COLUMN phone STRING;
ALTER TABLE mydb.users DROP COLUMN phone;
ALTER TABLE mydb.users MODIFY COLUMN name TEXT;
ALTER TABLE mydb.users RENAME COLUMN name TO username;

Views¶

Views are virtual tables defined by a SELECT query. Materialized views cache the query results for faster access.

-- Create a view
CREATE VIEW mydb.active_users AS SELECT * FROM mydb.users WHERE active = 1;

-- Create a materialized view (caches results)
CREATE MATERIALIZED VIEW mydb.user_stats AS 
    SELECT city, COUNT(*) AS count FROM mydb.users GROUP BY city;

-- Query views like tables
SELECT * FROM mydb.active_users;
SELECT * FROM mydb.user_stats;

-- Refresh materialized view after underlying data changes
REFRESH VIEW mydb.user_stats;

-- Show views in database
SHOW VIEWS IN mydb;

-- Drop views (works for both regular and materialized)
DROP VIEW mydb.active_users;
DROP VIEW IF EXISTS mydb.user_stats;

Data Manipulation¶

Insert¶

-- Single row
INSERT INTO mydb.users (id, name, email) VALUES (1, 'Alice', 'alice@example.com');

-- With timestamp
INSERT INTO mydb.users (id, name, created) VALUES (2, 'Bob', NOW());

-- Float and boolean literals (no quotes needed)
INSERT INTO mydb.users (id, name, score, active) VALUES (3, 'Charlie', 4.5, true);

-- Bulk insert (multiple rows)
INSERT INTO mydb.users (id, name, email) VALUES 
    (4, 'Diana', 'diana@example.com'),
    (5, 'Eve', 'eve@example.com');

Select¶

SELECT * FROM mydb.users;
SELECT name, email FROM mydb.users WHERE age > 25;
SELECT * FROM mydb.users ORDER BY name ASC LIMIT 10 OFFSET 5;
SELECT DISTINCT city FROM mydb.users;

Update & Delete¶

-- Update by primary key (fast path)
UPDATE mydb.users SET name = 'Bob' WHERE id = 1;

-- Update multiple rows by any column
UPDATE mydb.users SET status = 'inactive' WHERE age > 30;
UPDATE mydb.users SET verified = true WHERE email LIKE '%@company.com';

-- Delete by primary key (fast path)
DELETE FROM mydb.users WHERE id = 1;

-- Delete multiple rows by any column
DELETE FROM mydb.logs WHERE level = 'debug';
DELETE FROM mydb.sessions WHERE created < '2024-01-01';

Time-Travel Queries¶

Query data as it existed at a specific transaction using the AS OF clause:

-- Query data at a specific transaction (commit hash)
SELECT * FROM mydb.users AS OF 'abc1234';

-- Works with all SELECT clauses
SELECT name, email FROM mydb.users AS OF 'abc1234' WHERE active = 1;

-- Use full or abbreviated transaction IDs
SELECT * FROM mydb.users AS OF '93ef5b7512898a585dbc33881a2897b97bac4c05';
SELECT * FROM mydb.users AS OF '93ef5b75';

-- Works on views too
SELECT * FROM mydb.myview AS OF 'abc1234';

Transaction IDs are returned by all data-modifying operations (INSERT, UPDATE, DELETE).

Queries¶

WHERE Clauses¶

SELECT * FROM mydb.users WHERE age > 25;
SELECT * FROM mydb.users WHERE name = 'Alice' AND active = true;
SELECT * FROM mydb.users WHERE city IN ('NYC', 'LA', 'Chicago');

ORDER BY, LIMIT, OFFSET¶

SELECT * FROM mydb.users ORDER BY created DESC;
SELECT * FROM mydb.users ORDER BY name ASC LIMIT 10;
SELECT * FROM mydb.users LIMIT 10 OFFSET 20;

GROUP BY & HAVING¶

SELECT category, SUM(amount) FROM mydb.orders GROUP BY category;
SELECT city, COUNT(id) FROM mydb.users GROUP BY city HAVING COUNT(id) > 10;

Aggregate Functions¶

Function	Description
`COUNT(*)`	Count rows
`SUM(column)`	Sum numeric values
`AVG(column)`	Average of numeric values
`MIN(column)`	Minimum value
`MAX(column)`	Maximum value

JOINs¶

-- Inner Join
SELECT * FROM mydb.orders 
INNER JOIN mydb.customers ON customer_id = id;

-- Left Join
SELECT o.id, c.name FROM mydb.orders o
LEFT JOIN mydb.customers c ON o.customer_id = c.id;

-- Right Join
SELECT * FROM mydb.products
RIGHT JOIN mydb.categories ON category_id = id;

String Functions¶

Function	Description
`UPPER(str)`	Convert to uppercase
`LOWER(str)`	Convert to lowercase
`CONCAT(a, b, ...)`	Concatenate strings
`SUBSTRING(str, start, len)`	Extract substring (1-indexed)
`TRIM(str)`	Remove leading/trailing whitespace
`LENGTH(str)`	String length
`REPLACE(str, old, new)`	Replace occurrences

SELECT UPPER(name) FROM mydb.users;
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM mydb.users;
SELECT SUBSTRING(name, 1, 3) FROM mydb.users;

Date Functions¶

Function	Description
`NOW()`	Current date/time
`DATE(timestamp)`	Extract date part
`YEAR(date)`, `MONTH(date)`, `DAY(date)`	Extract date components
`HOUR(ts)`, `MINUTE(ts)`, `SECOND(ts)`	Extract time components
`DATE_ADD(date, n, unit)`	Add interval (DAY, MONTH, YEAR, etc.)
`DATE_SUB(date, n, unit)`	Subtract interval
`DATEDIFF(date1, date2)`	Days between dates
`DATE_FORMAT(date, format)`	Format date

SELECT NOW() FROM mydb.events;
SELECT YEAR(created_at), MONTH(created_at) FROM mydb.events;
SELECT DATE_ADD(created_at, 7, 'DAY') FROM mydb.events;
SELECT DATE_FORMAT(created_at, '%Y-%m-%d') FROM mydb.events;

JSON Functions¶

CommitDB supports storing and querying JSON data.

Working with JSON Data¶

-- Create table with JSON column
CREATE TABLE mydb.documents (
    id INT PRIMARY KEY,
    data JSON
);

-- Insert JSON data (must be valid JSON string)
INSERT INTO mydb.documents (id, data) VALUES (1, '{"name": "Alice", "age": 30}');
INSERT INTO mydb.documents (id, data) VALUES (2, '{"name": "Bob", "tags": ["admin", "user"]}');
INSERT INTO mydb.documents (id, data) VALUES (3, '{"name": "Charlie", "address": {"city": "NYC", "zip": "10001"}}');

JSON Functions Reference¶

Function	Description
`JSON_EXTRACT(json, path)`	Extract value using JSON path
`JSON_KEYS(json)`	Get comma-separated list of keys
`JSON_LENGTH(json)`	Number of elements in array/object
`JSON_TYPE(json)`	Type: object, array, string, number, boolean, null
`JSON_CONTAINS(json, value)`	Returns 1 if value exists, 0 otherwise

JSON Path Syntax¶

-- Simple key access
SELECT JSON_EXTRACT(data, '$.name') FROM mydb.documents;

-- Nested object access
SELECT JSON_EXTRACT(data, '$.address.city') FROM mydb.documents;

-- Array element access
SELECT JSON_EXTRACT(data, '$.tags[0]') FROM mydb.documents;

JSON in Queries¶

-- Get all keys from JSON column
SELECT JSON_KEYS(data) FROM mydb.documents;

-- Check JSON type
SELECT JSON_TYPE(data) FROM mydb.documents;

-- Count array/object elements
SELECT JSON_LENGTH(data) FROM mydb.documents;

-- Filter by JSON content
SELECT * FROM mydb.documents WHERE JSON_CONTAINS(data, '"admin"');
SELECT * FROM mydb.documents WHERE JSON_EXTRACT(data, '$.age') = '30';

Bulk Import/Export¶

-- Export table to CSV (local file)
COPY INTO '/path/to/users.csv' FROM mydb.users;
COPY INTO '/path/to/data.csv' FROM mydb.users WITH (HEADER = TRUE, DELIMITER = ',');

-- Import CSV into table (local file)
COPY INTO mydb.users FROM '/path/to/users.csv';
COPY INTO mydb.users FROM '/path/to/data.tsv' WITH (HEADER = TRUE, DELIMITER = '\t');

-- Import from HTTPS URL
COPY INTO mydb.users FROM 'https://example.com/data.csv';

-- Export to S3
COPY INTO 's3://bucket/path/file.csv' FROM mydb.users;
COPY INTO 's3://bucket/file.csv' FROM mydb.users WITH (AWS_REGION = 'us-east-1');

-- Import from S3
COPY INTO mydb.users FROM 's3://bucket/path/file.csv';
COPY INTO mydb.users FROM 's3://bucket/file.csv' WITH (
    AWS_KEY = 'AKIAIOSFODNN7EXAMPLE',
    AWS_SECRET = 'wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY',
    AWS_REGION = 'us-east-1'
);

S3 Authentication: - Uses AWS environment variables by default (AWS_ACCESS_KEY_ID, AWS_SECRET_ACCESS_KEY, AWS_REGION) - Or specify credentials via WITH clause (AWS_KEY, AWS_SECRET, AWS_REGION) - IAM roles work automatically on EC2/ECS/Lambda

Shared Databases¶

Query external Git repositories without copying data:

-- Create a share from an external repository
CREATE SHARE external FROM 'https://github.com/company/data.git';

-- With SSH authentication
CREATE SHARE reports FROM 'git@github.com:company/reports.git'
    WITH SSH KEY '/path/to/key';

-- With token authentication
CREATE SHARE data FROM 'https://github.com/company/data.git'
    WITH TOKEN 'ghp_xxxxxxxxxxxx';

-- Query shared tables using 3-level naming
SELECT * FROM external.mydb.users;

-- JOIN local and shared tables
SELECT o.id, u.name 
FROM local.orders o 
JOIN external.customers.users u ON o.user_id = u.id;

-- Sync latest changes
SYNC SHARE external;

-- List shares
SHOW SHARES;

-- Remove a share
DROP SHARE external;

Branch Operations¶

See Branching for full documentation on: - CREATE BRANCH, CHECKOUT, SHOW BRANCHES - MERGE, SHOW MERGE CONFLICTS, RESOLVE CONFLICT - COMMIT MERGE, ABORT MERGE

Remote Operations¶

See Remote Operations for full documentation on: - ADD REMOTE, SHOW REMOTES, DROP REMOTE - PUSH TO, PULL FROM, FETCH FROM - Authentication with tokens and SSH keys

Transactions¶

Every data-modifying operation (INSERT, UPDATE, DELETE) is automatically committed as a Git commit. CommitDB provides commands to work with transaction history.

Transaction History¶

-- View recent transactions (default 100)
SHOW TRANSACTIONS;

-- View specific number of transactions
SHOW TRANSACTIONS LIMIT 20;

Returns columns: TransactionId, Message, Author, Timestamp

Explicit Transactions¶

BEGIN;
-- ... multiple operations ...
COMMIT;

-- Or rollback
BEGIN;
-- ... operations ...
ROLLBACK;