DBGate Endpoints

DBGate Endpoints

Table of Contents

Introduction

DBGate serves the following categories of endpoints:

  1. Static files
  2. Application management endpoints
  3. Connection-based endpoints

Connection-based endpoints include:

  1. API endpoints
  2. Edit page endpoints
  3. Home page endpoints
  4. Login page endpoints
  5. Authentication endpoints

DBGate creates connection-based endpoints for each connection defined in the appsettings file.

For example, appsettings.json has the following contents:

{
  "ConnectionStrings": {
    "mssql": {
      "ProviderName": "System.Data.SqlClient",
      "ConnectionString": "Data Source=.\\SQLEXPRESS;Initial Catalog=master;User ID=user;Pwd=pass"
    }
  }
}

DBGate will serve the following base endpoints:

  1. https://dbgate/api/mssql/
  2. https://dbgate/edit/mssql/
  3. https://dbgate/home/mssql/
  4. https://dbgate/login/mssql
  5. https://dbgate/auth/mssql/

In this sample, api, edit, home, login, and auth are root segments.

You may change root segment names in the appsettings file.

Root and Connection Segments

By default, DBGate supports two orders of the root and connection segments like

  • https://dbgate/api/mssql/
  • https://dbgate/mssql/api/

You may set the required order only via the ConnectionSegment setting in the appsettings file.

Language Segments

DBGate allows specifying the desired culture in the URL like

  • https://dbgate/edit/mssql/en-us/
  • https://dbgate/edit/mssql/en-gb/

The Javascript client uses the culture to load language resources and format dates and numbers.

The Javascript client passes the culture to the DBGate backend like

  • https://dbgate/api/mssql/en-us/
  • https://dbgate/api/mssql/en-gb/

DBGate uses the culture to translate the model (names, comments, etc.) if this feature is configured in a database.

Also, DBGate passes the culture to stored procedure parameters defined in the LanguageParameters setting.

This feature allows returning translated data from stored procedures.

See details in the Developer Guide.

Language Folders

You may place language-specific files into wwwroot subfolders with the culture names like en-us, zh-hans, etc.

When a user specifies the culture in the URL like /en-us/, /en-gb/, or /zh-cn/, DBGate looks for the most appropriate folder.

For example, /edit/mssql/en-gb/ will load edit.htm from the wwwroot folder while /edit/mssql/zh-cn/ will load wwwroot/zh-hans/edit.htm.

For static files, for example, /en-gb/sample01.htm and /zh-cn/sample01.htm will be loaded from the same wwwroot/en-us/sample01.htm.

You may customize the default language folder using the DefaultLanguageFolder setting.

Container Segments

By default, DBGate includes into the web service model all database objects available to the user.

DBGate creates the default container.

You may add the desired container after the connection segment.

The following URLs have the same model:

  • https://dbgate/api/mssql/
  • https://dbgate/api/mssql/default/
  • https://dbgate/api/mssql/default/en-us/

Developers may create special views (of the QueryList type) to select specific database objects only.

For example, buyer and seller. In this case, DBGate allows using such views as the container names like

  • https://dbgate/api/mssql/buyer/
  • https://dbgate/api/mssql/seller/

In both cases, the model has all objects available to the user. However, the service documents include objects defined in the QueryList views.

See details in the Developer Guide.

API Endpoints

API endpoints return service documents, metadata, and data according to OData standards, and extended settings.

DBGate creates a set of endpoints for each connection specified in the appsettings file.

Endpoint formats (with the first API root segment):

  • <domain>/<API root>/<connection name>/
  • <domain>/<API root>/<connection name>/<container name>/
  • <domain>/<API root>/<connection name>/<container name>/<language>/
  • <domain>/<API root>/<connection name>/<language>/

As noted above, you may swap the API root and connection name segments.

The default root is api. You may customize the root name in the ApiRoot setting in the appsettings file.

Endpoint examples:

The basic endpoints return service documents.

Add $metadata segment to return the metadata.

Add an object name to return its data.

For example:

DBGate supports the following extensions (in the last segment): $excel_formats, $excel_format, $table_format, $workbooks, $definition.

$definition returns the object definition (if the user has the VIEW DEFINITION permission).

For example, https://dbgate.savetodb.com/api/mssql-023/usp_cashbook/$definition.

Other extensions require some database configuration.

See use samples in the DBGate online examples and details in the Developer Guide.

