SQL to PostgREST translator

TypeScript library that translates SQL queries to the equivalent PostgREST-compitable HTTP requests and client library code. Works on both browser and server.

What is PostgREST?

PostgREST is a REST API that auto-generates endpoints based on relations in your database (public schema only by default). It uses JWTs and RLS policies to handle authorization.

How can SQL be converted to REST?

The PostgREST API supports a lot of SQL-like features including:

• Vertical filtering (select only the columns you care about)
• Horizontal filtering (filter rows by comparing data in columns: =, >, <, in, like, etc)
• Sorting
• Limit and offset
• Resource embeddings (joins to other relations using foreign keys)
• Aggregate operations (count(), sum(), avg(), min(), max())
• Nested AND/OR expressions
• Aliasing and casting
• JSON columns (selecting, filtering, and sorting)

This library takes the SQL input and translates it to 1-to-1 to the equivalent PostgREST syntax. Any unsupported SQL will throw an error.

Example

The following SQL:

select
  title,
  description
from
  books
where
  description ilike '%cheese%'
order by
  title desc
limit
  5
offset
  10

Will get translated to:

cURL

curl -G http://localhost:54321/rest/v1/books \
  -d "select=title,description" \
  -d "description=ilike.*cheese*" \
  -d "order=title.desc" \
  -d "limit=5" \
  -d "offset=10"

Raw HTTP

GET /rest/v1/books?select=title,description&description=ilike.*cheese*&order=title.desc&limit=5&offset=10 HTTP/1.1
Host: localhost:54321

supabase-js

const { data, error } = await supabase
  .from('books')
  .select(
    `
    title,
    description
    `
  )
  .ilike('description', '%cheese%')
  .order('title', { ascending: false })
  .range(10, 15)

Install

npm i @supabase/sql-to-rest

yarn add @supabase/sql-to-rest

Usage

Note: This library is pre-1.0, so expect slight API changes over time.

import { processSql, renderHttp, formatCurl } from '@supabase/sql-to-rest'

// Process SQL into intermediate PostgREST AST
const statement = await processSql(`
  select
    *
  from
    books
`)

// Render the AST into an HTTP request
const httpRequest = await renderHttp(statement)

// Format the HTTP request as a cURL command (requires base URL)
const curlCommand = formatCurl('http://localhost:54321/rest/v1', httpRequest)

console.log(curlCommand)
// curl http://localhost:54321/rest/v1/books

// Or use it directly
const response = await fetch(`http://localhost:54321/rest/v1${httpRequest.fullPath}`, {
  method: httpRequest.method,
})

processSql()

Takes a SQL string and converts it into a PostgREST abstract syntax tree (AST) called a Statement. This is an intermediate object that can later be rendered to your language/protocol of choice.

import { processSql } from '@supabase/sql-to-rest'

const statement = await processSql(`
  select
    *
  from
    books
`)

Outputs a Promise<Statement>:

{
  type: 'select',
  from: 'books',
  targets: [
    {
      type: 'column-target',
      column: '*',
      alias: undefined,
    },
  ],
  filter: undefined,
  sorts: [],
  limit: undefined
}

renderHttp()

Takes the intermediate Statement and renders it as an HTTP request.

import { processSql, renderHttp } from '@supabase/sql-to-rest'

const statement = await processSql(`
  select
    *
  from
    books
`)

const httpRequest = await renderHttp(statement)

Outputs a Promise<HttpRequest>:

{
  method: 'GET',
  path: '/books',
  params: URLSearchParams {},
  fullPath: [Getter] // combines path with the query params
}

An HttpRequest can also be formatted as a cURL command or as raw HTTP.

cURL command

import {
  // ...
  formatCurl,
} from '@supabase/sql-to-rest'

// ...

const curlCommand = formatCurl('http://localhost:54321/rest/v1', httpRequest)

Outputs:

curl http://localhost:54321/rest/v1/books

Raw HTTP

import {
  // ...
  formatHttp,
} from '@supabase/sql-to-rest'

// ...

const rawHttp = formatHttp('http://localhost:54321/rest/v1', httpRequest)

Outputs:

GET /rest/v1/books HTTP/1.1
Host: localhost:54321

renderSupabaseJs()

Takes the intermediate Statement and renders it as supabase-js client code.

import { processSql, renderSupabaseJs } from '@supabase/sql-to-rest'

