REST-Jdbc — GitHub

The GitHub REST API requires authentication (personal access token) for most endpoints. This example lists repositories of the authenticated user.

Quick start with the JDBC client

SQL files can be run directly with the driver JAR:

java -jar /path/to/restjdbc.jar github.sql

The file github.sql contains the connection and sample queries. Steps:

  1. Obtain the driver JAR (absolute path to restjdbc.jar)
  2. Replace token=XXXX in github.sql with your GitHub token
  3. Run the command in the folder that contains github.sql and github-spec.json

In the JDBC client, JDBC URL and connection properties are combined on one line — separated by |, properties by comma:

connect 'jdbc:rest:https://api.github.com|spec=github-spec.json,auth=bearer,token=ghp_…'

The file ends with quit to exit the client.

1. JDBC URL

jdbc:rest:https://api.github.com

The API base URL is set in the JDBC URL.

Part Value
Driver prefix jdbc:rest:
API base URL https://api.github.com
SQL schema public (default)

Connection example (Java):

Properties props = new Properties();
props.setProperty("spec", "/path/to/github-spec.json");
props.setProperty("auth", "bearer");
props.setProperty("token", System.getenv("GITHUB_TOKEN"));

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

2. Connection properties

Property Value for GitHub Required
spec Path to spec (see below) Yes
auth bearer Yes
token Personal access token (PAT) Yes
password — (not for bearer) No
user — (not needed for bearer) No

Minimal:

props.setProperty("spec", "/path/to/github-spec.json");
props.setProperty("auth", "bearer");
props.setProperty("token", "ghp_…");

Or set only token — the driver auto-detects bearer.

Spec path: absolute path to your local copy of the spec file.

Spec file: github-spec.json

3. Spec file

File: github-spec.json

Excerpt:

{
  "entities": [
    {
      "name": "repos",
      "description": "Repositories of the authenticated user",
      "path": "/user/repos",
      "pagination": {
        "type": "page",
        "limitParam": "per_page",
        "pageParam": "page",
        "defaultLimit": 100
      },
      "write": false,
      "columns": [
        { "name": "id", "type": "BIGINT", "primaryKey": true },
        { "name": "name", "type": "VARCHAR" },
        { "name": "full_name", "type": "VARCHAR" },
        { "name": "private", "type": "BOOLEAN" },
        { "name": "description", "type": "VARCHAR" }
      ]
    }
  ]
}

Table repos

SQL table REST path Pagination
repos /user/repos page with per_page / page (default: 100 per page)

GitHub also returns a Link header (rel="next"). This example uses the explicit query parameters page and per_page, which GitHub supports as well — the driver automatically fetches all pages until an empty response is returned.

Example flow with many repositories:

# HTTP request Result
1 GET /user/repos?per_page=100&page=1 first 100 repos
2 GET /user/repos?per_page=100&page=2 next 100 repos
3 … until empty list

For OData APIs (e.g. Microsoft Graph), nextLink pagination via @odata.nextLink in the JSON response is available as an alternative.

"write": false disables INSERT/UPDATE/DELETE for this entity (see section 5).

4. Authentication

GitHub requires a personal access token (classic or fine-grained).

Create a token

  1. GitHub → Settings → Developer settings → Personal access tokens
  2. Create a token with at least repo (private repos) or public_repo (public repos only)
  3. Store the token securely — it is shown only once

Bearer in the driver

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

The driver sends: Authorization: Bearer ghp_… and a User-Agent header (required by GitHub).

Set the token e.g. as environment variable GITHUB_TOKEN and pass it via token.

5. SQL examples

SELECT

SELECT id, name, full_name, private FROM repos;

SELECT id, name, description FROM repos FILTER type=owner;

SELECT id, name FROM repos FILTER visibility=private;
SQL HTTP
SELECT … FROM repos GET https://api.github.com/user/repos
SELECT … FROM repos FILTER type=owner GET …/user/repos?type=owner
SELECT … FROM repos FILTER visibility=private GET …/user/repos?visibility=private

FILTER forwards query parameters to GitHub (server-side).

INSERT, UPDATE, DELETE

github-spec.json sets "write": false — write SQL fails before HTTP is called.

Why: GitHub identifies repositories for changes by owner/repo (full_name), not numeric id. Default mapping PUT/DELETE {path}/{id} does not match GitHub (PATCH /repos/{owner}/{repo}).

Without write: false, conceptually:

SQL Default HTTP (not GitHub-compatible)
INSERT INTO repos (name, …) VALUES (…) POST /user/repos — may work, needs write scope
UPDATE repos SET … FILTER id=123 PUT /user/repos/123 — does not match GitHub
DELETE FROM repos FILTER id=123 DELETE /user/repos/123 — does not match GitHub

Real writes would need custom update/delete paths with {full_name} — intentionally out of scope for this starter example.

6. System tables

SELECT table_name, remarks FROM system.table_list;

SELECT column_name, type_name FROM system.column_list WHERE table_name = 'repos';

SELECT column_name FROM system.pk_list WHERE table_name = 'repos';

7. Notes

  • Rate limits: GitHub throttles API calls; on 403 with X-RateLimit-Remaining: 0, wait or check the token.
  • 401 errors: missing, expired, or insufficient token scopes.
  • More fields: owner, html_url, default_branch, etc. are nested — only flat spec columns are queryable; nested JSON fields require jsonPath in the spec.