SQL by Example

This guide shows how to create APIs in RAW using SQL. We will create an API and modify it gradually:

• We start by creating an API that lists the contents of a table in a PostgreSQL database;
• We then modify this API to receive a query parameter and apply it as a filter;
• Finally, we do a join between two distinct databases and serve the results as a new API.

info

This guide assumes basic familiarity with RAW. If you are just getting started, we recommend first checking our Getting Started guides.

Creating an API that lists contents from a table

The first step is to establish a new database connection by adding a new credential. Consider our publicly accessible PostgreSQL database. You can add this database as a RAW credential either programmatically using our API, or through our UI by following this guide. After the credential is added, all tables within the specified schema become available for querying on our platform.

As shown above, within the example schema, you will find a table called airports, which contains information from some airports around the globe.

The table contains the following fields:

Name	Type
airport_id	integer
name	varchar(256)
city	varchar(256)
country	varchar(512)
iata_faa	varchar(3)
icao	varchar(6)
latitude	numeric(9,3),
longitude	numeric(9,3),
altitude	numeric(9,3)
timezone	integer
dst	varchar(128)
tz	varchar(256)

To retrieve all airports in Iceland, you can use the following query:

SELECT * FROM <credential_name>.airports WHERE country='Iceland'

Note that <credential_name> refers to the name of the PostgreSQL credential specified in RAW. For instance, if the credential is named db01, then the aforementioned query would be formulated as follows:

SELECT * FROM db01.airports WHERE country='Iceland';

Modifying the example to add a query parameter

APIs in RAW can receive parameters. In SQL, query parameters are defined using a special syntax, which consists of a colon followed by the query parameter name.

For instance, if you define an API such as:

SELECT * FROM db01.airports WHERE iata_faa LIKE :iata

Notice the condition iata_faa LIKE :iata.

The ":iata" SQL syntax automatically defines a query parameter called "iata". If you create an API with code above, then this API requires a parameter called "iata", which is mandatory and must be passed by the user to call the endpoint.

Combining two tables from two distinct remote databases

We now demonstrate how to join data from two different remote databases. For this example, we continue to use the PostgreSQL table with airports information, but we add a MySQL table with trips data.

First, you must add credentials in RAW to both systems.

Let's assume there is a trips table in MySQL with a destination column, and the airports table in PostgreSQL with an `iata_faa`` column. If so, you can join these tables based on these columns. Here's an example SQL query:

SELECT
    trips.*, airports.name, airports.city
FROM
    mysql_credential.trips
JOIN
    postgres_credential.airports
ON
    trips.destination = airports.iata_faa;

In this query, mysql_credential and postgres_credential are the names of the credentials you've set up for your MySQL and PostgreSQL databases, respectively.

This query retrieves all columns from the trips table and includes the name and city of the airport from the airports table. It joins these tables based on the destination column in trips matching the iata_faa column in airports.