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 ,
Database ID,
Type,
Description,
Connection status,
button that opens a modal with details about connection
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
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 the 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. 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 to database

Use on Data Discovery /db or Database Definitions /dbdef page to change the Connection status of the database. \ If you have Cache Schema

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 .

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

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

A simple configuration for single SQLite database called Chinook looks like below

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

It's easier to modify database configuration using Configuration GUI. Some features are hidden and require modifying databases.cfg manually.

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.

Example:

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

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 location of the database.

For SQLite it's path to file e.g. /home/john/database.sqlite.

For MS SQL, DB2, MySQL, MariDB, PostgreSQL it's host, port and database e.g. 192.168.1.1:1433/Chinook.

For Oracle it's host, port and SID e.g. 192.168.1.1:1521/xe

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.

Example

User admin has added the database to SlashDB.

Chinook:
  creator: admin

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_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.

Example:

Chinook:
  db_id: Chinook

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

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

read

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

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.

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.

Example:

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

Chinook:
  execute: [john, mike]