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 jsonPointer for nested fields)
• the driver supports SELECT for reading and INSERT, UPDATE, and DELETE for writing

Quick start

JSONPlaceholder is a free REST-service that returns users as a JSON array, with no authentication and in one request. It is enough to try the driver before adding authentication and pagination.

The response of GET /users starts like this:

[
  {
    "id": 1,
    "name": "Leanne Graham",
    "username": "Bret",
    "email": "Sincere@april.biz",
    "address": { "city": "Gwenborough" }
  }
]

A spec maps the flat fields to a table. address is left out. Without pagination, the default is none: one SELECT is one GET.

{
  "entities": [
    {
      "name": "users",
      "path": "/users",
      "columns": [
        { "name": "id", "type": "BIGINT", "primaryKey": true },
        { "name": "name", "type": "VARCHAR" },
        { "name": "email", "type": "VARCHAR" }
      ]
    }
  ]
}

Save it, for example, as users-spec.json next to the call. The base URL belongs in the JDBC URL, not in the spec. No auth property is required.

Properties props = new Properties();
props.setProperty("spec", "/path/to/users-spec.json");

Connection conn = DriverManager.getConnection(
    "jdbc:rest:https://jsonplaceholder.typicode.com", props);

In the JDBC client:

connect 'jdbc:rest:https://jsonplaceholder.typicode.com|spec=users-spec.json'

SELECT id, name, email FROM users;

The text after | is a connection property of the datasqill driver. In the Simple Client the same connection is opened with properties(...), and the URL stays without a suffix:

connect 'jdbc:rest:https://jsonplaceholder.typicode.com' properties(spec=users-spec.json)

See Properties in the JDBC URL and Simple Client.

id	name	email
1	Leanne Graham	Sincere@april.biz

The SELECT becomes GET https://jsonplaceholder.typicode.com/users.

FILTER, writing, and pagination are in the JSONPlaceholder example.

Features

REST API details are configured in a spec file in JSON or YAML. The file defines tables, columns, types, primary keys, and more. Shared settings and column lists can be reused via defaults and structures.

Seven authentication methods are available:

• none — no authentication
• basic — HTTP Basic Auth with user and password
• bearer — authentication with a bearer token
• jwt — locally signed HS256 JWT (for example Momentus Enterprise / Ungerboeck)
• 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 default is object rows. With rowFormat: "tabular" the same entity reads a column header and value rows instead; see Tabular 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.

HTTP redirects (301, 302, 303, 307, 308) are followed automatically; the Authorization header is kept even when the host changes (e.g. Power BI preferClientRouting=true).

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);

If the client can pass only the URL, the same properties follow the first |, for example jdbc:rest:https://api.example.com/v1|spec=/path/to/api-spec.json,auth=bearer,token=my-bearer-token. The rules are in Properties in the JDBC URL. The Simple Client uses properties(...) instead.

Connection properties

