Retool is a platform used for the quick development of internal tools for organizations. You can create a variety of tools using it ranging from embeddable components, mobile, to web apps.

Turso is a distributed database powered by libSQL, the open-source, open-contribution fork of SQLite.

In this blog, I’ll demonstrate how to create a fast, performant admin UI using Retool and Turso.

Among the reasons you’d want to place the data to your Retool apps on Turso is the snappy final experience to your apps made possible by Turso’s placement of data at the edge.

We are going to create a simple dashboard for managing the items in an e-commerce store’s database that is finally accessed via a REST API as referenced in a previous blog post. The dashboard will let us list the items present in the database’s products table and also enable us to add new ones into it.

Let’s get down to business.

Prerequisites:

To continue with this tutorial, you need the following:

A retool account.
An installation of the Turso CLI.

Creating a Turso database

Follow the instructions provided on the README of The Mug Store API’s GitHub repository to create a new database and seed it with some demo data.

Creating a Retool resource

Follow these steps to add your Turso database as a Retool resource.

When inside the Retool dashboard, go to the “Resources” page and create a new folder called “Turso”
Inside the Turso folder, create a new resource by clicking on the “Create new” button on the top right.
Choose “REST API” as the resource type.
Fill in the “Configure REST API” form, using the following template as guidance:
- FIll in the “General” section of the form with the expected information.
- Get the database URL and authentication token of your Turso database by following the instructions provided in the api-mug-store-api repo.
- In the “Credentials” section of the form, fill in the “Base URL” field with the Turso database URL replacing the libsql protocol with https.
- Add two Headers, the first, an Authorization header with the key set as authorization and the value set as a bearer token constructed by using the authentication token prefixed with “Bearer “ Bearer <obtained authenticationt token>, the second, a Content Type header to notify the responding REST API of the data type we’re sending. content-type: application/json

Finalize the resource creation by clicking on the “Create resource” button found on the top right side of the page.

Next, we’ll see how to use the created resource inside Retool apps.

Creating a Retool application

Back on the “Apps” page, create a new app by clicking on the “Create” button and choosing “App” from the resulting dropdown.

Fill in the name of the app.

Inside the new app’s dashboard, go to the code tab on the left side menu and add a new query by clicking on the “Create resource query” button.

Since we want the first query to fetch all the products (mugs) from the Turso database, name the query “fetch_all_mugs” and choose the Turso resource we created earlier on the “Resource” field.

Select “POST” as the “Action type”, and, for the request body select Raw input, filling in the following code in the respective field.

{{
{ statements: ["select * from mugs"] }
}}

Click on the “Preview” button on the top right of the query form to see if the created query works as expected, which should fetch the following results.

[
  {
    "results": {
      "columns": [
        "id",
        "name",
        "description",
        "price",
        "category_id",
        "image",
        "created_at",
        "updated_at"
      ],
      "rows": [
        [
          "fc34b736-dcac-4198-9105-9b726df8836d",
          "The lazy mug",
          "Excepteur minim officia ad labore aliqua irure nulla fugiat voluptate dolor. Aliquip commodo deserunt nisi magna.",
        ],
        ...
      ]
    }
  }
]

We’ll want to transform the results into something that can easily be formatted into a table, to do so, enable “Transform results” and add the following code into the input field.

function adaptData(data){
  if (!data.columns || !data.rows) {
    throw new Error("Malformed response from turso");
  }

  const { columns, rows } = data;
  const formattedData = [];

  for (const row of rows) {
    const rowData = {};
    for (let i = 0; i < columns.length; i++) {
      rowData[columns[i]] = row[i];
    }
    
    formattedData.push(rowData);
  }

  return formattedData;
}

return adaptData(data[0].results);

When you run “Preview” again, you should see results that are formatted as follows.

[
  {
    "id": "fc34b736-dcac-4198-9105-9b726df8836d",
    "name": "The lazy mug",
    "description": "Excepteur minim officia ad labore aliqua irure nulla fugiat voluptate dolor. Aliquip commodo deserunt nisi magna..",
    "price": 72,
    "category_id": "326b8429-1af7-401e-a15e-92f7c9c97a09",
    "image": "<https://res.cloudinary.com/djx5h4cjt/image/upload/v1686293638/twitpics/mug-club/the-lazy-mug.jpg>",
    "created_at": 1699654048177,
    "updated_at": 1699654048177
  },
  ...
]

