Writing SQL endpoints

Every .sql file under sql_dir becomes one HTTP endpoint. This chapter covers the directory layout, parameter binding, result shaping, and the batch API.

Directory layout → URL

The scanner expects three levels:

<sql_dir>/<project>/<GET|POST>/<name...>.sql

• <project> — the first URL segment. Also the default datasource name.
• <GET|POST> — an all-caps method directory. Other names (including PUT, DELETE) are silently ignored.
• <name...>.sql — filename with an optional subdirectory prefix.

Resolved endpoint:

<METHOD> /<project>/<name...>

Examples:

Rules:

• Project and path lookups are case-insensitive. /CRM/find and /crm/find route to the same file.
• Non-.sql files are ignored (README notes, .keep files, etc.).
• Duplicate endpoints (same case-insensitive key) fail startup.
• Empty files fail startup.
• Startup fails loudly if sql_dir doesn't exist.

Named parameters

Use :name in your SQL. Any JSON body key with a matching name binds to it. Repeated :name binds the same value at every position.

-- sql/crm/POST/users/find.sql
SELECT id, email
FROM users
WHERE (:login IS NULL OR login = :login)
  AND (:status IS NULL OR status = :status);

curl -X POST http://localhost:8080/crm/users/find \
     -H "content-type: application/json" \
     -d '{"login":"alice","status":null}'

The parser understands:

• String literals ('…', "…") with doubled-quote escapes — :xyz inside a string is not a parameter.
• Line comments (-- …) and block comments (/* … */).
• Postgres cast syntax (::type) is left alone.

For GET endpoints, use the query string. Every key becomes a bind target with the value as a string:

curl "http://localhost:8080/crm/users/find?login=alice"

Missing / extra parameters

• Missing: any :name in the SQL that has no matching JSON key → HTTP 400 with error: InvalidDataAccessApiUsageException and the parameter name in the message.
• Extra: extra JSON keys are silently ignored.

Type coercion

JSON values bind at their natural type:

• null → SQL NULL
• true / false → BOOLEAN
• integer → 64-bit integer
• floating-point → 64-bit double
• string → text
• arrays / objects (Postgres only) → JSONB (bind with ::jsonb cast if you want the DB to enforce it)

For dates and timestamps, cast in SQL:

INSERT INTO events (occurred_at)
VALUES (cast(:occurredAt AS TIMESTAMPTZ));

Result columns

Every result column is renamed from snake_case to camelCase:

• user_id → userId
• PASSWORD_HASH → passwordHash
• id → id (single tokens are unchanged)

Results come back as a JSON array of objects. DDL and other statements with no result set return [].

Datasource selection

For a URL /<project>/<name>:

1. If X-Datasource: <name> is present and allow_datasource_header is true, that name wins.
2. Otherwise, if project_datasource_map has an entry for <project>, the mapped name is used.
3. Otherwise, <project> itself is the datasource name.

If the resolved name is not in datasources, the response is HTTP 400 with error: UnknownDataSourceNameException.

Batch endpoint

Any POST endpoint accepts a batched variant at the same path with /batch appended. The body wraps a list of parameter objects; the response is a list-of-lists, one entry per input.

curl -X POST http://localhost:8080/crm/users/find/batch \
     -H "content-type: application/json" \
     -d '{"queries":[{"login":"alice"},{"login":"bob"}]}'
# [[{"id":1,"email":"alice@x"}], [{"id":2,"email":"bob@x"}]]

Atomicity guarantee (since v0.1.0-alpha.2): the whole batch runs inside a single database transaction. Every iteration binds and executes through the same transaction; a failure on any iteration rolls the entire transaction back — no partial writes ever land. The response body on failure is the standard 400 shape as if the failing query had been sent on its own.

# If the 2nd iteration violates a UNIQUE constraint, iterations 1 AND 3
# are rolled back too. A subsequent SELECT sees zero of these rows.
curl -X POST http://localhost:8080/crm/users/create/batch \
     -H "content-type: application/json" \
     -d '{"queries":[{"login":"a"},{"login":"a"},{"login":"c"}]}'
# → 400 {"error":"BadSqlGrammarException", "message":"..."}

Missing-parameter checks run against every parameter set BEFORE the transaction opens, so a batch that couldn't possibly succeed fails fast without any DB round-trips.

Soft ceiling: batching 10⁴ rows in one request works fine, but the transaction holds row locks until commit. Consumers doing 10⁵+ row loads should split into multiple batches at the caller.

Native array parameters (Postgres)

When a JSON parameter is an array of homogeneous scalars, it binds natively as a Postgres array (text[], int8[], float8[], bool[]) so a single SQL statement using unnest() can process the whole batch in one round-trip:

-- sql/audit/POST/append-many.sql
INSERT INTO audit_log (actor, action)
SELECT unnest(:actors), unnest(:actions)
RETURNING id;

curl -X POST http://localhost:8080/audit/append-many \
     -H "content-type: application/json" \
     -d '{"actors":["alice","bob"],"actions":["created","updated"]}'
# → 200 [{"id":42}, {"id":43}]   (one INSERT, two rows)

Rules:

• Non-null elements must all be the same scalar kind. Nulls are permitted anywhere and become SQL NULL elements.

• Mixed kinds ([1, "two", true]), nested ([[1,2],[3,4]]), all-null, and empty arrays fall back to JSONB binding — use jsonb_array_elements* in your SQL to unpack.

• Int + float mixed promotes to float8[].

• SQLite has no native array type. Arrays bind as a JSON string; use json_each() to unpack:

  SELECT value AS actor FROM json_each(:actors);
  

Per-file @transactional marker

Add -- @transactional as the very first comment in a SQL file to have the endpoint's execution wrapped in a single database transaction (commit on success, rollback on any error). Batch endpoints are always transactional regardless — this marker is for single-shot POST endpoints whose SQL contains multiple statements or where the caller wants explicit rollback semantics on failure.

-- @transactional
INSERT INTO audit_log (actor, action) VALUES (:actor, :action);
UPDATE users SET last_seen = now() WHERE id = :user_id;

Rules:

• Only recognised in the leading comment block. Once real SQL starts, no further marker is honoured.
• Applies to both GET and POST (transaction is essentially free for a read-only statement; primary use case is POST).
• Absence of the marker preserves the pre-v0.1.0-alpha.2 behaviour of auto-commit per statement.