How to edit rows in bulk with Baserow API

Baserow excels at keeping your data organized, but as your database grows, the need to update or edit existing information becomes inevitable. Maybe your project details have changed, customer information needs refreshing, or compliance regulations require adjustments to your data set. Whatever the reason, keeping your Baserow database up-to-date ensures accuracy and streamlines workflows.

While updating data row by row is manageable for small datasets, it quickly becomes a chore when dealing with hundreds or even thousands of entries. One powerful feature that Baserow offers is the ability to perform bulk operations via its API, so you can streamline workflow and make large-scale changes to databases quickly and effectively.

What we’ll do

In this tutorial, we’ll explore effective methods for bulk editing rows in Baserow, empowering you to maintain a clean and efficient database. We’ll cover the following steps:

• Access Baserow database.
• Retrieve Baserow database token.
• Reference database API documentation.
• Construct the JSON payload to update rows.
• Set up an API client.
• Configure the API request.
• 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 editing in Baserow with the API

Remember that bulk updates 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 editing the intended information. Snapshots are complete copies of a database that allow you to restore it to a specific point in time.

1. Access Baserow database

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.

Baserow is API-first and developer-friendly. Bulk operations refer to the ability to perform actions on multiple rows simultaneously. This means updating or editing multiple rows within a table at once, rather than making individual changes one by one.

Baserow uses a simple token based authentication. You need to generate at least one database token in your settings to use the endpoints described below. It is possible to give create, read, update and delete permissions up until table level per token. You can authenticate to the API by providing your token in the HTTP authorization bearer token header. All API requests must be authenticated and made over HTTPS.

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.

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 its REST API endpoints to create, read, update, and delete rows.

3. Reference database API documentation

The rules and functions to follow 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 look at the API documentation to figure out the right way to update rows.

4. Construct the JSON payload to update rows

Baserow Update row endpoint accepts the unique identifier of the row that needs to be updated or an array.

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, reassigned, or deleted, even after the row is deleted.

To update a row or multiple rows, a PATCH request will be made to the endpoint.

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

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

curl \
-X PATCH \
-H "Authorization: Token YOUR_DATABASE_TOKEN" \
-H "Content-Type: application/json" \
"https://api.baserow.io/api/database/rows/table/158251/batch/?user_field_names=true" \
--data '{
    "items": [
        {
            "id": 0,
            "Name": "string",
            "Role": [
                1
            ],
            "Hire date": "2020-01-01",
            "Phone number": "+1-541-754-3010",
            "Email": "example@baserow.io",
            "Shifts worked": [
                1
            ],
            "Certification code": [
                1
            ],
            "Hourly rate": "0.00"
        }
    ]
}'

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.

You can paste Curl to import the request or follow the steps below to configure the API request.

6. Configure the API request

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

1. In Insomnia, select PATCH 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/?user_field_names=true. Replace TABLE_ID placeholder with the ID of the table containing the rows you want to edit.

3. 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.

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

4. 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

5. Select JSON body type to send data in the body of a request.

   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 update request.

   {
   	"items": [
   		{
   			"id": 1,
   			"Name": "Mary Jane",
   			"Role": [
   				2
   			],
   			"Hire date": "2024-06-04",
   			"Phone number": "+1-541-754-3010",
   			"Email": "example@baserow.io",
   			"Shifts worked": [
   				34,
   				35
   			],
   			"Certification code": [
   				2
   			],
   			"Hourly rate": "12.00"
   		}
   	]
   }
   

   Replace rowId1 with the actual ID of the row you want to update.

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

That’s it! You should receive a response from Insomnia indicating whether the update was successful or not. If the request succeeds, the server returns the HTTP 200 OK status code along with the file’s metadata.

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

7. Build and test automation

Finally, integrate Baserow data into your automation solution using code snippets from the response. For instance, you can automate data updates, trigger actions based on specific events in Baserow, or fetch data from external sources into Baserow.

Depending on your use case, schedule to run at specific intervals or utilize webhooks to trigger it in response to specific events within Baserow.

Popular workflow automation platforms like Zapier or Make integrate seamlessly with Baserow. These platforms enable you to create automated workflows that trigger bulk edits based on specific criteria. For instance, you can set up a workflow to automatically update customer information in Baserow whenever a new record is created in a CRM system.

Before deploying your solution, thoroughly test it to ensure it functions as intended and closely monitor the performance of the automation to guarantee accurate data transfer.

Summary

Baserow offers a robust API that allows you to interact with your database programmatically. This enables bulk editing of rows through API calls, ideal for large-scale data manipulation.

By using bulk operations through the Baserow API, you can efficiently manage and manipulate large datasets within your databases. Whether updating hundreds of records or making sweeping changes across tables, bulk operations provide a streamlined solution for data management tasks.

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.