# SQL to PostgREST translator

[![Tests](https://github.com/supabase-community/sql-to-rest/actions/workflows/tests.yml/badge.svg)](https://github.com/supabase-community/sql-to-rest/actions?query=branch%3Amain)
[![Package](https://img.shields.io/npm/v/@supabase/sql-to-rest)](https://www.npmjs.com/package/@supabase/sql-to-rest)
[![License: MIT](https://img.shields.io/npm/l/@supabase/sql-to-rest)](#license)

TypeScript library that translates SQL queries to the equivalent [PostgREST](https://github.com/PostgREST/postgrest)-compitable HTTP requests and client library code. Works on both browser and server.

### What is PostgREST?

[PostgREST](https://postgrest.org/) 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](#sql-is-a-very-open-language---how-can-it-all-translate-to-rest).

### Example

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

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

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

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

Outputs a `Promise<Statement>`:

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

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

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

const httpRequest = await renderHttp(statement)
```

Outputs a `Promise<HttpRequest>`:

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

### `renderSupabaseJs()`

Takes the intermediate `Statement` and renders it as [`supabase-js`](https://github.com/supabase/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<SupabaseJsQuery>`, 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?

<picture>
  <source media="(prefers-color-scheme: dark)" srcset="./assets/diagram-dark.png">
  <img alt="SQL to REST diagram" src="./assets/diagram-light.png">
</picture>

1. The SQL string is parsed into a PostgreSQL abstract syntax tree (AST) using [`pg-parser`](https://github.com/supabase-community/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`](https://github.com/supabase/supabase-js) code (which wraps PostgREST). Other languages are on the roadmap (PR's welcome!)

## Roadmap

### SQL features

#### Statements

- [x] `select` statements ([`GET` requests](https://postgrest.org/en/latest/references/api/tables_views.html#read))
- [ ] `insert` statements ([`POST` requests](https://postgrest.org/en/latest/references/api/tables_views.html#insert))
  - [ ] `on conflict update` ([upsert](https://postgrest.org/en/latest/references/api/tables_views.html#upsert))
- [ ] `update` statements ([`PATCH` requests](https://postgrest.org/en/latest/references/api/tables_views.html#update))
- [ ] `delete` statements ([`DELETE` requests](https://postgrest.org/en/latest/references/api/tables_views.html#delete))
- [ ] `explain` statements ([Execution plan](https://postgrest.org/en/latest/references/observability.html#execution-plan))

#### [Filters](https://postgrest.org/en/latest/references/api/tables_views.html#horizontal-filtering)

##### 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](https://postgrest.org/en/latest/references/api/tables_views.html#ordering)

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

#### [Pagination](https://postgrest.org/en/latest/references/api/pagination_count.html)

- [x] `limit` (`limit`)
- [x] `offset` (`offset`)
- [ ] HTTP range headers

#### [Aggregates](https://postgrest.org/en/latest/references/api/aggregate_functions.html)

##### 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](https://postgrest.org/en/latest/references/api/resource_embedding.html) (Resource Embedding)

SQL joins are supported using PostgREST resource embeddings with the [spread `...` syntax](https://postgrest.org/en/latest/references/api/resource_embedding.html#spread-embedded-resource) (flattens joined table into primary table).

#### [Aliases](https://postgrest.org/en/latest/references/api/tables_views.html#renaming-columns)

- [x] column aliases
- [x] table aliases

#### [Casts](https://postgrest.org/en/latest/references/api/tables_views.html#casting-columns)

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

#### [JSON columns](https://postgrest.org/en/latest/references/api/tables_views.html#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`](https://github.com/supabase/supabase-js)
- [ ] [`supabase-flutter`](https://github.com/supabase/supabase-flutter)
- [ ] [`supabase-swift`](https://github.com/supabase/supabase-swift)
- [ ] [`supabase-py`](https://github.com/supabase-community/supabase-py)
- [ ] [`supabase-csharp`](https://github.com/supabase-community/postgrest-csharp)
- [ ] [`supabase-kt`](https://github.com/supabase-community/supabase-kt)

## FAQs

### Are you parsing SQL from scratch?

Thankfully no. We use [`pg-parser`](https://github.com/supabase-community/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.

### 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](./src/renderers/http.test.ts) for every feature supported. The vast majority of PostgREST features have been implemented, but it doesn't cover 100% yet (see [Roadmap](#roadmap)). If you discover an error in the translation, please [submit an issue](https://github.com/supabase-community/sql-to-rest/issues/new/choose).

## License

MIT