Property	Group	Description	Required
spec	Spec file	Path to the spec file (.json, .yaml, or .yml; filesystem or classpath:...)	Yes
auth	Auth property	Auth method: none, bearer, basic, jwt, oauth2, apikey, clientcert (see below)	No
password	Auth property	Password	For basic
user	Auth property	Username	For basic
token	Auth property	Bearer token	For bearer
tokenurl	Auth property	OAuth2 token endpoint	For oauth2 (or tenant)
tenant	Auth property	Azure AD tenant ID or name; derives tokenurl for Microsoft Graph	For oauth2 (alternative to tokenurl)
clientid	Auth property	OAuth2 client ID (client_id alias supported)	For oauth2
clientsecret	Auth property	OAuth2 client secret (client_secret alias supported)	For oauth2
scope	Auth property	OAuth2 scope (e.g. https://graph.microsoft.com/.default)	For oauth2, if required by the API
oauth2granttype	Auth property	client_credentials (default) or device_code	For oauth2
devicecodeurl	Auth property	OAuth2 device code endpoint	For device_code (or use tenant)
jwtissuer	Auth property	JWT claim iss (Momentus: API User ID)	For jwt
jwtkey	Auth property	JWT claim key (Momentus: API User Key)	For jwt
jwtsecret	Auth property	HMAC-SHA256 secret (Momentus: API User Secret); not a claim	For jwt
jwtttl	Auth property	Token lifetime in seconds (default: 60)	No
jwtsubject	Auth property	Optional JWT claim sub (Momentus: proxied user)	No
apikey	Auth property	API key	For apikey
apikeylocation	Auth property	Delivery: query (default) or header	For apikey
apikeyparam	Auth property	Query parameter or HTTP header name (default: apiKey)	For apikey
keystore	Auth property	Path to keystore with client certificate and private key	For clientcert
keystorepassword	Auth property	Password for the keystore	For clientcert
keystoretype	Auth property	Keystore type (default: PKCS12)	No
keypassword	Auth property	Password for the private key if different from keystorepassword	No
requestintervalms	Retry property	Minimum milliseconds between HTTP requests (0 = off)	No
retryon429	Retry property	Automatically retry on HTTP 429 (default: true)	No
maxretries	Retry property	Maximum retries on HTTP 429 (default: 5)	No
stdoutlog	Misc	Debug output to stdout: cursor, http, rows, warn, or all (combine with +, e.g. cursor+http+rows)	No

For the authentication properties see section Authentication, for the retry properties see section Rate limits.

Property names are case-insensitive. CamelCase such as clientId or requestIntervalMs also works.

Spec path:

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

SQL syntax

SELECT

SELECT id, name, email
FROM users

This query is executed by the driver as GET {baseUrl}/users.

INSERT

INSERT writes new records via the REST API. By default this results in POST to the entity path.

The following example creates a user on JSONPlaceholder. The table users has the path /users in the spec.

INSERT INTO users (name, username, email)
VALUES ('Ada Lovelace', 'ada', 'ada@example.com')

The driver sends POST https://jsonplaceholder.typicode.com/users for this statement. The JSON body is {"name":"Ada Lovelace","username":"ada","email":"ada@example.com"}.

Caution: The JSONPlaceholder test API does not store the user permanently.

The path can be overridden:

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

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

This example results in POST {baseUrl}/v2/posts. The JSON body is built from the columns and values of the INSERT statement.

UPDATE

UPDATE modifies records via the REST API. A FILTER is required. By default HTTP PUT is used, e.g. PUT to {path}/{id}.

The following example changes a user's email address on JSONPlaceholder. The table users has the path /users in the spec.

UPDATE users SET email = 'ada@example.com' FILTER id=1

The driver sends PUT https://jsonplaceholder.typicode.com/users/1 for this statement. The JSON body is {"email":"ada@example.com"}.

The body contains only the columns from SET. FILTER id=1 replaces {id} in the URL and is not part of the JSON. PUT and PATCH use the same body. The driver does not read the record first and does not fill in missing fields. An UPDATE that sets a single column therefore stays a partial object, even when the method is PUT.

The method can be overridden with PATCH in the spec:

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

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

This example results in PUT {baseUrl}/posts/1. The JSON body is {"title":"New title","body":"New content"}. If the spec sets "method": "PATCH", the driver sends PATCH with the same body.

DELETE

DELETE removes records in the REST API via HTTP DELETE. By default the path from the spec is used ({path}/{id}).

The following example deletes a user on JSONPlaceholder. The table users has the path /users in the spec.

DELETE FROM users FILTER id=1

The driver sends DELETE https://jsonplaceholder.typicode.com/users/1 for this statement.

The path can be overridden in the spec:

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

DELETE FROM posts FILTER id=1

The value from id=1 replaces the {id} placeholder in the path. The driver therefore sends DELETE {baseUrl}/posts/1.

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).

FILTER, ORDERBY, and further clauses

The FILTER clause forwards conditions to the REST API, on the server rather than locally like WHERE. ORDERBY passes the sort order to the API (datasqill dialect, not ORDER BY). selectParam and expandParam in the spec control which columns and related resources are requested. Placeholders in the path, such as {userId}, are bound from FILTER or INSERT.

These further features are described in Further SQL features.

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)

The complete example file is 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)

The complete example file is spec-example.yaml.

