Connecting client applications
Learn how to connect your applications to utilize caching
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 OptimizerProxy
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 Server1433
, MongoDB27017
- PostgreSQL:
-
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
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:
-
In the DB Optimizer console, navigate to the Databases table.
-
Click on the name of the logical database you want to connect to.
- In the drawer that opens on the right, locate the Connection string field.
- Copy the alphanumeric string in the field.
- 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 named8255a89a-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 theapplication_name
, see Option 2 above.
Read more about PostgreSQL connection strings from the PostgreSQL documentation here and more about the application_name
parameter here.
Updated 9 days ago