Skip to content

Databases

One of the most powerful and unique features of SlashDB is the ability to automatically discover tables, views and relations in databases.

In this section we're going to connect SlashDB to a sample database and make it usable to user admin. The procedure is fairly simple and will take just a moment.

Database Definitions list

The Database Definitions page provides a list of all data sources available for viewing and interaction. It can be searched using search field above the list or sorted by clicking on headers of the columns.

The list is accessible from the main Menu Configure > Databass or direct URL /dbdef. It is available only admin and users with Administrative privilege to view list of Databases.

The list can be searched using search field above the list or sorted by clicking on headers of the columns.

On the list you can find:

  • button to edit database details view edit button,
  • Database ID,
  • Type,
  • Description,
  • Connection status,
  • button that opens a modal with details about connection db info button
  • when status is connecting modal shows reflection progress
  • when status is connected modal shows reflected tables and views
  • when status is failed modal shows the error message
  • button that toggles the state of the selected database db off on button
  • and link to JSON representation.

Adding a new database

1. Click on Configure > Databases in menu to show list of database definitions

2. Next click the in top right corner. The button is visible for admin and users with Administrative Privilege to create new database.

3. The modal with configuration wizard will appear.

Start by choosing database type you wish to connect by clicking on database logo.

At this point out of the box SlashDB can connect to:

  • IBM DB2
  • MariaDB
  • MS SQL Server
  • MySQL
  • ORACLE
  • PostgreSQL
  • SQLCipher
  • SQLite

Plugins are also available for the following:

database type choice

Next, fill in the connection details for the database. Note that depending on selected database type the list of fields may be different than pictured.

Click "Test Connection" to verify that the database server is reachable. If it fails we recommend resolving connection problems before proceeding.

You can ignore the connection problem for now and finish adding new database. Later you can adjust the connection details by editing database configuration.

Click "Next" to proceed.

database connection

Fill the details for the API that will be created. Hover your mouse cursor over the icon to learn more about the purpose of the field and configuration tips.

  • Choose a name for your database in SlashDB. This will become the Endpoint in Data Discovery and Database ID in SQL Pass-thru.
  • Pick charset encoding used on your database server.
  • Optionally add a description.

Option Auto Discover and Connect Automatically are checked by default. This way SlashDB will discover all available tables and views in you database and will do that automatically whenever system starts.

Additionally, Cache Schema comes checked by default so that connecting to the database is quicker. However this will add a prompt asking if you would like to load from the cached schema or not. If you are using a smaller database that changes its structure frequently, then you may want to disable schema caching so that the database will always find new changes on startup without having to select "No" in the prompt.

Cache Schema Prompt

Keep in mind that database credentials you provided in previous step are used only for the process of discovering tables. After adding the database you should modify SlashDB user settings and add a database mapping. See user configuration section Database Mapping.

Clicking "Next" will save your new database configuration and start connecting to the database.

database connection

For small databases the tables are reflected immediately. If there are many large tables in the database you will see a progress bar on the screen and reflection status for each created API.

If Schema Caching is enabled and a cached schema is present, then this loading process will still be shown, but it will only load the tables/views that were present in the cached schema if the "Yes" option was selected in the schema caching prompt.

Otherwise, SlashDB will reflect all tables and views available in database schema used in connection configuration. The list of reflected tables can be limited by:

  • using different database login or schema which has access to needed objects,
  • setting option skip_tables_without_pk = True in file /etc/slashdb/slashdb.ini which limits reflection only to tables and views that have a primary key.

When process is completed you will see a screen like below with a message "Ready". It means you can start exploring your data.

For convenience a direct link is shown to newly created API. In our example it's /db/Northwind.

database connection

3. In the Database ID field give the database a unique name and optionally put a short description in the Description field.

4. Select appropriate database type from the Type drop-down menu and fill out the remaining fields on the screen. Hover your mouse cursor over the icon to learn more about the purpose of the field and configuration tips.

Note that depending on the database type selected the list of fields may be different than pictured.

5. The "Test Connection" button can be used to confirm that SlashDB can connect to the database server with provided host information and credentials.

6. When finished click the Save button. If Use Credentials for Current User is selected then on saving another modal will popup showing updated user configuration with mapping to the database defined using Database User and Database Password. Save and close and then Close the configuration of the database.

Now the newly added database will appear in the Database Definitions table.

Connect database

Use action button on Data Discovery /db or Database Definitions /dbdef page to connect and disconnect a database. The Connection status will change.

If the database was previously connected and Cache Schema was enabled in the database config, the model could be already cached.

In such case you will receive a prompt asking whether you prefer to:

  • load the model from cache, which speeds up the process, or
  • do full reflect to take into account changes made in the database
  • close the prompt without taking any action.

