Managing Data API

Managing Data API#

Introduction#

Querona ships with a built-in Data API service, that provides modern REST, GraphQL and MCP (Model Context Protocol) endpoints to configured database objects in your virtual databases.

Database objects are securely exposed via REST, GraphQL or MCP endpoints so that your data can be accessed using modern techniques on any platform, any language, and any device. The MCP endpoint additionally makes virtual databases directly consumable by AI assistants and agents.

Features:

General
- Tables and views access via REST, GraphQL and MCP
REST
- CRUD operations via POST, GET, PUT, PATCH, DELETE
- filtering, sorting and pagination
GraphQL
- queries and mutations
- filtering, sorting and pagination
- relationship navigation.

Exposed resources#

Data API exposes the following types of resources:

URI format	Description
GET /{rest-path}/openapi	OpenAPI description document of all endpoints in OpenAPI format. Querona generates an OpenAPI description document (also referred to as a schema file) using the database objects metadata. The schema file is generated using functionality provided by the OpenAPI.NET SDK. Currently, the schema file is generated in adherence to OpenAPI Specification v3.0.1 formatted as JSON.
/swagger	OpenAPI (Swagger) GUI offering a web-based UI that provides information about the service, using the generated OpenAPI specification. The “Swagger” endpoint is not nested under the rest-path in order to avoid naming conflicts with runtime configured entities.
/api	REST API

Result set format#

The Data API service returns JSON objects in the following format:

{
   "value": []
}

The items related to the requested entity are available in the value array. For example:

{
  "value": [
    {
      "id": 1000,
      "title": "Foundation"
    },
        {
          "id": 1001,
          "title": "Foundation and Empire"
        }
  ]
}

Note

Only the first 100 items are returned by default.

REST API documentation with Swagger / OpenAPI#

The OpenAPI specification is a programming language-agnostic standard for documenting HTTP APIs. Data API supports the OpenAPI standard with its ability to:

Generate information about all runtime config defined entities that are REST enabled.
Compile the information into a format that matches the OpenAPI schema.
Exposes the generated OpenAPI schema via a visual UI (Swagger) or a serialized file.

Entity is the terminology used to reference a REST API resource in Data API. By default, the URL route value for an entity is the entity name. An entity’s REST URL path value is configurable within the entity’s REST settings.

REST HTTP methods#

Data API uses the HTTP method on your request to determine what action to take on the request designated entity. The following HTTP verbs are available, dependent upon the permissions set for a particular entity, generated based on a database object permissions.

Method	Description
GET	Get zero, one or more items.
POST	Create a new item.
PATCH	Update an item with new values if one exists. Otherwise, create a new item.
PUT	Replace an item with a new one if one exists. Otherwise, create a new item.
DELETE	Delete an item.

GET#

Using the GET method you can retrieve one or more items of the requested entity.

URL parameters#

REST endpoints support the ability to return an item via its primary key, using URL parameter:

GET /api/{entity}/{primary-key-column}/{primary-key-value}

Example:

GET /api/book/id/1001

Query parameters#

REST endpoints support the following query parameters (case sensitive) to control the returned items:

$select: returns only the selected columns,
$filter: filters the returned items,
$orderby: defines how the returned data is sorted,
$first and $after: returns only the top n items.

Query parameters can be used together.

POST#

Create a new item for the specified entity. For example:

POST /api/book
Content-type: application/json

{
  "id": 2000,
  "title": "Do Androids Dream of Electric Sheep?"
}

creates a new book. All the fields that can’t be nullable must be supplied. If successful the full entity object, including any null fields, is returned:

{
  "value": [
    {
      "id": 2000,
      "title": "Do Androids Dream of Electric Sheep?",
      "year": null,
      "pages": null
    }
  ]
}

PUT#

PUT creates or replaces an item of the specified entity. The query pattern is:

PUT /api/{entity}/{primary-key-column}/{primary-key-value}

for example:

PUT /api/book/id/2001
Content-type: application/json

{
   "title": "Stranger in a Strange Land",
   "pages": 525
}

If there’s an item with the specified primary key 2001, the provided data completely replaces that item. If instead an item with that primary key doesn’t exist, a new item is created.

