Skip to main content

How to build and host (for free!) an API that shares data from a SQL Server table using RAW

In this example, we are going to build an API that reads data from a SQL Server table. This data will then be hosted as a REST API. For this, we are going to be using RAW, a platform to quickly build and host APIs.

Let's get started!

info

If you are familiar with RAW and want to deploy this endpoint on your account, click below:

API over Microsoft SQL Server
Search a dataset on airports, optionally filtering by IATA code.

Sample usage:

/api/sqlserver[?code=<iata_code>]

For instance, to get La Guardia's (LGA) information use:

/api/sqlserver?code=LGA

Create a blank API endpoint in RAW​

Once you login to RAW, you should head up to the 'Workspace' section as shown below.

Choose the workspace

Then, click on 'Add Endpoint' to create a new endpoint and choose a new 'Snapi' as shown below. You could also choose an existing template, but for this example, let's start blank and write the code ourselves.

Create blank endpoint

Write the endpoint code​

Now that you have a blank endpoint, let's start writing some code. Before we start, here's an overview of the RAW Workspace.

Overview of the workspace

Now let's follow in steps:

Step 1: Write the code​

In RAW, endpoints are written in Snapi, a simple-to-use programming language specifically created specifically for building APIs. You will see this is very simple to create.

Let's copy/paste the following code for our endpoint (see figure, as step '1'):

main(code: string = null) =
  let
    airports = SQLServer.InferAndRead
      "raw", "example", "airports",
      host = "example-mssql.raw-labs.com",
      username = "******",
      password = "******"
    )
  in
    Collection.Filter(airports,
      x -> Nullable.IsNull(code) or x.iata_faa == code)

// The following test will run if you press the play button.
main("LIS")

Don't worry if you don't follow all the code just yet. This will be explained in detail below.

Step 2: Test the code​

Next, let's test the code. Click on the play button (shown in the figure as step '2') and you will have a live preview of the result of calling the last line of the code.

caution

The code will not work as-is because the password is not set. If you instead deploy the endpoint by expanding the box below, an example will be generated automatically that users a demo database we provide, with username/password set.

API over Microsoft SQL Server
Search a dataset on airports, optionally filtering by IATA code.

Sample usage:

/api/sqlserver[?code=<iata_code>]

For instance, to get La Guardia's (LGA) information use:

/api/sqlserver?code=LGA

Step 3: Choose the final URL​

You can choose the exact path where your API will be hosted. This is shown in the figure as step '3'.

Step 4: Edit the metadata​

Optionally, you can edit the metadata metadata (shown in the figure as step '4'). The metadata is important since RAW includes a built-in API Catalog that helps you and your users find API endpoints later.

Step 5: Deploy the endpoint live!​

We are almost done. Now click to deploy your endpoint (shown in the figure as step '5').

Congratulations, your API is now published! It will be served right away and visible in the API Catalog as well.

How does the code work?​

Let's look closer at how the Snapi code works!

main(code: string = null) =
  let
    airports = SQLServer.InferAndRead
      "raw", "example", "airports",
      host = "example-mssql.raw-labs.com",
      username = "******",
      password = "******"
    )
  in
    Collection.Filter(airports,
      x -> Nullable.IsNull(code) or x.iata_faa == code)

// The following test will run if you press the play button.
main("LIS")
  • Line 1 defines the main method. Its arguments will become query parameters in the URL call. These arguments all have default values (null to be precise), which means they are optional arguments. The value null (i.e. not defined) will be handled later during the filtering operation. In this case,t he only argument is the IATA airport code.
  • Lines 3-8 defines the data source. In this case, it is a SQL Server table. Credentials are hidden and can be specified using "Secrets".
  • Line 10 applies a filter. This will filter the SQL Server table so that we are left only with the rows whose values match the arguments (if defined).
  • The filter condition is defined in Line 11. The pattern Nullable.IsNull(code) or x.iata_faa == code is used to apply the filter on IATA code only if the code value was defined by the user. The Nullable.IsNull(code) will return true if the value is null. Otherwise, if the value is not null it means the user passed a code argument to filter on; in that case we check that x.iata_faa == code, i.e. the airport code info in the table matches the code passed by the user.
  • Line 13 defines the test to run when the play button is pressed.
warning

Never store sensitive information as clear text in the code. Instead use secrets, which are key/value pairs that are stored securely outside of the source code. Secrets can be accessed using the built-in function Environment.Secret.

Let's improve this API!​

Now that you understand the basic concepts, there's many improvements that can be done. Below is a list of pointers:

What's next!​

Take a look at other examples, or join us in our Community to learn more!

Ready to try it out?

Pick a template!

Otherwise, if you have questions/comments, join us in our Community!