Edit Page Endpoints

Edit page endpoints return pages to view or edit database data.

DBGate creates endpoints for each edit root specified in the EditRoots setting in the appsettings file with the following template:

  • <domain>/<edit root>/<connection name>/
  • <domain>/<edit root>/<connection name>/<container name>/
  • <domain>/<edit root>/<connection name>/<container name>/<language>/
  • <domain>/<edit root>/<connection name>/<language>/

As noted above, you may swap the edit root and connection name segments.

For example:

The first URL show all available objects (from the service document). Other URLs shows data of the specified objects.

The default root is edit and the default page is edit.htm. You may configure multiple roots with the different pages.

Please note, edit pages use the window.location.href value to calculate an actual API URL.

For the example above, the edit pages request the following URLs:

Home Page Endpoints

Home page endpoints redirect to the service document edit pages or to the pages configured in the Home settings.

The JavaScript client uses these endpoint in the Home button action.

DBGate creates endpoints for each connection specified in the appsettings file with the following template:

  • <domain>/<home root>/<connection name>/
  • <domain>/<home root>/<connection name>/<container name>/
  • <domain>/<home root>/<connection name>/<container name>/<language>/
  • <domain>/<home root>/<connection name>/<language>/

As noted above, you may swap the edit root and connection name segments.

You may customize the home root name in the HomeRoot setting in the appsettings file.

Login Page Endpoints

Login page endpoints return the pages to login. The login pages use the authentication endpoints discussed below.

DBGate creates endpoints for each login root specified in the LoginRoots setting in the appsettings file with the following template:

  • <domain>/<login root>/<connection name>/
  • <domain>/<login root>/<connection name>/<container name>/
  • <domain>/<login root>/<connection name>/<container name>/<language>/
  • <domain>/<login root>/<connection name>/<language>/

As noted above, you may swap the login root and connection name segments.

Such pages do not require initial authentication credentials to allow users to login.

The default root is login and the default page is login.htm. You may configure multiple roots with the different pages, for example to support sign-up.

Authentication Endpoints

Authentication endpoints allows supports the JWT authentication.

DBGate creates POST endpoints for each connection specified in the appsettings file with the following template:

  • <domain>/<auth root>/<connection name>/login
  • <domain>/<auth root>/<connection name>/logout
  • <domain>/<auth root>/<connection name>/refresh
  • <domain>/<auth root>/<connection name>/getToken

As noted above, you may swap the auth root and connection name segments.

The default auth root is auth. You may customize the auth root name in the AuthRoot setting in the appsettings file.

The login endpoint returns the access and refresh tokens, cookie and access token expiration time.

The access token and cookie are required to load protected resources. The refresh token and cookie are required to renew the access token.

You may customize the token expiration time using the AccessTokenExpirationInMinutes and RefreshTokenExpirationInMinutes settings.

The logout endpoint clears access and refresh tokens at the server-side.

The refresh endpoint returns a new access token and its expiration time and may return a new refresh token and cookie.

The getToken endpoint returns a one-time access token. This allows getting protected resources that do not support JavaScript.

DBGate implements the JWT authentication using best practices.

You may check the implementation details using the browser developer tools.

Please note that you may not see the cookie in some queries as they are set for the API and auth roots only with SameSite=Strict; HttpOnly=true.

Application Management Endpoints

You may use the following POST endpoints to stop the console application, show or hide its window on the Windows platform:

  • <domain>/<app root>/stop
  • <domain>/<app root>/show
  • <domain>/<app root>/hide

For example:

  • https://localhost:5003/app/stop
  • https://localhost:5003/app/show
  • https://localhost:5003/app/hide

These operations are useful for integrating DBGate into your desktop apps.

These operations are available only for the Windows console application with the StopEnabled true.

The default app root is app. You may customize the app root name in the AppRoot setting in the appsettings file.

Static Files

DBGate serves static files from the wwwroot folder.

For example, it serves such files like /css/db.min.css or /js/db.min.js, sending gzip contents if applicable.

As noted above, DBGate processes language folders in a special manner.

For example, it returns the wwwroot/en-us/sample01.htm file for the /en-gb/sample01.htm request if the wwwroot/en-gb/sample01.htm file does not exist.

DBGate supports the following index pages: default.htm, default.html, index.htm, index.html.

DBGate automatically adjusts the <base href="/" /> tag in HTML files for IIS applications.