Data Access


This page explains the low level functions exposed by the library and is intended for advanced users and curious minds. In most cases you'll be leveraging the library through express route handler.

Importing the library

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

import Subzero, {
} 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);

The getIntrospectionQuery function has a 3rd optional parameter that allows replacing some placeholders with json objects (with specific structure). This is the main mechanism through which the library allows configuration of custom relations between entities (that ones that can not be automatically detected) and custom application level permissions.

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

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

Check out a sample for how to specify permissions and relations

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

    // the schema object we got after introspection

    // 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)

     // the base path of the HTTP api (ex: /rest, /api)
    // 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

    // the http request object

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

    // 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);
REST Library Overview