DBGate Application Settings

DBGate Application Settings

Table of Contents

Introduction

DBGate reads its configuration from JSON files, specifically appsettings.json and appsettings.Production.json.

  • The appsettings.json file contains settings for all environments.
  • The appsettings.Production.json file contains settings specific to the Production environment.

The appsettings.json file can be empty, represented by {}.

DBGate also supports configuration through environment variables and command-line options.

For more details, see Configuration in ASP.NET Core.

Configuration Sample

Here’s a sample configuration file:

{
  "AllowedHosts": "*",
  "Kestrel": {
    "EndPoints": {
      "Http": {
        "Url": "http://localhost:5003"
      }
    }
  },
  "Logging": {
    "LogLevel": {
      "Default": "Warning",
      "System": "Information",
      "Microsoft": "Information"
    }
  },
  "DBGate": {
    "Auth": "jwt",
    "WebRoot": "wwwroot",
    "ApiRoot": "api",
    "AppRoot": "app",
    "AuthRoot": "auth",
    "HomeRoot": "home",
    "EditRoots": {
        "edit": "edit.htm"
    },
    "LoginRoots": {
        "login": "login.htm"
    },
    "ApplicationPathBase": null,
    "ConnectionSegment": 0,
    "DefaultPort": 5003,
    "DefaultLanguageFolder": "en-us",
    "AccessTokenExpirationInMinutes": 10,
    "RefreshTokenExpirationInMinutes": 20160,
    "MaxPageSize": 1000,
    "UppercaseNamesInLowercase": true,
    "DefaultContainerName": "default",
    "DisableMetadataCache": false,
    "DisableEditPageCache": false,
    "IncludeAnnotations": true,
    "HideConfigurationObjects": true,
    "HideEditProcedures": true,
    "EnableCodeBrowser": true,
    "BinaryAsHex": false,
    "BigNumbersAsString": false,
    "FunctionPrefixes": "xl_validation_list_, xl_parameter_values_",
    "FunctionSuffixes": "_select",
    "LanguageParameters": "data_language, DataLanguage",
    "TraceSQL": true,
    "StopEnabled": false
  },
  "DBGateW": {
    "WebRoot": "wwwroot",
    "IntegratedSecurityEnabled": false,
    "StopEnabled": true
  },
  "ConnectionStrings": {
    "mssql": {
      "Title": "SQL Server",
      "Offline": false,
      "AllowDatabaseChange": true,
      "AllowServerChange": true,
      "ProviderName": "System.Data.SqlClient",
      "ConnectionString": "Data Source=.\\SQLEXPRESS;Initial Catalog=master;User ID=user;Pwd=pass"
    },
    "pgsql": {
      "Title": "PostgreSQL",
      "Offline": false,
      "AllowDatabaseChange": true,
      "AllowServerChange": true,
      "ProviderName": "Npgsql",
      "ConnectionString": "Server=localhost;Password=pass;User ID=user;Database=postgres"
    },
    "mysql": {
      "Title": "MySQL",
      "Offline": false,
      "AllowDatabaseChange": true,
      "AllowServerChange": true,
      "ProviderName": "MySqlConnector",
      "ConnectionString": "Server=localhost;Password=pass;User ID=user;Database=mysql"
    },
    "mssql-023": {
      "Offline": false,
      "Home": "sample02.htm",
      "QueryList": "",
      "IncludeSchemas": "s02 xls",
      "ExcludeSchemas": null,
      "HideSchemas": "xls",
      "HideConfigurationObjects": true,
      "HideEditProcedures": true,
      "OmitSchemasInNames": "s02",
      "ProviderName": "System.Data.SqlClient",
      "ConnectionString": "Data Source=mssql.savetodb.com;Initial Catalog=AzureDemo100;User ID=sample02_user3;Pwd=Usr_2011#_Xls4168"
    },
    "marketplace": {
      "Title": "Marketplace",
      "Offline": false,
      "OmitSchemasInNames": "marketplace",
      "ApiNameReplacements": {
        "usp_buyer_": "",
        "usp_seller_": ""
      },
      "ProviderName": "MySqlConnector",
      "ConnectionString": "Server=localhost;Password=pass;User ID=user;Database=marketplace",
      "SignIn": "marketplace.usp_sign_in",
      "SignUp": "marketplace.usp_sign_up",
      "SignInRoleField": "role",
      "SignInMessageField": "message",
      "AuthContextValues": {
        "auth_account_id": 1
      },
      "AuthContextParams": "auth_user_id auth_seller_id",
      "RoleUsers": {
        "auth": {
          "Username": "marketplace_auth",
          "Password": "Usr_2011#_Xls4168"
        },
        "default": {
          "QueryList": "marketplace.xl_buyer_views",
          "Username": "marketplace_buyer",
          "Password": "Usr_2011#_Xls4168"
        },
        "buyer": {
          "QueryList": "marketplace.xl_buyer_views",
          "Username": "marketplace_buyer",
          "Password": "Usr_2011#_Xls4168"
        },
        "seller": {
          "QueryList": "marketplace.xl_seller_views",
          "Username": "marketplace_seller",
          "Password": "Usr_2011#_Xls4168"
        }
      }
    }
  }
}

