--- title: "How to Convert a SQL Query to an API Request" description: "You can convert SQL Queries into API requests easily using the RQL or OData formats for sending requests, or by using a framework like PostgREST on top of your database." canonicalUrl: "https://zuplo.com/learning-center/sql-query-to-api-request" pageType: "learning-center" authors: "adrian" tags: "API Design, API Tooling, Tutorial" image: "https://cdn.zuplo.com/cdn-cgi/image/fit=crop,width=1200,height=630/www/media/posts/2024-11-20-sql-query-to-api-request/image.png" --- 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)](#resource-query-language-rql) 2. [Open Data Protocol (OData)](#open-data-protocol-odata) 3. [PostgREST](#postgrest) 4. [Comparative Analysis](#comparative-analysis) 5. [Code Samples](#code-samples) - [Using curl](#using-curl) - [Using TypeScript](#using-typescript) 6. [FAQ: SQL to REST API Conversion](#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](https://github.com/persvr/rql) - **CloudBlue RQL Documentation**: [https://connect.cloudblue.com/community/developers/api/rql/](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:** ```sql 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](https://www.npmjs.com/package/rql) - **Python RQL Parser**: [rql-py](https://pypi.org/project/rql/) - **Middleware**: Integrations available for Express.js and other frameworks. ## Open Data Protocol (OData) ### What is OData? [**OData** ](https://www.odata.org/)(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/](https://www.odata.org/documentation/) ### OData Syntax and Examples OData uses URL query parameters prefixed with `$` to denote operations. **Example SQL Query:** ```sql 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](https://www.npmjs.com/package/odata) - **Java OData Library**: [Olingo](https://olingo.apache.org/) ## PostgREST ### What is PostgREST? [**PostgREST**](https://docs.postgrest.org/en/stable/) 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](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:** ```sql 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](https://hub.docker.com/r/postgrest/postgrest/) - **PostgREST Client Libraries**: - **JavaScript**: [postgrest-js](https://github.com/supabase/postgrest-js) - **Python**: [PostgREST-py](https://pypi.org/project/postgrest/) - **GUI Clients**: Tools like [Supabase](https://supabase.io/) 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](https://github.com/supabase-community/sql-to-rest). We've actually built several samples using PostgREST syntax, including [PostgREST + Neon](/learning-center/neon-postgrest-rest-api) and [PostgREST + MySQL](/learning-center/mysql-postgrest-rest-api). --- ## 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:** ```bash curl "http://api.example.com/users?ge(age,18)&eq(status,'active')&sort(+name)" ``` **OData:** ```bash curl "http://api.example.com/users?$filter=age ge 18 and status eq 'active'&$orderby=name asc" ``` **PostgREST:** ```bash 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. ```bash npm install rql-query ``` ```typescript 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. ```bash npm install odata-query ``` ```typescript 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. ```bash npm install @supabase/postgrest-js ``` ```typescript 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](https://zuplo.com/features/rate-limiting), and even [custom code](https://zuplo.com/features/programmable) to filter out malicious queries.