Introduction
How cool would it be if we could create APIs in a couple minutes notice? What was once a long and tedious task of getting your backend to feed information to your front end with every request, has been simplified by using WayScript. With WayScript, we can build fully functional APIs that can respond to different request types, return proper JSON, and allow authorization control in just a few minutes.
But let's take this one step further. What if we wanted to create an API that returns information stored in our data storage already created? Easy. Let's see how we can connect our already functioning SQL based database to an API.
Prerequisites
No requirements, but some documentation you may find useful:
Creating a API URL
For our API to work, we need to achieve a couple of different criteria. First, we need to be able to send different request types to a URL. Once we receive these requests, we need to be able to respond with the right information depending on the request. Finally, the response must be a typical API response, so JSON formatting.
In WayScript, a script is activated whenever a trigger is, well, triggered. If we want something to happen whenever a user visits or sends a request to a url, we'll need to use a HTTP trigger.
When placing this trigger into our script, we get some customization options that we can use to the left. Some of these customizations include:
- URL endpoint
- Authorization Credentials (If password protecting your endpoint)
- Expected Request Parameter Data
We'll use a combination of the url endpoint and request parameter settings to figure out what we should return back to the user. For this example, let's say our database contains purchase order information, and the user is attempting to send api requests to return records of what was purchased.
For that, we could set a request parameter of PO, expecting a purchase order number. This number is what we'll use to query the database in the coming step.
Here we're expecting to receive a value for 'po' in the url parameters. When we create this, a variable within WayScript will be created that we can use in the coming steps.
Database Query using the Parameter
At this point, the user has sent the request to our URL. Now, we need to determine what to do with that information. Since we want to query a database with the passed information. We'll need to drag in an SQL module into our workflow.
With WayScript, we get access to a full cde editor that we can use to write our own custom queries.
Let's write some SQL code that looks like this:
You'll notice the po variable is bubbled in the above photo. This is because that value has been dragged in from the variables panel. This is the query parameter we collected from the url. When we execute this code, we're given access to the SQL data. From here, we'll need to import the values that we want from the table into WayScript by clicking import and assigning their variable names.
Once we assign these variable names, they're created as wayscript variables within our script.
Here, only one entry matched the parameter the user requested. However, these variables could be list types. This is something we'll need to process using a programming language in the next step.
Processing the Data in a JSON Response
We have successfully queried the database, now we need the data to be in an acceptable format to pass back to the user. To convert our SQL entries into JSON, we'll use a bit of python to do this.
import json
# Reading in Variables from Previous WayScript Modules
po_number = variables['PO\_Number']
name = variables['Name']
product = variables['Product']
quantity = variables['Quantity']
# Creating Dict objects to store values to
data = {}
order = {}
# Beginning index i = 0
#Iterate over potentially many products on a single PO
for p in product:
order['product'] = p
order['quantity'] = quantity[i]
order['buyer'] = name[i]
i += 1
# write data to our dict
data['order'] = order
# create json format to pass to API
return_data = json.dumps(data)
variables['return_data'] = return_data
So what's happening here? The first few lines are reading int he variables from wayscript and creating them as variables within our python script. We do this by using the variables dictionary.
From there, we're creating the dictionaries that we will fill with our data, then dumping our data into these dictionaries. The final line creates the wayscript variable that we will return to the user.
Returning the Response to the User
We've done the query depending on the request parameters, processed the data, and now we only need to respond with the appropriate JSON. Since we've already created this variable in the python step, we can just drag in an http response module, and place that variable as the response.
Questions, Feedback?
As always, if you have any questions please reach out to us on our discord and we would be happy to help. If you want to view this completed script, you can find it here.
Top comments (0)