Connecting to PolyScale with JetBrains DataGrip

PolyScale is a plug-and-play global database cache that reduces global query latency for databases. Using PolyScale, database reads can be distributed and cached, seamlessly scaling your current database without writing code or altering query or transactional semantics.

When you create your first PolyScale cache, you probably want to connect to it right away to check it out, play with the Observability features and test the AI caching. In this article we show how to configure your data sources in JetBrains DataGrip to work with your PolyScale cache.

What is DataGrip?

I work with a lot of different databases, and a lot of different database engines: MySQL, PostgreSQL, MS SQL Server… the list goes on. Some of these engines come with GUI tools; some of those tools are good, others maybe not so much. But chopping and changing between those different tools, or having two or three of them running at once, gets a bit much. So I use DataGrip from JetBrains, the same company that makes IntelliJ, WebStorm, PyCharm and a dozen other great IDEs. DataGrip is an IDE for all your databases: it supports all the RDBMS, NoSQL and Cloud database engines you can think of.

If you’re using DataGrip to manage your database already, getting it connected to PolyScale can be done in a few clicks. If you’re not using DataGrip, I’ll show you how to connect directly to your database, then switch to connecting via PolyScale.

PolyScale Connection Basics

Every cache in PolyScale has a UUID cache identifier, which is passed with the connection credentials to tell PolyScale which cache and upstream database to connect to. This identifier is passed in as part of the connection credentials in different ways depending on which database engine you are using, I’ll show how to pass it for MySQL and PostgreSQL later in this post.

Connecting Directly to MySQL With DataGrip

When you open DataGrip, the left-hand side is the Database Explorer. To add a new Data Source, click the + icon in the toolbar, and choose Data Source and then select MySQL.

Then you just need to enter the database host and port, and your user credentials, and hit Test Connection to make sure you’ve got everything right.

Creating the PolyScale MySQL Cache

My demo MySQL server is running in the Microsoft Azure West US 2 data-center, and I’m in south-east England, so this is a great use case for PolyScale. I just log into the dashboard, click the New Cache button, and enter the details for my Azure database:

When I click the Create button, I get the Connection Details popup with the settings, including the template for the username, which includes the Cache ID; for more information on how that works, check out the

Getting Connected page in the docs.

Creating the PolyScale MySQL Data Source in DataGrip

For MySQL connections, the cache identifier is used as a prefix on the MySQL username. So if my cache identifier is 26cdf745-4c2d-4664-920d-6e4a5bc6e0c5 and my username is mark, I would use 26cdf745-4c2d-4664-920d-6e4a5bc6e0c5-mark as the username in my DataGrip connection.

Back in DataGrip, I could edit my existing data source, but I’m going to clone it so I can still connect directly to the original server if necessary. I just right-click the data source in Database Explorer and choose Duplicate from the context menu. That brings up the properties window again, and I can just edit the name, and the Host and User fields with the connection settings from PolyScale. Note that I also have to re-enter the Password as it’s not copied over when you duplicate a Data Source.

I can now use DataGrip to run queries against the MySQL database through PolyScale, see those queries in the Observability tab on my Cache dashboard, and experiment with that AI caching algorithm, or perhaps with manually specifying cache policies and TTL settings. Queries are cached automatically at the closest Point of Presence (PoP) to my location. PolyScale will execute cached queries in ~1ms plus the additional network latency back and forth to my DataGrip client, depending on my specific location. I’m still connecting over the public internet however so I won’t be getting the same millisecond response times I could expect from an app running right in or close to the data-center.

Connecting Directly to PostgreSQL With DataGrip

OK, now let’s see how this works with PostgreSQL. The way of specifying the cache identifier for PolyScale is a little different to MySQL, but still easy to set up with DataGrip.

I can connect to my PostgreSQL server by clicking the + icon in the Database Explorer toolbar and choosing Data Source > PostgreSQL, and setting the Host, User and Password fields.

Creating the PolyScale PostgreSQL Data Source in Datagrip

Again, I just need to click New Cache in my PolyScale workspace and enter the hostname and port number for my Azure PostgreSQL server:

And when I click Create I get the connection properties for my new PostgreSQL cache. Notice that here, instead of a username, I get an application_name property; I’ll need that when I connect to this cache from DataGrip.

Creating the PolyScale PostgreSQL Data Source in Datagrip

For PostgreSQL, the cache identifier is passed as the application_name property in the connection string.

Just as before, I’m going to Duplicate my direct connection Data Source and modify it to connect to PolyScale.

On the General tab I just enter a new Name and change the Host to point to psedge.global. For PostgreSQL connections, the username doesn’t change.

To connect to my specific cache, I need to enter that application_name value in the Advanced tab, which lets you override all the custom connection properties for DataGrip’s underlying Java JDBC driver. I set the ApplicationName property to my cache identifier; I also need to set assumeMinServerVersion to 13. (This is due to a wrinkle in the JDBC driver: if the expected server version is less than 9 (or not set) then it doesn’t support the ApplicationName property. Setting it to 10 will work, but it makes sense to use the major version of my actual server.)

Connecting to PostgreSQL From Other GUI Apps

Unlike DataGrip, most database GUI apps don’t allow you to set the application_name property when setting up a connection to PostgreSQL. For those apps, you can use an alternative connection method: create an actual user in the database using the cache identifier as the username, and then use that username when connecting to PolyScale. Learn more about the options for connection to PostgreSQL in this page in our docs.

Summary

In this post I showed you how to connect to MySQL and PostgreSQL caches in PolyScale from JetBrains DataGrip, which is a fantastic GUI for managing and exploring all your databases, whether connected directly or through your PolyScale caches.