Top-level Keys and Sections

AllowedHosts
This optional value enables host filtering.
See details at Host filtering with ASP.NET Core Kestrel web server.
Use * to serve endpoints for all hosts.
Kestrel
This optional section configures the Kestrel web server.
Use it to define specific endpoint SSL certificates.
See details at Kestrel web server implementation in ASP.NET Core.
Logging
This optional section configures logging features.
See details at Logging Configuration.
DBGate
This section contains DBGate settings.
DBGateW
This section overrides DBGate settings for the console version built for .NET Framework.
It can only contain the WebRoot, IntegratedSecurityEnabled, and StopEnabled settings.
ConnectionStrings
This dictionary defines connection names and related properties, including the required connection strings.
DBGate uses connection names to configure endpoints.
For example, the sample above defines endpoints like /api/mssql/, /api/pgsql/, /api/mysql/, /api/mssql-023/, and /api/marketplace/.

Common DBGate Settings

Auth
This value defines the authentication schema.
Possible values: jwt and basic.
The default value is jwt, which also allows using basic authentication.
WebRoot
This value defines the folder containing the website contents.
The default value is wwwroot.
ApiRoot
This value defines the API root, such as api in the /api/mssql/ endpoint.
The default value is api.
AppRoot
This value defines the console application management root, such as app in the /app/stop URL.
Supported POST commands: stop, hide, and show.
To enable commands, set StopEnabled to true.
AuthRoot
This value defines the root for JWT operations, such as auth in the /auth/mssql/login URL.
Supported POST commands: login, logout, refresh, and getToken.
HomeRoot
This value defines the root for home redirect endpoints, such as home in the /home/mssql/ endpoint.
DBGate redirects such URLs to home pages set with the Home value or to the service document if the page is not set.
The default value is home.
EditRoots
This section defines edit roots, such as edit in the /edit/mssql/ endpoint, and the associated HTML pages.
For example, DBGate returns the edit.htm page content for the /edit/mssql/ endpoint.
The default root is edit, and the default page is edit.htm.
LoginRoots
This section defines login roots, such as login in the /login/mssql/ endpoint, and the associated HTML pages.
DBGate returns the login.htm page content for the /login/mssql/ endpoint.
These endpoints do not require initial authentication.
The default root is login, and the default page is login.htm.
ApplicationPathBase
This value defines the application path base.
Usually, you do not need to change this value.
See details at UsePathBaseExtensions.UsePathBase.
ConnectionSegment
This value defines the order of the API root and connection name segments.
By default, DBGate allows using endpoints like /api/mssql/ and /mssql/api/.
You can set it to 2 to allow only endpoints with the connection in the second segment like /api/mssql/, or set it to 1 to allow only the connection in the first segment like /mssql/api/.
The default value is 0.
DefaultPort
This value defines the default port used when service URLs are not specified in the configuration, environment variables, or command-line arguments.
The default value is 5003.
DefaultLanguageFolder
This value defines the default language folder for finding files when no specific language folder is specified in the URL.
For example, DBGate will return pages from the en-us subfolder.
The default value is en-us.
AccessTokenExpirationInMinutes
This value defines the expiration time for access tokens in minutes.
The default value is 10 minutes.
RefreshTokenExpirationInMinutes
This value defines the expiration time for refresh tokens in minutes.
The default value is 20160 minutes (14 days).
MaxPageSize
This value defines the maximum number of records returned in a response.
UppercaseNamesInLowercase
This value enables converting uppercase object and column names to lowercase.
DBGate leaves mixed-case names unchanged.
The default value is true.
DefaultContainerName
This value defines the name of the default entity container and its schema.
The default entity container can be omitted in the URLs.
For example, endpoints like /api/mssql/ and /api/mssql/default/ use the same model.
The default value is default.
DisableMetadataCache
This value allows disabling the metadata model cache.
Developers can use it to load the model from a database every time during development.
Note that you can also use the URL system parameter $reloadMetadata=true or press Ctrl and click the Reload button instead.
The default value is false.
DisableEditPageCache
This value allows disabling the edit page cache.
Developers can use it when developing edit pages.
The default value is false.
IncludeAnnotations
This value enables metadata annotations.
The built-in DBGate client does not depend on this option.
The default value is true.
HideConfigurationObjects
This value hides configuration objects like xls.objects, xls.handlers, and other objects of the SaveToDB Framework.
The DBGate section contains the default value, which you may override in the connection settings.
The default value is true.
HideEditProcedures
This value hides edit procedures like usp_cashbook2_insert, usp_cashbook2_update, and usp_cashbook2_delete of the usp_cashbook2 procedure.
DBGate uses these procedures internally to support POST, PUT, and DELETE operations.
You may publish these procedures to call them via POST requests.
The DBGate section contains the default value, which you may override in the connection settings.
The default value is true.
EnableCodeBrowser
This value enables retrieving object definitions using the /$definition URL segment.
A user must have the VIEW DEFINITION permission to access the definition.
The default value is false.
BinaryAsHex
This value enables serializing binary data as hex strings by default, instead of base64.
You can also use the URL system parameter $binaryAsHex=true.
The default value is false.
BigNumbersAsString
This value enables serializing large numbers that lose precision in JavaScript as strings.
You can also use the URL system parameter $bigNumbersAsString=true.
The default value is false.
FunctionPrefixes
This comma-separated value defines prefixes of stored procedure names to assign the function type instead of the default action type.
For example, if DBGate cannot detect the select nature of the xl_list_users procedure, it assigns the action type.
You can set prefixes directly.
The example above contains prefixes used in SaveToDB and DBGate examples.
FunctionSuffixes
This comma-separated value defines suffixes of stored procedure names to assign the function type instead of the default action type.
For example, if DBGate cannot detect the select nature of the usp_users_select procedure, it assigns the action type.
You can set suffixes directly.
The example above contains suffixes used in SaveToDB and DBGate examples.
LanguageParameters
This comma-separated value defines language context parameter names.
DBGate does not publish these parameters in the model but passes the defined values to all procedures that have these parameters.
This feature is useful for multi-tenant applications.
The example above includes the auth_account_id parameter, which can be used in stored procedures to process data for account 1.
TraceSQL
This value enables SQL command tracing.
The default value is false.
StopEnabled
This value allows stopping, showing, or hiding the console application using the /app/stop, /app/show, and /app/hide POST requests.
You can use these commands when integrating DBGate into your desktop apps.
You can change the /app root using the AppRoot setting.
The default value is false.
IntegratedSecurityEnabled
This value enables integrated security in the console version built with .NET Framework.
Typically, this version serves localhost only and runs under the current user account, making it safe to allow integrated security for database connections.
The default value is false.

