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.

$3

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.

$3

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.

$3

The following SQL:

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

Will get translated to:

_cURL_

`shell
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_

`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_

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

title,
description

)
.ilike('description', '%cheese%')
.order('title', { ascending: false })
.range(10, 15)
`

Install

`shell
npm i @supabase/sql-to-rest
`

`shell
yarn add @supabase/sql-to-rest
`

Usage

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

`js
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,
})
`

$3

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.

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

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

Outputs a Promise:

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

$3

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

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

const statement = await processSql(
select
*
from
books
)

const httpRequest = await renderHttp(statement)
`

Outputs a Promise:

`js
{
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

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

// ...

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

Outputs:

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

#### Raw HTTP

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

// ...

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

Outputs:

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

$3

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

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

const statement = await processSql(
select
*
from
books
)

const { code } = await renderSupabaseJs(statement)
`

Outputs a Promise, where code contains:

`js
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 pg-parser, a JavaScript SQL parser that uses C code from the official PostgreSQL codebase (compiled to WASM). Supports Postgres 17 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

$3

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

$3

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

FAQs

$3

Thankfully no. We use pg-parser which compiles source code from the real PostgreSQL parser to WASM and wraps it in JavaScript bindings.

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.

$3

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.

$3

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