SQL Reference¶

DecentDb supports a PostgreSQL-like SQL subset.

Data Definition Language (DDL)¶

CREATE TABLE¶

CREATE TABLE table_name (
    column1 datatype [constraints],
    column2 datatype [constraints],
    ...
);

Example:

CREATE TABLE users (
    id INT PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE,
    created_at INT
);

CREATE INDEX¶

-- B-Tree index (default)
CREATE INDEX index_name ON table_name(column_name);

-- Trigram index for text search
CREATE INDEX index_name ON table_name USING trigram(column_name);

-- Unique index
CREATE UNIQUE INDEX index_name ON table_name(column_name);

DROP TABLE / DROP INDEX¶

DROP TABLE table_name;
DROP INDEX index_name;

ALTER TABLE¶

Modify the structure of an existing table.

Add Column¶

ALTER TABLE table_name ADD COLUMN column_name datatype [constraints];

Adds a new column to the table. Existing rows will have NULL values for the new column.

Example:

-- Add a new column with no default
ALTER TABLE users ADD COLUMN age INT;

-- Add a column with NOT NULL constraint
-- (will fail if table has existing rows)
ALTER TABLE users ADD COLUMN status TEXT NOT NULL DEFAULT 'active';

Drop Column¶

ALTER TABLE table_name DROP COLUMN column_name;

Removes a column from the table. This operation: - Deletes all data in that column - Automatically drops any indexes on that column - Rebuilds remaining indexes - Migrates all data to a new table structure

Example:

ALTER TABLE users DROP COLUMN age;

Notes: - ADD COLUMN and DROP COLUMN are the only supported operations in v1.0.0 - Advanced operations like RENAME COLUMN, MODIFY COLUMN (type changes), and ADD CONSTRAINT are planned for future releases - Schema changes require an exclusive lock on the database

Data Manipulation Language (DML)¶

INSERT¶

INSERT INTO table_name VALUES (val1, val2, ...);
INSERT INTO table_name (col1, col2) VALUES (val1, val2);

SELECT¶

SELECT * FROM table_name;
SELECT col1, col2 FROM table_name WHERE condition;
SELECT * FROM table_name ORDER BY col1 ASC, col2 DESC;
SELECT * FROM table_name LIMIT 10 OFFSET 20;

UPDATE¶

UPDATE table_name SET col1 = val1 WHERE condition;

DELETE¶

DELETE FROM table_name WHERE condition;

Query Features¶

WHERE Clause¶

Supports: - Comparison operators: =, !=, <, <=, >, >= - Logical operators: AND, OR, NOT - Pattern matching: LIKE, ILIKE - Null checks: IS NULL, IS NOT NULL - IN operator: col IN (val1, val2, ...)

SELECT * FROM users WHERE age > 18 AND name LIKE '%son%';
SELECT * FROM users WHERE email IS NOT NULL;
SELECT * FROM users WHERE id IN (1, 2, 3);

JOINs¶

-- Inner join
SELECT * FROM users JOIN orders ON users.id = orders.user_id;

-- Left join
SELECT * FROM users LEFT JOIN orders ON users.id = orders.user_id;

Aggregate Functions¶

SELECT COUNT(*) FROM users;
SELECT COUNT(email) FROM users;  -- Count non-NULL
SELECT SUM(amount) FROM orders;
SELECT AVG(price) FROM products;
SELECT MIN(created_at), MAX(created_at) FROM users;
SELECT category, SUM(amount) FROM orders GROUP BY category;
SELECT category, COUNT(*) FROM orders GROUP BY category HAVING COUNT(*) > 5;

Transactions¶

BEGIN;
-- ... your operations ...
COMMIT;

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

Constraints¶

Primary Key¶

CREATE TABLE users (
    id INT PRIMARY KEY,
    ...
);

Foreign Key¶

CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT REFERENCES users(id),
    ...
);

Unique¶

CREATE TABLE users (
    id INT PRIMARY KEY,
    email TEXT UNIQUE,
    ...
);

NOT NULL¶

CREATE TABLE users (
    id INT PRIMARY KEY,
    name TEXT NOT NULL,
    ...
);

Parameters¶

Use positional parameters with $1, $2, etc.:

SELECT * FROM users WHERE id = $1 AND name = $2;

CLI usage:

decentdb exec --db=my.ddb --sql="SELECT * FROM users WHERE id = \$1" --params=int:42

Unsupported Features¶

Not currently supported: - Subqueries in SELECT - Window functions - Common Table Expressions (CTE) - Views - Stored procedures

See Known Limitations for details.