DBGate Application Settings
Table of Contents
- Introduction
- Configuration Sample
- Top-level Keys and Sections
- Common DBGate Settings
- Connection Settings
Introduction
DBGate reads the configuration from the JSON configuration files like appsettings.json
and appsettings.Production.json
.
The appsettings.json
file contains settings for all environments. The appsettings.Production.json
file contains settings for the Production
environment.
The appsettings.json
file can be empty with the {}
content.
DBGate also supports configuring via environment variables and command-line options.
See details at Configuration in ASP.NET Core.
Configuration Sample
Below is a sample of the 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 allows enabling 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 allows configuring the Kestrel web server.
For example, use this section to define specific endpoint SSL certificates.
See details at Kestrel web server implementation in ASP.NET Core. - Logging
- This optional section allows configuring 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 contain theWebRoot
,IntegratedSecurityEnabled
, andStopEnabled
settings only. - 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
andbasic
.
The default value isjwt
that also allows using the basic authentication. - WebRoot
- This value defines the folder with the website contents.
The default value iswwwroot
. - ApiRoot
- This value defines the API root, like
api
in the/api/mssql/
endpoint.
The default value isapi
. - AppRoot
- This value defines the console application management root, like
app
in the/app/stop
URL.
Supported POST commands:stop
,hide
, andshow
.
To enable commands, setStopEnabled
to true. - AuthRoot
- This value defines the root of JWT operations, like
auth
in the/auth/mssql/login
URL.
Supported POST commands:login
,logout
,refresh
, andgetToken
. - HomeRoot
- This value defines the root of home redirect endpoints, like
home
in the/home/mssql/
endpoint.
DBGate redirects such URLs to home pages set with theHome
value or to the service document if the page is not set.
The default value ishome
. - EditRoots
- This section defines edit roots, like
edit
in the/edit/mssql/
endpoint, and used HTML pages.
For example above, DBGate returns theedit.htm
page content for the/edit/mssql/
endpoint.
The default root isedit
and the default page isedit.htm
. - LoginRoots
- This section defines login roots, like
login
in the/login/mssql/
endpoint, and used HTML pages.
For example above, DBGate returns thelogin.htm
page content for the/login/mssql/
endpoint.
These endpoints do not require the initial authentication.
The default root islogin
and the default page islogin.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 may set 2 to allow only endpoints with the connection in the second segment like/api/mssql/
and 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 the 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 to find files when there is no folder of the desired language specified in the URL.
For example above, DBGate will return pages from theen-us
subfolder.
The default value isen-us
. - AccessTokenExpirationInMinutes
- This value defines a number of minutes of the access token expiration.
The default value is 10 minutes. - RefreshTokenExpirationInMinutes
- This value defines a number of minutes of the refresh token expiration.
The default value is 20160 minutes (14 days). - MaxPageSize
- This value defines the maximum number of records returned in the response.
- UppercaseNamesInLowercase
- This value enables converting uppercase object and column names to lowercase.
DBGate leaves mixed-case names as is.
The default value is true. - DefaultContainerName
- This value defines a name of the default entity container and its schema.
The default entity container can be omitted in the URLs.
For the example above, endpoints like/api/mssql/
and/api/mssql/default/
use the same model.
The default value isdefault
. - DisableMetadataCache
- This value allows disabling the metadata model cache.
Developers can use it to load the model from a database every time during the development phase.
Note that you can use the URL system parameter$reloadMetadata=true
or pressCtrl
and click theReload
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.
TheDBGate
section contains the default value. You may rewrite it in the connection settings.
The default value is true. - HideEditProcedures
- This value hides edit procedures like
usp_cashbook2_insert
,usp_cashbook2_update
, andusp_cashbook2_delete
of theusp_cashbook2
procedure.
DBGate uses such procedures internally to support POST, PUT, and DELETE operations.
You may publish such procedures to call them via POST requests.
TheDBGate
section contains the default value. You may rewrite it in the connection settings.
The default value is true. - EnableCodeBrowser
- This value enables getting object definitions using the
/$definition
URL segment.
A user must have theVIEW DEFINITION
permission to get the definition.
The default value is false. - BinaryAsHex
- This value enables serializing binary data as hex strings by default contrary to base64.
Note that you can use the URL system parameter$binaryAsHex=true
instead.
The default value is false. - BigNumbersAsString
- This value enables serializing big numbers that lose precision in JavaScript as strings.
Note that you can use the URL system parameter$bigNumbersAsString=true
instead.
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 thexl_list_users
procedure, it assigns the action type.
So, you may 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 theusp_users_select
procedure, it assigns the action type.
So, you may 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 such parameters in the model and passes language values automatically.
DBGate detects the language by the URL segments like/en/
,/en-us/
,/en-gb/
, or/zh-cn/
.
DBGate detects configured translations in databases and passes existing code language values if possible or generic code languages if not.
For example, it may passen
for/en-gb/
andzh-hans
for/zh-cn/
.
The example above contains language-context parameters used by the SaveToDB add-in. - 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 may use these commands when integrating DBGate into your desktop apps.
You may change the/app
root using theAppRoot
setting.
The default value is false. - IntegratedSecurityEnabled
- This value enables integrated security in the console version built with .NET Framework.
Usually, this version serves the localhost only and started under the current user account.
So, it's safe to allow the integrated security to connect to databases.
The default value is false.
Connection Settings
- ProviderName
- This required value defines a provider name.
- ConnectionString
- This required value defines a connection string.
Use a real username and password or theuser
andpass
placeholders.
In the last 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 form like
<connection>:<database>
.
For example, a user can use the URL like/edit/mssql:AzureDemo100/
to connect to theAzureDemo100
database.
The default value is true for themaster
,postgres
andmysql
databases and is false for others. - AllowServerChange
- This value allows changing a connection server and database via the URL in the form like
<connection>:<server>[,<port>][,<database>]
.
For example, a user can use the URL like/edit/mssql:mssql.savetodb.com,AzureDemo100/
to connect to theAzureDemo100
database at themssql.savetodb.com
server.
The default value is false. - AllowPortChange
- This value allows changing a connection port via the URL in the form like
<connection>:<server>[,<port>][,<database>]
.
For example, a user can use the URL like/edit/mssql:mssql.savetodb.com,1433,AzureDemo100/
to connect to theAzureDemo100
database at themssql.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 trying to connect to the database.
The default value is false. - Home
- This value allows setting the homepage of the connection.
DBGate redirects to the homepage when a user clicks theHome
button.
To open the default page, hold theCtrl
key when clicking theHome
button.
You may specify the file name only and place language-specific pages into folders likeen-us
andzh-hans
. DBGate will return the page depending on the user language.
In the example above, the connectionmssql-023
usessample02.htm
as the homepage. - QueryList
- This value defines a view in the SaveToDB QueryList format to advertise the 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 that advertises all select-nature database objects available to the user. - IncludeSchemas
- This space-separated value defines an explicit list of schemas to include into the model.
If the value is empty, the model includes the only database for MySQL and all schemas except for specified in theExcludeSchemas
value for other servers.
You may specify*
to include all schemas for MySQL. - ExcludeSchemas
- This space-separated value defines a list of schemas to not include into the model.
- HideSchemas
- This space-separated value defines a list of schemas to not advertize the schema objects into 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 theDBGate
section. - HideEditProcedures
- This value hides edit procedures like
usp_cashbook2_insert
,usp_cashbook2_update
, andusp_cashbook2_delete
of theusp_cashbook2
procedure.
DBGate uses such procedures internally to support POST, PUT, and DELETE operations.
You may publish such procedures to call them via POST requests.
This value overrides the default value defined in theDBGate
section. - OmitSchemasInNames
- This space-separated value defines a list of schemas omitted in the service object names.
For example, database objects likes02.cashbook
ors02.usp_cashbook
are published by default ass02_cashbook
ands02_usp_cashbook
.
In the example above, the schemas02
is omitted. So, database objects are published ascashbook
andusp_cashbook
.
If the value is empty, DBGate omits the schema if a database contains a single schema not including thexls
schema.
To disable this feature, specify a dummy schema likenone
. - ApiNameReplacements
- This section defines strings to replace in the generated object names.
In the example above, DBGate replacesusp_buyer_
andusp_seller_
with empty strings.
So, for example, names likeusp_buyer_purchases
andusp_seller_orders
are published aspurchases
andorders
. - SignIn
- This value defines a procedure to authenticate users.
DBGate executes this procedure using the credentials of theauth
role of theRoleUsers
section.
The procedure must have at least two parameters, for username and password.
Also, the procedure may have parameters defined in theAuthContextValues
section.
The procedure must return at least one field defined in theAuthContextParams
value, usually a user id.
Also, the procedure may return the role and message fields defined in theSignInRoleField
andSignInMessageField
values.
If the procedure returns an error, DBGate tries to authenticate the user using the database login and password. - SignUp
- This value defines a procedure to sign-up new users.
Features and requirements are the same as ofSignIn
. - SignInRoleField
- This value defines the name of the result field with the signed user role.
DBGate uses the role value to load settings from theRoleUsers
section.
The default value isrole
. - SignInMessageField
- This value defines the name of the result field with the error message.
If the value is not null, DBGate raises an exception and returns the message to the user.
The default value ismessage
. - AuthContextValues
- This section defines context parameter names and values.
DBGate does not publish such 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 has theauth_account_id
parameter that can be used in stored procedures to process data of the account 1. - AuthContextParams
- This value defines parameters populated from the
SingIn
andSignUp
procedures.
DBGate does not publish such 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.
For example above, the application has two parameters:auth_user_id
andauth_seller_id
. And procedures may use one of them or both. - RoleUsers
- This section contains settings for user roles.
The section must have definitions at least for two built-in roles:auth
anddefault
.
DBGate uses theauth
role credentials to execute theSignIn
andSignUp
procedures.
DBGate uses thedefault
role credentials to serve authenticated user requests when theSignIn
andSignUp
procedures do not return the role or the role is not found in theRoleUsers
section.
Each role must have at least two values:Username
andPassword
.
Also, the role may contain theQueryList
value that defines a view that selects objects to advertize in the service document.
The example above, contains two additional roles:buyer
andseller
. This allows having different models for each user role.