caching prompt

Failed Connection

When something goes wrong while connecting to a database, SlashDB will change the Connection status to Failed.

To find out more about the reason go to Configure > Databases and click on the info button info button.

A failed connection usually occurs because of the following two reasons:

  • an error in the database configuration - please check the database definition for typos/errors,
  • network errors due to a firewall blocking access to the database or network down time - check the firewall and grant access from the SlashDB host to the host and port on which the database server is running.

You may need to ask your database and/or network administrator for help.

Configuration File

Although it is easier to modify database configuration using Database Configuration GUI, some attributes may not be available in the GUI and require modifying databases.cfg manually.

Database configurations are stored in a YAML file /etc/slashdb/databases.cfg.

A example configuration file with single SQLite database called Chinook will look as follows:

Chinook:
  alternate_key: {}
  autoconnect: false
  autoload: true
  autoload_user: {dbpass: '', dbuser: ''}
  cache_dbmodel: false
  connection: null
  creator: admin
  db_dialect: null
  db_driver: null
  db_encoding: utf-8
  db_host: null
  db_id: Chinook
  db_name: /var/lib/slashdb/sqlite/chinook.sqlite
  db_port: null
  db_schema: null
  db_type: sqlite
  desc: Example SQLite database
  excluded_columns: {}
  execute: []
  foreign_keys: {}
  read: []
  write: []

Database Configuration Attributes

The file stores all database configurations in dictionary like structure. The keys of the highest level are unique database ids. Each database configuration has several attributes. Below you can find all attributes explained and some examples.

alternate_key

Data Discovery reflects only tables with primary keys. When a primary key is not defined it may be manually added to config under in alternate_key dictionary.

Since database views typically don't have primary key, this feature allows to them to SlashDB.

The alternate_key is a dictionary. The keys in the dictionary are names of tables, values are lists of column names that can be treated as artificial primary key.

Note - Value for alternate_key must start with a letter or an underscore, and may contain only letters, numbers or symbols: @, _, -, and ~.

Example:

Chinook:
  alternate_key:
    Table1: [col1, col2]
    ViewCustomer: [CustomerId]

autoconnect

This option accept true or false value that define if the API should connect automatically to the server when SlashDB starts.

Example:

To load (reflect) Chinook database automatically on start of the service.

Chinook:
  autoconnect: true

autoload

When true tells SlashDB to make a full reflect on database. This will discover tables, columns, primary and foreign keys and automatically add them to the API.

When false SlashDB will be looking for the model installed as plugin with the same name. This feature is used for advanced use cases with custom built ORM model.

Example:

SlashDB will automatically discover database model

Chinook:
  autoload: true

SlashDB will be looking for a custom model installed as a plugin.

Chinook:
  autoload: false

autoload_user

It's a dictionary that stores login and password to the database to be used for reflect purposes. It must be used together with autoload = true

Example:

Chinook:
  autoload: true
  autoload_user: {dbuser: 'john', dbpass: 'secretpasssowrd'}

cache_dbmodel

cache_dbmodel (Referred to as "Cache Schema" in the database configs/wizard) controls if a database's schema will be saved for the next time the database is connected. If it will be saved, then it will be saved inside the caches_folder.

When cache_dbmodel is set to true, it means that when connecting to a database there will be a popup asking if the existing cache should be used or not if one is present. If the existing cache is used, then any changes that may have occurred to the database's structure will not be recognized, however the database will load significantly quicker if it is large enough. It is recommended to keep schema caching enabled, and to refresh the schema via the "No" prompt or by going to the /uncache-schema/{database id}.json api endpoint.

Note - Uncaching the schema only indirectly refreshes the schema. Afterward the database must be connected in order to generate the schema that will be stored.

Example

Chinook:
  cache_dbmodel: true

connection

Connection stores value of the connection string override. This is an advanced feature which allows to create custom connection string with extra parameters.
For more details please contact techsupport@slashdb.com

Example

Chinook:
  connection: database.my-domain:1433/Chinook

creator

Creator options is used for administrative purposes. User which adds a new database config is assigned as a creator and is automatically granted permissions to read, modify and connect the database.

Note - Value for creator must start with a letter or an underscore, and may contain only letters, numbers or symbols: @, _, -, and ~.

Example

User admin has added the database to SlashDB.

Chinook:
  creator: admin

db_dialect

This parameter is closely related to database type (parameter db_type). It allows to override the default dialect.

This is advanced features, for more information please contact techsupport@slashdb.com.

db_driver

This parameters allows to override the default DBAPI should be used to connect to the the database server.

This is advanced feature, for more information please contact techsupport@slashdb.com.

db_encoding

