Add data to your database with a REST API in just few lines of code

In this tutorial we will show you how to add data to your database table using a REST API

The REST API will be created using API Spreadsheets. And as you will see, the whole setup will use just a few lines of code.

Connect to a database

First, sign into your APISpreadsheets account and open the Database page from the main menu.

NOTE this is a paid feature. BUT, feel free to reach out on sales@apispreadsheets.com to try out this feature for free. We will set you up with a 14-day free trial.

Click the Connect a new database button to set up a connection.

This will open a window where you'll have to enter database credentials.

First, select the type of database. APISpreadsheets currently supports MySQL and PostgreSQL databases.

Provide credentials by filling in ‘Database Name’, ‘Port’ and ‘Hostname’ fields.

If the database is password protected, enter ‘Username’ and ‘Password’ values as well.

Finally, enter the description of the database. Mention the purpose and important details about the database.

Note: Enter hostname value without http prefix, starting or ending slashes.

Click the Connect button to finish connecting the database.

Connect to your database table

Once you’ve successfully connected the database, APISpreadsheets will redirect you to the database settings page.

Clicking the Add a New API button will open a form.

First, enter a descriptive name for your API.

In the code block, write the query for the table to which you wish to add data with these rules:

1. It is very important that you only use only 1 table name in your query. Otherwise adding data will give an error. This is a different behavior that if you were simply using this API to read data

2. The query must begin with select. You can select any column names, or enter any number of where clauses since these do NOT affect adding data. We provide this format so the same API can be used for both reading, and creating data.

Example query

There is a table in our database called movies that has four columns: title, genre, director and release_year

Let's say we want to add rows of data to this table. AND we also want to use this API to read only the title and release_year columns

For this, we enter a simple query:

And then we click Test SQL Query to see that is working properly

If the query is working correctly, click the Save API button.

Get quickstart code to add data

Once your query is saved, you will be redirected to the File page of this API

On this page, you will se a Quickstart Code button that will have code snippets on adding data to your movies table

We will be selecting the Create menu item. And for this tutorial we will use the Python code

Copy this code and save it somewhere. We will now format the data that we need to add in the proper JSON object, and then insert it in this code

Preparing the data

Let's say we want add the following data to our movies table

title	genre	director	release_year
The Avengers	Action	Joss Whedon	2012
Parasite	Drama	Bong Joon-ho	2019
Knives Out	Thriller	Rian Johnson	2019

We need to create an array where each element is a JSON object containing the column name and value of the row to add. And then use that array as a value in a JSON object for the key data

Adding the data to the table

Now that we have our data, and our quickstart code, we can replace the data provided in the quickstart code with our data

For Python, that looks like the following

Then we simply run the code! And if the request has returned a 201 status code that means it succeeded!

We can verify that from our database by checking our movies table again

And we see that the rows have indeed been added to our database table

Reading data from this API

You can use the Quickstart Code for Read to read data from this API

In this case, it will result in the title and release_year columns being returned

Just to emphasize again, the columns you select in the query when you create the API DO NOT affect adding data. You can still add data to any column in the table

If you want to learn more about reading data you can check out our read data from database tutorial

Thank you for reading! Let us know if you have any questions by emailing us at info@apispreadsheets.com