REST-Jdbc Driver

Introduction

The RestJdbc driver enables reading and writing data from and to REST APIs via JDBC with SQL.

The following conventions apply:

the database connection uses jdbc:rest:
the JDBC URL contains the base URL of the REST API (jdbc:rest:https://…)
the API is described by a spec file (JSON or YAML); optionally, baseUrl in the spec can serve as a fallback when the JDBC URL has no HTTP URL
the schema public is used by default
entity names from the spec are used as tables (e.g. users, orders)
columns are defined in the spec; by default they map to top-level JSON fields (use jsonPath for nested fields)
the driver supports SELECT for reading and INSERT, UPDATE, and DELETE for writing

Features

REST API details are configured in a spec file in JSON or YAML. The file defines tables, columns, types, primary keys, and more.

Six authentication methods are available:

none — no authentication
basic — HTTP Basic Auth with user and password
bearer — authentication with a bearer token
oauth2 — OAuth 2.0 client credentials (token is fetched and refreshed automatically)
apikey — API key as a query parameter or HTTP header
clientcert — mutual TLS with a client certificate (keystore)

A SELECT is mapped to HTTP GET with a JSON response. The driver expects an array in the JSON response that contains the result rows.

The driver supports several pagination schemes:

none — no pagination
offset — pagination with limit and offset
page — pagination with a page number
nextLink — OData/REST pagination via an opaque URL in the JSON response (e.g. @odata.nextLink)

INSERT maps to HTTP POST.

DELETE removes records via HTTP DELETE.

UPDATE is supported and maps to HTTP PUT or HTTP PATCH.

With the FILTER clause, data can be filtered server-side for SELECT, UPDATE, or DELETE.

The driver provides system tables for available tables, columns, and primary keys.

JDBC URL

jdbc:rest:https://api.example.com/v1

The part after jdbc:rest: is the base URL of the REST API. It takes precedence over an optional baseUrl in the spec. If the JDBC URL has no HTTP URL, baseUrl in the spec can be used as a fallback.

Example connection (Java)

Properties props = new Properties();
props.setProperty("spec", "/path/to/api-spec.json");
props.setProperty("auth", "bearer");
props.setProperty("token", "my-bearer-token");

Connection conn = DriverManager.getConnection(
    "jdbc:rest:https://api.example.com/v1", props);

Connection properties

Property	Description	Required
`spec`	Path to the spec file (`.json`, `.yaml`, or `.yml`; filesystem or `classpath:...`)	Yes
`auth`	Auth method: `none`, `bearer`, `basic`, `oauth2`, `apikey`, `clientcert` (see below)	No
`password`	Password	For basic
`user`	Username	For basic
`token`	Bearer token	For bearer
`tokenUrl`	OAuth2 token endpoint	For oauth2 (or `tenant`)
`tenant`	Azure AD tenant ID or name; derives `tokenUrl` for Microsoft Graph	For oauth2 (alternative to `tokenUrl`)
`clientId`	OAuth2 client ID (`client_id` alias supported)	For oauth2
`clientSecret`	OAuth2 client secret (`client_secret` alias supported)	For oauth2
`scope`	OAuth2 scope (e.g. `https://graph.microsoft.com/.default`)	For oauth2, if required by the API
`oauth2GrantType`	`client_credentials` (default) or `device_code`	For oauth2
`deviceCodeUrl`	OAuth2 device code endpoint	For `device_code` (or use `tenant`)
`apiKey`	API key	For apikey
`apiKeyLocation`	Delivery: `query` (default) or `header`	For apikey
`apiKeyParam`	Query parameter or HTTP header name (default: `apiKey`)	For apikey
`keyStore`	Path to keystore with client certificate and private key	For clientcert
`keyStorePassword`	Password for the keystore	For clientcert
`keyStoreType`	Keystore type (default: `PKCS12`)	No
`keyPassword`	Password for the private key if different from `keyStorePassword`	No
`requestIntervalMs`	Minimum milliseconds between HTTP requests (0 = off)	No
`retryOn429`	Automatically retry on HTTP 429 (default: `true`)	No
`maxRetries`	Maximum retries on HTTP 429 (default: `5`)	No
`stdoutlog`	Debug output to stdout: `cursor`, `http`, `rows`, or `all` (combine with `+`, e.g. `cursor+http+rows`)	No

Property names are case-insensitive (e.g. apikey, requestintervalms, and stdoutlog also work).

Spec path:

/path/absolute/api-spec.json
/path/absolute/api-spec.yaml
classpath:de/softquadrat/jdbc/rest/mock-spec.json

Authentication

Auth method and credentials are configured via connection properties.

The auth property sets the method: none, bearer, basic, oauth2, apikey, or clientcert. If auth is not set, it is detected automatically:

user and password set → basic
only token set → bearer
only apiKey set → apikey
only keyStore set → clientcert
otherwise → none

Client certificate authentication works on the TLS layer (mutual TLS). It can be combined with bearer or basic if the API requires both mTLS and an Authorization header.

Server certificate validation uses the JVM default truststore (cacerts or -Djavax.net.ssl.trustStore=...). The driver does not provide separate truststore connection properties.

No authentication

props.setProperty("auth", "none");

Or simply omit auth properties.

Bearer token

props.setProperty("auth", "bearer");
props.setProperty("token", "eyJhbGciOiJIUzI1NiIs...");

The driver sends: Authorization: Bearer <token>

OAuth 2.0 client credentials

For APIs such as Microsoft Graph with application permissions (app-only, e.g. /users, /groups):

props.setProperty("auth", "oauth2");
props.setProperty("oauth2GrantType", "client_credentials"); // default, may be omitted
props.setProperty("tenant", "your-tenant-id");
props.setProperty("clientId", "your-app-client-id");
props.setProperty("clientSecret", "your-client-secret");
props.setProperty("scope", "https://graph.microsoft.com/.default");

Alternatively with an explicit token endpoint instead of tenant:

props.setProperty("auth", "oauth2");
props.setProperty("tokenUrl", "https://login.microsoftonline.com/your-tenant-id/oauth2/v2.0/token");
props.setProperty("clientId", "your-app-client-id");
props.setProperty("clientSecret", "your-client-secret");
props.setProperty("scope", "https://graph.microsoft.com/.default");

On connect, the driver obtains an access token via POST (grant_type=client_credentials) and sends it as Authorization: Bearer …. Before expiry (expires_in, with a 60-second buffer), the token is refreshed automatically.

OAuth 2.0 device code (delegated)

For delegated permissions with user sign-in (e.g. Microsoft Graph /me/messages). The driver prints a URL and code, waits for sign-in, then uses refresh tokens:

props.setProperty("auth", "oauth2");
props.setProperty("oauth2GrantType", "device_code");
props.setProperty("tenant", "your-tenant-id");
props.setProperty("clientId", "your-app-client-id");
props.setProperty("scope", "Mail.Read offline_access");

clientSecret is optional (public client). offline_access enables refresh-token renewal.

HTTP Basic

props.setProperty("auth", "basic");
props.setProperty("user", "api-user");
props.setProperty("password", "secret");

The driver sends: Authorization: Basic <base64(user:password)>

API key

With auth=apikey, the key is sent either as a query parameter or as an HTTP header. The apiKeyLocation property sets this (query or header, default: query). apiKeyParam is the parameter or header name (default: apiKey).

Query parameter

For APIs that expect the key in the URL (e.g. OpenWeather with appid):

props.setProperty("auth", "apikey");
props.setProperty("apiKey", "your-api-key");
props.setProperty("apiKeyLocation", "query");
props.setProperty("apiKeyParam", "appid");

The driver appends appid=your-api-key to every request URL. apiKeyLocation=query can be omitted because it is the default.

HTTP header

For APIs that expect the key in a header (e.g. header name apikey):

props.setProperty("auth", "apikey");
props.setProperty("apiKey", "your-api-key");
props.setProperty("apiKeyLocation", "header");
props.setProperty("apiKeyParam", "apikey");

The driver sends on every request: apikey: your-api-key

Client certificate (mTLS)

For APIs that require a client certificate during the TLS handshake:

props.setProperty("auth", "clientcert");
props.setProperty("keyStore", "/path/to/client.p12");
props.setProperty("keyStorePassword", "secret");
// optional:
props.setProperty("keyStoreType", "PKCS12");
props.setProperty("keyPassword", "secret");

If keyStoreType is omitted, PKCS12 is used (typical for .p12 / .pfx files). Other types such as JKS are supported if available in the JVM (KeyStore.getInstance(...)).

Combined with bearer token:

props.setProperty("keyStore", "/path/to/client.p12");
props.setProperty("keyStorePassword", "secret");
props.setProperty("auth", "bearer");
props.setProperty("token", "my-bearer-token");

Note: OAuth2 token acquisition (login flow) is not yet implemented in the driver. The token must be obtained externally and passed as a property.

Rate limits

Some APIs limit the number of requests per time unit. The driver supports proactive throttling and automatic retry on HTTP 429:

Property	Description	Default
`requestIntervalMs`	Minimum milliseconds between HTTP request starts (`0` = off)	`0`
`retryOn429`	Automatically retry on HTTP 429	`true`
`maxRetries`	Maximum retries on HTTP 429	`5`

With requestIntervalMs=1000, the driver does not wait a fixed 1 second after every call. It tracks the start time of the last request and waits only for the remaining time until the interval has elapsed. This is especially efficient with pagination: if the last request was fast and more than 1 second ago, the next one starts immediately.

On HTTP 429, the request is retried automatically. Wait time comes from the Retry-After header (seconds or HTTP date) or — if not set — from requestIntervalMs (at least 1000 ms).

props.setProperty("requestIntervalMs", "1000");
props.setProperty("retryOn429", "true");

Spec file

Every REST API to be connected requires a spec file in JSON or YAML format. The format is detected by file extension: .json → JSON, .yaml / .yml → YAML. Complete examples: spec-example.json, spec-example.yaml.

Structure (JSON)

Example file: spec-example.json

Excerpt (the base URL is set in the JDBC URL, not in the spec):

{
  "entities": [
    {
      "name": "users",
      "path": "/users",
      "pagination": {
        "type": "offset",
        "limitParam": "limit",
        "offsetParam": "offset",
        "defaultLimit": 100
      },
      "columns": [
        { "name": "id", "type": "BIGINT", "primaryKey": true },
        { "name": "name", "type": "VARCHAR" },
        { "name": "email", "type": "VARCHAR" }
      ]
    }
  ]
}

Structure (YAML)

Example file: spec-example.yaml

Same content as above — field names and structure are identical:

entities:
  - name: users
    path: /users
    pagination:
      type: offset
      limitParam: limit
      offsetParam: offset
      defaultLimit: 100
    columns:
      - name: id
        type: BIGINT
        primaryKey: true
      - name: name
        type: VARCHAR
      - name: email
        type: VARCHAR

Spec fields

Each entity entry defines a table.

Field	Description
`baseUrl`	API base URL (optional fallback when the JDBC URL has no HTTP URL)
`entities`	List of JDBC “tables”
`entities[].name`	Table name
`entities[].columns`	Columns with JDBC type
`entities[].path`	REST path
`entities[].dataPath`	JSON Pointer
`entities[].filterParam`	Query parameter for server-side FILTER clause (content passed through unchanged)
`entities[].selectParam`	Query parameter for column selection from SELECT (e.g. OData `$select`)
`entities[].orderByParam`	Query parameter for ORDERBY clause
`entities[].pagination`	Pagination (see below)
`entities[].write`	`false` = disable all write operations (default: `true`)
`entities[].insert`	Optional: override for INSERT, or `false` to disable
`entities[].update`	Optional: override for UPDATE, or `false` to disable
`entities[].delete`	Optional: override for DELETE, or `false` to disable

Required fields:

name — table name as used in SQL
columns — columns with names, data types, and optional primary key flag
path — REST path for API calls
dataPath — JSON Pointer to the row list in query results (see below)

All other entries are optional.

Column types

Supported types in the spec: BIGINT, INTEGER, BOOLEAN, DOUBLE, TIMESTAMP, DATE, JSON, plus the character and numeric types described below.

Primary keys are marked with "primaryKey": true on the column.

SQL-like length and precision

For VARCHAR, CHAR, VARBINARY, and DECIMAL, length and precision can be specified directly in the type field using SQL notation. The values appear in JDBC metadata (type_name, column_size, decimal_digits) and in system.column_list.

Type	Syntax	Example	Metadata
`VARCHAR`	`VARCHAR` or `VARCHAR(n)`	`"type": "VARCHAR(255)"`	`column_size` = n
`CHAR`	`CHAR` or `CHAR(n)`	`"type": "CHAR(10)"`	`column_size` = n
`VARBINARY`	`VARBINARY` or `VARBINARY(n)`	`"type": "VARBINARY(64)"`	`column_size` = n
`DECIMAL`	`DECIMAL`, `DECIMAL(p)`, or `DECIMAL(p,s)`	`"type": "DECIMAL(10,2)"`	`column_size` = p, `decimal_digits` = s
`NUMERIC`	Alias for `DECIMAL`	`"type": "NUMERIC(18)"`	same as `DECIMAL`

Notes:

Case is ignored (varchar(255) = VARCHAR(255)).
DECIMAL without parentheses is still treated as DOUBLE (backward compatibility). With parentheses (DECIMAL(p) or DECIMAL(p,s)), the JDBC type is DECIMAL.
Length and precision are not enforced at runtime; they describe metadata for downstream tools.
Invalid definitions (e.g. VARCHAR(0), DECIMAL(2,5), INTEGER(10)) cause an error when the spec is loaded.

Example:

"columns": [
  { "name": "code", "type": "CHAR(10)" },
  { "name": "email", "type": "VARCHAR(255)" },
  { "name": "payload", "type": "VARBINARY(256)" },
  { "name": "amount", "type": "DECIMAL(10,2)" }
]

Optional per column (for nested JSON mapping):

Field	Description
`jsonPath`	JSON Pointer for read and write (RFC 6901, same syntax as `dataPath`)
`readPath`	Read-only override; defaults to `jsonPath`
`writePath`	Write-only override; defaults to `jsonPath`; `null` = read-only column
`readOnly`	Ignore column on INSERT/UPDATE
`writeOnly`	Do not populate from API response
`defaultWrite`	Default value for INSERT when the column is omitted
`selectName`	API field name for `$select` (default: column name or first segment of `jsonPath`)

Without jsonPath, the column name is used as the top-level JSON field (previous behaviour).

jsonPath — nested JSON fields

Many APIs (e.g. Microsoft Graph) nest data in the JSON body. Use jsonPath to map SQL columns to nested locations:

{
  "name": "start_dateTime",
  "type": "TIMESTAMP",
  "jsonPath": "/start/dateTime"
},
{
  "name": "start_timeZone",
  "type": "VARCHAR",
  "jsonPath": "/start/timeZone",
  "defaultWrite": "Europe/Berlin"
}

INSERT INTO calendar_events (subject, "start_dateTime", "end_dateTime")
VALUES ('Review', '2026-06-26T10:00:00', '2026-06-26T11:00:00');

Request body:

{
  "subject": "Review",
  "start": { "dateTime": "2026-06-26T10:00:00", "timeZone": "Europe/Berlin" },
  "end":   { "dateTime": "2026-06-26T11:00:00", "timeZone": "Europe/Berlin" }
}

For complex structures (e.g. attendee lists), use type JSON and pass a JSON literal in SQL:

{ "name": "attendees", "type": "JSON", "jsonPath": "/attendees", "writeOnly": true }

On SELECT, values are read from the same paths. Columns with underscores in the name must be quoted in SQL ("start_dateTime").

dataPath — where are the rows in the JSON?

To parse the JSON response, navigate to the array of records using a JSON Pointer. See the JSON Pointer RFC for syntax.

API response	dataPath
Flat array `[{...},{...}]`	`""` (or omit the field)
`{ "data": [{...}] }`	`/data`
`{ "items": [{...}] }`	`/items`

The empty JSON Pointer "" denotes the root document per RFC 6901 — for a direct array response, that is the array itself. / is not the root.

Flat array — response is a JSON array directly (default, omit dataPath or use ""):

[
  { "id": 1, "title": "Hello" },
  { "id": 2, "title": "World" }
]

Nested under data (dataPath: "/data"):

{
  "data": [
    { "id": 1, "name": "Alice" },
    { "id": 2, "name": "Bob" }
  ]
}

Nested under items (dataPath: "/items"):

{
  "items": [
    { "sku": "A-100", "qty": 5 },
    { "sku": "B-200", "qty": 12 }
  ]
}

Only flat fields at the top level of each array element are read as columns by default. Nested objects and arrays are serialized as strings — unless you map them with jsonPath (see above).

API response:

[
  {
    "id": 1,
    "name": "Alice",
    "address": { "city": "Berlin", "zip": "10115" },
    "tags": ["vip", "beta"]
  }
]

Result as JDBC row (one column per top-level field):

id	name	address	tags
1	Alice	`{"city":"Berlin","zip":"10115"}`	`["vip","beta"]`

The fields city and zip inside address are not separate columns unless you add jsonPath entries (e.g. "jsonPath": "/address/city").

Pagination

type	Meaning	Parameters
`none`	One request, all rows	—
`offset`	Limit/offset	`limitParam`, `offsetParam`, `defaultLimit`
`page`	Page number (1-based)	`limitParam`, `pageParam`, `defaultLimit`
`nextLink`	Opaque URL from the response	`limitParam`, `nextLinkPath`, `defaultLimit`

defaultLimit is the page size (number of rows per request). For offset and page, the driver automatically fetches further pages until the API returns an empty list or fewer rows than defaultLimit. For nextLink, the driver follows the URL from nextLinkPath (default: /@odata.nextLink) until no link is returned.

No pagination (`none`)

The API returns all records in one request:

"pagination": { "type": "none" }

SELECT id, name FROM users;

→ one call: GET /users

Offset pagination

Spec when the API expects limit and offset as query parameters:

"pagination": {
  "type": "offset",
  "limitParam": "limit",
  "offsetParam": "offset",
  "defaultLimit": 2
}

SELECT id, name FROM users;

Example: 5 records in the API, page size 2 — the driver executes sequentially:

#	HTTP request	Response (excerpt)
1	`GET /users?limit=2&offset=0`	`[{"id":1,"name":"Alice"},{"id":2,"name":"Bob"}]`
2	`GET /users?limit=2&offset=2`	`[{"id":3,"name":"Carol"},{"id":4,"name":"Dave"}]`
3	`GET /users?limit=2&offset=4`	`[{"id":5,"name":"Eve"}]`

After request 3, fetching stops (only 1 row, less than defaultLimit). The result set contains all 5 rows — for the application it behaves like a single SELECT.

Page pagination

Spec when the API expects limit and page (starting at page 1):

"pagination": {
  "type": "page",
  "limitParam": "limit",
  "pageParam": "page",
  "defaultLimit": 2
}

SELECT id, name FROM users;

Same 5 records, page size 2:

#	HTTP request	Response (excerpt)
1	`GET /users?limit=2&page=1`	`[{"id":1,"name":"Alice"},{"id":2,"name":"Bob"}]`
2	`GET /users?limit=2&page=2`	`[{"id":3,"name":"Carol"},{"id":4,"name":"Dave"}]`
3	`GET /users?limit=2&page=3`	`[{"id":5,"name":"Eve"}]`

nextLink pagination (OData)

For APIs such as Microsoft Graph that return @odata.nextLink in the JSON response:

"dataPath": "/value",
"pagination": {
  "type": "nextLink",
  "limitParam": "$top",
  "nextLinkPath": "/@odata.nextLink",
  "defaultLimit": 100
}

The first request uses $top; subsequent pages are fetched via the complete nextLink URL (without custom query parameters).

JSONPlaceholder example

See JSONPlaceholder — including pagination with _limit/_page and the full spec.

Writing — defaults and overrides

Every entity requires an explicit path in the spec (REST endpoint for GET {baseUrl}{path}). The driver does not derive it from the SQL table name (name) — name and path can differ (e.g. "name": "orders", "path": "/api/v2/order").

The same path applies to writing. INSERT, UPDATE, and DELETE are enabled by default and mapped as follows:

SQL	Default HTTP
`INSERT INTO …`	`POST {path}`
`UPDATE … FILTER id=1`	`PUT {path}/{id}`
`DELETE … FILTER id=1`	`DELETE {path}/{id}`

Default behavior: writing is allowed; HTTP method and URL pattern are derived from the path ({path} for INSERT, {path}/{id} for UPDATE/DELETE).

{id} in the path is replaced by the value from FILTER param=value (e.g. FILTER id=1 → /posts/1).

Override for a different API

Path and method can be overridden, for example for UPDATE:

{
  "name": "posts",
  "path": "/posts",
  "update": { "path": "/v2/orders/{id}", "method": "PATCH" }
}

Disabling writes

Writing can be disabled as follows:

{
  "name": "users",
  "path": "/users",
  "write": false
}

Or per operation: "insert": false, "update": false, "delete": false.

SQL syntax

Basic SELECT query

SELECT id, name, email
FROM users

This corresponds to: GET {baseUrl}/users.

FILTER clause

The FILTER clause forwards filters to the REST API — server-side, not local like WHERE. Behavior depends on the spec:

Default: `param=value`

Without filterParam in the spec, syntax is param=value:

SELECT id, name FROM users FILTER id=2
;

SELECT id, title FROM posts FILTER userId=1
;

DELETE FROM posts FILTER id=1
;

This produces e.g. GET /users?id=2, GET /posts?userId=1, DELETE /posts/1.

The name to the left of = is the HTTP query parameter or placeholder in the UPDATE/DELETE path ({id}).

API with a generic search parameter (e.g. q):

SELECT id, name FROM users FILTER q=status = 'active'
;

→ GET /users?q=status+%3D+%27active%27

Multiple parameters with `AND`

Without filterParam, multiple query parameters can be combined in one FILTER clause:

SELECT company_id, company_name
FROM wem_company
FILTER company_id='12345'
;

→ GET /company?company_id=12345

AND is case-insensitive. Use single quotes for string values. See the Webmetic example.

With `filterParam`: native API expression

If filterParam is set in the spec (e.g. "$filter" for OData/Microsoft Graph), the entire FILTER content is passed unchanged to that query parameter — like Salesforce (SOQL) or Eloqua (OData):

"filterParam": "$filter"

SELECT id, "displayName" FROM users
  FILTER startswith(displayName,'A') AND accountEnabled eq true
;

→ GET /users?$filter=startswith(displayName,'A')+AND+accountEnabled+eq+true

For UPDATE and DELETE, FILTER param=value is still required to identify the resource in the path.

Note: WHERE filters locally on already loaded rows. For REST APIs, use FILTER.

If FILTER syntax is invalid, the driver reports the received FILTER text and a specific hint (e.g. missing =, OR instead of AND). The HTTP URL is not built yet in that case — for successful requests, use stdoutlog=http.

ORDERBY clause

If orderByParam is set in the spec, it can be used in the query:

SELECT id, name
FROM users
ORDERBY name ASC

Note: The keyword is ORDERBY (datasqill dialect), not ORDER BY.

Column selection (`selectParam`)

If selectParam is set in the spec (e.g. "$select" for OData), the driver sends only columns referenced in SELECT to the API. Field names come from the spec:

column name when no jsonPath is set
selectName when configured
otherwise the first segment of jsonPath (e.g. /from/emailAddress/address → from)

"selectParam": "$select"

SELECT id, "displayName", mail FROM users
;

→ GET /users?$select=id,displayName,mail

Without selectParam, all columns defined in the spec are requested (previous behavior).

Column names and quoting

Unquoted identifiers are normalized to lowercase by the SQL parser (userId → userid). Column names in the spec with mixed case (e.g. userId from JSON APIs) must be quoted in INSERT and UPDATE:

INSERT INTO posts ("userId", title, body)
VALUES (1, 'New post', 'Content')
;

UPDATE posts SET title = 'New title' FILTER id=1
;

The same applies in SELECT for columns with mixed case:

SELECT id, "userId", title FROM posts
;

FILTER parameters are passed as text to the API and are not affected by column name normalization (FILTER userId=1 stays userId).

INSERT

INSERT writes new records via the REST API. By default this results in POST to the entity path. It can be overridden:

"insert": { "path": "/v2/posts", "method": "POST" }

INSERT INTO posts ("userId", title, body)
VALUES (1, 'New post', 'Content')
;

This corresponds to: POST {baseUrl}/posts with a JSON body from the columns/values.

UPDATE

UPDATE modifies records via the REST API. A FILTER is required. By default HTTP PUT is used, e.g. PUT to {path}/{id}. This can be overridden with PATCH in the spec:

"update": { "method": "PATCH" }

UPDATE posts SET title = 'New title', body = 'New content' FILTER id=1
;

This corresponds to: PUT {baseUrl}/posts/1 (or PATCH with override).

DELETE

DELETE removes records in the REST API via HTTP DELETE. By default the path from the spec is used ({path}/{id}). The path can also be overridden in the spec:

"delete": { "path": "/posts/{id}" }

DELETE FROM posts FILTER id=1
;

The value from id=1 replaces {id} in the path → DELETE {baseUrl}/posts/1.

Path parameters in the spec

If an entity path contains placeholders such as {userId}, they are bound from FILTER or INSERT:

"path": "/users/{userId}/events",
"update": { "path": "/users/{userId}/events/{id}", "method": "PATCH" }

SELECT id, subject FROM user_calendar_events FILTER userId='GUID' AND startswith(subject,'Team');

INSERT INTO user_calendar_events ("userId", subject, "start_dateTime", "end_dateTime")
VALUES ('GUID', 'Meeting', '2026-07-01T10:00:00', '2026-07-01T11:00:00');

UPDATE user_calendar_events SET subject = 'Updated' FILTER userId='GUID' AND id=EVENT-ID;

Path parameters (userId=…) come first in FILTER, separated by AND from the OData part ($filter) or further path parameters (id=…).

System tables

The system schema provides virtual tables:

Table	Content
`table_list`	All entities from the spec
`column_list`	Columns of all entities
`pk_list`	Primary keys from the spec

SELECT table_name, table_type, remarks
FROM system.table_list
WHERE table_name = 'users'
;

SELECT column_name, type_name, column_size, decimal_digits
FROM system.column_list
WHERE table_name = 'users'
;

SELECT table_name, column_name, key_seq
FROM system.pk_list
WHERE table_name = 'users'
;

Troubleshooting

For debugging connection and query issues:

stdoutlog=cursor+http+rows

Level	Output
`cursor`	Opened table and FILTER text
`http`	HTTP method and full URL including query parameters
`rows`	Number of rows received per HTTP response and total when the cursor closes
`all`	all levels

Common errors:

Message	Cause / fix
`FILTER could not be mapped to query parameters`	Check FILTER syntax: `param=value` with `AND`, strings in `'…'`
`HTTP 401` / `HTTP 403`	Check auth (e.g. Webmetic: `auth=apikey`, header `Authorization` without `Bearer`)
`HTTP 429`	Set `requestIntervalMs` (Webmetic: `1000`), see Rate limits
Property not recognized	Property names are case-insensitive; check values

Compatible REST APIs

Overview of well-known APIs that can be connected with a spec file:

Compatible REST APIs

Examples

Step-by-step guides for concrete REST APIs:

JSONPlaceholder — public test API without authentication
GitHub — GitHub API with bearer token
REST Countries — country data (read-only, pagination, jsonPath)
OpenWeatherMap — weather forecast with API key and FILTER q=…
Microsoft Graph — Microsoft 365 (OAuth2, OData FILTER, nextLink, $select)
Webmetic — B2B company master data (wem_company), pagination, and rate limiting (requestIntervalMs=1000)

Notes

Nested JSON

Fields like "address": { "city": "Berlin" } are not automatically exposed as column address.city. Only top-level fields of each array element are columns.

Error handling

HTTP errors (4xx, 5xx) result in SQLException with status code and response body.