SQL by Example

This guide shows how to create APIs 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.

Creating an API that lists contents from a table

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. 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 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.

Documenting query parameters

To document query parameters, specific markup can be added to the top comment block. In the example below, we include a formal description of the iata parameter and specify its type. The @return keyword documents the query output.

-- @param iata The airport IATA code
-- @type iata varchar
-- @return Info about the airport matching the given IATA code
SELECT * FROM db01.airports
WHERE iata_faa LIKE :iata

You can specify a default parameter value with the @default keyword. This value should be a valid SQL expression that aligns with its associated @type (if present). The example query below demonstrates how to filter trips using a date parameter for which a default value is declared.

-- @type min_date date
-- @default min_date '2023-01-01'
SELECT * FROM db01.trips
WHERE departure_date >= :min_date

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 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.