12 Days of Retool: Four CRUD Operations

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

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.

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.

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.

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.

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

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.

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.

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.

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.