Overview

Connecting client applications to DB Optimizer simply requires changes to the connection string. Specifically, these are:

• hostname - update the hostname or IP to connect to the DB Optimizer Proxy component, rather than directly to the database.

• port - update the port property to use the database engine of choice:

  • PostgreSQL: 5432, MySQL: 3306, MariaDB: 3307, SQL Server 1433, MongoDB 27017

• cache_id - provide a unique identifier for the cache being connected to. See below for further details.

Cache Identifier

In order to connect to any database client, a cache identifier must be specified as part of the connection string. This tells DB Optimizer which upstream database to connect to. The method of specifying this as part of the database connection credentials is database-specific. See below for details on each database.

Postgres

For PostgreSQL databases, DB Optimizer supports two options for passing the cache identifier as part of the connection string. If you can access the Postgres connection URI (typically an environment variable for web applications, microservices, etc.), an additional application_name parameter is appended to the connection string (see Option 1 below).

Sometimes, it's impossible to modify the connection URI, such as in desktop tools where a UI dictates the available parameters. In these situations, a specific database username can be created and utilized by DB Optimizer (see Option 2 below).

Option 1 - Append application_name

To use this method, you'll need to append an application_name parameter containing the DB Optimizer cache identifier to your connection string. Here's how to do it:

1. In the DB Optimizer console, navigate to the Databases table.

2. Click on the name of the logical database you want to connect to.

3. In the drawer that opens on the right, locate the Connection string field.

4. Copy the alphanumeric string in the field.
5. Append the application_name parameter with this cache ID to your connection string. The format should look like this:

postgres://[user]:[password]@[hostname]:[port]/[database-name]?application_name=[cache_id_you_copied]

For example, if your cache ID is 12738h218d91h9-2h3, your connection string might look like:

postgres://dbuser:[email protected]:5432/mydb?application_name=12738h218d91h9-2h3

This method works well for web applications, microservices, and other environments where you can easily modify the connection string.

📘

Note

If you're using a desktop tool or another environment where you can't modify the connection URI, see Option 2 for an alternative method.

Read more about PostgreSQL connection strings in the PostgreSQL documentation and about the application_name parameter here.

Option 2 - Database Username

DB Optimizer can also use a cache identifier specified as the database username. In this case, a database user must be created with a username identical to the cache ID provided by DB Optimizer.

For example, if the cache ID is 8255a89a-4365-4e64-8e78-38db272d8fdd:

CREATE USER "8255a89a-4365-4e64-8e78-38db272d8fdd" WITH ENCRYPTED PASSWORD 'foo';

GRANT ALL PRIVILEGES ON DATABASE employees TO "8255a89a-4365-4e64-8e78-38db272d8fdd";

🚧

Database Permissions

When creating a database user and granting privileges, be sure to only grant the minimum needed for the purpose.

📘

Database Name

The database name can optionally be appended by a hyphen and any other string. This can be useful for identifying DB Optimizer specific database users.

For example, if the cache ID was 8255a89a-4365-4e64-8e78-38db272d8fdd, the database user could be named 8255a89a-4365-4e64-8e78-38db272d8fdd-fooBar

Testing PostgreSQL Connectivity

CLI Example

psql "host=[Proxy host or IP] \
sslmode=require \
port=9010 \
user='dbuser' \
dbname='dbname' \
application_name='CACHE_ID'"

👍

Desktop Clients

If you would like to test connectivity using a a desktop client application, we recommend JetBrains DataGrip as it supports setting the application_name property. If you are unable to set the application_name , see Option 2 above.

Read more about PostgreSQL connection strings from the PostgreSQL documentation here and more about the application_name parameter here.