Skip to main content

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.

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.

example schema

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:


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';

Example 1 - Results

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.

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

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.