The content and the field names are the same as in the JSON example:

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)
defaults	Default values for entity fields (write, pagination, dataPath, rowFormat, columnsPath, unknownColumns, filterParam, selectParam, expandParam, structure, …); entity values take precedence
structures	Named column lists that multiple tables can share
entities	List of JDBC “tables”
entities[].name	Table name
entities[].columns	Columns with JDBC type (alternative to structure)
entities[].structure	Name of a shared structure from structures (alternative to columns)
entities[].path	REST path; {name} placeholders are filled from FILTER (param=value). A static query string is allowed (/groups?preferClientRouting=true); further parameters are appended with &. Avoid this for writes — defaults would build {path}/{id} after the ?. Prefer a column with binding: "query" for constant query parameters
entities[].dataPath	JSON Pointer to the row list
entities[].rowFormat	object (default) or tabular. tabular reads a column header and value rows instead of object rows; see Tabular rows
entities[].columnsPath	JSON Pointer to the column header. Required when rowFormat is tabular; must start with /
entities[].unknownColumns	How to treat header columns with no spec column: error (default), warn, or ignore. Only for rowFormat: "tabular"
entities[].filterParam	Query parameter for the remaining FILTER after path/query bindings (content passed through unchanged)
entities[].selectParam	Query parameter for column selection from SELECT (e.g. OData $select)
entities[].expandParam	Query parameter for related resources (e.g. OData $expand)
entities[].expand	Default $expand value: string or list of strings
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

These fields are required:

• name — table name as used in SQL
• columns or structure — columns with names, data types, and optional primary key flag, or a reference to a shared structure
• 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

The spec accepts these types.

Type	Syntax	JDBC type	Note
BIGINT	BIGINT	BIGINT	—
INTEGER	INTEGER	INTEGER	Alias INT
BOOLEAN	BOOLEAN	BOOLEAN	—
DOUBLE	DOUBLE	DOUBLE	—
TIMESTAMP	TIMESTAMP	TIMESTAMP	—
DATE	DATE	TIMESTAMP	Type name stays DATE. Read the same way as TIMESTAMP.
JSON	JSON	VARCHAR	Type name stays JSON. Not a SQL type.
VARCHAR	VARCHAR or VARCHAR(n)	VARCHAR	column_size = n
CHAR	CHAR or CHAR(n)	CHAR	column_size = n
VARBINARY	VARBINARY or VARBINARY(n)	VARBINARY	column_size = n
DECIMAL	DECIMAL, DECIMAL(p), or DECIMAL(p,s)	DOUBLE or DECIMAL	Without parentheses, DOUBLE. With parentheses, DECIMAL; column_size = p, decimal_digits = s.
NUMERIC	NUMERIC(p) or NUMERIC(p,s)	DECIMAL	Same as DECIMAL with parentheses. Without parentheses, VARCHAR.

JSON is a REST spec type, not a SQL type. JDBC reports VARCHAR (type name JSON). On read, objects and arrays are returned as JSON text, same as VARCHAR. On write, the string is parsed and inserted as a JSON tree (array/object), not as a JSON string.

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

Length and precision

For VARCHAR, CHAR, VARBINARY, DECIMAL, and NUMERIC, length and precision can be written directly in the type field. n, p, and s appear in JDBC metadata (column_size, decimal_digits) and in system.column_list. They are not checked at runtime.

• Case is ignored (varchar(255) = VARCHAR(255)).
• If the scale is omitted in DECIMAL(p) or NUMERIC(p), it is 0. The scale must not exceed the precision.
• An empty type or an unknown type without parentheses is loaded as VARCHAR.
• Invalid definitions (e.g. VARCHAR(0), DECIMAL(2,5), INTEGER(10)) cause an error when the spec is loaded.

An example of these type definitions:

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

Optional column fields

The following fields are optional on each column:

Field	Description
jsonPointer	JSON Pointer for read and write (RFC 6901, same syntax as dataPath). "" = whole row object
readPath	Read-only override; defaults to jsonPointer
writePath	Write-only override; defaults to jsonPointer; 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 jsonPointer)
expand	$expand fragment sent when this column is selected (e.g. attachments)
binding	Request routing: path (URI placeholder) or query (dedicated query parameter). Alias: parameterType
required	With binding: "query": FILTER must supply this parameter
sourceName	Exact name in the response header when rowFormat is tabular. Defaults to name. Excludes sourcePosition
sourcePosition	Index in the header when rowFormat is tabular. 0 is the first column, -1 the last
sourceMeta	Only columnName: the column returns the header name at that position, not the cell value

Without jsonPointer, the column name is used as the top-level JSON field (previous behaviour). sourceName, sourcePosition, and sourceMeta apply to tabular rows. On object rows they are a spec error, as are columnsPath and unknownColumns on the entity.

The empty pointer "" denotes the whole row object (after dataPath) per RFC 6901, not the complete HTTP body:

{ "name": "row", "type": "VARCHAR", "jsonPointer": "" }

SELECT id, row
FROM users

