Managing Data API

Introduction

Querona ships with a built-in DataAPI service, that provides modern REST and GraphQL endpoints to configured database objects in your virtual databases.

Database objects are securely exposed via REST or GraphQL endpoints so that your data can be accessed using modern techniques on any platform, any language, and any device.

Features:

• General

  • Tables and views access via REST and GraphQL

• 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

DataAPI 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 DataAPI serivce 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

DataAPI 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

Querona manages DataAPI configuration and lifecycle of the DataAPI that works in a dedicated Dotnet v6 process.

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

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

Option	Description
Name	Friendly DataAPI 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 incomming 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	DataApi 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 DataApi 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 DataAPI 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).

See Also

• qua_data_api_instances