If you haven't heard about it yet, Retool.com is a low-code solution to create internal tools at your company. Their slogan is:

Stop wrestling with UI libraries, hacking together data sources, and figuring out access controls. Start shipping apps that move your business forward.

Low-code and no-code solutions are exciting but scary. They promise easy to create UI, make it easy to connect to your production databases, and easy to screw things up unintentionally. If I were connecting Retool to my production database, I would be concerned about making irreversible changes.

Luckily, we have Dolt which is a version controlled database. It's built from the ground up to enable existing apps to be version controlled. Primitives like commits, branches, and pull requests are built into Dolt. If we use it as a backing store for our Retool app, then we will get those primitives in our app automatically. We will be able to make changes on Retool, then verify and preview the changes in Dolt.

Let's try it out.

The use case for Retool and Hosted Dolt

Imagine you have feature flags for your customers. Enabling the feature flag shows a feature to that customer, disabling it hides the feature. Your CTO says that the customer support team needs to toggle these features now. You can't give them direct access to the production database so you need to build some app. You could build your own from scratch, but then you have to worry about deploying it, authenticating the support team users, connecting to your database, creating CI workflows, etc. etc. Instead, you decide to use Retool. Retool will allow you to quickly host, authenticate your support team, and connect to your database. It's almost too easy.

You also want to enable a review workflow for any changes made by the support team. You want the support team to be able to stage changes somewhere, then open a PR that a teammate can review before making it live. Building this functionality would be cumbersome. You might have to change the schema of your existing production app to support it. Instead, you opt to use a Dolt database which has these features built in.

We'll call this app the Feature Flag Editor. Let's create it in Retool and back it with a Hosted Dolt database.

Creating the Hosted Dolt database

The first step is to create the hosted Dolt database. For this tutorial, we'll host our Dolt database in the cloud using Hosted Dolt, but you can also self-host the dolt database.

Navigate to hosted.doltdb.com and create an account. Click on the deployments tab, then Create Deployment. Add a deployment name, change Instance Type to t2.medium and Storage to Trial 50GB EBS. This configuration will cost about $50 / month. Finally hit, Create Deployment. Here is my configuration:

Once created, wait for the database to be created. Then click on the database in the deployments tab and note down the connection information, you'll need it when we connect Retool to this database.

Next, let's connect to the database and create our schema.

Copy the command under the Connect to Server header from above, use it to open a MySQL shell to the database. Then run the following queries to create the schema for our app:

CREATE database retool_db;
use retool_db;

CREATE TABLE customers (
  id int NOT NULL, 
  region varchar(100) NOT NULL, 
  joined_date date NOT NULL, 
  PRIMARY KEY (id)
);

CREATE TABLE customer_feature_flags (
  customer_id int NOT NULL, 
  feature_flag varchar(MAX) NOT NULL, 
  PRIMARY KEY (customer_id, feature_flag)
);

INSERT INTO customers 
VALUES 
  (1, 'US/PA', '2023-01-01'), 
  (2, 'US/CA', '2022-06-01'), 
  (3, 'CA/QC', '2022-06-01'), 
  (4, 'CA/QC', '2022-06-01'), 
  (5, 'US/DE', '2022-04-01');

-- Commit our changes as a Dolt commit. 
-- See https://docs.dolthub.com/concepts/dolt/git/commits
CALL DOLT_COMMIT('-Am', 'Initial data');

-- Create a second branch for our Retool app
-- See https://docs.dolthub.com/concepts/dolt/git/branch
CALL DOLT_BRANCH('retool_dev');

We create a second branch off of the main branch so that we isolate the edits between the Retool app and our production app that runs on the main branch. If we dolt merge, the retool_dev branch into the main branch, that will deploy all of our pending changes.

Connecting Retool to the Dolt database

1. Create dummy MySQL Certs

To connect Retool to the Dolt database, we'll first have to create dummy SQL certs. Dolt does not support Two-Way SSL authentication today and Retool does not support One-Way SSL. (If you need Two-Way SSL, contact us!)

Follow the instructions in the MySQL Docs to create SSL files using the command line. You'll need the ca.pem, client-key.pem, and client-cert.pem for the next step.