The column returns the row object as JSON text — exactly what the API returned after dataPath (and $select, if any). SELECT row fetches the full object because no $select is sent. SELECT id, row sends $select=id; row then contains the reduced JSON. Writing via jsonPointer: "" is not supported (the root object cannot be replaced by pointer); without an explicit writePath the column is read-only. VARCHAR is enough as the column type, because the column only reads the JSON text. The spec type JSON would differ only on write: the driver then inserts the value as a JSON tree rather than as text.

jsonPointer — nested JSON fields

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

{
  "name": "start_dateTime",
  "type": "TIMESTAMP",
  "jsonPointer": "/start/dateTime"
},
{
  "name": "start_timeZone",
  "type": "VARCHAR",
  "jsonPointer": "/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')

The request body looks like this:

{
  "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", "jsonPointer": "/attendees", "writeOnly": true }

On SELECT, the driver reads values from the same paths it uses when writing. A column name with mixed case must be quoted in SQL. Without quotes the parser turns start_dateTime into start_datetime, and the name no longer matches the spec. The query therefore uses "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. The response is a JSON array directly. That is the default. dataPath can be omitted or set to "".

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

Nested under data. The rows are in the data field. Set dataPath to "/data" for that.

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

Nested under items. The rows are in the items field. Set dataPath to "/items" for that.

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

For object rows (the default, when rowFormat is omitted or object), only flat fields at the top level of each array element are read as columns — unless you set jsonPointer. Nested objects and arrays are otherwise serialized as strings (see jsonPointer 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 become separate columns only with jsonPointer (e.g. "jsonPointer": "/address/city").

Further topics

defaults and structures share column lists across tables. rowFormat: "tabular" reads a header and value rows; those tables are read-only. Writing defaults to POST, PUT, and DELETE on the entity path and can be overridden or disabled in the spec. These further features are in Further spec features.

Pagination (none, offset, page, nextLink) has its own chapter Pagination.

Authentication

Auth method and credentials are configured via connection properties.

The auth property sets the method: none, bearer, basic, jwt, 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

oauth2 and jwt are not detected from their properties. Set auth explicitly.

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");

Alternatively, set no auth properties. The driver then detects the method none.

Bearer token

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

The driver sends the header Authorization: Bearer <token>.

JWT (locally signed)

For APIs that validate a short-lived HS256 token signed by the client, such as Momentus Enterprise (Ungerboeck). The driver does not call a token endpoint. It builds the JWT and sends Authorization: Bearer <jwt>.

Claim	Property	Momentus field
iss	jwtissuer	API User ID, from the API User window
key	jwtkey	API User Key (GUID)
sub	jwtsubject	Proxied user; omit the property if unused
iat, exp	jwtttl	Lifetime in seconds; default 60

jwtsecret is the HMAC key (the API User Secret) and is not placed in the token. Header: {"typ":"JWT","alg":"HS256"}. iat and exp are Unix seconds.

The same token is reused and signed again five seconds before exp, so a paged SELECT does not stop when the first token runs out. The published Momentus examples use 30 or 60 seconds. If the server rejects a longer lifetime, set jwtttl=30.

props.setProperty("auth", "jwt");
props.setProperty("jwtissuer", "APIUSERID");
props.setProperty("jwtkey", "4Fe7f631-2224-4098-82b9-38bb89c7247e");
props.setProperty("jwtsecret", "2fa24f34-1b2c-4ecf-8645-44ba766d6b89");
props.setProperty("jwtttl", "60");
// optional, Momentus proxied user:
// props.setProperty("jwtsubject", "PROXYUSER");

jdbc:rest:https://your-site.ungerboeck.com/PROD/api/v1

The API help of a site is typically https://your-site.ungerboeck.com/PROD/api/help. Calls go to /PROD/api/v1, not to the help page. List endpoints usually take pageSize, page, and search; see Page pagination.

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");

For a public client, clientsecret can be omitted. The scope offline_access lets the driver renew the token later through a refresh token.

See also: Microsoft Graph example.

HTTP Basic

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

The driver sends the header 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.

See also: OpenWeatherMap example.

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(...)).

The client certificate can be combined with a 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 client credentials and device code are configured as shown above. A separately obtained token can still be passed with auth=bearer.

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

This results in one call: GET /users.

Offset pagination

The spec looks like this 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

The spec looks like this 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

For the same 5 records and a page size of 2, the sequence looks like this:

#	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"}]

Momentus Enterprise uses the same type with different parameter names. page stays 1-based. filterParam sends the FILTER text as the search query parameter:

"filterParam": "search",
"pagination": {
  "type": "page",
  "limitParam": "pageSize",
  "pageParam": "page",
  "defaultLimit": 100
}

Paths and column names come from the site's API help. If the list is wrapped in an object, set dataPath (for example /Results). Authentication for this API is JWT.

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 plus other spec query options ($select, $expand, $filter, …). Subsequent pages are fetched via the complete nextLink URL (without rebuilding those parameters).

JSONPlaceholder example

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

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");

See also: Webmetic example.

HTTP redirects

The driver follows 301, 302, 303, 307, and 308 (at most 5 hops). 303 switches to GET. A redirect from HTTPS to HTTP is rejected.

Unlike Java HttpClient (which drops Authorization on a different host), the driver sends the original request headers to the redirect target. That is required when an API sends the client to another cluster.

Power BI: With the query parameter preferClientRouting=true, a request that hits the wrong cluster returns 307 Temporary Redirect and a Location on wabi-…-redirect.analysis.windows.net. Without the parameter, Power BI proxies internally (200 with data); with it, the client must follow — otherwise the body is empty and no rows are returned. Model the parameter as a column with binding: "query", do not put it permanently into path (see the FILTER clause).

With stdoutlog=http, redirects are logged as 307 redirect <url> -> <url>.

Further SQL features

This chapter describes FILTER, sorting, column selection via selectParam, related resources via expandParam, and path parameters in the spec.

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 recognized regardless of case. String values are written in single quotes. See the Webmetic example.

With filterParam: native API expression

If filterParam is set in the spec (e.g. "$filter" for OData/Microsoft Graph), the remaining FILTER content (after path and query bindings) 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

Path placeholders ({userId} in path) are still taken from leading param=value clauses and are not sent as $filter. The remainder is passed to filterParam.

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.

Path and query bindings (binding)

Many APIs mix URI path segments, dedicated query parameters, and a filter expression in one request — e.g. /orders?from=2025-01-01&to=2025-12-31, /reports?year=2025, or Graph calendarView. Declare that on the column, independently of Graph:

{ "name": "customerId", "type": "VARCHAR", "binding": "path" }
{ "name": "from", "type": "VARCHAR", "binding": "query", "required": true }
{ "name": "to", "type": "VARCHAR", "binding": "query" }
{ "name": "status", "type": "VARCHAR" }

SELECT id, status FROM orders
FILTER customerId='c-1' AND from='2025-01-01' AND to='2025-12-31' AND status='open'

binding	Effect
path	Fills {customerId} in path (placeholders in the path template are bound even without this field)
query	Dedicated query parameter; not sent as $filter
omitted	Remaining FILTER: filterParam if set, otherwise generic param=value query parameters

parameterType is accepted as an alias for binding. With required: true on a query binding, a missing FILTER value is an error.

Fixed flags such as Power BI preferClientRouting=true belong in the spec as a query binding, not in the REST path:

{ "name": "preferClientRouting", "type": "BOOLEAN", "binding": "query" }

SELECT id, name FROM groups FILTER preferClientRouting=true

Bound name=value clauses must come first (combined with AND). Anything after that may be a native expression (eq, startswith, …) when filterParam is set:

"path": "/users/{mailbox}/calendarView",
"filterParam": "$filter",
"columns": [
  { "name": "mailbox", "binding": "path", "readOnly": true },
  { "name": "startDateTime", "binding": "query", "required": true, "readOnly": true },
  { "name": "endDateTime", "binding": "query", "required": true, "readOnly": true },
  { "name": "subject", "type": "VARCHAR" }
]

SELECT id, subject FROM calendar_view
FILTER mailbox='user@example.com'
  AND startDateTime='2020-01-01T00:00:00Z'
  AND endDateTime='2020-12-31T23:59:59Z'
  AND subject eq 'Meeting'

→ GET /users/user@example.com/calendarView?startDateTime=…&endDateTime=…&$filter=subject eq 'Meeting'

Path and query columns are not included in $select. In the result set they are filled from the FILTER values used for the request (they are typically absent from the JSON body). See Microsoft Graph — Calendar view.

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 jsonPointer is set
• selectName when configured
• otherwise the first segment of jsonPointer (e.g. /from/emailAddress/address → from)
• columns with jsonPointer: "" do not contribute to $select; if the SELECT contains only such a column, $select is omitted (full object)
• columns with binding path or query (and path placeholders) are omitted from $select

"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).

Related resources (expandParam)

If expandParam is set (e.g. "$expand" for OData), the driver can request nested or related resources on SELECT. Values come from:

• entity field expand — always sent (string or list)
• column field expand — sent when that column is in the SELECT list
• SELECT * / no column list — all column expand values plus the entity default

Multiple fragments are joined with commas. Commas inside OData parentheses (e.g. $filter=…) are not treated as separators. Duplicates are removed.

"expandParam": "$expand",
"expand": "singleValueExtendedProperties($filter=id eq 'String {guid} Name extra')"

Several values are given as a list:

"expandParam": "$expand",
"expand": ["calendar", "attachments"]

A column-level expand is sent only when the column appears in the SELECT:

{ "name": "attachments", "type": "JSON", "jsonPointer": "/attachments", "expand": "attachments" }

SELECT id, subject, attachments FROM events

→ GET /events?$select=id,subject,attachments&$expand=attachments

$expand can be combined with $select. Pagination via @odata.nextLink is unchanged: only the first request is built from the spec; follow-up pages use the nextLink URL as returned by the API.

Without expandParam, no expand query parameter is sent.

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=…).

