Skip to content

sql-compatibility.md

Latest commit

 

History

History
83 lines (66 loc) · 4.66 KB

sql-compatibility.md

File metadata and controls

83 lines (66 loc) · 4.66 KB
title Basin SQL compatibility
nav_section sql
sidebar_position 10
summary What Postgres SQL Basin accepts, what it doesn't, and the design rationale for each gap.

SQL / ORM compatibility — survey

Output of tests/integration/tests/orm_smoke.rs. The test drives seven representative query patterns that real ORMs (Diesel, SeaORM, Prisma, SQLAlchemy, ActiveRecord) emit through the Postgres extended-query protocol against a fresh basin-server. The numbers are protocol-level: if a pattern works here, the chance every ORM that funnels through tokio-postgres / asyncpg / pgjdbc works is high.

Building a TypeScript/JavaScript app against Basin? Prefer the typed basin-js client over hand-rolled pgwire — it talks engine-direct (auth + REST) and ships as jsr:@bas-in/basin-js and npm:@bas-in/basin-js.

This file is survey-grade for the ORM protocol surface. For the complete per-syntax fragment matrix (490 fragments, auto-generated by tests/integration/tests/sql_support_matrix.rs), see docs/sql-support.md.

Result snapshot (run 2026-05-15): 7 / 7 ORM patterns pass = 100%. Fragment-level coverage: 423 / 435 non-design-excluded fragments = 97.2%. The remaining 55 fragments are explicit design-exclusions (🚫): CREATE EXTENSION, VACUUM, server-side PREPARE/EXECUTE, DROP/TRUNCATE TABLE, BEGIN/COMMIT/ROLLBACK, and PG-extension-only types. These are not gaps — they are deliberate out-of-scope decisions (see ADR 0002).

# Pattern ORM analogue Result Notes
1 INSERT … VALUES ($1,$2,$3),($4,$5,$6),($7,$8,$9) Diesel insert_into(t).values(&vec), ActiveRecord insert_all PASS Multi-row INSERT with parameter placeholders works end-to-end.
2 client.prepare(sql) + 100× client.query(&stmt, &[…]) Any pooled prepared-statement cache (sqlx, asyncpg, HikariCP) PASS Cached statement handle survives 100 Bind/Execute/Sync cycles with varying parameters.
3 WHERE id = $1 AND name = $2 AND active = $3 (i64, &str, bool) ActiveRecord where(...), Prisma findMany({where:{...}}) PASS Mixed-type predicate inference works for INT8, TEXT, BOOL.
4 INSERT … VALUES ($1, $2) with Option::<&str>::None, then assert NULL on read Diesel Option<String>, Prisma optional fields PASS NULL parameters round-trip cleanly.
5 SELECT … LIMIT $1 with &2i64 Diesel .limit(?), Prisma take(n), SeaORM limit(n) PASS LIMIT/OFFSET placeholder now typed as int8; driver binds i64 without error.
6 INSERT … VALUES ($1, $2, $3) with "O'Brien" Any text-bound input PASS Single-quote in TEXT parameter survives — the binary protocol path bypasses textual substitution.
7 CREATE TABLE blobs (… data BYTEA NOT NULL) + INSERT/SELECT Vec<u8> Diesel Vec<u8>, Prisma Bytes PASS BYTEA wired through DDL parser → Arrow LargeBinary; INSERT literal coercion and binary result-row encoding both land.

Real v0.2 gaps (non-design-excluded)

The 12 non-excluded failing fragments fall into two tiers:

Runtime gaps (🛠, 5 fragments) — parse and plan fine, fail at execution:

  • LATERAL joins — correlated lateral subquery in FROM
  • WITH RECURSIVE combined with DML (e.g. WITH RECURSIVE … DELETE)
  • Advanced window frames: RANGE INTERVAL mode, GROUPS mode, EXCLUDE clause
  • JSON_AGG(t) whole-row aggregate (table-reference inside json_agg)

Planner/parser gaps (📜/❌, 7 fragments) — rejected before execution:

  • EXCLUDE USING gist on CREATE TABLE (GiST exclusion constraints)
  • Six additional long-tail DDL/DML forms documented in docs/sql-support.md

All of the above are v0.2 targets. See docs/sql-support.md for the per-fragment status.

Verdict

The protocol surface is solid and the SQL coverage is broad. All seven ORM patterns pass. At the fragment level, 97.2% of non-design-excluded SQL runs end-to-end. The remaining real gaps are narrow v0.2 items — none block typical ORM migrations or application workloads.

How to re-run

# ORM protocol smoke test (7 patterns):
cargo test -p basin-integration-tests --test orm_smoke -- --nocapture

# Full SQL fragment matrix (490 fragments):
cargo test -p basin-integration-tests --test sql_support_matrix -- --nocapture

Each test seeds a fresh TempDir per run and binds an ephemeral port, so both are safe to run in parallel with other integration tests. The machine-readable ORM result lands at benchmark/data/viability_orm_compat.json; the fragment matrix is auto-written to docs/sql-support.md.