How to delete rows in bulk with Baserow API

Baserow is a powerhouse for data management, letting you organize everything from project details to customer information.

Managing data in Baserow can be easy, but as your database grows, so can the need to clean up old or irrelevant information. Your table might hold information that has a limited lifespan, like project tasks or customer support tickets. Regularly deleting completed tasks or resolved tickets can help keep your database organized and improve performance.

Manually deleting rows one by one can become incredibly time-consuming, especially when dealing with large datasets. Perhaps you’re restructuring your table or want to start fresh with a new data set or your table contains data subject to compliance regulations that require deletion after a specific period, like GDPR or HIPAA. This is where bulk deletion comes in.

What we’ll do

In this tutorial, we’ll guide you through deleting rows in bulk, saving you time and effort. We’ll cover the following steps:

• Access Baserow database and identify deletion criteria.
• Retrieve Baserow database token.
• Reference database API documentation.
• Construct the JSON payload to delete rows.
• Set up an API client.
• Configure the API request.
• Restore deleted data, if needed.
• Build and test automation.

Prerequisites

To complete this tutorial, you’ll need the following:

• A Baserow account with access to the database and table you want to work with.
• Basic knowledge of RESTful APIs and how to make HTTP requests.
• API testing tool like Postman or Insomnia.

Let’s dive in!

Bulk deletion in Baserow: Batch mode

1. Access Baserow database and identify deletion criteria

Log in to your existing Baserow account or create a new account if you don’t have one already.

Once you log in, you can access the dashboard to add workspaces and databases.

The first step is to determine which rows you want to target for deletion.

Next, we want to create a Baserow database token and give it the necessary permissions for reading and writing data.

2. Retrieve Baserow database token

Baserow is API-first and developer-friendly that makes authentication easy and secure for seamless integration with third-party applications. Baserow API allows you to interact with your database programmatically, for the most control and efficiency for large datasets.

To use bearer token authentication, you need to generate a database token with appropriate permissions in your account settings. These tokens can grant specific permissions to create, read, update, and delete data up to the table level.

We will use the database token to access the endpoints provided in the database documentation. It’s only possible to use the endpoints if the database token has read, create, or update permissions.

To generate a database token, follow these steps:

1. Access your profile and then click on Settings.
2. Click on the Database tokens tab.
3. Click on the Create token + button.
4. Input a name and choose the workspace.
5. Click on the Create token button to generate a new database token for the workspace, which will be authorized for your user account.

Copy the generated API key and note it securely, you’ll need it to authenticate your requests. Your database token serves as a digital key, granting authorized users access to the database’s resources while ensuring data integrity and privacy.

Refer to this article to learn more about Baserow authentication and token types.

After you have created your database schema and API key in the settings, your Baserow database provides his own REST API endpoints to create, read, update and delete rows.

3. Reference database API documentation

The rules and functions to follow in order to make an API call are laid out in the database documentation. Understanding this will help efficiently manage data retrieval and manipulations through the API.

REST APIs are core operational tools that enable organizations and developers to consume and build on top of Baserow’s various database capabilities. Baserow’s API follows REST semantics, uses JSON to encode objects, and relies on standard HTTP codes, and machine and human-readable errors to signal operation outcomes.

To access the database API documentation, click on the vertical ellipsis ⋮ beside the database. Select View API Docs from the menu. Alternatively, click Dashboard → API documentation → Select database.

The database API consists of different endpoints for specific actions. The endpoints can be found in the left sidebar.

Let’s take a look at the API documentation to figure out the right way to delete rows.

4. Construct the JSON payload to delete rows

Baserow Delete row endpoint accepts a unique identifier of the row that needs to be deleted or an array of row ids that should be deleted.

A Row ID is a number that uniquely identifies that row of data. The assigned number is permanently attached to a row and cannot be changed or deleted, even after the row is deleted.

To delete a row, a DELETE request will be made to the endpoint.

To delete multiple rows, a POST request will be made to the endpoint.

In this case, we want to delete multiple rows, so select Batch mode.