Without filterParam, remaining FILTER clauses after the path parameters become ordinary query parameters. Graph calendarView uses this for the required startDateTime and endDateTime arguments.

Further spec features

This chapter describes shared structures, tabular rows, and overrides for INSERT, UPDATE, and DELETE.

Defaults and shared structures

APIs with many similar tables (e.g. lookup entities with the same columns) do not need to repeat column lists and shared settings on every entity.

• defaults sets entity fields for all tables. A value on the entity overrides the default (whole object replacement, no deep merge of nested fields).
• structures defines named column lists. An entity references one with structure instead of listing columns. structure may also be set in defaults when almost all tables share the same layout.

After loading, every entity has a complete column list. structures are not tables and do not appear in system.table_list.

Rules:

• An entity specifies either structure or columns, not both.
• An unknown structure name or missing columns causes an error when the spec is loaded.
• Existing specs without defaults/structures remain valid.

Complete examples: spec-defaults-example.json, spec-defaults-example.yaml.

{
  "defaults": {
    "write": false,
    "pagination": { "type": "none" }
  },
  "structures": {
    "codeDescription": [
      { "name": "code", "type": "VARCHAR" },
      { "name": "description", "type": "VARCHAR" }
    ]
  },
  "entities": [
    {
      "name": "department",
      "description": "Department",
      "path": "/lookup/department",
      "structure": "codeDescription"
    },
    {
      "name": "currency",
      "description": "Currency",
      "path": "/lookup/currency",
      "structure": "codeDescription"
    },
    {
      "name": "budget",
      "description": "Budget",
      "path": "/lookup/budget",
      "write": true,
      "columns": [
        { "name": "id", "type": "VARCHAR", "primaryKey": true },
        { "name": "amount", "type": "DECIMAL" }
      ]
    }
  ]
}

