REST-JDBC — Microsoft Graph

The Microsoft Graph API provides access to Microsoft 365 data (users, groups, mail, calendar, and more). This example covers OAuth 2.0 client credentials for tenant-wide read queries and OAuth 2.0 device code for delegated read/write on /me/… resources.

Quick start with the JDBC client

Users and groups (OAuth2 client credentials)

java -jar restjdbc.jar graph-oauth2.sql
  1. Register an Azure app with application permissions (see section 4)
  2. Replace tenant, clientId, and clientSecret in graph-oauth2.sql
  3. Run the command in the folder containing graph-spec.json
connect 'jdbc:rest:https://graph.microsoft.com/v1.0|spec=graph-spec.json,auth=oauth2,tenant=…,clientId=…,clientSecret=…,scope=https://graph.microsoft.com/.default'

Mailbox (OAuth2 device code, delegated)

The /me/messages endpoint requires a delegated token (signed-in user). Use the device code flow built into the driver:

java -jar restjdbc.jar graph-devicecode.sql
  1. Configure the Azure app as a public client with delegated permission Mail.Read
  2. Replace tenant and clientId in graph-devicecode.sql
  3. On connect, URL and code for browser sign-in are printed
connect 'jdbc:rest:https://graph.microsoft.com/v1.0|spec=graph-spec.json,auth=oauth2,oauth2GrantType=device_code,tenant=…,clientId=…,scope=Mail.Read offline_access'

Alternatively, use a manual bearer token: graph-bearer.sql

Calendar (OAuth2 device code, delegated, write)

Read and write the signed-in user's events (INSERT, UPDATE, DELETE):

java -jar restjdbc.jar graph-calendar.sql
  1. Add delegated permission Calendars.ReadWrite in the Azure app
  2. Replace tenant and clientId in graph-calendar.sql
  3. Scope: Calendars.ReadWrite offline_access User.Read
connect 'jdbc:rest:https://graph.microsoft.com/v1.0|spec=graph-spec.json,auth=oauth2,oauth2GrantType=device_code,tenant=…,clientId=…,scope=Calendars.ReadWrite offline_access User.Read'

Nested Graph fields (start, end, body, location) are mapped via jsonPointer and defaultWrite in the spec — see entity calendar_events in graph-spec.json.

User calendar (OAuth2 client credentials, application permission)

Read and write calendar events for any tenant user (/users/{userId}/events):

java -jar restjdbc.jar graph-user-calendar.sql
  1. Add application permission Calendars.ReadWrite and grant admin consent
  2. Replace tenant, clientId, clientSecret, and USER-OBJECT-ID in graph-user-calendar.sql
  3. Scope: https://graph.microsoft.com/.default

Path parameter userId is supplied as a column or via FILTER userId='…' (placeholder {userId} in the spec). Example:

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

UPDATE user_calendar_events SET subject = 'Meeting (updated)' FILTER userId='USER-OBJECT-ID' AND id=EVENT-ID;

Calendar view (OAuth2 client credentials)

Read a mailbox calendar window (/users/{mailbox}/calendarView). Graph requires startDateTime and endDateTime as query parameters; further predicates go to $filter. The spec declares that with column binding (path / query) plus filterParam — the same generic mechanism as for other REST APIs (see Path and query bindings).

java -jar restjdbc.jar graph-calendar-view.sql
  1. Add application permission Calendars.Read (or Calendars.ReadWrite) and grant admin consent
  2. Replace tenant, clientId, clientSecret, and the mailbox in graph-calendar-view.sql
  3. Scope: https://graph.microsoft.com/.default
SELECT id, subject, location, start, "end", categories
FROM calendar_view
FILTER mailbox='user@example.com'
  AND startDateTime='2020-01-01T00:00:00'
  AND endDateTime='2020-12-31T23:59:59';

Additional OData predicates belong after the bound parameters:

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

$expand is configured in the spec (expandParam). Selecting attachments adds $expand=attachments. A fixed default such as singleValueExtendedProperties($filter=id eq '…') can be set on the entity via expand.

The SQL keyword END is reserved — quote the column as "end".

JDBC URL

jdbc:rest:https://graph.microsoft.com/v1.0

The API base URL is set in the JDBC URL.

Part Value
Driver prefix jdbc:rest:
API base URL https://graph.microsoft.com/v1.0
SQL schema public (default)

Connection example (Java, OAuth2):

