How to Convert a SQL Query to an API Request

Many of the APIs we develop these days are simple CRUD (Create Read Update Delete) which are simply proxies over wherever your data is stored. Occasionally, we will need to create APIs that allow for both complex queries (ex. filters) while also being flexible to support different types of queries. A classic example is an analytics dashboard where users can perform sorting, filtering, and other transformations.

One approach to building these APIs is to simply expose your database via API. This allows frontend applications and external services to interact with our data securely and efficiently. Building SQL queries from clients is difficult and can lead to SQL injections, so how can we translate complex SQL queries into REST API requests? In this guide, we'll explore three popular query languages and frameworks—RQL, OData, and PostgREST—that help bridge the gap between SQL and REST.

Table of Contents

1. Resource Query Language (RQL)
2. Open Data Protocol (OData)
3. PostgREST
4. Comparative Analysis
5. Code Samples
   • Using curl
   • Using TypeScript
6. FAQ: SQL to REST API Conversion

Resource Query Language (RQL)

What is RQL?

RQL stands for Resource Query Language. It's a query language designed to be used in URIs to filter and manipulate data. RQL provides a standard way to express complex queries in a RESTful API.

• Official GitHub Repository: https://github.com/persvr/rql
• CloudBlue RQL Documentation: https://connect.cloudblue.com/community/developers/api/rql/

RQL Syntax and Examples

RQL uses a syntax inspired by Lisp, using parentheses to denote operations.

Example SQL Query:

SELECT * FROM users WHERE age >= 18 AND status = 'active' ORDER BY name ASC LIMIT 10 OFFSET 20;

Equivalent RQL Query:

/users?select()&ge(age,18)&eq(status,'active')&sort(+name)&limit(10,20)

Explanation:

• ge(age,18): age >= 18
• eq(status,'active'): status = 'active'
• sort(+name): ORDER BY name ASC
• limit(10,20): LIMIT 10 OFFSET 20

RQL Pros and Cons

Pros:

• Expressive Syntax: Can represent complex queries.
• Standardized: Provides a consistent query language across APIs.
• URL-Friendly: Designed to be used within URIs.

Cons:

• Learning Curve: Unfamiliar Lisp-like syntax.
• Complexity: Can become verbose for complex queries.
• Tooling: Less widespread support compared to OData.

RQL Tools and Libraries

• Node.js RQL Parser: rql-node
• Python RQL Parser: rql-py
• Middleware: Integrations available for Express.js and other frameworks.

Open Data Protocol (OData)

What is OData?

OData (Open Data Protocol) is an open protocol that allows the creation and consumption of queryable and interoperable RESTful APIs. It was initiated by Microsoft and has become an OASIS standard.

• Official Website: https://www.odata.org/documentation/

OData Syntax and Examples

OData uses URL query parameters prefixed with $ to denote operations.

Example SQL Query:

SELECT * FROM Products WHERE Price gt 20 AND Category eq 'Books' ORDER BY Name DESC SKIP 5 LIMIT 10;

Equivalent OData Query:

/Products?$filter=Price gt 20 and Category eq 'Books'&$orderby=Name desc&$skip=5&$top=10

Explanation:

• $filter: Filters records.
• $orderby: Sorts the results.
• $skip: Skips the first N records.
• $top: Limits the number of records returned.

OData Pros and Cons

Pros:

• Widely Adopted: Broad support across languages and platforms.
• Rich Query Options: Supports complex querying, including aggregation.
• Metadata: Can provide data model metadata.

Cons:

• Complex Specification: Can be heavy for simple use cases.
• Overhead: Might introduce unnecessary complexity.
• Learning Curve: The syntax can be verbose.

Odata Tools and Libraries

• OData Client for JavaScript: o.js
• Java OData Library: Olingo

PostgREST

What is PostgREST?