const statement = await processSql(`
  select
    *
  from
    books
`)

const { code } = await renderSupabaseJs(statement)

Outputs a Promise<SupabaseJsQuery>, where code contains:

const { data, error } = await supabase.from('books').select()

The rendered JS code is automatically formatted using prettier.

How does it work?

1. The SQL string is parsed into a PostgreSQL abstract syntax tree (AST) using libpg-query-node, a JavaScript SQL parser that uses C code from the official PostgreSQL codebase (compiled to WASM for browser targets, NAPI for Node targets). Supports Postgres 15 syntax.
2. The PostgreSQL AST is translated into a much smaller and simpler PostgREST AST. Since PostgREST supports a subset of SQL syntax, any unsupported SQL operation will throw an UnsupportedError with a description of exactly what wasn't supported.
3. The intermediate PostgREST AST can be rendered to your language/protocol of choice. Currently supports HTTP (with cURL and raw HTTP formatters), and supabase-js code (which wraps PostgREST). Other languages are on the roadmap (PR's welcome!)

Roadmap

SQL features

Statements

• [x] select statements (GET requests)
• [ ] insert statements (POST requests)
  • [ ] on conflict update (upsert)
• [ ] update statements (PATCH requests)
• [ ] delete statements (DELETE requests)
• [ ] explain statements (Execution plan)

Filters

Column operators

• [x] = (eq)
• [x] > (gt)
• [x] >= (gte)
• [x] < (lt)
• [x] <= (lte)
• [x] <> or != (neq)
• [x] like (like)
• [x] ilike (ilike)
• [x] ~ (match)
• [x] ~* (imatch)
• [x] in (in)
• [ ] is (is): partial support, only is null for now
• [ ] is distinct from (isdistinct)
• [x] @@ (fts, plfts, phfts, wfts)
• [ ] @> (cs)
• [ ] <@ (cd)
• [ ] && (ov)
• [ ] << (sl)
• [ ] >> (sr)
• [ ] &< (nxr)
• [ ] &> (nxl)
• [ ] -|- (adj)

Logical operators

• [x] not (not)
• [x] or (or)
• [x] and (and)
• [ ] all (all)
• [ ] any (any)

Ordering

• [x] asc (asc)
• [x] desc (desc)
• [x] nulls first (nullsfirst)
• [x] nulls last (nullslast)

Pagination

• [x] limit (limit)
• [x] offset (offset)
• [ ] HTTP range headers

Aggregates

Functions

• [x] count()
• [x] sum()
• [x] avg()
• [x] max()
• [x] min()

Features

• [x] aggregate over entire table
• [x] aggregate on joined table column
• [x] aggregate with group by
• [x] aggregate with group by on joined table column

Joins (Resource Embedding)

SQL joins are supported using PostgREST resource embeddings with the spread ... syntax (flattens joined table into primary table).

Aliases

• [x] column aliases
• [x] table aliases

Casts

• [x] column casts (in select target only)
• [x] aggregate function casts (in select target only)

JSON columns

JSON columns (eg. select metadata->'person'->>'name') are supported in the following places:

• [x] select targets
• [x] filters
• [x] sorts

Renderers

• [x] HTTP
  • [x] cURL formatter
  • [x] Raw HTTP formatter
• [x] supabase-js
• [ ] supabase-flutter
• [ ] supabase-swift
• [ ] supabase-py
• [ ] supabase-csharp
• [ ] supabase-kt

FAQs

Are you parsing SQL from scratch?

Thankfully no. We use libpg-query-node which takes source code from the real PostgreSQL parser and wraps it in JavaScript bindings. It compiles the C code into WASM for browser environments and uses native NAPI bindings for server environments.

This means we never have to worry about the SQL itself getting parsed incorrectly - it uses the exact same code as the actual PostgreSQL database. This library uses code from PostgreSQL 15.

SQL is a very open language - how can it all translate to REST?

It can't. PostgREST only supports a subset of SQL-like features (by design), so this library only translates features that can be mapped 1-to-1.

When it detects SQL that doesn't translate (eg. sub-queries), it will throw an UnsupportedError with a description of exactly what couldn't be translated.

How can I be confident that my SQL is translating correctly?

We've built unit tests for every feature supported. The vast majority of PostgREST features have been implemented, but it doesn't cover 100% yet (see Roadmap). If you discover an error in the translation, please submit an issue.

License

MIT