Connection Settings

ProviderName
This required value defines the provider name.
ConnectionString
This required value defines the connection string.
Use a real username and password or the user and pass placeholders.
In the latter case, DBGate requires a username and password when a user connects to the endpoint and replaces placeholders with the actual values.
AllowDatabaseChange
This value allows changing a connection database via the URL in the format <connection>:<database>.
For example, a user can use the URL /edit/mssql:AzureDemo100/ to connect to the AzureDemo100 database.
The default value is true for the master, postgres, and mysql databases, and false for others.
AllowServerChange
This value allows changing a connection server and database via the URL in the format <connection>:<server>[,<port>][,<database>].
For example, a user can use the URL /edit/mssql:mssql.savetodb.com,AzureDemo100/ to connect to the AzureDemo100 database at the mssql.savetodb.com server.
The default value is false.
AllowPortChange
This value allows changing a connection port via the URL in the format <connection>:<server>[,<port>][,<database>].
For example, a user can use the URL /edit/mssql:mssql.savetodb.com,1433,AzureDemo100/ to connect to the AzureDemo100 database at the mssql.savetodb.com server on port 1433.
The default value is false.
Title
This value defines a connection title shown to end users.
The default value is the connection name.
Offline
This value allows disabling the endpoint.
DBGate immediately returns an offline message without attempting to connect to the database.
The default value is false.
Home
This value sets the homepage of the connection.
DBGate redirects to this homepage when a user clicks the Home button.
To open the default page, hold the Ctrl key while clicking the Home button.
You can specify the file name only and place language-specific pages in folders like en-us and zh-hans. DBGate will return the page based on the user's language.
In the example above, the connection mssql-023 uses sample02.htm as the homepage.
QueryList
This value defines a view in the SaveToDB QueryList format to advertise view objects only in the service document.
Use this feature to configure the entity container and keep the service root clear.
The default value is null, which advertises all select-nature database objects available to the user.
IncludeSchemas
This space-separated value defines an explicit list of schemas to include in the model.
If the value is empty, the model includes only the database for MySQL and all schemas except those specified in the ExcludeSchemas value for other servers.
You may specify * to include all schemas for MySQL.
ExcludeSchemas
This space-separated value defines a list of schemas to exclude from the model.
HideSchemas
This space-separated value defines a list of schemas whose objects will not be advertised in the service document.
HideConfigurationObjects
This value hides configuration objects like xls.objects, xls.handlers, and other objects of the SaveToDB Framework.
This value overrides the default value defined in the DBGate section.
HideEditProcedures
This value hides edit procedures like usp_cashbook2_insert, usp_cashbook2_update, and usp_cashbook2_delete of the usp_cashbook2 procedure.
DBGate uses these procedures internally to support POST, PUT, and DELETE operations.
You may publish these procedures to call them via POST requests.
This value overrides the default value defined in the DBGate section.
OmitSchemasInNames
This space-separated value defines a list of schemas to omit from service object names.
For example, database objects like s02.cashbook or s02.usp_cashbook are published by default as s02_cashbook and s02_usp_cashbook.
In the example above, the schema s02 is omitted, so database objects are published as cashbook and usp_cashbook.
If the value is empty, DBGate omits the schema if a database contains a single schema that does not include the xls schema.
To disable this feature, specify a dummy schema like none.
ApiNameReplacements
This section defines strings to replace in the generated object names.
In the example above, DBGate replaces usp_buyer_ and usp_seller_ with empty strings.
For instance, names like usp_buyer_purchases and usp_seller_orders are published as purchases and orders.
SignIn
This value defines a procedure for authenticating users.
DBGate executes this procedure using the credentials of the auth role from the RoleUsers section.
The procedure must have at least two parameters for the username and password.
It may also include parameters defined in the AuthContextValues section.
The procedure must return at least one field defined in the AuthContextParams value, usually a user ID.
Additionally, it may return the role and message fields defined in the SignInRoleField and SignInMessageField values.
If the procedure returns an error, DBGate attempts to authenticate the user using the database login and password.
SignUp
This value defines a procedure for signing up new users.
The features and requirements are the same as for SignIn.
SignInRoleField
This value defines the name of the result field containing the signed user role.
DBGate uses this role value to load settings from the RoleUsers section.
The default value is role.
SignInMessageField
This value defines the name of the result field containing the error message.
If the value is not null, DBGate raises an exception and returns the message to the user.
The default value is message.
AuthContextValues
This section defines context parameter names and values.
DBGate does not publish these parameters in the model but passes the defined values to all procedures that have these parameters.
This feature is useful for multi-tenant applications.
The example above includes the auth_account_id parameter, which can be used in stored procedures to process data for account 1.
AuthContextParams
This value defines parameters populated from the SignIn and SignUp procedures.
DBGate does not publish these parameters in the model but passes the values to all procedures that have these parameters.
Usually, this is an internal user ID.
However, you may define and return any number of parameters.
In the example above, the application has two parameters: auth_user_id and auth_seller_id, which procedures may use.
RoleUsers
This section contains settings for user roles.
It must define at least two built-in roles: auth and default.
DBGate uses the auth role credentials to execute the SignIn and SignUp procedures.
DBGate uses the default role credentials to serve authenticated user requests when the SignIn and SignUp procedures do not return a role or the role is not found in the RoleUsers section.
Each role must have at least two values: Username and Password.
Additionally, the role may include the QueryList value, which defines a view that selects objects to advertise in the service document.
The example above also contains two additional roles: buyer and seller, allowing for different models for each user role.

This website is using cookies. By continuing to browse, you give us your consent to our use of cookies as explained in our Cookie Policy.