12 Days of Retool: Four CRUD Operations

Welcome back for day four of 12 Days of Retool! Last week, we discussed the === operator in JavaScript, and how it compares to the == operator (type coercion). If you missed that post, or any other one during the 12 Days of Retool, a link for every post in the series can be found at the bottom of this page.

Today, we'll examine the four HTTP verbs for CRUD. We will also demonstrate how you can use Retool to create and send API requests to an endpoint, get data from a data source, and test an API’s functionality - all from inside a Retool app.

CRUD is an acronym used to refer to four basic operations that can be performed on database applications: create, read, update, and delete. Many programming languages and protocols have their own equivalent of CRUD. For example, SQL calls the four functions Insert, Select, Update, and Delete. For RESTful APIs, each of the CRUD operations is mapped to an HTTP verb:

HTTP Verb	CRUD	Operation
POST	Create	Creates and sends new data to an API endpoint
GET	Read	Retrieves data from an API endpoint
PUT	Update	Updates existing data
DELETE	Delete	Removes existing data

Create a new resource for our REST API

Before we can test the four CRUD operations in Retool, we need to create a Resource for the API endpoint we want to play around with.

💡

For the purpose of this demo, we will be using a Books API endpoint created using this Generate API from CSV tool provided by Retool.

Login to Retool, and then click on Resources in the top menu bar. Then click on Create new -> Resource -> REST API

Give the API a name, like Books Demo API, and set the Base URL to https://api-generator.retool.com/kxrLKC/

💡

You may get a prompt to "create app". If you're following along, you can skip this step, since we will be importing a new app using JSON in the next step.

Test the CRUD operations inside your Retool app

To play around with the CRUD operations for this resource, we will build a simple Retool app. You can see the app in action here, or you can also download the application JSON definition, and import it into your own Retool instance to play around with it yourself.

You will notice that the resource we created is already selected, with a few pre-defined queries on the left. We are now ready to test our API endpoint using the four HTTP methods.

Sending a GET Request

We will use the GET method to retrieve all books from our Books Demo API. On the bottom panel, select the query getBooks, and then click on Run to test the query.

💡

If you receive an error that says the resource does not exist, make sure you've selected the resource you just created in the "Resource" field for the query. You may have to do this for each of the queries.

Sending a POST Request

We will use the POST method to create a new book record. On the bottom panel, select the query addNewBook, and then click on Run to test the query.

JSON response confirming that the record was added.

You can run the getAllBooks query again to confirm this as well.

Running the GET query again to confirm a new book was added.

Sending a PUT Request

We will use the PUT method to update the book information identified by "id”, and update the price for the book we just added. On the bottom panel, select the query updateBook, and then click on Run to test the query.

JSON response confirming the price was updated.

Sending a DELETE Request

We will use the DELETE method to delete a book identified by "id", and delete the book we just added. On the bottom panel, select the query deleteBook, and then click on Run to test the query. You should see a success response, along with a JSON response confirming that the record was deleted.

(BONUS) Sending a PATCH Request

While we can use the PUT method to update a record, it requires us to specify all attributes even if we want to change only one attribute. The PATCH method allows us to update only the fields we need, instead of including all fields in our request.

We will use the PATCH request to update the price of a book. On the bottom panel, select the query updateAuthor, and then click on Run to test the query. You should see a success response, along with a JSON response confirming that the record was deleted.

Give it a REST!

Today, we learned how to use a Retool app to create and test basic CRUD operations, using a REST API. We also learned you can quickly create a sample API endpoint using the free Generate API from CSV tool.

I hope you enjoyed playing around with REST APIs today and found this hack useful. Below, you'll find a list of every post from the 12 Days of Retool. Make sure to swing by tomorrow for our next post in the 12 Days of Retool series where we will demonstrate five ways to use the newly launched application sidebar frame.