Ridge SQL Reference

ridge_sql implements a bounded relational SQL surface. Familiar syntax does not imply complete PostgreSQL, MySQL, or SQLite behavior. Unsupported shapes fail explicitly.

Types

The generation-2 schema and row format supports bigint, double, bounded exact numeric/decimal, boolean, UTF-8 text, bytea, date, timestamp, timestamptz, interval, UUID, canonical JSON, nulls, defaults, generated columns, and optimistic version columns.

Some advanced analytical paths retain narrower documented column-shape limits. Codec support must not be interpreted as unrestricted SQL coverage.

Data definition

CREATE TABLE account_records (
    account_id BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    name TEXT NOT NULL,
    balance BIGINT NOT NULL DEFAULT 0,
    active BOOLEAN NOT NULL DEFAULT true,
    version BIGINT NOT NULL DEFAULT 1
);

The bounded surface includes stable named multi-column indexes, primary, unique, ID-range check, and primary-ID foreign-key constraints, additive schema evolution, rename/drop guards, REINDEX, and crash-safe table/index definition drop.

Data manipulation

Supported application shapes include multi-row INSERT, arbitrary-column defaults, identity and positive-increment sequences, primary-key UPSERT, general UPDATE and DELETE, prepared batches, and RETURNING *.

INSERT INTO account_records (name, balance)
VALUES ('Ada', 125)
RETURNING *;

UPDATE account_records
SET balance = 135
WHERE account_id = 1
RETURNING *;

Queries

The documented surface covers typed projections and aliases, null semantics, bounded arithmetic and casts, COALESCE, null-fallback CASE, LIKE, BETWEEN, bounded IN, ordering, limits, joins, grouping, aggregates, materialized non-recursive CTEs, set operations, bounded subqueries, aggregate filters, distinct counts, and documented window functions.

Use EXPLAIN or EXPLAIN ANALYZE to inspect the deterministic bounded plan and actual work.

Transactions

BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT * FROM account_records WHERE account_id = 1 FOR UPDATE;
UPDATE account_records SET balance = 110 WHERE account_id = 1;
COMMIT;

Supported isolation levels are read committed and repeatable read. Autocommit and explicit transactions share the same visibility and SQLSTATE rules. After a lost connection, never replay a mutation automatically when the commit outcome is unknown.

Introspection

SHOW DATABASES;
SHOW SCHEMAS;
SHOW TABLES;
SHOW FULL TABLES FROM ridge;
SHOW COLUMNS FROM accounts;
SHOW FIELDS IN accounts;
DESCRIBE accounts;
DESC accounts;
SHOW INDEXES FROM accounts;
SHOW KEYS IN accounts;
SHOW CONSTRAINTS FROM accounts;

The current service exposes one configured database and the synthetic public compatibility schema. The result shapes are versioned Ridge metadata, not MySQL catalog compatibility.

Explicitly unsupported

Do not generate triggers, stored procedures, extensions, recursive CTEs, general window frames, partial/expression indexes, COPY, broad information_schema, or PostgreSQL pg_catalog queries. Check grove/libs/ridge_sql/README.md before relying on a complex expression or protocol-specific behavior.