Save the created query by clicking on the “Save” button. After a successful save of the query the button will be renamed to “Run”, click on it once more to run the query.

Plotting the Turso data on a table

To plot the Turso data fetched by the “fetch_all_mugs” query to a table, go to the “Component tree” tab on the left side menu, click on the plus icon next to “Main Components” and choose a table.

Rename the resulting table which will have been seeded with some demo data to “All_Mugs”.

With the table selected, the component’s inspector window will be opened on the right side of the page showing the table properties, for the “Data source” select the “fetch_all_mugs” query that we just created.

After changing the data source the table will be replaced with the data that has been fetched from Turso.

Retool table component populated with data from Turso

You can modify the properties of the resulting table columns into more fitting types to have more coherent data presentation on this table.

Submitting new data to Turso

To add new products to our e-commerce store database, we’ll create a form and set it up to submit data to Turso.

Since the form is supposed to submit product categories (“Category ID”) as seen from the data presented on the All_Mugs table above, we need to list product categories in a select field of the form.

Add a new query by duplicating the existing “fetch_all_categories” query, naming the duplicate “fetch_all_categories”, and changing the request body contents to the following.

{{
{ statements: ["select * from categories"] }
}}

When you save and run this new query, you should get a list of the product categories shown below:

[
  {
    "id": "326b8429-1af7-401e-a15e-92f7c9c97a09",
    "name": "Cool Mugs"
  },
  {
    "id": "ca037b28-edbe-4d00-b629-356397ea6fd8",
    "name": "Lame Mugs"
  }
]

To add the product data submission form, go through the component composition process we used to add the All_Mugs table, this time choosing a form instead. Name the form “newMug” through the component inspector window and add 5 fields inside its body with the following properties:

name: “mugName”, label: “Name”.
name: “mugDescription”, label: “Description”.
name: “mugPrice”, label: “Price”.
name: “mugImageURL”, label: “Image URL”.
A select field with the name “mugCategoryId” and labeled as “Category”.

You can use the inspector window to modify the input properties such as the addition of validation rules, default values, placeholders, etc.

For the “Category” select field, select the “fetch_all_categories” query we just added as its data source.

Afterwards, you should see the product categories listed when you place the cursor on the select field.

A new query that takes care of submitting the form’s data to Turso is required to proceed. Create one by once again duplicating the “fetch_all_categories” query, renaming it to “submit_mug”.

Update the request body, replacing its contents with the following code as the raw data.

"{ \"statements\": [\"insert into mugs(id, name, description, price, category_id, image, created_at, updated_at) values ('{{uuid.v4()}}', '{{newMug.data.mugName}}', '{{newMug.data.mugDescription}}', {{newMug.data.mugPrice}}, '{{newMug.data.mugCategoryId}}', '{{newMug.data.mugImageUrl}}', {{Date.now()}}, {{Date.now()}})\"] }"

Also, update the “Transform results” field replacing its code with the following.

return data;

Lastly, select the form component, on the resulting component inspector window, click on the input field under “Event handlers” to add a new form submission action.

Select the “submit_mug” query we just created as the value of the “Query” select field and close the inspector settings.

Triggering products refetch on successful data submission

You should now be able to fill in new product information in the newMug form and submit it to add new product data to the Turso database.

To refresh the items listed on the “All_Mugs” table component after new data has been submitted, add a success event handler to the “submit_mug” query. You can do so by going to its “Event Handlers” section, selecting the input field under “Success” and assigning the “fetch_all_mugs” query to the “Query” field.

Refetch products on successful product submission

You can repeat these steps, adding new components that trigger queries to add product updating and deleting features to the dashboard.

Here’s a preview of the complete Admin UI.

For more information on the technologies used in this tutorial, visit the following links:

#REST API

Create a fast, performant admin UI with Retool and Turso