In this tutorial, you will learn how to create an API route to an endpoint that will update a specific book identified by its ID in the BookStore database.
The target route you are going to build and test should look something like this:
https://rest.synatic.com/indri/bookstore-api/Books
When executing this API request, you use a PUT request loaded with the JSON Object containing the book's changed details, add the id of the book's record to the end of the URL, then submit the request. The response should update the book details.
For the PUT Route, you will also be adding a Security Scheme to the API.
Watch the video below to learn how to create the API Route using the Synatic platform.
The following topics explain each step of the process as shown in the video.
Let's create an API
The BookStore API has already been created in the API tutorial - Part 2, so you only need to create the required Flow and Paths and then add them to the API.
This tutorial will guide you through creating a PUT operation to update a book identified by its ID only.
To do this, you must first create a backing Synatic flow that will access the database to update the data, which you then connect to the API.
Then you'll create an API path and connect the Flow to the API.
Lastly, you can test the Route using Postman.
Create the backing Flow for the Update Book by ID route
This flow requires the following steps to be configured; Input Parameters, JSON, Calculator, and SQL Server.
Open the Solution menu:
Click ⨁ adjacent to the Flows option to create a new Flow.
Give it the name 'Update Book by ID' and a meaningful description.
Click Save to open the flow designer canvas.
Create an 'id' parameter in the flow
You will be accessing a SQL database that requires the Key ID in the database to link to the parameter ID.
To update a book by its ID, you must first create an Input Parameter for the ID in the flow.
Select the Input Parameter step in the flow.
Enter 'id' for the parameter name.
Select the data type as Integer and also the required attribute.
Click Save.
Add a JSON step
You will be submitting a JSON data file to update the SQL database, so let's insert the JSON step from the Reader drawer in the Steps palette to read the book data from a JSON formatted script.
Select and drag it to the flow.
The JSON step opens. Click to save it as no configuration is required.
Add a Calculator step
The Calculator step is required to add some JavaScript to link the 'id' parameter to the record.
Let's insert the Calculator step from the Mapper drawer in the Steps palette to add the JavaScript code to link to the record in the database.
Select and drag it to the flow.
The Calculator step opens.
Enter the following code in the JavaScript code block:
let inputRecord=input.record;
inputRecord.Id = input.parameters.Id;
return inputRecord;
Add the SQL Server step
You will also be accessing the SQL database to update a record, so let's insert the SQL Server step from the Destination drawer in the Steps palette.
Select and drag it to the flow.
The SQL Server step opens.
SQL Server database connection
The connection to the database has already been created in the API tutorial - Part 2, so you just need to select the connection.
Click on the SQL Server connection field.
Select the BookStore Connection.
Select the Query type
Enter the SQL Query type to update an existing book in the database.
Select the Update SQL Query type.
In Update Options, enter the Object Name as 'tutorial.BookStore', the table name for the book data.
In Column Mappings, enter the paths and columns with their related SQL Data Types and Field length as shown below.
- It's necessary to map the incoming data to the associated database columns.
- The names used in the JSON data file and the database must be the same.
- For this tutorial, it's an easy process as they are both the same.Configure the Key Mapping structure to link the ID in the JSON data file to the ID in the record that must be updated.
Now click Save Step to confirm and preserve the column mappings.
Test the Connection and the Query
It's always going to be a good idea, and advisable to test the query before continuing.
Click the blue Run Test button on the Flow to run a simulation.
Now you should add a JSON test object into the Capture Source Text code block, such as:
{
"Name": "Misery",
"Author": "Stephen King",
"User Rating": 4.6,
"Reviews": 9847,
"Price": 35,
"Year": 1975,
"Genre": "Suspense"
}Click Test to execute the query.
Check the response to verify that the update was successful.
Connect the Flow to the API Route
To complete the API, the Update Book by ID backing Flow must be connected.
Open the API Builder again.
Click ⨁ adjacent to the Paths tab to create a new Route.
Select the PUT operation and type in the Path's name /Books.
Select the Definition tab and add the API Key Scheme.
Select the Source tab and then the Flow tab.
Scroll to select the Flow Update Book by ID from the available list.
Test the API Route with Postman
You can test your API endpoint using the Postman platform.
Open the API Builder again.
Select the info tab and copy the Service URL.
Launch Postman, select a PUT request, and paste the URL
https://rest.synatic.com/indri/bookstore-apiAdd
'/Books/564'to the URL. (564 is the ID of the book.)Select the Body tab and enter your JSON object:
{
"Name": "Misery",
"Author": "Stephen King",
"User Rating": 4.6,
"Reviews": 9847,
"Price": 35,
"Year": 1975,
"Genre": "Suspense"
}Select the RAW tab and select JSON.
Click Send to test.
