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 *;

File-based CSV ingest is available as COPY ... FROM 'path' WITH (FORMAT csv, HEADER, DELIMITER, QUOTE, ESCAPE, NULL) with streaming bounded-memory parsing and partial rollback/commit semantics; the interactive pgwire COPY protocol and binary formats are not claimed.

CSV export is available as COPY <table> [(<col>, ...)] TO 'path' WITH (FORMAT csv, HEADER, DELIMITER, QUOTE, ESCAPE, NULL). Rows are written in primary-key (physical) order with the same quoting rules the connector uses, including the NULL token rendered raw and unquoted while empty strings render as the quoted empty pair, so exported files round-trip losslessly through COPY ... FROM. LATIN1 remains an import-only conversion and is rejected on TO. The embedded copy_csv_file helper is a thin proxy that builds this SQL statement; CSV import/export is implemented only in ridge_sql.

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.

Database and security administration

Administrative statements live in the ridge_security_sql catalog layer, not in the ridge_sql data plane. They are accepted on an idle session connected to an existing cataloged database (for example the bootstrap ridge database), execute as the authenticated principal, and must be admitted by the security catalog. CREATE DATABASE name [OWNER owner] registers the database in the security catalog and creates its storage directory; the owner defaults to the executing principal. DROP DATABASE name tombstones the catalog entry and removes the storage directory.

CREATE DATABASE projects OWNER ada;
CREATE DATABASE scratch;              -- owner defaults to the executing actor
ALTER DATABASE projects RENAME TO projects_v2;
ALTER DATABASE projects_v2 OWNER bob;
DROP DATABASE projects_v2;
SHOW DATABASES;

A created database is connectable like any other cataloged database. Admin statements require an idle session: running one inside an open transaction fails explicitly rather than silently participating in it.

Ridge's ALTER DATABASE ... OWNER form omits the TO keyword that PostgreSQL uses (ALTER DATABASE name OWNER owner, not OWNER TO owner).

The same administration path manages principals, roles, and privileges with bounded forms:

CREATE USER ada PASSWORD 'secret' SUPERUSER;
CREATE ROLE reader;
ALTER USER ada PASSWORD 'secret';
ALTER USER ada LOGIN;
ALTER USER ada NOLOGIN;
ALTER USER ada SUPERUSER;
ALTER USER ada NOSUPERUSER;
DROP USER ada;                         -- DROP ROLE ada also accepted
GRANT reader TO ada;
REVOKE reader FROM ada;
GRANT CONNECT ON DATABASE projects TO ada;
GRANT SELECT ON TABLE accounts TO reader WITH GRANT OPTION;
REVOKE SELECT ON TABLE accounts FROM reader CASCADE;
GRANT USAGE ON SEQUENCE accounts_id_seq TO reader;
GRANT CREATE ON SCHEMA public TO ada;
ALTER DEFAULT PRIVILEGES GRANT SELECT ON TABLES TO reader;
ALTER DEFAULT PRIVILEGES REVOKE SELECT ON TABLES FROM reader;
SHOW USERS;
SHOW ROLES;
SHOW AUDIT;

Database privileges are CONNECT, CREATE (schema), and TEMPORARY/TEMP; table/schema/sequence privileges are SELECT, INSERT, UPDATE, DELETE, ALTER, DROP, TRUNCATE, REFERENCES, TRIGGER, USAGE, and CREATE. Each GRANT/REVOKE names exactly one privilege; REVOKE accepts an optional CASCADE or RESTRICT, and GRANT accepts an optional WITH GRANT OPTION. Only these bounded shapes are supported; deviations such as IF NOT EXISTS, WITH parameters on database creation, or comma-separated privilege lists fail explicitly.

Explicitly unsupported

Do not generate triggers, stored procedures, extensions, recursive CTEs, general window frames, partial/expression indexes, the pgwire COPY protocol and binary formats, 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.