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

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.

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.

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

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

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.

See also: Microsoft Graph example.

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.

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

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

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

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)
defaults	Default values for entity fields (write, pagination, dataPath, 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
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

Required fields:

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

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:

• Use 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": "abteilung",
      "description": "Board Abteilung",
      "path": "/schema/Entities/Abteilung",
      "structure": "codeDescription"
    },
    {
      "name": "currency",
      "description": "Board Currency",
      "path": "/schema/Entities/Currency",
      "structure": "codeDescription"
    },
    {
      "name": "forecast",
      "description": "Board Forecast",
      "path": "/schema/Entities/Forecast",
      "write": true,
      "columns": [
        { "name": "id", "type": "VARCHAR", "primaryKey": true },
        { "name": "amount", "type": "DECIMAL" }
      ]
    }
  ]
}

abteilung and currency inherit write: false and the columns from codeDescription. forecast overrides write and defines its own columns.

The same in YAML:

defaults:
  write: false
  pagination:
    type: none
structures:
  codeDescription:
    - name: code
      type: VARCHAR
    - name: description
      type: VARCHAR
entities:
  - name: abteilung
    description: Board Abteilung
    path: /schema/Entities/Abteilung
    structure: codeDescription
  - name: currency
    description: Board Currency
    path: /schema/Entities/Currency
    structure: codeDescription

Column types

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

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.

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

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

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 sufficient; JSON is not required here.

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

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", "jsonPointer": "/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 jsonPointer (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 jsonPointer entries (e.g. "jsonPointer": "/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 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.

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

Or several values:

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

Column-level expand (only when the column is selected):

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

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

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

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; redirects as 307 redirect … -> …
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 '…'
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

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.

Error handling

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