In either case, the result is something like:

{
  "value": [
    {
      "id": 2001,
      "title": "Stranger in a Strange Land",
      "year": null,
      "pages": 525
    }
  ]
}

PATCH#

PATCH creates or updates the item of the specified entity. Only the specified fields are affected. All fields not specified in the request body aren’t affected. If an item with the specified primary key doesn’t exist, a new item is created.

The query pattern is:

PATCH /api/{entity}/{primary-key-column}/{primary-key-value}

for example:

PATCH /api/book/id/2001
Content-type: application/json

{
  "year": 1991
}

DELETE#

DELETE deletes the item of the specified entity. The query pattern is:

DELETE /api/{entity}/{primary-key-column}/{primary-key-value}

for example:

DELETE /api/book/id/2001

If successful, the result is an empty response with status code 204.

Database transactions for REST API requests#

To process POST, PUT, PATCH and DELETE API requests, Data API executes the database queries in a transaction.

Instance management#

The Data API is powered by Microsoft Data API Builder (DAB). On startup, Querona generates the DAB configuration — which is versioned — and runs it as a dedicated DAB process whose lifecycle Querona manages. Alternatively, DAB can be hosted separately as a container, in which case it is managed outside Querona.

Data API instance management options can be found under the Administer ‣ Data API Instances.

The following instance configuration options are supported and, if changed, require the Data API instance restart:

Option	Description
Name	Friendly Data API instance name.
User	User account name whose permissions are used to generate configuration of the REST interface. All user permissions are taken into account and can be granted to any securable in any VDB.
Host configuration	Type of host configuration used for environment initialization of the instance. The Development configuration additionally enables debug logging and Swagger.
Authentication option	Controls how REST client requests are authenticated. Possible values are Anonymous and JWT. If you use JWT, the Api Key value is needed to authenticate. If a password is required by a client, use any value as password.
Port	Http(s) port number to listen for incoming client requests.
Endpoint name template	Template used for generating endpoint names from database object names. The default template value is [database]_[schema]_[object_name]. The template allows string literals consisting of digits, lower and upper case letters, and a limited list of special characters. Special characters like .:?#/[]@!$&’()*+,;= will be replaced with hyphen. We recommend separating parts of the endpoint name with hyphens.
Automatically resolve duplicate endpoint names	Database object name conversion may cause a duplicate endpoint name, for example, when the same database object name is used in multiple schemas or databases and the template does not differentiate by schema or database name. In such a case, when the option is disabled, an error will be raised during service startup. When enabled, to ensure endpoint name uniqueness, the generated duplicate endpoint name will be postfixed by a consecutive number.
Endpoint name normalization rule	Controls how a generated endpoint name gets normalized (sanitized). In Strict mode, the following modifications apply: conversion to lowercase, conversion of diacritic characters to their ISO-8859-8 equivalent, special characters .:?#/[]@!$&’()+,;= replacement with a hyphen, underscore characters replacement with hyphen, white space characters replacement with hyphens. In Loose mode, the following modifications apply: special characters .:?#/[]@!$&’()+,;= replaced with a hyphen, white space characters replacement with hyphens.
Auto-start	Automatically starts the Data API instance during system startup.
Comment	Data API instance user-friendly comment.
Object inclusion criteria	Holds the list of criteria used to select objects (tables and views) that should be included in the Data API endpoint configuration, making them available to the REST interface.
Inclusion rules	Rule definition consists of up to 3 dot-separated parts, surrounded with square brackets: [database name].[schema].[object_name]. Special character * (star) can be used as a wildcard that accepts all input. Each part of the name is optional, with default of ``. For example, the expression .[my schema].* matches all tables and views in schema my schema in all databases accessable by the user selected in the User dropdown. Parts of the expression are interpreted from left to right, for example, the expression *.[dbo] matches all tables in schema dbo in all accessable databases.
Enable CORS	Enables cross-origin requests (CORS).
Set Access-Control- Allow-Credentials header	Sets the Access-Control-Allow-Credentials response header.
Origins	Specifies the list of allowed origins.

Viewing instance status#

Selecting the instance brings up the details blade with all configuration values in read-only mode.

