Reference

Importing the library

The main interaction with the library is done through the default exported object (Subzero) and the getIntrospectionQuery function.

import Subzero, {
    getIntrospectionQuery,
    fmtPostgreSqlEnv,
    QueryEnv
} from '@subzerocloud/rest';

Initializing the library

The first step in initializing the library is defining the structure of the database in a JSON object. This can be done manually or by using the getIntrospectionQuery function.

const { query, parameters } = getIntrospectionQuery(
    'postgresql', // database type
    ['public'], // the schema name that is exposed to the HTTP api
);
// run the query and get the result
const result = await dbPool.query(query, parameters);
const schema = JSON.parse(result.rows[0].json_schema);

import permissions from './permissions'
import custom_relations from './relations'

const placeholders = new Map([
    ['relations.json', custom_relations],
    ['permissions.json', permissions],
])

Once we have the schema object (which in essence is a JSON representation of the database schema) we can initialize the library.

const subzero = new Subzero(
     // the database type
    'postgresql',

    // the schema object we got after introspection
    schema,

    // a list of allowed functions that can be used in the select parameter
    ['concat', 'sum', 'avg', 'max']
);

Handling requests

When dealing with tools that work in similar fashion to PostgREST (ex. Postgraphile, Hasura), what essentially happens is the system will inspect the incoming http request, generate a single SQL query and then execute it against the database and return the result to the http client.

With this in mind, instead of exposing just a handleRequest function, to allow for maximum flexibility, the library exposes as it's main interaction point the fmtStatement function.

First we define a list of environment variables that can be used by the sql query (underlying RLS rules, views, triggers)

let queryEnv: QueryEnv = [
    ['role', 'authenticated'],
    ['request.method', req.method],
    ['request.headers', JSON.stringify(req.headers)],
    ['request.get', JSON.stringify(Object.fromEntries(url.searchParams))],
    //...
];

Optionally we can run a query that sets these environment variables for the current transaction

// generate the SQL query that sets the env variables for the current request
const { 
    query: envQuery, 
    parameters: envParameters
} = fmtPostgreSqlEnv(queryEnv);
await db.query(envQuery, envParameters);

The next step is to generate the main SQL query from the request object

const { query, parameters } = await subzero.fmtStatement(
     // the schema name that is exposed to the HTTP api (ex: public, api)
    'public',

     // the base path of the HTTP api (ex: /rest, /api)
    '/rest',
    
    // the role of the user making the request (ex: anonymous, authenticated)
    // these are database roles and usually you would read it from the JWT token
    'authenticated',

    // the http request object
    req,

    // a list of environment variables that can be used but the sql query (underlying RLS rules, views, triggers)
    queryEnv,

    // optional, the maximum number of rows that can be returned by the query
    // 1000,
);

Then we run the query and get the result (the result is always a single row)

const result = (await db.query(query, parameters)).rows[0];

Finally we return the result to the http client

// ...
// return the result to the http client
const status = Number(result.status) || 200;
let response_headers = result.response_headers
    ? JSON.parse(result.response_headers)
    : {};
response_headers['content-length'] = Buffer.byteLength(result.body);
response_headers['content-type'] = 'application/json';
res.writeHead(status, response_headers).end(result.body);