Irakli Tchigladze
on 2022-06-07
In this article, we will show how to set up a REST API and get data from your database without having to set up a server!
First, sign into your APISpreadsheets account and open the ‘Database’ page from the main menu.
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.
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. Here you can write a query to select data and share it over REST API.
First, enter a descriptive name for your API.
In the code block, write a query to select data that is going to be transmitted by the REST API.
Click ‘View DB Tables & Columns’ to explore potential sources of data in the database.
When writing the query, you’ll have to specify the tables and columns from which you want to extract data.
If you’ve never written SQL query before, click ‘Read query rules’ to understand how to write queries to select data from a database.
As a demonstration, let’s create an API to share movie data.
Creating a REST API makes your data available to outside software like web applications.
We will take data from the ‘movies’ table in the connected database, and share it to outside software using REST API.
The table has four columns: title, genre, director and release_year.
As an example, let’s select data from the title column.
First, we enter a basic query:
Click the ‘Test SQL Query’ button to check if the query selects data as it should.
APISpreadsheets will run the query and return the sample output of the first five rows. Currently the column stores only 4 values.
If the query is working correctly, click the ‘Save API’ button.
Once saved, you will be redirected to the ‘File’ page of the API. This is where you can configure security, sharing, analytics and other features of your API.
On this page you can find a link to view data transmitted by your API.
Copy and visit this URL to see the data shared from your database.
REST API is a way to share data between entities on the internet. For example, this movie data could be used to create entries for a movie database.
APISpreadsheets allows you to change query at any moment, which means you can change which data is selected from the database.
We can also write SQL queries to filter records. We can use any valid SQL query, as long as it adheres to APISpreadsheets query rules mentioned here.
For instance, instead of selecting only the values in the title column, we can select all columns to get more detailed information about each movie.
If we visit the URL of our API, we’ll see that each object contains fuller information about each movie.
The REST API returns data from the current state of the database table. If you add or remove data from the database, REST API will return a JSON object that reflects these changes.
You can add data to your database using database tools like SQL Workbench, or create a spreadsheet importer that inserts uploaded data into the database. Read this article to learn more about this process.
For example, if we added four more records of movies to the table, API URL will show the data for eight movies, instead of four:
We can also edit the query to select all columns instead of just title:
Our movie database reads data from the API URL, so it will also contain 8 records instead of 4:
APISpreadsheets users can use multiple features to maintain control over who can access data transmitted through the REST API.
First, you can require authentication to access data transmitted from your REST API.
You can do this by going to ‘Files’, and opening the settings of the right File.
You can control API privacy by switching the toggle in the left sidebar.
Private APIs are only accessible when the request contains credentials, available on the ‘API Keys’ section of File settings.
To use this feature, users must have a ‘Pro’ account (or better).
Accounts with ‘Team’ (or higher) subscriptions can enable or disable certain CRUD operations (create, read, update, delete) to further define the intended use of their API.
Accounts with ‘Enterprise’ or ‘Business’ subscriptions can restrict API access to specific domains.
Thanks for reading! Let us know if you have any questions by emailing us at info@apispreadsheets.com
