tutorials
Back to overview

How to Easily Create REST API from a CSV file

How to Easily Create REST API from a CSV file

REST APIs are important as a communication channel for passing information between services. This is due to the fact that it uses a consistent interface to share messages between two different systems.

Baserow is API first, meaning you can use our API docs and webhooks to connect with any other software and automate your workflows virtually. Data can then be analysed and stored in a database. With Baserow API, you can integrate your data with thousands of services. The API can be used to send or retrieve information for other apps with configurable token permissions and webhooks.

When adding a large number of rows to a database, Baserow provides the option to upload a CSV (comma-separated values) file instead of adding each row individually.

What You Will Build

In this tutorial, we will generate a secure RESTful API from a Comma Separated Values (CSV) file in a few minutes. We will cover:

  • How to get data into a Baserow database from a CSV file.
  • Create a Database token
  • Use the API docs to create an API

Let’s begin.

1. Set up a group and database

First, create an account or log in to your existing Baserow account.

Baserow is organised into groups. A group is a workspace where you can store multiple databases and invite collaborators. Each group has users invited to become members.

To get started, we will create a new group.

On the Dashboard page, click the ‘+ Create group’ button. This button will also be visible on the sidebar.

https://baserow-media.ams3.digitaloceanspaces.com/pagedown-uploads/7054e8ab-bf5d-4ef6-901d-01c2a237405c/Screenshot_2022-10-23_at_18.12.00.png

For detailed instructions on working with Baserow groups, visit this documentation.

Next, create a database within the group.

A database is a collection of tables, each containing rows, and columns. A Baserow database can include multiple tables containing information for different projects.

To create a database, click ‘**+ Create new’ and select ‘Database’ from the dropdown option.

https://baserow-media.ams3.digitaloceanspaces.com/pagedown-uploads/fa33cac9-6d2e-44be-8d78-5c2a88028d29/Untitled.png

For more detailed instructions, view this article on how to create a database.

2. Get CSV data

In this tutorial, we will export a CSV file with pre-populated data - privately-owned public open spaces in the City of San Francisco.

To use this sample dataset, visit this link to download the CSV File. The spreadsheet has a header row and details of each department.

3. Import CSV data into Baserow

Next, we need to upload and import the CSV file into your database using Baserow import features.

To import the CSV file, you can either:

  • create a new table from the file import or
  • import it into an existing table.

Option 1: Import a new table

Click on ’+ Create table’ within the database, select ‘Import a CSV file’ then input a name for the table.

Click the “Choose CSV file” button to browse and upload a file from your computer.

Finally, click the ‘Add table’ button to import. Your data will be immediately visible in your database. Learn more about how to add a new table via import to a database.

enter image description here

Option 2: Add new records from the CSV file into an existing table

Alternatively, you can add additional data to an existing table if you have existing data in the database already configured with the header columns.

💡 This step is optional and can be skipped.

Click the ellipsis ••• beside the view at the top of the table. From the menu, click ‘Import file’:

enter image description here

Select ‘Import a CSV file’ then click the ‘Choose CSV file’ button to browse and upload a file from your computer.

The columns of the Baserow fields will be automatically mapped to the correct fields in your table. View this article to learn more about how to map fields.

After the import process, you can view and edit your data, click into cells, modify fields, add new fields, and other customization options.

Data can also be imported in several various file formats, such as XML, JSON, or CSV. View this article about importing files into Baserow.

4. Create a Database token

An API call is a way to communicate database updates. Now let’s generate a REST API endpoint on top of your CSV data which we can query via HTTP.

Baserow uses a simple token-based authentication. The Database token is used to make API calls on behalf of the user or application. You need to generate at least one Database token in your account settings to use the endpoints. You can use the Database token to authenticate with the REST API endpoints where you can create, read, update and delete rows.

The REST endpoint connects two or more services and enables data exchange between the server, database, and app.

Get Database token

Click on the dropdown icon beside your account name in the top-right of the home screen to access your account page. Next, click ‘Settings’ from the dropdown menu.

Within your ‘Settings’ page, select the ‘Database token’ tab, then click on the ‘Create token +’ button to create a new Database token for the selected group. Input the name and select the group created.

Click on the ‘Create token’ button:

enter image description here

From the Database token page, select the ellipsis ••• beside the group to copy the token:

enter image description here

Set Database token permissions

Next, let’s continue to set permissions on the table level. You can toggle the permissions to indicate per operation the permissions of the Database token to create, read, update or delete data within the group.

For this tutorial, we will check all tables in the group. The token then creates permissions for all the tables in the database selected.

When sharing publicly, you must be cautious about setting the appropriate permissions. If you expose your token publicly, anyone with that token can create, read, update, and delete your data.  If you accidentally expose your Database token to the public, visit the Database token page as soon as possible to generate a new token.

Create an API with the API docs

The database API documentation is generated automatically based on the tables and fields that are in your database.

Tip 💡 If you make changes to your database, table, or fields, it could change the API interface. Therefore, make sure that you update your API implementation accordingly.

To view the API documentation, click on the vertical ellipsis ⋮ beside the selected group then select ‘View API Docs’ from the group menu:

enter image description here

We’ll get an API endpoint with GET, POST, PUT, PATCH, and DELETE methods with the Base URL: https://api.baserow.io.

Now, let us list the rows in the CSV file. You can alternatively replace the List rows endpoint with an endpoint with:

  • List fields
  • Get row
  • Create row
  • Update row
  • Move row
  • Delete row

For this tutorial, we want to get data from the imported database. To list rows in the created table, we will make a GET request to the table endpoint. For example in this tutorial, we want to display rows in the Privately Owned Public Open Spaces table:

enter image description here

Copy the URL to your clipboard.

Now that we have uploaded the CSV file and generated the API docs, let’s send a GET request to fetch the CSV data via Postman.

5. Test endpoint in Postman

We will paste the GET URL into Postman to list the rows in the imported CSV file.

In Postman, create an API request

  1. Select GET then input the Request URL retrieved earlier: https://api.baserow.io/api/database/rows/table/115797/?user_field_names=true

    enter image description here

  2. Next, use the header to add the authorization and authentication data

    Click on the Authorization tab then select API Key as the Authorization type. Input the following values:

    • Key = Authorization
    • Value = Token *YOUR_API_KEY.* Replace YOUR_API_KEY with the Database token retrieved in the earlier step.
    • Add to = Header

    enter image description here

Your Postman header UI should look like this:

enter image description here

Click Send. A 200 - OK Response should appear in the lower pane:

enter image description here

Save the Request and add it to your Postman collection. With this, you can automatically import the Postman collections to pull in similar requests.

What’s your story? Tell us how you use Baserow

release
January 18, 2023 by Bram Wiepjes
1.14 release of Baserow

Today we are releasing Baserow 1.14 with a new security feature — audit log, and big updates to Baserow RBAC that allow assigning roles to users or teams on individual databases and tables for even more granular permission control.

info
January 26, 2023 by Georgina Brisk
Best Airtable alternatives
info
January 10, 2023 by Olivier
No-Code Platforms in 2023
Want to contribute?

Want to write a post in collaboration with us?

How to contribute?