department and currency inherit write: false and the columns from codeDescription. budget overrides write and defines its own columns.

The same spec looks like this in YAML:

defaults:
  write: false
  pagination:
    type: none
structures:
  codeDescription:
    - name: code
      type: VARCHAR
    - name: description
      type: VARCHAR
entities:
  - name: department
    description: Department
    path: /lookup/department
    structure: codeDescription
  - name: currency
    description: Currency
    path: /lookup/currency
    structure: codeDescription

Tabular rows (rowFormat)

Some APIs return a column header and value rows instead of objects. A metrics table is the typical case: each report has its own dimensions and its own order, the last column name differs per report, and every value arrives as a string. One entity defines the possible SQL columns. For each response the driver reads columns and matches them by name. The same entity serves every report through a path placeholder, for example path: "/reports/{report}" and FILTER report='…'.

A response looks like this:

{
  "columns": ["Year", "Region", "Product", "Revenue"],
  "data": [
    ["2024", "North", "Widget", "1200"],
    ["2024", "South", "Widget", "800"]
  ]
}

columns is the header. Each element of data is one row, in the same order and with the same length. The last name is the measure and changes per report. Object rows would be {"Year": "2024", "Region": "North"} instead.

Without rowFormat, or with rowFormat: "object", object rows are still read via jsonPointer, readPath, or the field name. columnsPath, unknownColumns, sourceName, sourcePosition, and sourceMeta are then an error when the spec is loaded.