Click the Refresh button to refresh the instance list and to ensure the State column shows the latest information.

Starting and stopping Data API instance#

The following buttons control the instance state:

Button	Action
Start	Starts the instance, if not already started.
Stop	Stops the instance, if not already stopped, by sending a “self-shutdown” command, allowing the instance to free all resources and close itself gracefully. This may take up to one minute, depending on the load and instance size.
Kill	Kills the instance without waiting for a clean shutdown, by sending an OS-level kill signal which terminates the underlying process (not recommended, use if a graceful shutdown fails).

Configuration endpoint for a separately hosted DAB#

A DAB container that runs outside Querona obtains the instance configuration from the following endpoint. The instance’s API key is the credential and it identifies the instance, so the request names no instance:

GET /api/1.0/dataapiinstances/configuration
Authorization: ApiKey <api key>

The configuration is generated from the current state of the virtual databases and permissions at the time of the call, in the form a separately hosted DAB needs: the data source points at the Querona TDS endpoint by host name, encryption is required, and the login is left to the container through the placeholders @env('QUERONA_DAB_LOGIN') and @env('QUERONA_DAB_PASSWORD'), which DAB substitutes from its environment. Each placeholder sits inside double quotes, so the password may contain any character except a double quote. The login must be the instance’s user, or a member of its role. No appsettings.json is produced: an external host terminates TLS itself.

Two conditions follow from the host being elsewhere. The connection requires encryption and verifies the certificate the TDS endpoint presents, so configure a certificate on the node (the SSL thumbprint instance parameter); the fallback certificate Querona generates when none is configured cannot be trusted by a container. And a JWT-authenticated instance can be rendered for an external host only while the Querona API runs over HTTPS, because DAB validates tokens against the API URL, which is http://localhost otherwise; the endpoint refuses such an instance with a message that says so.

The answer is the standard API envelope:

{
  "success": true,
  "data": {
    "instanceId": 33,
    "instanceName": "sales-api",
    "hash": "5F1C...E9",
    "files": [
      { "name": "dab-config.json", "sha256": "5F1C...E9", "content": "{ ... }" }
    ]
  }
}

Save every entry of files under its name into one directory - an instance that publishes more than one virtual database returns one dab-config.<database id>.json per additional database, which DAB resolves by name next to dab-config.json. A failure is reported inside the envelope (success: false with errors), never as an HTTP error status: an unknown or missing key, or a configuration that cannot be generated (the instance’s user sees no matching object, a published table or view has no primary key, duplicate endpoint names).

Change detection uses HTTP validation:

every successful answer carries ETag: "<hash>" - the same value as data.hash, computed from the file contents only. The contents name the node that rendered them - its TDS network name and, for a JWT instance, its API URL - so in a cluster the nodes answer the same hash only when they name themselves the same way: set the same TDS network name or IP override instance parameter on every node;
send it back as If-None-Match and Querona answers 304 Not Modified, without a body, while the cached answer is unchanged. When the cache period has run out the configuration is generated again and answered with 200 even if nothing changed, so a caller compares the hash rather than relying on the status alone; the following calls answer 304 again.

Answers are cached per instance for the period set by the engine configuration option Data API configuration cache TTL (default 30 seconds, see Engine settings); within that period a call costs nothing, after it one configuration is generated per instance however many callers ask. Saving the instance in Querona clears its cached answer at once on the node that handled the save (in a cluster the other nodes keep theirs until the TTL); a change in the virtual databases or permissions becomes visible after the TTL. DAB reads column metadata when it starts, so apply a changed configuration by restarting the container.

A complete OpenShift deployment - init container, sync script, refresh job - is described in Hosting DAB on OpenShift.

Managing Data API

Contents

Managing Data API#

Introduction#

Exposed resources#

Result set format#

REST API documentation with Swagger / OpenAPI#

REST HTTP methods#

GET#

URL parameters#

Query parameters#

POST#

PUT#

PATCH#

DELETE#

Database transactions for REST API requests#

Instance management#

Viewing instance status#

Starting and stopping Data API instance#

Configuration endpoint for a separately hosted DAB#

See Also#