Query a REST API with SQL

Query a REST API with SQL#

This tutorial walks you through connecting Querona to a REST API and querying it with plain SQL. By the end you will have run a SELECT against live REST data — with paging and JSON flattening handled for you — using the operation-first REST provider.

For the full capabilities of the provider, see REST.

What you’ll use#

  • A running Querona instance (see Installation if you need one).

  • A REST API described by an OpenAPI specification. This tutorial uses the free, well-known Swagger Petstore as the example; see Going further below for an example using a production API, KSeF.

Step 1 — Create the REST connection#

Add a connection using the REST provider as the source (Create a connection), and configure:

  • OpenAPI configuration — paste the specification. For Petstore it is available at https://petstore3.swagger.io/api/v3/openapi.json (JSON or YAML both work).

  • Base URL — for example https://petstore3.swagger.io/api/v3.

  • Authentication — choose the method the API requires. Petstore’s read operations need none; for protected APIs pick API Key, HTTP Basic, JWT, OAuth2 or KSeF.

Click Test to validate the specification and reachability, then Save.

Step 2 — Create a virtual database#

Create a virtual database on top of the REST connection. Querona reads the OpenAPI specification and generates a tabular function for each REST operation.

Step 3 — Find the generated functions#

Browse the new database’s functions (in the web interface, or via the system catalog). You will see one tabular function per REST operation — each auto-parameterized from that operation’s parameters and returning a strongly typed, tabular result. Note the name and parameters of the function that wraps the operation you want to call.

Note

The function name is derived from the operation’s path (normalized), and its parameters mirror the operation’s parameters, each with its default value. The exact identifiers therefore depend on the API, so use the names you see here in the queries below. For a worked example of how an operation maps to a function, see REST.

Step 4 — Run your first query#

Call the generated function in the FROM clause and pass the operation’s parameters, just like any table-valued function:

SELECT *
FROM   petstore.find_pets_by_status('available');   -- use the actual function name from Step 3

Querona calls the REST operation, parses the response, and returns rows you can filter, join and aggregate with standard SQL.

Step 5 — Paging and JSON, handled for you#

Two things happen automatically:

  • Paging — if the API pages its results and the paging protocol is recognized (HATEOAS, parameterized offset, or designated next-page URLs), Querona fetches and combines every page, so your single SELECT returns the complete result set.

  • JSON flattening — nested JSON in the response is collapsed into columns down to a defined depth. Anything deeper is returned in-row as JSON text, which you can query with the JSON functions:

SELECT id,
       JSON_VALUE(details, '$.manufacturer.name') AS manufacturer
FROM   my_api.some_operation();   -- 'details' is a field returned in-row as JSON

See JSON for the JSON functions.

Step 6 — Customize a generated function#

The generated functions are a starting point, not a fixed contract. Open a function’s definition to:

  • rename it, or adjust its parameters and output columns, and

  • change the REST-to-SQL type mapping — the mapping from the REST data types to Querona’s SQL Server–compatible types is expressed right in the function definition.

Rewrite the function to match how you want to consume the data, then query your customized version.

Going further — a production API (KSeF)#

The same operation-first workflow applies to production APIs. For example, KSeF (the Polish National e-Invoicing System) publishes an OpenAPI specification: create a REST connection with its specification and Base URL, choose KSeF authentication, and Querona generates tabular functions over its operations — letting you query e-invoicing data with standard SQL, with paging and JSON flattening included.

Next steps#

  • REST — full REST provider reference.

  • JSON — working with JSON results.