Here’s a CURL command structure including the JSON payload:

curl \
-X POST \
-H "Authorization: Token YOUR_DATABASE_TOKEN" \
-H "Content-Type: application/json" \
"https://api.baserow.io/api/database/rows/table/158251/batch-delete/" \
--data '{
    "items": [
        0
    ]
}'

Next, we will test the API using Postman or Insomnia.

5. Set up an API client

Open your preferred API client, such as Insomnia or Postman. These tools are useful for performing repetitive tests, regression testing, and integration testing.

Insomnia is an open-source API development platform that allows you to create and manage API requests, write and run automated tests, and collaborate with team members. Postman also allows you to automate your API tests by creating collections of requests and tests.

We’ll be using Insomnia in this tutorial, but the same steps apply to any API that allows performing repetitive tests, regression testing, and integration testing.

Within Insomnia, create a new project then create a new HTTP request:

6. Configure the API request

Refer to the API documentation for the endpoint URLs for various actions.

1. In Insomnia, select POST method as the request method to be used. This is based on the action you want to perform with the API.

2. Input the Request URL. For Baserow hosted cloud version: https://api.baserow.io/api/database/rows/table/TABLE_ID/batch-delete/. Replace TABLE_ID placeholder with the ID of the table containing the rows you want to delete.

3. Next, provide the authentication credentials: Authorization: Token YOUR_DATABASE_TOKEN. You can authenticate to the API by providing your token in the HTTP authorization bearer token header. Bearer token authentication involves including a token in the authorization header of your API requests.

4. In the Authorization tab, select API Key Auth as the Authorization type. Input the following values:

   • Prefix = Token.

   • Token = YOUR_DATABASE_TOKEN. Replace YOUR_DATABASE_TOKEN with the token retrieved in the earlier step.

     

5. Use the HTTP Content-Type header on the request to set the proper content. The Content-Type header is added to help the server identify the media type of the request body that is present in this request. Content-Type: application/json

6. Select JSON body type to send data in the body of a request. To delete rows, input an array of row ids that should be deleted.

   Use the row_id() formula to return the row’s unique identifying number. You can also retrieve row IDs using the GET method before including them in the deletion request.

   
   {
     "items": [
       "rowId1",
       "rowId2",
       "rowId3"
     ]
   }
   

   Replace "rowId1", "rowId2", and "rowId3" with the actual IDs of the rows you want to delete.

7. Click the Send button in Insomnia to test the connection.

Save the Request and add it to your collection. You can import the collections to pull in similar requests.

7. Restore deleted data, if needed

Remember that bulk deletion should be done with caution, especially if you’re dealing with critical information. Make sure you have a backup of your data before proceeding, and double-check that you’re deleting the intended information. Snapshots are complete copies of a database that allow you to restore it to a specific point in time.

Data you delete will remain in your account trash for three days after being removed. You can recover deleted database items including tables, fields, rows, and views during that period.

8. Build and test automation

Finally, you can automate deletion, or trigger actions based on specific events in Baserow.

Depending on your use case, schedule your deletion to automate the process or use webhooks to trigger it in response to specific events within Baserow. Use API to create custom automation solutions and make any necessary improvements.

Before deploying your solution, thoroughly test it to ensure it functions as intended. Also, closely monitor the performance.

Summary

If your table has accumulated outdated information, like past project records or old customer details, bulk deletion can help you remove this clutter and improve the overall efficiency of your base.

Feel free to explore more advanced features of Baserow’s API, like handling attachments, batch processing, or using webhooks for event-driven automation.

Other useful resources

The following articles may also be helpful:

• How to authenticate to Baserow using database and JSON web tokens
• Create a Baserow plugin for language translation with ChatGPT
• API integration: How to upload a file to Baserow using multipart/form-data
• API integration: How to upload files via URL
• Simplify your workflow: Automate with Baserow batch API calls
• How to connect Baserow to external tools with the API

If you run into any issues following this tutorial, feel free to reach out to the Baserow community.