DEV Community

Derrick Sherrill for WayScript

Posted on

6 4

How to Create a SQL based API with WayScript

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.

API Trace View

Struggling with slow API calls?

Dan Mindru walks through how he used Sentry's new Trace View feature to shave off 22.3 seconds from an API call.

Get a practical walkthrough of how to identify bottlenecks, split tasks into multiple parallel tasks, identify slow AI model calls, and more.

Read more →

Top comments (0)

Billboard image

Try REST API Generation for Snowflake

DevOps for Private APIs. Automate the building, securing, and documenting of internal/private REST APIs with built-in enterprise security on bare-metal, VMs, or containers.

  • Auto-generated live APIs mapped from Snowflake database schema
  • Interactive Swagger API documentation
  • Scripting engine to customize your API
  • Built-in role-based access control

Learn more

👋 Kindness is contagious

Explore this insightful post in the vibrant DEV Community. Developers from all walks of life are invited to contribute and elevate our shared know-how.

A simple "thank you" could lift spirits—leave your kudos in the comments!

On DEV, passing on wisdom paves our way and unites us. Enjoyed this piece? A brief note of thanks to the writer goes a long way.

Okay