Properties props = new Properties();
props.setProperty("spec", "/path/to/graph-spec.json");
props.setProperty("auth", "oauth2");
props.setProperty("tenant", System.getenv("AZURE_TENANT_ID"));
props.setProperty("clientId", System.getenv("AZURE_CLIENT_ID"));
props.setProperty("clientSecret", System.getenv("AZURE_CLIENT_SECRET"));
props.setProperty("scope", "https://graph.microsoft.com/.default");

Connection conn = DriverManager.getConnection(
    "jdbc:rest:https://graph.microsoft.com/v1.0", props);

Connection properties

OAuth2 client credentials (users, groups)

Property Value Required
spec Path to spec Yes
auth oauth2 Yes
tenant Azure AD tenant ID Yes (or tokenUrl)
clientId App registration client ID Yes
oauth2GrantType client_credentials (default) No
clientSecret Client secret Yes
scope https://graph.microsoft.com/.default Yes

Alternatively, instead of tenant:

props.setProperty("tokenUrl",
    "https://login.microsoftonline.com/your-tenant-id/oauth2/v2.0/token");

The driver fetches and refreshes the access token automatically.

OAuth2 device code (messages /me)

Property Value Required
auth oauth2 Yes
oauth2GrantType device_code Yes
tenant Azure AD tenant ID Yes
clientId App registration (public client) Yes
scope Mail.Read offline_access Yes
clientSecret — No (public client)

The driver prints the sign-in URL, waits for user authentication, and renews the token via refresh token.

Bearer (alternative for messages /me)

Property Value Required
auth bearer Yes
token Delegated access token Yes

Spec file: graph-spec.json

Spec file

File: graph-spec.json

Tables

SQL table REST path Auth type Notes
users /users OAuth2 client credentials User.Read.All
groups /groups OAuth2 client credentials Group.Read.All
messages /me/messages OAuth2 device code or bearer Mail.Read delegated
calendar_events /me/events OAuth2 device code or bearer Calendars.ReadWrite delegated, INSERT/UPDATE/DELETE
user_calendar_events /users/{userId}/events OAuth2 client credentials Calendars.ReadWrite app, path param userId
calendar_view /users/{mailbox}/calendarView OAuth2 client credentials Calendars.Read app, binding path/query (mailbox, startDateTime/endDateTime), optional $filter

Shared OData settings can be set once under defaults instead of repeating them on every entity:

"defaults": {
  "dataPath": "/value",
  "filterParam": "$filter",
  "selectParam": "$select",
  "expandParam": "$expand",
  "orderByParam": "$orderby",
  "pagination": {
    "type": "nextLink",
    "limitParam": "$top",
    "nextLinkPath": "/@odata.nextLink",
    "defaultLimit": 100
  }
}
  • nextLink: the driver follows @odata.nextLink automatically across all pages
  • filterParam: remaining FILTER content is sent as OData $filter (native syntax); leading param=value clauses with binding are split off first
  • selectParam: only columns named in SELECT are requested via $select (path/query bindings are omitted)
  • expandParam: related resources via $expand (entity expand and/or column expand)
  • binding: path or query — maps FILTER columns to URI placeholders or dedicated query parameters (required for mandatory query params)
  • jsonPointer / selectName: nested fields (e.g. sender on messages)

Entity values override the defaults (e.g. write on the calendar entities). See Defaults and shared structures.

Azure app registration

For users / groups (client credentials)

  1. Microsoft Entra ID → App registrations → New registration
  2. Certificates & secrets → create a client secret
  3. API permissions → Microsoft Graph → Application permissions:
    • User.Read.All
    • Group.Read.All
  4. Grant admin consent

For user calendar (user_calendar_events), also add application permission: Calendars.ReadWrite.

For calendar view (calendar_view), add application permission: Calendars.Read (or Calendars.ReadWrite).

For messages / calendar_events (delegated)

Also add Delegated permissions: Mail.Read (mailbox), Calendars.ReadWrite (calendar). Enable public client flows for device code, or obtain a token manually for bearer auth.

SQL examples

Column names from the spec with mixed case (camelCase, e.g. displayName, start_dateTime) must be double-quoted in SELECT, INSERT, and UPDATE — the SQL parser otherwise normalizes unquoted identifiers to lowercase. FILTER and ORDERBY are not affected: use OData syntax with Graph property names there (displayName, accountEnabled).

Users (OAuth2)

SELECT id, "displayName", mail, "userPrincipalName" FROM users;

SELECT id, "displayName", mail FROM users
  FILTER startswith(displayName,'M') AND accountEnabled eq true
  ORDERBY displayName asc;