PostgREST is a standalone web server that turns your PostgreSQL database directly into a RESTful API. It maps HTTP requests to SQL queries efficiently and securely. Even if you aren't using Postgres, you can adopt the syntax structure (lets call it PostgREST-Flavored Query Syntax or PFQS).

• Official Query Syntax Docs: https://docs.postgrest.org/en/v12/references/api/tables_views.html

PostgREST Syntax and Examples

PostgREST uses query parameters to filter, order, and limit results.

Example SQL Query:

SELECT * FROM employees WHERE department = 'Sales' AND salary > 50000 ORDER BY hire_date LIMIT 5;

Equivalent PostgREST Query:

/employees?department=eq.Sales&salary=gt.50000&order=hire_date.asc&limit=5

Explanation:

• department=eq.Sales: department = 'Sales'
• salary=gt.50000: salary > 50000
• order=hire_date.asc: ORDER BY hire_date ASC
• limit=5: LIMIT 5

PostgREST Pros and Cons

Pros:

• Automatic API Generation: If you are using Postgres, there's no need to write controllers.
• Security: Leverages PostgreSQL's role-based access control.

Cons:

• Database-Specific: Only works with PostgreSQL. If you're using a different database then you'll have to write your own parser and query builder.
• Difficult to Write and Parse: At a glance, the syntax definitely seems harder to parse than RQL (the parenthesis for operations can be easily processed with recursion).

PostgREST Tools and Libraries

• PostgREST Docker Image: Docker Hub
• PostgREST Client Libraries:
  • JavaScript: postgrest-js
  • Python: PostgREST-py
• GUI Clients: Tools like Supabase use PostgREST under the hood.
• Standalone Converter: If you just want the syntax conversion from SQL to REST, checkout Supabase's sql-to-rest project.

We've actually built several samples using PostgREST syntax, including PostgREST + Neon and PostgREST + MySQL.

Comparative Analysis

Code Samples

Let's look at how to perform basic queries using curl and TypeScript.

Using curl

Example: Get all active users aged 18 or older, sorted by name.

RQL:

curl "http://api.example.com/users?ge(age,18)&eq(status,'active')&sort(+name)"

OData:

curl "http://api.example.com/users?$filter=age ge 18 and status eq 'active'&$orderby=name asc"

PostgREST:

curl "http://api.example.com/users?age=gte.18&status=eq.active&order=name.asc"

Using TypeScript

RQL with TypeScript

We'll use the rql-query library to build RQL.

npm install rql-query

import { Query } from "rql-query";

// Build the RQL query
const rqlQuery = new Query().gt("price", 100).toString();

// Make the API request
const response = await fetch(`http://api.example.com/products?${rqlQuery}`);
const data = await response.json();

console.log("Products over $100:", data);

OData with Typescript

Let's use odata-query for this one.

npm install odata-query

import { buildQuery } from "odata-query";

// Build the OData query
const odataQuery = buildQuery({
  filter: {
    price: { gt: 100 },
  },
});

// Make the API request
const response = await fetch(`http://api.example.com/products${odataQuery}`);
const data = await response.json();

console.log("Products over $100:", data);

PostgREST with TypeScript

We'll use the @supabase/postgrest-js library provided by Supabase.

npm install @supabase/postgrest-js

import { PostgrestClient } from "@supabase/postgrest-js";

// Initialize the PostgREST client
const postgrest = new PostgrestClient("http://api.example.com");

// Fetch products priced over $100
const { data, error } = await postgrest
  .from("products")
  .select("*")
  .gt("price", 100);

if (error) {
  console.error("Error fetching products:", error);
} else {
  console.log("Products over $100:", data);
}

Conclusion

Converting SQL queries to REST API requests doesn't have to be daunting. Tools like RQL, OData, and PostgREST provide robust solutions to bridge the gap between databases and RESTful services. Exposing your database via REST API can cause unforeseen security issues, even if you do protect from SQL injections. You should always use an API gateway like Zuplo to add security measures like authentication, rate-limiting, and even custom code to filter out malicious queries.