Example

{
  "name": "metrics",
  "path": "/reports/{report}",
  "rowFormat": "tabular",
  "columnsPath": "/columns",
  "dataPath": "/data",
  "unknownColumns": "error",
  "pagination": { "type": "none" },
  "columns": [
    { "name": "report", "type": "VARCHAR(200)", "binding": "path", "required": true },
    { "name": "year", "type": "VARCHAR(4)", "sourceName": "Year" },
    { "name": "region", "type": "VARCHAR(50)", "sourceName": "Region" },
    { "name": "product", "type": "VARCHAR(50)", "sourceName": "Product" },
    { "name": "channel", "type": "VARCHAR(50)", "sourceName": "Channel" },
    { "name": "segment", "type": "VARCHAR(50)", "sourceName": "Segment" },
    { "name": "amount", "type": "VARCHAR(100)", "sourcePosition": -1 },
    { "name": "amount_name", "type": "VARCHAR(200)", "sourcePosition": -1, "sourceMeta": "columnName" }
  ]
}

The query sales_by_region and the matching response look like this, for example:

SELECT report, year, region, product, channel, segment, amount, amount_name
FROM metrics
FILTER report='sales_by_region'

{
  "columns": ["Year", "Region", "Product", "Channel", "Segment", "Revenue"],
  "data": [
    ["2024", "North", "Widget", "Web", "Retail", "1200"]
  ]
}

report	year	region	product	channel	segment	amount	amount_name
sales_by_region	2024	North	Widget	Web	Retail	1200	Revenue

Segment appears in this report's header. Matching uses the exact name, not the position among the dimensions. If the spec omits one of these names, unknownColumns: "error" aborts the SELECT, including SELECT *. report comes from FILTER, not from the value row. SELECT * returns only the spec columns.

A second report has different dimensions and a different order. Region may be absent, Product may appear before Channel, and the last column has another name:

SELECT report, year, region, product, channel, segment, amount, amount_name
FROM metrics
FILTER report='units_by_product'

{
  "columns": ["Year", "Product", "Channel", "Units"],
  "data": [
    ["2024", "Widget", "Web", "40"]
  ]
}

Here region is NULL, and so is segment, because the header omits them. amount is 40, and amount_name is Units.

The {query} placeholder is encoded per path segment: spaces as %20, non-ASCII characters as UTF-8. With pagination type none (also the default), one SELECT performs exactly one GET.

Mapping

Field	Meaning
rowFormat	object (default) or tabular
columnsPath	JSON Pointer to an array of strings. Required for tabular; must start with /
dataPath	JSON Pointer to the array of value rows; in the example, /data
unknownColumns	error (default), warn, or ignore
sourceName	Exact header name. Defaults to name. Excludes sourcePosition
sourcePosition	Index in the header. 0 is the first column, -1 the last
sourceMeta	Only columnName: each row receives the header name at that position, not the cell value

sourceName is a different field from selectName. When selectParam is set, selectName is sent to the server as $select. jsonPointer and readPath are forbidden when rowFormat is tabular. A column with binding: "path" or binding: "query" has no position in the header; sourceName, sourcePosition, and sourceMeta are a spec error there. After the rows are read, the bound value is written from FILTER, as with path and query bindings.

Per SELECT:

1. Resolve path placeholders from FILTER and perform one GET.
2. Read the header at columnsPath. It must be an array of strings. Duplicate names are an error. Comparison is exact, including case. The response header is not normalized.
3. Resolve the position: sourcePosition, otherwise sourceName, otherwise name. If the name is missing from the header, the column value is NULL on every row. A sourcePosition outside the header is an error.
4. Handle every header position that no spec column claims (unknownColumns, below).
5. Read dataPath. The node must be an array. A missing or wrong path is an error. An empty array is an empty result. Each row must be an array of the same length as the header. The message names the row number starting at 1.

Two spec columns may share a position. amount and amount_name both use -1: amount is the cell value, amount_name is that column's name in the header. A header position counts as claimed as soon as at least one spec column points at it. The unknown-column check uses every column of the entity, not the SELECT list.

Unknown columns

unknownColumns	Effect
error	SQLException. The message names the entity, the bound values, and the unknown column names
warn	The same message once on stdout, level warn. Visible with stdoutlog=warn or stdoutlog=all
ignore	The column is skipped