SQL HTTP (simplified)
SELECT id, "displayName", mail FROM users GET /users?$top=100&$select=id,displayName,mail
… FILTER startswith(…) …&$filter=startswith(displayName,'M') AND accountEnabled eq true

FILTER contains an OData expression — not param=value (because filterParam is set in the spec).

Groups (OAuth2)

SELECT id, "displayName", "mailEnabled" FROM groups
  FILTER mailEnabled eq true AND securityEnabled eq true;

Mailbox (device code or bearer)

SELECT id, subject, from_address, "receivedDateTime" FROM messages
  FILTER isRead eq false;

Use graph-devicecode.sql (OAuth2 device code) or graph-bearer.sql (pre-obtained token).

Columns from_address and from_name use jsonPointer; $select requests the parent field from (selectName).

Calendar (device code or bearer)

INSERT INTO calendar_events (subject, "start_dateTime", "end_dateTime", body_content)
VALUES ('Team meeting', '2026-07-01T10:00:00', '2026-07-01T11:00:00', 'Agenda …');

SELECT id, subject, "start_dateTime", "end_dateTime" FROM calendar_events
  FILTER startswith(subject,'Team meeting');

UPDATE calendar_events SET subject = 'Team meeting (moved)' FILTER id=EVENT-ID;

DELETE FROM calendar_events FILTER id=EVENT-ID;
SQL HTTP (simplified)
INSERT … POST /me/events with JSON {subject, start:{dateTime,timeZone}, end:…}
UPDATE … FILTER id=… PATCH /me/events/{id}
DELETE … FILTER id=… DELETE /me/events/{id}

If start_timeZone/end_timeZone are omitted, the spec applies Europe/Berlin via defaultWrite. Full example: graph-calendar.sql.

User calendar (client credentials)

INSERT INTO user_calendar_events ("userId", subject, "start_dateTime", "end_dateTime")
VALUES ('USER-OBJECT-ID', 'Team meeting', '2026-07-01T10:00:00', '2026-07-01T11:00:00');

SELECT id, subject FROM user_calendar_events
  FILTER userId='USER-OBJECT-ID' AND startswith(subject,'Team meeting');

UPDATE user_calendar_events SET subject = 'Team meeting (moved)'
  FILTER userId='USER-OBJECT-ID' AND id=EVENT-ID;

DELETE FROM user_calendar_events FILTER userId='USER-OBJECT-ID' AND id=EVENT-ID;

Path parameters (userId) come before the OData filter part, separated by AND. Full example: graph-user-calendar.sql.

Calendar view (client credentials)

calendar_view uses binding so path, query parameters, and $filter can be combined. Bound FILTER clauses (mailbox, startDateTime, endDateTime) must come first (param=value); an OData remainder is optional.

SELECT id, subject, location, start, "end", categories
FROM calendar_view
FILTER mailbox='user@example.com'
  AND startDateTime='2020-01-01T00:00:00'
  AND endDateTime='2020-12-31T23:59:59';

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

SELECT id, subject FROM calendar_view
FILTER mailbox='user@example.com'
  AND startDateTime='2020-01-01T00:00:00'
  AND endDateTime='2020-12-31T23:59:59'
  AND subject eq 'Meeting';
SQL HTTP (simplified)
FILTER mailbox=… AND startDateTime=… AND endDateTime=… GET /users/{mailbox}/calendarView?startDateTime=…&endDateTime=…&$select=…&$top=999
… AND subject eq 'Meeting' additionally $filter=subject eq 'Meeting'
SELECT … attachments additionally $expand=attachments

Quote "end" (END is a SQL keyword). A default $expand for extended properties belongs in the spec (expand), not in SQL. Spec excerpt:

{ "name": "mailbox", "binding": "path", "readOnly": true },
{ "name": "startDateTime", "binding": "query", "required": true, "readOnly": true },
{ "name": "endDateTime", "binding": "query", "required": true, "readOnly": true }

Full example: graph-calendar-view.sql.

System tables

SELECT table_name, remarks FROM system.table_list;

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

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

Notes

  • Throttling: Graph may respond with HTTP 429 — respect Retry-After.
  • 401/403: missing permission, expired token, or no admin consent.
  • /me/…: requires a delegated user token, not client credentials.
  • More entities: add further Graph resources to graph-spec.json using the same pattern (dataPath, filterParam, selectParam, expandParam, binding, nextLink, optionally via defaults).