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