PlanetScale is an advanced MySQL-compatible database service powered by Vitess, offering almost unlimited scale for your application's database. Retool connects to almost any data source you can imagine, including MySQL databases. In this tutorial, we'll show you how to connect PlanetScale and Retool together, enabling you to quickly build internal tools and dashboards on your data in PlanetScale. Let's get started!

Setting up a PlanetScale database

Your first step will be signing up for a free PlanetScale account. Once you sign up, you will be prompted to create your first database. You can skip the onboarding wizard for now!

Create your first PlanetScale database!

After electing to create a new database, you can name it whatever you would like. In this example, we're going to create a database for baseball teams and players, so we'll call our database baseball_data.

Create a new database in the default AWS region in Northern Virginia. Have you ever stopped to think about how much of the world's data is randomly in Northern Virginia?

Click "Create database", and in a few moments, you will be the proud owner of a shiny new MySQL-compatible database hosted by PlanetScale. Once your database has been created, click the "Connect" button.

Smash that "Connect" button to see how to connect to your database.

If this is your first time connecting to the database, you will be prompted to create a password for the database. Be sure to note these values in a text document or other secure location on your computer, because you will not be able to see them again after they've been initially created.

These are the connection details you will need to set up your database resource in Retool.

Once you have made a note of these values, you're ready to connect to your new database from Retool! There's a lot more useful stuff you can do in the PlanetScale console, but we'll be using the Retool interface to complete the rest of this tutorial.

Connecting to your PlanetScale database in Retool

If you haven't already, sign up for a free Retool account. Once you do, head over to the Resources tab in the Retool console, and create a new MySQL resource (this will be our PlanetScale database).

Create a new MySQL resource in the Retool console.

You can start by naming your resource (it can be anything you like), and then filling in the host and database name from the database you created in PlanetScale.

Configure a resource name, host, and database name first.

Further down the page, you will need to configure your database username and password.

Your database username and password come next!

Now comes a slightly tricky part. Connecting securely to a database on PlanetScale requires a secure connection, which means you need to check the box below the password field to "Use SSL/TLS".

Enable SSL/TLS before attempting to connect to PlanetScale.

If you're interested in learning more about secure connections to your database in PlanetScale, you can read this tutorial from PlanetScale support. But as a practical matter, you will need to upload the CA Certificate used by PlanetScale (provided by Let's Encrypt) to Retool, so the two systems can securely communicate with one another. Save the following text to a file called planetscale.pem on your computer (include the entire text below, including the "BEGIN" and "END" bits).


After doing this, upload the file to Retool in the configuration UI where it says "CA Certificate". You can leave the "Client Key" and "Client Certificate" fields blank.

Upload the .pem file to Retool. Can't be too careful these days.

After uploading the file, click the "Test connection" button at the top of the configuration screen. If all goes according to plan, you'll have a successful connection to your PlanetScale database!

Connecting with others is an essential part of the human experience - maybe that's why a successful connection test feels so good.

After establishing a successful connection, click the "Create resource" button to save your configuration. This will enable you to use this database across all your Retool apps and workflows. After creating your resource, it may be tempting to immediately create an app - but for now, head back to the Resources screen, and then navigate to the Query Library. We have a bit of work to do there first.

I know the "Create an app" button looks tempting, but hold your horses! We have a little more work to do first.

Seeding our database using the Query Library

Retool's Query Library lets you set up shared queries that you can use across all your applications. But it's also a great way to execute arbitrary SQL queries against your database if you need a quick-and-dirty console for doing so. It just so happens we are in the market for such a console – our PlanetScale database doesn't have any tables or records in it yet, so we need to add a couple first. Create a new query, and then select the database we just created as the Resource in the dropdown menu.

Let's use the query library to bootstrap our database.

In the text field below, paste in the following SQL query. It will create two tables, called teams and players. This will set up a basic schema for our database with some relational data (a one-to-many relationship between teams and players).

  name varchar(255) NOT NULL

CREATE TABLE players (
  name varchar(255) NOT NULL,
  image_url varchar(255),
  team_id INT,
  KEY team_id_idx (team_id)

You might notice that this simple schema does not have foreign key constraints, as they are not supported in PlanetScale. Run the SQL statement now, using the "Play" button in the top right corner of the UI.

Run the SQL above to create the database structures for our sample app.

Staying in the Query Library, let's create a new query against our database. This time, we'll insert some sample data we can use in a Retool app.

  ('Minnesota Twins'),
  ('Detroit Tigers'),
  ('Chicago White Sox');
INSERT INTO players (name, team_id) VALUES
  ('Torii Hunter', 1),
  ('Joe Mauer', 1),
  ('Justin Verlander', 2),
  ('Alan Trammell', 2),
  ('Frank Thomas', 3),
  ('Paul Konerko', 3);

Run the query as before using the Play button.

Put them in coach, they're ready to play!

Now, we have a database schema set up and some sample data we can use in a Retool application. Let's create one now!

Using MySQL data from PlanetScale in a Retool user interface

With our database connected and configured, we can finally create a new Retool application that uses the data! Create a new app from the console UI, naming it anything you would like. After creating the app, you should be greeted with a relatively blank canvas like this one. If your PlanetScale database was the last resource you created, it will probably even helpfully include a query you can customize, as we will want to do next.

A blank app canvas, except for a query that is awaiting your input.

In Retool, queries contain logic that connects to your data or takes action in your UI. Let's start with a basic one - a SQL query that will get a list of all the teams in our database. Rename query1 to list_teams and paste in the following SQL.

SELECT * from teams;

Click the "Save & Run" button, and you should see that data coming back from your database. You should even be able to see the data coming back in a preview below your query.

Great teamwork, right there.

Now let's create some UI by dragging in a couple components from the righthand side of the page - a text component and a select component.

Drag and drop the two components as shown above

You can click on the label of the selected component to customize it. Next, we need to make the select component not show default hard-coded data, but rather data from our database. In the inspector UI for the select component, switch it from "Manual" to "Mapped" mode, and select our list_teams query as the data source. While you're at it, rename the select component to teamSelect and make the default value 1 . In the configuration below, the name column from the teams table in our database is being used as the label, and the value is the team's primary key ID.

After doing this, your UI should look something like the below.

Now we're getting somewhere - the AL Central Division, to be precise.

Next, let's create a second query that fetches a list of players for the currently selected team. This query will need to make use of Retool's double curly brace syntax to dynamically insert the database ID of the currently selected team, as shown below. Be sure to rename the query to list_players or something similarly useful.

A successful "list_players" query run.

The SQL for this query is below, for your copy/pasting pleasure. It assumes that you renamed your select component to teamSelect.

SELECT * from players where team_id = {{ teamSelect.value }};

Next, let's drag in a table to display the players for the current team. The query you just created should be helpfully pre-selected as the data source behind the table. You can resize and configure columns as you prefer!

Torii Hunter and Joe Mauer are indeed excellent at setting the table.

Go ahead and play around with your select component - this list of players should update dynamically!

More to explore

In this tutorial, we barely scratched the surface of what's possible when you use Retool and PlanetScale together. But now that you know how to connect these two systems, you're well on your way to building powerful internal tools. If you have any questions, or would just like to say hi, please be sure to visit our community forums and Discord server. We'd love to hear about what you're building!