Introduction

Resql turns a directory of .sql files into a REST API. There is no controller code to write. Drop a file at sql/<project>/<GET|POST>/<name>.sql; the file becomes an HTTP endpoint at <METHOD> /<project>/<name> on next startup. JSON payload keys bind to :named SQL parameters. Result columns are re-cased snake → camel and returned as a JSON array.

Version: 0.1.0-alpha.2 License: Apache-2.0 Container: docker.io/turnerrainer/resql:0.1.0-alpha.2 Source: github.com/turnerrainer/Resql

One-command demo

The published image ships with a working SQLite-backed multi-database demo: two datasources (users and audit) wired to two URL projects.

docker run --rm -p 8080:8080 turnerrainer/resql:0.1.0-alpha.2

# Hits the `users` datasource:
curl "http://localhost:8080/users/hello?name=world"
# [{"greeting":"hello from users db, world!"}]

# Hits a *different* datasource — same server, same request shape:
curl "http://localhost:8080/audit/tail?n=42"
# [{"entry":"audit entry 42","rowId":42}]

What it replaces

Resql is a Rust rewrite of the original Bürokratt Resql Spring Boot service. It keeps the same public shape (SQL file → endpoint, :named binding, snake→camel result columns, JSON array response) and fixes a shortlist of pain points captured in docs/DESIGN.md.

Notable behavioural improvements over the Spring Boot original:

Where to read next

1. Getting started — install, run, add your first SQL file.
2. Configuration — every YAML key + env var.
3. Writing SQL endpoints — file layout, parameter binding, batch API.
4. Failure modes — every HTTP status and error class you might see.

Getting started

Five steps: install, run the demo, verify, add your own SQL file, call it.

1. Install

Use one of the two officially supported paths:

Docker (recommended):

docker pull turnerrainer/resql:0.1.0-alpha.2

From source (Rust 1.88+):

git clone https://github.com/turnerrainer/Resql.git
cd Resql
cargo build --release --locked

The built binary is at target/release/resql.

2. Run the demo

docker run --rm -p 8080:8080 turnerrainer/resql:0.1.0-alpha.2

From source:

./target/release/resql --config resql.yaml

3. Verify

The shipped image wires two datasources (users and audit) so you can see multi-database routing without any config.

curl http://localhost:8080/health
# {"appName":"resql","version":"0.1.0-alpha.2","appStartTime":..., "serverTime":..., "status":"UP"}

curl http://localhost:8080/datasources
# [{"name":"audit","url":"sqlite::memory:","driver":"sqlite"},
#  {"name":"users","url":"sqlite::memory:","driver":"sqlite"}]

# Hits the users datasource (URL project = users):
curl "http://localhost:8080/users/hello?name=world"
# [{"greeting":"hello from users db, world!"}]

curl -X POST http://localhost:8080/users/echo \
     -H "content-type: application/json" \
     -d '{"msg":"pong"}'
# [{"echoed":"pong","servedFrom":"users"}]

# Hits the audit datasource (URL project = audit):
curl "http://localhost:8080/audit/tail?n=42"
# [{"entry":"audit entry 42","rowId":42}]

# Force any endpoint onto any datasource with X-Datasource:
curl "http://localhost:8080/users/hello?name=world" \
     -H "X-Datasource: audit"
# [{"greeting":"hello from users db, world!"}]   ← same SQL, different pool

4. Add your first endpoint

