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
6. Using curl
7. Using TypeScript
8. 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

| Feature | RQL | OData | PostgREST | | -------------------- | ---------------------------------- | -------------------------- | ------------------------------------------------------------------------------------------ | | Adoption | Niche | Widely adopted | Growing among PostgreSQL users | | Complexity | Moderate | High | Low to Moderate (tooling helps a lot) | | Flexibility | High | High | Tied to PostgreSQL capabilities | | Ease of Use | Requires learning Lisp-like syntax | Verbose syntax | Straightforward query parameters, but queries can be difficult to build and parse manually | | Tooling | Limited but sufficient | Extensive across platforms | Good support in JavaScript and Python | | Database Support | Database-agnostic | Database-agnostic | PostgreSQL only |

---

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.

FAQ: SQL to REST API Conversion

Q1: Why convert SQL queries to REST API requests?

A: Converting SQL queries to REST API requests allows for secure and controlled access to database operations over HTTP, enabling stateless communication suitable for web and mobile applications.

Q2: Can I use these query languages with any database?

A: RQL and OData are database-agnostic and can be used with various backends. PostgREST is specific to PostgreSQL. You can convert your database tables to OpenAPI to make onboarding your database easier for CRUD use-cases.

Q3: How do I handle complex SQL queries with joins?

A: With OData and RQL, you can expand related entities. PostgREST supports resource embedding and RPC for complex operations.

Q4: Are there security concerns when exposing database queries over REST?

A: Yes, always validate and sanitize input to prevent injection attacks. Use authentication and authorization mechanisms to secure your API.

Q5: Can I perform write operations (INSERT, UPDATE, DELETE) using these APIs?

A: Yes, all three support CRUD operations via appropriate HTTP methods like POST, PUT, PATCH, and DELETE.

Q6: Is there overhead in using these query languages?

A: There can be additional parsing and processing overhead. However, the benefits of standardization and expressiveness often outweigh the performance costs.

Q7: How do I choose the right query language for my project?

A: Consider factors like your team's familiarity, the complexity of queries, the need for standardization, and the specific features of each language.