This option tells SlashDB what is the charset encoding in the database. This information in required to correctly process diacritic characters. Allowed values are:
utf-16-be utf-16-le utf-7 utf-8 ascii
big5 big5hkscs cp037 cp424 cp437
cp500 cp737 cp775 cp850 cp852
cp855 cp856 cp857 cp860 cp861
cp862 cp863 cp864 cp865 cp866
cp869 cp874 cp875 cp932 cp949
cp950 cp1006 cp1026 cp1140 cp1250
cp1251 cp1252 cp1253 cp1254 cp1255
cp1256 cp1257 cp1258 ujis eucjis2004
eucjisx0213 euckr gb2312 gbk gb18030-2000
hz-gb-2312 iso-2022-jp iso-2022-jp-1 iso-2022-jp-2 iso-2022-jp-2004
iso-2022-jp-3 iso-2022-jp-ext iso-2022-kr iso-8859-1 iso-8859-2
iso-8859-3 iso-8859-4 iso-8859-5 iso-8859-6 iso-8859-7
iso-8859-8 iso-8859-9 iso-8859-10 iso-8859-13 iso-8859-14
iso-8859-15 cp1361 koi8_r koi8_u mac_cyrillic
mac_greek mac_iceland mac_latin2 mac_roman mac_turkish
ptcp154 shiftjis shiftjis2004 shiftjisx0213 utf-16

Example:

The character set used by database is UTF-8.

Chinook:
  db_encoding: 'utf-8'

db_host

Network host name or IP address where database server is located.

Example:

yaml Chinook: db_host: 10.1.2.29

db_id

Unique database id that is used in other configuration files: users.cfg and querydefs.cfg. Also the id becomes an API endpoint in Data Discovery e.g. /db/Chinook.

Note - Value for db_id must start with a letter or an underscore, and may contain only letters, numbers or symbols: @, _, -, and ~.

Example:

Chinook:
  db_id: Chinook

db_name

For all database types except Oracle the value is the database name in the database server. For Oracle the parameter stores the SID.

Example:

The Microsoft SQL Server has a sample database named Northwind, that's why db_name is "Northwind".

Chinook:
  db_host: 10.1.2.29
  db_port: 1433
  db_name: Northwind
  ...

db_port

Is the port on the host that accept connections to the database server.

db_schema

This option is used if SlashDB should connect to certain schema in the database. That's applicable for databases that support use schema like MS SQL, PostgreSQL, Oracle, DB2. If not provided a default user schema is used.

Example:

Use database schema dbo when reflecting model.

Chinook:
  db_schema: dbo

db_type

Defines what sort of a database server SlashDB is connecting.

Allowed values are:

  • sqlite for SQLite
  • mssql for MS SQL Server
  • oracle for Oracle
  • db2 for IBM DB2
  • mysql for MySQL or MariaDB
  • postgresql for PostgreSQL

Example:

The database Chinook is SQLite

Chinook:
  db_type: sqlite

desc

Non-obligatory additional description of the purpose of the API or any other useful notes.

Example:

Chinook:
  desc: A sample music store database

excluded_columns

This option allows to exclude certain columns from the API but columns cannot be part on relationship constraint.

When primary key column is excluded the table won't be added to the API. This can be used as a feature to hide some tables.

Option excluded_columns expects a dictionary object where key is table name and value is list of column names to be excluded form API.

Example: To exclude a regular columns Company, Address and City from table Customer and BillingAddress from table Invoice

Chinook:
  excluded_columns:
    Customer: [Company, Address, City]
    Invoice: [BillingAddress]

To exclude a table from API exclude the primary key.

Chinook:
  excluded_columns:
    InvoiceLine: [InvoiceLineId]

foreign_keys

Deprecated. Should be empty dictionary or null.

Chinook:
  foreign_keys: null

read

List of users allow to view the database config. Account admin is always privileged and doesn't have to listed.

Note - all user's names must start with a letter or underscore, and may only contain letters, numbers, and the symbols @, _, -, and ~.

Example:

Only john, mike and admin will be able view the config details.

Chinook:
  read: [john, mike]

write

List of users allowed to modify the database config.Account admin is always privileged and doesn't have to listed.

Note - all user's names must start with a letter or underscore, and may only contain letters, numbers, and the symbols @, _, -, and ~.

Example:

Only john, mike and admin will be able view the config details.

Chinook:
  read: [john, mike]

execute

It's a list of SlashDB users allowed to control state of that particular API - connect or disconnect. Account admin is always privileged and doesn't have to listed.

Note - all user's names must start with a letter or underscore, and may only contain letters, numbers, and the symbols @, _, -, and ~.

Example:

Only john, mike and admin will be able change the state of the connection.

Chinook:
  execute: [john, mike]