SQL Database Support

Adding SQL Database Support

MUST invariants: .cursor/rules/sql-database-support.mdc. Compose/workflow package ownership: .cursor/rules/integration-layout.mdc. This skill is the full analysis → where-to-change → scaffold playbook.

Use this to think through and start support for a SQL engine — not to ship a full product surface in one pass.

Reference engine: integration/timescaledb/ (+ DIFFERENCES.md). See examples.md. Integration request style: skill prest-integration-tests.

Phase A — Classify

Decide before scaffolding CI or adapters.

Decision criteria

Ask whether these match stock Postgres:

• Wire protocol / driver (lib/pq vs another)
• SQL dialect (quoting, types, RETURNING, upserts)
• Catalog SQL used by pREST listing endpoints
• Identifier quoting and reserved words

If protocol and catalog SQL can stay on the postgres adapter with only seed/extension quirks → wire variant. If ports must emit different SQL or use another driver → new dialect. Do not greenwash a dialect as compose-only.

Phase B — Gap analysis → DIFFERENCES.md

Write integration/<db>/DIFFERENCES.md before Make/CI targets that claim support. Answer every item below (N/A with reason is fine).

Worksheet

1. Runtime image / init — image tag; extensions; required init SQL
2. Connection / DSN / driver — same as Postgres or different?
3. Identifier quoting / reserved words — impacts builders and path params?
4. Catalog listing — /databases, /schemas, /tables, /columns vs stock Postgres (extra relation kinds, schemas, system catalogs)
5. DDL / engine-native features — hypertables, policies, etc.; which need specific tests?
6. System schemas and ACL — access_confine / allowlists implications
7. Compose ownership — which stacks this DB job owns vs leave on Postgres (auth, multicluster, queries)

Template shape: see integration/timescaledb/DIFFERENCES.md.

Phase C — Where to change

Wire-compatible variant (Timescale path)

Do not: new adapters/<engine>; fold into integration/postgres/docker-compose.yml.

New dialect (future MySQL / SQLite path)

Do not: import the engine from controllers/, middlewares/, or router/ (hexagonal: hexagonal-architecture.mdc).

Layout reminder

integration/
  helpers/ testutils/ suites/   # shared; suites stay on Postgres job
  <db>/
    docker-compose.yml
    DIFFERENCES.md
    ... engine-specific tests ...

Details: integration-layout.mdc.

Phase D — Test policy

1. Smoke / specific — extension present, one engine-native feature under integration/<db>/.
2. Compat — shared suites/ run on Postgres compose; add a DB-specific compat job only if you intentionally gate that engine against the suite.
3. Document every request per skill prest-integration-tests / integration-tests.mdc.

Safety

• Parameterized SQL only; no concatenating untrusted identifiers unchecked.
• Auth / ACL / JWT / secrets fail closed — never weaken for “compatibility”.
• Do not call postgres.Load() or open live DBs outside integration/ and cmd/.

Adapter-Specific Features

When a feature is unique to one database engine (e.g., TimescaleDB's time_bucket):

Pattern:

1. Base adapter provides empty/no-op implementation with a clear comment
2. Specialized adapter overrides with the actual feature
3. Handlers depend on the interface method, not the concrete adapter

Example — time_bucket (TimescaleDB-only):

Base postgres adapter:

func (adapter *postgres) TimeBucketClause(r *http.Request) (groupBySQL string, err error) {
	return  // Not supported in base postgres
}

TimescaleDB adapter:

func (a *Adapter) TimeBucketClause(req *http.Request) (groupBySQL string, err error) {
	// Full time_bucket implementation here
	return
}

Why: Base adapter must remain database-agnostic. Wire-compatible variants (Citus, Cockroach) inherit the base adapter unchanged; specialized variants override only what they need.

See rule: adapter-features.mdc.

Checklist (opening a new DB)

• Engine classified (wire variant vs new dialect)
• Gap worksheet answered; integration/<db>/DIFFERENCES.md written
• Where-matrix paths identified for this kind
• integration/<db>/docker-compose.yml + seed/init
• Specific smoke + feature tests under integration/<db>/
• make test-integration-<db> (compose runs ./integration/<db>/... only)
• Dedicated workflow .github/workflows/test-integration-<db>.yml
• Workflow does not run other DBs’ packages
• New dialect only: adapter + app selection + mocks + unit tests (≥80%, TDD)
• Cross-link docs/skills as needed

Examples

examples.md — TimescaleDB skeleton + new-dialect file stub list.