error is the default, including when the attribute is omitted.

Values

A JSON value null is returned as SQL NULL. An empty string also becomes NULL when it is copied into the JDBC row. That applies to object rows and to tabular rows alike. If a cell contains a nested object or an array, the driver fails with an error. Numbers and booleans are converted to the JDBC value in the same way as for object rows. A VARCHAR stays text, including values such as 1.5E-12. The other spec types use the same conversion. If it fails, the error message names the column and the cause.

Metadata and one request per statement

SQL names and JDBC metadata come from the spec. The driver does not create columns from the response headings. DatabaseMetaData.getColumns and SELECT … FROM system.column_list read only the spec and do not call /reports/…. An OAuth token refresh is a separate call to the auth server.

One statement stays one GET. A loop over several reports does not belong in the driver. Action modules with Loop Query (Insert template, Remote, CallWebService) replace ${…} and run the statement again for each row. 45 reports are 45 requests per run.

Read-only

An entity with rowFormat: "tabular" does not support INSERT, UPDATE, or DELETE, even when write is not false.

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}

Writing is allowed by default. The HTTP method and the URL pattern are derived from the path: {path} for INSERT, {path}/{id} for UPDATE and 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
}

Individual operations can be turned off separately: "insert": false, "update": false, or "delete": false.

An entity with rowFormat: "tabular" is read-only. INSERT, UPDATE, and DELETE are rejected even when write is not false. See Tabular rows.

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

table_list, column_list, pk_list, and DatabaseMetaData.getColumns read only the spec. They do not send an HTTP request to the API. An OAuth token refresh goes to the auth server, not to the entity's data path.

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

To debug connection and query problems, set the connection property stdoutlog to the levels you need, for example:

stdoutlog=cursor+http+rows

Level	Output
cursor	Opened table and FILTER text
http	HTTP method and full URL including query parameters; redirects as 307 redirect … -> …
rows	Number of rows received per HTTP response and total when the cursor closes
warn	Warnings, once per response. Unknown header columns when unknownColumns is warn
all	all levels

The following messages occur often:

Message	Cause / fix
FILTER could not be mapped to query parameters	Check FILTER syntax: param=value with AND, strings in '…'
has both "structure" and "columns"	Specify only structure or columns per entity
unknown structure / neither "columns" nor "structure"	Check the structure name, or set columns or structure
HTTP 401 / HTTP 403	Check auth (e.g. Webmetic: auth=apikey, header Authorization without Bearer)
HTTP 429	Set requestintervalms (Webmetic: 1000), see Rate limits
Empty result set despite a valid URL	Often an unfollowed redirect; check with stdoutlog=http. Power BI preferClientRouting=true yields 307 — the driver follows it (see HTTP redirects)
Too many HTTP redirects	Redirect loop or more than 5 hops
Property not recognized	Property names are case-insensitive; check values
Unknown columns for entity	The response header contains a name that no spec column claims. Add the column, or set unknownColumns to warn or ignore. See Tabular rows
INSERT is not supported / UPDATE is not supported / DELETE is not supported	The entity is not writable, for example with rowFormat: "tabular" or write: false

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, jsonPointer)
• OpenWeatherMap — weather forecast with API key and FILTER q=…
• Microsoft Graph — Microsoft 365 (OAuth2, OData FILTER, nextLink, $select, $expand, calendar view)
• 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. Without a further entry, address arrives as JSON text in one column.

jsonPointer turns a nested field into its own column. The pointer starts at the row object:

{ "name": "city", "type": "VARCHAR", "jsonPointer": "/address/city" }

SELECT city FROM users then reads Berlin. Further nested fields are described in jsonPointer.

Error handling

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

UPDATE and PUT

On UPDATE, the JSON body contains only the columns from SET. This applies to PUT and PATCH. FILTER fills the URL. PUT is the default because many APIs offer changes only through this verb and leave unspecified fields unchanged.

If the server expects the complete object on PUT and checks required fields, it rejects the call. The driver forwards the status and the response body. For UPDATE users SET email = 'ada@example.com' FILTER id=1, the SQLException can look like this:

HTTP 400 for PUT https://api.example.com/users/1: {"message":"name is required"}

The text after the colon comes from the API. Another server responds with 200 and replaces the resource with the partial object. Name and other fields are then empty. That shows up on the next SELECT.

If the API applies partial changes through PATCH, set "update": { "method": "PATCH" } in the spec. The body stays the same.