Connecting to PolyScale with JetBrains DataGrip
Mark Rendle
Apr 27, 2022PolyScale 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
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.