Connect to MySQL
You can connect a MySQL database and make it available as a resource in Retool. Once complete, you can write queries that interact with the database.
Requirements
All users for Retool organizations on Free or Team plans have global Edit permissions and can add, edit, and remove resources. If your organization manages user permissions for resources, you must be a member of a group with Edit all permissions.
To create a MySQL resource, you need:
- Authentication credentials for the MySQL database. This can be either a username and password, or AWS IAM authentication (for AWS-hosted MySQL databases).
- Connection details for the MySQL database host. This includes the hostname, port, and database name.
- Firewall rules that allow Retool to access your MySQL host.
MySQL authentication
Retool authenticates with MySQL databases using either a username and password, or with AWS IAM credentials. The authentication method you use determines the configuration settings required.
Username and password
You can provide a username and password for Retool to authenticate with your MySQL host. It is strongly recommended that you create a username and password for this purpose and not reuse an existing user. This provides more granular control over MySQL users and allows you to control the scope of permissions.
AWS IAM
For AWS-hosted MySQL databases, Retool supports IAM authentication that enables authentication using access keys. Use the AWS IAM console to create and manage AWS IAM keys.
SSL connections
Both authentication methods use SSL by default. If required by your database host, you can provide CA certificate, client key, and client certificate credentials that allow Retool to connect using SSL.
Retool automatically configures the necessary SSL credentials and certificates for AWS-hosted databases.
Allow Retool to access your MySQL host
If your MySQL database is behind a firewall, you must allow access from Retool's IP addresses. Add the IP addresses to your firewall's Allowlist before you create the resource.
1. Create a MySQL resource
Retool can connect to almost any API or database, and has built-in integrations for popular data sources. Sign in to your Retool organization and create a new MySQL resource from the Resources tab.
2. Configure the resource
Retool can automatically populate values from a MySQL connection string.
Specify a name and location for the BigQuery resource. Retool displays the resource name and type in query editors to help users identify them.
General
| Setting | Description |
|---|---|
| Host | The URL or IP address. |
| Port | The port number. |
| Database name | The name of the database. |
| Connection options | Additional options for the MySQL connection. |
Authentication
Retool can connect to a MySQL database using either user and password, or AWS IAM authentication.
User and password authentication
Select User and Password as the authentication method, then provide the following information.
| Setting | Description |
|---|---|
| Database username | The username. |
| Database password | The password. |
AWS IAM authentication
Select AWS IAM Authentication as the authentication method, then provide the following information.
| Setting | Description |
|---|---|
| Database username | The username. |
| AWS Region | The AWS region in which the database is located. |
| Role to assume (ARN) | The role for the IAM user to assume. |
| AWS Access Key ID | The access key for the IAM user. |
| AWS Secret Key | The secret key for the IAM user. |
SSL connections
Retool strongly recommends using SSL and not to skip TLS certificate validation. This ensures the connection is secure and prevents attackers from using invalid server certificates to gain access to your data.
Retool supports SSL connections for both authentication methods using custom certificate credentials. If you're using username and password authentication, check Connect using SSL to display additional fields for this information. SSL is required for AWS IAM authentication and cannot be disabled.
Provide the following information to configure SSL:
| Setting | Description |
|---|---|
| CA Cert | The CA certificate. |
| Client Key | The client key. |
| Client Cert | The client certificate. |
Advanced options
Retool organization administrators can configure the following settings for MySQL resources.
| Setting | Description |
|---|---|
| Use dynamic database names | Allow the database name to be overridden by a dynamically generated value. This allows using Retool with a database that has been sharded into several different databases. |
| Use dynamic database host | Enable this to allow the Database Host to be overridden by a dynamically generated value. This allows using Retool with several different databases. |
| Disable converting queries to prepared statements | This disables SQL injection protection to allow dynamically generated SQL using JavaScript. |
| Show write GUI mode only | This restricts queries to GUI mode only and prevents users from writing raw SQL queries. |
| Convert MySQL data strings to JavaScript | Convert MySQL date strings into JavaScript Date objects. |
| Enable SSH tunnel | Enable SSH tunneling through bastion host. |
Click Test Connection to verify that Retool can connect to the MySQL database. If the test fails, check the resource settings and try again. Testing a connection only checks whether Retool can successfully connect to the resource. It cannot check whether the provided credentials have sufficient privileges or can perform every supported action.
3. Save the resource
Click Create resource to complete the setup. You can then click either Create app to immediately start building a Retool app or Back to resources to return to the list of resources.
Wrap up
Your MySQL resource is now ready to use. To interact with MySQL data, select the resource in the query editor. You can read data using SQL queries, write data using GUI queries (if enabled), and use the schema browser to search tables or columns.