2. Create resource in Retool

In Retool, go to the main dashboard and click Resources along the top. Click Create new on the top right, then Resource in the drop down.

Select MySQL under DATABASES when it asks for the Resource Type. Then configure the following options:

• Name: Type "Hosted DB"

• Host: The host field from the connection page on Hosted Dolt

• Port: The port field from the connection page on Hosted Dolt

• Database name: Type retool_db/retool_dev. This will connect Retool to a Revision Database of the retool_dev branch.

• Authentication: Select User and Password

• Database username: The username field from the connection page on Hosted Dolt

• Database password: The password field from the connection page on Hosted Dolt

• Connect using SSL: Enable this checkbox

• CA cert: Copy paste the contents of ca.pem from the previous step.

• Client Key: Copy paste the contents of client-key.pem from the previous step.

• Client Cert: Copy paste the contents of client-cert.pem from the previous step.

• Verification Mode: Select Skip CA Certificate verification.

Under Admin Only, make sure to enable Disable converting queries to prepared statements as well. This will allow you to use as of queries and revision database queries with Retool's templating syntax.

Once all the options have been configured, click Test Connection at the bottom. If it works, then click Create resource.

Now that our Dolt database has been added to Retool, we are ready to create our app.

Create the Retool app

To make this easy for you, we've created a Retool app and exported it. You can download the JSON by clicking here.

To import the app, Go to Retool's home page, then click on Apps along the top, then Create new. In the drop down select From JSON. Then upload the json file you just downloaded.

When the app loads, you should see the following queries highlighted in Red.

To fix them, click on each one and select the hosted database resource you created.

Also, you'll need to update the urls for the Open Hosted Workbench button and the Create PR button to match your dolt database. Replace dolthub/us-jails-2 in the link with $ownerName/$dbName.

This is what the preview of the app should look like:

Let's tour the functionality.

Feature Flag Editor

The feature flag editor allows you to view and modify the feature flags for the customers in the database. First we can select a customer to view their feature flags.

We can also add a new feature flag for a customer.

Notice that when there are changes that are not deployed to the main branch, a create PR button is created.

We can also delete the feature flag.

Notice that how the deployment button disappears when there is no difference between the main branch and the retool_dev branch that the app is operating on. This is the magic of Dolt. It can efficiently tell you what the difference is between your database at two points in time. It's powered by this query:

-- From check_needs_deployment query in Retool
select count(*) > 0 as needs_deployment from dolt_diff_summary('main..retool_dev');

The dolt_diff_summary table function returns a summarized diff between the main branch and the retool_dev branch. If there are no rows, then there are no differences between the databases in main and in retool_dev.

Create a Pull Request

After the customer support rep is satisfied with the changes that they have made, they can click the Create PR button to open the Hosted Workbench. The Hosted Workbench allows you to read the data in the dolt database, create and review PRs, and view diffs.

After clicking the Create PR button, select main as the target branch and retool_dev as the source branch. Then create the PR and click View Diff.

This will show the diff between main and retool_dev. If you are satisfied with the changes, you can click merge to deploy those changes to the main app.

Future changes

All of the data and UI in the Hosted Workbench are powered by SQL queries that are run against your Dolt database. Any of its UI can potentially be recreated in Retool, or in your own custom app. This is the power of Dolt. You get a SQL interface to your data across all of time, lineage and time travel.

In the future, it might be nice to have the diff between the main and retool_dev branches right in the Retool app. Or maybe, you want to support multiple dev branches in the Retool app. This is all possible with some work.

In addition, you could even view the audit log for a specific customer. You would be able to see the history of all feature flags for that customer, and which user merged those changes into main.

Conclusion

Dolt is a MySQL database that was built from the ground up to support version control. You can use it to power a Retool app and instantly get access to version control features like commits, branches, merge, and pull-requests. You can use Hosted Dolt to deploy your dolt database in the cloud where Retool can access it. Or you can self-host because Dolt is open-source.

We're excited to see more tools and apps powered by Dolt. Come talk to us in our Discord about the new tools that you are building or if you would like some support!