Connect a database

Overview

Flow can connect to databases to fetch data when running a query.

This topic explains how to create database connections, both through the user interface, and through Flow’s config files.

Use the UI

The UI allows you to create new connections to databases, as well as see a list of the connections already configured.

Currently, only creating new connections in the UI is supported. To edit and remove existing connections, modify the config file.

Create a new connection

There are two ways to add a new database connection in Flow:

Create a connection in the Connection Manager

The connection manager is the place that allows you to create and view connections.

Click on the Connection Manager icon in the left-hand navigation menu
Specify a connection name
Select the database type from the list of possible connections
Based on the database you select, a list of required parameters is shown
Fill out the form
Click Test connection
If the connection test is successful, click Create

connection manager

The connection is created, and written to the config file.

Create a connection when importing a new data source

You can also create a connection in-line, when specifying a new datasource.

From the home page, click Add a data source.
Alternatively, click Schema Explorer in the left-hand navigation menu, then click Add new
For the schema type to import, select Database table

new data source flow

Click the connection name drop-down
Select Add new connection… A pop-up form appears, allowing you to create a connection
Specify a connection name
Select the database type from the list of possible connections. Based on the database you select, a list of required parameters is shown
Fill out the form
Click Test connection
If the connection test is successful, click Create

After the connection has been created, the popup form closes and your new connection is populated into the schema form.

create connection popup

Required permissions

To view, create or edit database connections through the UI, users must have the following permissions granted.

Activity Required permission

Activity	Required permission
View the list of database connections	`VIEW_CONNECTIONS`
Create or modify a database connection	`EDIT_CONNECTIONS`

View the list of database connections

VIEW_CONNECTIONS

Create or modify a database connection

EDIT_CONNECTIONS

For more information about role-based security, see the topic on authorization.

Use a configuration file

All the connections configured for Flow are stored in a config file, including any that you configure through the UI.

By default, this file is called connections.conf, and is located in the conf/ directory under where you launch Flow from. If this file does not exist when Flow is launched, it’s created the first time a connection is created via the UI.

You can specify the name of the configuration file when launching Flow, by setting the parameter Flow.connections.config-file. The same configuration file is used for all types of connections, not just databases, including Kafka connections etc.

Define a database connection

jdbc { # The root element for database connections
   another-connection { # Defines a connection called "another-connection"
      connectionName = another-connection # The name of the connection.  Must match the key used above.
      jdbcDriver = POSTGRES # Defines the driver to use.  See below for the possible options
      connectionParameters { ## A list of connection parameters.  The actual values here are defined by the driver selected.
         database = transactions # The name of the database
         host = our-db-server # The host of the database
         password = super-secret # The password
         port = "2003" # The port
         username = jack # The username to connect with
      }
   }
}

For the full specification, including supported database drivers and their connection details, see Configure connections

Pass sensitive data

It may not always be desirable to specify sensitive connection information directly in the config file, especially if these are being checked into source control.

Environment variables can be used anywhere in the config file, following the HOCON standards.

For example:

jdbc {
   another-connection {
      connectionName = another-connection
      jdbcDriver = POSTGRES # Defines the driver to use.  See below for the possible options
      connectionParameters {
         # .. other params omitted for brevity ..
         password = ${postgres_password} # Reads the environment variable "postgres_password"
      }
   }
}