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 API is described by a spec file (JSON or YAML)
the JDBC URL contains the base URL of the REST API (can be overridden in the spec)
the schema public is used by default
entity names from the spec are used as tables (e.g. users, orders)
columns are also defined in the spec and must match flat JSON fields in the API response
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.

Three authentication methods are available:

none — no authentication
basic — HTTP Basic Auth with user and password
bearer — authentication with a bearer token

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

INSERT maps to HTTP POST.

DELETE removes records via HTTP DELETE.

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

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

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

JDBC URL

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

The part after jdbc:rest: is the base URL of the REST API. It can be overridden or extended in the spec (baseUrl in the spec takes precedence when set).

Example connection (Java)

Properties props = new Properties();
props.setProperty("spec", "/path/to/api-spec.json");
props.setProperty("auth", "bearer");
props.setProperty("password", "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` (see below)	No
`password`	Bearer token or password (depending on auth type)	For bearer/basic
`user`	Username	For basic
`token`	Alternative to `password` for bearer token	For bearer (optional)

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, or basic. If auth is not set, it is detected automatically:

user and password set → basic
only password or token set → bearer
otherwise → none

No authentication

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

Or simply omit auth properties.

Bearer token

props.setProperty("auth", "bearer");
props.setProperty("password", "eyJhbGciOiJIUzI1NiIs...");
// alternatively:
props.setProperty("token", "eyJhbGciOiJIUzI1NiIs...");

The driver sends: Authorization: Bearer <token>

HTTP Basic

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

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

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

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

{
  "baseUrl": "https://api.example.com/v1",
  "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:

baseUrl: https://api.example.com/v1
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, default from JDBC URL)
`entities`	List of JDBC “tables”
`entities[].name`	Table name
`entities[].columns`	Columns with JDBC type
`entities[].path`	REST path
`entities[].dataPath`	JSON Pointer
`entities[].orderByParam`	Query parameter for ORDERBY clause
`entities[].pagination`	Pagination (see below)
`entities[].write`	`false` = disable all write operations (default: `true`)
`entities[].insert`	Optional: override for INSERT, or `false` to disable
`entities[].update`	Optional: override for UPDATE, or `false` to disable
`entities[].delete`	Optional: override for DELETE, or `false` to disable

Required fields:

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

All other entries are optional.

Column types

Supported types in the spec: BIGINT, INTEGER, VARCHAR, BOOLEAN, DOUBLE, DECIMAL, TIMESTAMP, DATE.

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

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. Nested objects and arrays are serialized as strings.

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 — they would need to be modeled as columns in the spec and the API would need to be adapted accordingly.

Pagination

type	Meaning	Parameters
`none`	One request, all rows	—
`offset`	Limit/offset	`limitParam`, `offsetParam`, `defaultLimit`
`page`	Page number (1-based)	`limitParam`, `pageParam`, `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.

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

JSONPlaceholder example

JSONPlaceholder uses _limit and _page:

"pagination": {
  "type": "page",
  "limitParam": "_limit",
  "pageParam": "_page",
  "defaultLimit": 10
}

SELECT id, title FROM posts;

→ GET /posts?_limit=10&_page=1, then _page=2, … until all 100 posts are loaded.

At JSONPlaceholder, users and albums use "type": "none" instead — one request is enough.

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 — filtering is server-side, not local (unlike WHERE). Syntax is always 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 DELETE path ({id}). It does not have to match a SELECT column — e.g. FILTER userId=1 filters posts by author even when you select id (post 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

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

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

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
FROM system.column_list
WHERE table_name = 'users'
;

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

JSONPlaceholder

JSONPlaceholder is a public test API without authentication — well suited for trying the driver. A ready-made spec with the endpoints users, posts, comments, albums, photos, and todos is available at jsonplaceholder-spec.json.

Connection (Java):

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

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

Example queries:

SELECT id, name, email FROM users
;

SELECT id, title FROM posts FILTER userId=1
;

SELECT id, name, email FROM comments FILTER postId=1
;

FILTER userId=1 on posts produces GET /posts?userId=1. FILTER postId=1 on comments returns comments for post 1.

For posts, comments, photos, and todos, pagination is configured via _limit and _page; the driver fetches all pages automatically. users and albums are returned in full in one request (pagination none).

Nested fields on /users (address, company) are deliberately omitted in the spec — the driver only reads flat top-level fields.

INSERT example:

INSERT INTO posts ("userId", title, body) VALUES (1, 'Test', 'Body');

UPDATE example:

UPDATE posts SET title = 'Test', body = 'Body' FILTER id=1;

DELETE example:

DELETE FROM posts FILTER id=1;

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.