Build an admin panel for a MySQL database in PlanetScale

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!

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.

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.

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.

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.

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).

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.

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

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".

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.

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!

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.

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.

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).

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.

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.

Run the query as before using the Play button.

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

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.

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.

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.

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.

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.

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.

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

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!

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

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!