The demo image bakes sql/users/* and sql/audit/* in. For your own endpoints you'll want to mount a directory over /app/sql.

Create a file ./mysql/users/GET/find-by-login.sql:

SELECT id, email FROM users WHERE login = :login;

Run with your directory mounted and a Postgres datasource wired up:

docker run --rm -p 8080:8080 \
  -v "$PWD/mysql:/app/sql:ro" \
  -v "$PWD/resql.yaml:/app/resql.yaml:ro" \
  -e USERS_DB_PASSWORD="secret" \
  turnerrainer/resql:0.1.0-alpha.2

Where resql.yaml (see Configuration for the full reference) points at your database and names the env-var holding the password:

sql_dir: /app/sql
datasources:
  - name: users
    url: "postgres://localhost:5432/appdb"
    username: "app"
    password_env: "USERS_DB_PASSWORD"

5. Call it

curl -X POST http://localhost:8080/users/find-by-login \
     -H "content-type: application/json" \
     -d '{"login":"alice"}'
# [{"id":1,"email":"alice@example.com"}]

The URL segment users selects the users datasource by name. Override per-request with the X-Datasource header if you need a different backend for the same SQL file.

Configuration

Resql reads a single YAML file at startup. The path is set with --config <path> or the RESQL_CONFIG env var (default /app/resql.yaml inside the container).

All fields have safe defaults except sql_dir. Every unknown YAML field is rejected — typos fail startup instead of silently ignoring config.

Top-level fields

server

datasources (list of)

Startup refuses in any of these cases:

• Two datasources share a name.
• A username is set but password_env is empty.
• password_env names a variable that is not set in the environment.
• project_datasource_map refers to a name absent from datasources.

project_datasource_map

Maps URL project segment → datasource name. When a request arrives at POST /crm/find-user, the default behaviour is to look up a datasource named crm. Add an entry crm: primary-db to route it elsewhere.

Falls back to the project name if the map has no entry — a bare sql_dir layout of sql/foo/… will look up datasource foo with no config.

allow_datasource_header

When true (default), the X-Datasource: <name> request header overrides both the map and the project name. Set to false in locked-down deployments where operators pick datasources centrally.

cors

logging

The RESQL_LOG env var overrides level at runtime without touching the config file.

Env-var overrides

Any string in password_env is looked up in the process environment. Nothing else is env-driven — the config file is authoritative. If you need per-environment overrides, use one config file per environment or mount a config file from a secret at runtime.

Complete example

server:
  bind: "0.0.0.0:8080"
  max_body_bytes: 2097152   # 2 MiB

sql_dir: "/app/sql"

allow_datasource_header: true

project_datasource_map:
  legacyapp: primary
  reports: analytics

datasources:
  - name: primary
    url: "postgres://pg-primary.internal:5432/appdb"
    username: "resql"
    password_env: "RESQL_PRIMARY_PASSWORD"
    max_connections: 20
  - name: analytics
    url: "postgres://pg-analytics.internal:5432/warehouse"
    username: "resql_ro"
    password_env: "RESQL_ANALYTICS_PASSWORD"
    max_connections: 5

cors:
  allowed_origins: "https://ops.internal, https://console.internal"

logging:
  level: "info,resql=debug,sqlx=warn"
  format: "json"

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.

Postgres + Liquibase setup

Resql does not manage schema. Schema and migrations are Liquibase's job; Resql just executes SQL that assumes the schema is already there. This chapter shows the pattern for local test runs and for production deployments.

Why this split

• Resql's binary stays small (no JVM, no Liquibase runtime linked in).
• Schema changes go through the same review + rollback story as every other Liquibase-managed project in your infrastructure.
• Test fixtures reuse the same changelog machinery — one source of truth for "what rows should exist for the integration suite."

Directory layout

db/changelog/
├── master.yaml               # includes the ordered changesets below
├── 001-schema.yaml           # tables, indexes, constraints — always applied
└── 002-test-fixtures.yaml    # seed data, context: test — skipped in prod

Every changeset in 002-test-fixtures.yaml carries context: test. That gate is enforced by Liquibase, not by Resql — passing --contexts=test applies fixtures; omitting the flag applies schema only.

Local test workflow

The Makefile at the repo root wraps the three steps:

make pg-up        # start Postgres 16 on :5433 in a throwaway container
make pg-schema    # apply master.yaml with --contexts=test
make test-pg     # run cargo test with TEST_POSTGRES_URL set

Or the combined shortcut:

make test-all     # pg-up → pg-schema → cargo test (SQLite + Postgres suites)

Without a running Postgres and TEST_POSTGRES_URL, the Postgres tests skip silently and the SQLite suite still runs to completion. Every integration test is designed to skip cleanly, so cargo test never fails just because Postgres isn't up.

Teardown:

make pg-down      # remove the throwaway container

Container port defaults to 5433 (not 5432) so a local dev Postgres you might already be running doesn't collide.

CI workflow

.github/workflows/tests.yml runs on both ubuntu-latest and ubuntu-24.04-arm. Each job:

1. Starts a postgres:16 service container on :5432.
2. Runs liquibase update --contexts=test against it via docker run --network host liquibase/liquibase:4.29.
3. Exports TEST_POSTGRES_URL so the Rust suite picks it up.
4. Runs cargo test --no-fail-fast --locked.

No secrets are involved; the CI Postgres password is a fixed throwaway value that only lives for the length of one job.

Production deployment

The runtime image ships without Liquibase. Apply schema out-of-band before (or beside) your Resql pods. Two common patterns:

Kubernetes init container

initContainers:
  - name: db-migrate
    image: liquibase/liquibase:4.29
    args:
      - --url=jdbc:postgresql://pg.internal:5432/appdb
      - --username=migrator
      - --password=$(PGPASSWORD)
      - --changeLogFile=changelog/master.yaml
      - update
    env:
      - name: PGPASSWORD
        valueFrom:
          secretKeyRef:
            name: pg-secrets
            key: migrator-password
    volumeMounts:
      - name: changelog
        mountPath: /liquibase/changelog

Note the absence of --contexts=test. Production applies schema only.

One-shot job

kubectl create job schema-2026-08-01 --image=liquibase/liquibase:4.29 -- ... before rolling out the new Resql version. Idempotent — Liquibase records applied changesets in databasechangelog, so re-running is a no-op.

Kubernetes Job with wait-for

Wrap the init or job with a readiness probe that gates the Resql deployment on SELECT 1 FROM databasechangelog WHERE id = '<expected-id>'. Resql itself will start regardless of schema — a missing table only shows up on the first request as BadSqlGrammarException (HTTP 400).

FAQ

Can I use Flyway instead? — Yes. Nothing in Resql cares. The db/ directory shape is just a convention; swap in db/flyway/ and flyway migrate if that's your team's tool.

Can I skip Liquibase and write raw SQL migrations? — Yes. The architectural rule is only "Resql does not touch schema" — how you get schema in is your choice. The Rust integration tests assume the schema in db/changelog/001-schema.yaml; if you want to use a different tool, either recreate that schema by hand or keep the Liquibase files around for tests and use your preferred tool in prod.

Why is 002-test-fixtures.yaml in the repo? — Because tests/integration_postgres.rs reads the rows it defines. Keeping schema + fixtures together as one Liquibase changelog is the point of the pattern — deleting the fixtures file breaks the test suite in a useful way (missing rows), not a mysterious way (schema drift).

Failure modes

Every error response is a JSON object of shape:

{"error": "<ExceptionClassName>", "message": "<human-readable>"}

error is a stable identifier suitable for programmatic branching. message is descriptive and may change between minor versions.

HTTP status codes

Note: JVM Resql returned 400 for every error class, including "query not found." Resql keeps that behaviour for compatibility; only 413 (body too large) and 500 (unhandled internal) sit outside.

Error catalog

Startup failures

The process exits non-zero and writes a single line at ERROR level. It does not attempt to run in a degraded state.

• Missing / invalid sql_dir
• Duplicate SQL endpoints
• Empty SQL file
• Duplicate datasource name
• Datasource with username but no password_env
• password_env naming an unset environment variable
• project_datasource_map referring to an unknown datasource
• Unknown fields in the config YAML (typo protection)

Runtime failures

• Datasource pool exhaustion → BadSqlGrammarException with the pool message; usually means max_connections is set too low.
• Client cancels mid-query → connection returned to pool; nothing logged unless the underlying driver reports it.
• Panic in a handler → 500 with InternalError; the panic goes to logs with backtrace when RUST_BACKTRACE=1.
• Batch rollback on iteration N (since v0.1.0-alpha.2): the batch endpoint runs all iterations inside one transaction. When iteration N fails (SQL error, constraint violation, etc.), iterations 1..N-1 are rolled back atomically. The client sees a single 400 with the standard error shape from the failing iteration; there is no per-iteration status array. To debug WHICH iteration triggered the failure, log the request body on the client side or split the batch.
• @transactional marker rollback: an endpoint marked -- @transactional behaves the same on failure — the whole SQL file's execution rolls back. Without the marker, mid-string failures in multi-statement SQL may leave earlier statements committed.

Debugging

Turn logging up with:

RESQL_LOG="debug,resql=trace,sqlx=debug" \
  ./resql --config resql.yaml

sqlx=debug prints every SQL statement and bound parameter — helpful for tracing missing-param and grammar errors back to the source SQL. Do not run production with sqlx=debug: bound parameters may contain PII.

Changelog

All notable changes to this project will be documented in this file. The format follows Keep a Changelog, and this project adheres to Semantic Versioning.

Unreleased

0.1.0-alpha.2 - 2026-08-05

Added — batch atomicity and array binding (task 007)

• /batch is now atomic. The full batch runs inside one database transaction; any failure rolls the whole batch back with the standard 400 response. Missing-parameter checks run against every set BEFORE the tx opens for fail-fast. Supersedes the batch-atomicity part of task 003.
• Native Postgres array binding. Homogeneous scalar JSON arrays bind as native text[] / int8[] / float8[] / bool[] so a single SQL statement using unnest() handles the whole set in one round-trip. Mixed / nested / all-null / empty arrays fall back to JSONB with a debug-level log. Nulls inside a homogeneous array stay as SQL NULL elements (via Vec<Option<T>>). SQLite continues to bind arrays as JSON strings — use json_each() to unpack.
• New book sections in sql-files.md: atomicity guarantee for /batch, native array parameters, per-file @transactional marker. failure-modes.md entry documenting batch rollback semantics.

Added — per-file @transactional marker (task 003)

• SQL files with -- @transactional in the leading comment block execute inside a single database transaction (commit on success, rollback on any error). Recognised only before the first non-comment line; applies to both GET and POST endpoints (primary use case: multi-statement POST files).

Added — Java compatibility layer

• src/config_compat.rs: reads Java application.yml (and application-{prod,dev,test}.yml) at boot, translates Spring-shape keys to Resql config, and records diagnostics for anything unsupported so operators know exactly what won't carry over.
• Auto-discovery of Java-style config paths at boot: /app/resql.yaml, ./resql.yaml, ./application.yml, ./application-{prod,dev,test}.yml — first hit wins.
• /datasources response uses Java-canonical camelCase (jdbcUrl, driverClassName) so existing Spring-era dashboards keep working. Driver class inferred from the pool type.
• /health shape extended with Java-canonical fields; /healthz alias preserved.
• Boot logs every captured compat diagnostic per Java §6.2 so operators can determine unsupported-feature dependencies from a single log read.

Added — Postgres integration test suite (task 006 — actually shipped in alpha.1, retroactively documented here)

• tests/integration_postgres.rs — now 24 tests covering type mapping (JSONB, TIMESTAMPTZ, NUMERIC, BOOLEAN, DATE), snake→camel columns, INSERT+RETURNING, batch endpoint, SQL errors, password masking, and the new atomicity + array binding + transactional-marker behaviours. Skips silently without TEST_POSTGRES_URL.
• Liquibase-managed schema and test fixtures (db/changelog/master.yaml + 001-schema.yaml + 002-test-fixtures.yaml). Test-only data is gated on context: test so production applications never see it.
• CI (tests.yml): Postgres 16 service container + Liquibase update step (--contexts=test) on both amd64 and arm64 matrix rows.
• Makefile with pg-up / pg-schema / test-pg / test-all targets for local Postgres development.
• New book chapter book/src/postgres-setup.md covering the Liquibase pattern, test workflow, and deployment recipes (init container + one-shot job).

Added — reference-shape regression tests

• tests/integration_reference_shapes.rs + tests/fixtures/*.json — golden-file assertions that /health, /datasources, and error responses keep the Java-canonical field set that Spring-era clients depend on.
• tests/integration_java_compat.rs — end-to-end tests for the compat translator (config parsing, diagnostic capture, health/datasources shape).
• Docs: docs/DIVERGENCES.md, docs/PORTING.md, docs/MIGRATION.md, docs/REFACTO-DEVIATIONS.md, docs/audits/2026-08-04-spec-compliance.md — explicit inventory of what differs from JVM Resql and why.

Changed

• Postgres testing promoted from backlog task 006 to a first-class CI requirement. The runtime image still ships without Liquibase or a JVM — schema is applied out-of-band.
• query::execute_batch and query::execute_transactional are the new atomicity entry points; the classic query::execute still works for non-transactional single-shot queries.

Fixed

• Postgres INT4 columns now decode correctly (previously fell through to null because the extractor only tried i64; sqlx-postgres decodes INT4 as i32). Surfaced by the new Postgres suite.
• Postgres NUMERIC columns preserve full precision as a JSON string (previously null because the required rust_decimal sqlx feature was off).

0.1.0-alpha.1 - 2026-07-29

Initial Rust rewrite of the Bürokratt Resql Spring Boot service. Interface-compatible with the original for the SQL-file-to-endpoint, :named-parameter, and snake→camel result semantics.

Added

• SQL file loader (sql/<project>/<GET|POST>/<name>.sql → <METHOD> /<project>/<name>).
• Named-parameter binder with awareness of string literals, comments, and Postgres :: casts.
• Repeated-parameter support: SELECT :x, :x, :y binds x once.
• Multi-datasource routing with three-tier resolution:
  1. X-Datasource request header (when allow_datasource_header: true).
  2. project_datasource_map entry.
  3. Project name as datasource name.
• Postgres and SQLite pools (dispatched by URL scheme).
• Batch endpoint at <POST-path>/batch.
• Health endpoint (/health and /healthz alias).
• Datasource listing endpoint (/datasources) with password masking.
• CORS layer + configurable body-size cap (413 on overflow).
• Structured JSON error responses matching JVM Resql shape.
• Docker image (multi-stage, non-root, tini, self-contained demo).
• CI: tests (matrix amd64 + arm64), security (audit + deny + daily cron), publish (multi-arch, provenance, SBOM, cosign, Trivy), docs (mdBook to Pages).
• mdBook: introduction, getting-started, configuration, sql-files, failure-modes.

Fixed (vs JVM Resql)

• Datasource-by-project routing (JVM version hardcoded a single datasource name — multi-database deployments were impossible without patching the source).
• Startup refuses to boot on any misconfigured datasource (JVM version silently ignored several).
• Passwords never appear in config file (JVM defaulted keystore password to "123456").
• Request body cap prevents unbounded memory growth.

Security

• deny.toml bans openssl, openssl-sys, serde_yaml (unmaintained).
• cargo audit --deny warnings runs on every push, PR, and daily cron.
• Container image signed with cosign keyless via GHA OIDC.
• Trivy HIGH/CRITICAL scan gates image signing.