Database connections as workspace resources

This article explains how to manage database connections on workspace level.

By creating a database connection, you add it to workspace resources without attaching to any particular notebook. These can database connections can then be attached to any notebook from this workspace.

Use the New database connection dialog to add a database connection to a workspace.

Add a database connection to workspace resources

Open the New database connection dialog

On the Home page, select the workspace to which you want to add a database connection.
From the left-hand menu, select Databases to open the list of all workspace database connections.
In the Databases view, click the New connection in the upper right corner.

The New database connection dialog offers three options:

Use database-specific interface

You select a database type and manually set up the parameters required for this particular type. This procedure is described here.

Use a pre-built connection URL

Create a connection based on a pre-built connection URL. Switch to the Enter URL tab, provide the URL in the text field, and click Next. This will open the connection dialog for the respective database type with all the fields pre-filled.

Import XML

Create a connection using data provided in XML format.

Copy the database parameters you want to use to the clipboard.
Switch to the Import XML.
Click the Import from clipboard button. This will process the provided data and open the connection dialog for the respective database type with all the fields pre-filled.

Configure a connection using database-specific interface

This procedure describes the general workflow for all supported database types. For specific procedures, refer to the topics under this article.

In the New database connection dialog, select a database type.
In the New [database_type_name] connection dialog, fill in the following fields:
- On the General tab:
  1. Select the connection type.
    The actual list of options will depend on the selected database type.
    - Default: to connect by specifying the Host, Port, and Database.
    - IAM cluster/region: to connect by using Database, Region, and Cluster.
    - URL only: to connect by providing the URL of a pre-built connection.
      Use this method to pass additional parameters. For example, add &SSL=true to the URL string to enable SSL.
    - SID: to connect by using a unique name of an Oracle instance.
    - Service name: to connect by using an alias to an Oracle instance.
  2. Select whether to use authentication.
    For some database types, you can select their specific authentication methods. For example, for Amazon Redshift or Athena, you can select AWS Profile (more details here). For Snowflake, you can select Authenticator (more details here) or Snowflake OAuth with stored tokens, a method described here.
  3. Provide your user credentials if required by the connection type.
  4. Specify the database if required by the connection type.
  5. Select whether to use the IAM database authorization (where applicable).
- On the SSH tab, enable SSH and configure an SSH connection. See more details in this procedure.
- (Optional) Configure the database introspection scope on the Schemas tab:
  1. Click Refresh to get the list of the database schemas.
  2. Select or deselect schemas for introspection.
Click the Test connection button at the bottom of the dialog.
Once successfully tested, click the Create connection button.

Enable SSH tunneling

Datalore can create an SSH tunnel based on the parameters you configure in the New [database_type] connection dialog.

Switch to the SSH tab of the New [database_type] connection dialog.
Select the SSH checkbox.
Provide your connection details in the Host, Port, and User fields.
Use one of the two authentication types:
- To use password-based authentication, fill in the Password field.
- To use a key pair:
  1. Expand the Authentication type list and select Key pair.
  2. Under Key pair, expand the list and select one of the key pairs saved in your account settings.

Manage additional parameters for your connection

Use the Advanced tab of the database connection dialog to manage additional parameters. For example, you can manage your JDBC driver custom options here.

Switch to the Advanced tab of the dialog.
Click Add key-value pair to add a parameter and its value.
Repeat the previous step for each parameter required for your connection.
Click Save and close to finish your work in the dialog or switch to another tab to continue creating the connection.

Manage database connections as workspace resources

The Databases view allows you to manage all listed database connections.

To rename a database connection

On the Home page, select the workspace to which you want to add a database connection.
From the left-hand menu, select Databases to open the list of all workspace database connections.
Right-click the connection you want to manage. This will open a popup menu.
Select Rename.
Enter the new name and press Enter.

Edit a database connection

On the Home page, select the workspace to which you want to add a database connection.
From the left-hand menu, select Databases to open the list of all workspace database connections.
Right-click the connection you want to manage. This will open a popup menu.
Select Edit. This will open the Edit database connection dialog.
In the Edit database connection dialog, edit the connection parameters. Find more details about database-specific parameters in the respective articles in this section.

Clone a database connection

On the Home page, select the workspace to which you want to add a database connection.
From the left-hand menu, select Databases to open the list of all workspace database connections.
Right-click the connection you want to manage. This will open a popup menu.
Select one of the two options:
- Clone to this workspace: to clone the connection to the same workspace. It will be added to the same list under a slightly different name.
- Clone to other workspaces: to clone the connection to other workspaces:
  1. In the Clone [database_connection_name] to other workspaces dialog, expand the Workspace list.
  2. Select the workspaces you want to clone the connection to.
  3. Click the Clone button.

Export database connection in XML format

This procedure saves the parameters of a database connection in XML format with all current values and copies them into your clipboard. You can use the copied XML content to create another database connection.

On the Home page, select the workspace to which you want to add a database connection.
From the left-hand menu, select Databases to open the list of all workspace database connections.
Right-click the connection you want to manage. This will open a popup menu.
Select Export to .xml.

The data is copied to your clipboard.

Delete a database connection

This procedure deletes database connections from your Datalore system without moving it to Trash.

On the Home page, select the workspace to which you want to add a database connection.
From the left-hand menu, select Databases to open the list of all workspace database connections.
Right-click the connection you want to manage. This will open a popup menu.
Select Delete.

Sharing database connections

Shared workspace

When you share one of your own workspaces, the database connections are shared with it.

Editors can create and edit database connections.
Editors can create and run SQL cells to access the connected databases.
Viewers can view the connection list and respective database schemas

Home workspace notebook

When you share a Home workspace notebook, the attached database connections are shared with it.

Editors can create and execute SQL cells to access connected databases.
Viewers can only view the currently attached database connections.

Last modified: 28 November 2023