Overview - subZero Documentation

subZero REST library is a Rust library (with JavaScript/TS bindings) that will generate a PostgREST compatible REST API.

Besides supporting PostgreSQL (like PostgREST), it can also be used with SQLite, MySQL, Clickhouse and all their variants.

In addition to the standard PostgREST api format, it adds a few extra features like:

Calling functions in select parameter (ex. select=id,sku:$concat('#',id))
Analytical queries using aggregate and window functions in combination with groupby parameter

For a complete list of features and capabilities supported by a PostgREST compatible API see here

Importing

import debug from 'debug';
import { 
    init as restInit, 
    getRequestHandler as rest, 
    getSchemaHandler, getPermissionsHandler, onSubzeroError
} from '@subzerocloud/rest';
import { Client, Pool } from 'pg';
// import Client from 'better-sqlite3'; // when using sqlite as a database

Initializing

const dbPool = new Pool({ connectionString: 'postgres://...'});
// when using sqlite as a database
// const dbPool = new Client('/path/to/sqlite.db');
const app = express();
const dbSchemas = ['public']; // list of schemas to expose
// ....
// run this on server initialization
await restInit(
    app, // app is an express app
    dbPool, // dbPool is a pg.Pool or a better-sqlite3 client
    dbSchemas, // list of schemas to expose,
    {   // optional configuration
        debugFn: debug('subzero:rest'),
        
        // check internal permissions (default: true)
        // useInternalPermissionsCheck: true,

        // when introspecting the database, include all roles
        // (by default roles with login permissions are ignored)
        // includeAllDbRoles: false,

        // define custom permissions using javascript/json objects
        // permissions: [],
        
        // define custom relations between tables
        // (useful when introspection does not detect the relations correctly)
        // customRelations: [],

        // list of functions that can be called in select parameter using $functionName
        // allowedSelectFunctions: ['sum', 'count', 'avg', 'min', 'max', ...],

        // database connection settings related to connection retries
        // dbMaxConnectionRetries: 10,
        // dbMaxConnectionRetryInterval: 10, //seconds

        // the names of the instances that will be stored in express app context
        // this is useful when you want to use two instances of the library 
        // schemaInstanceName: '__schema__',
        // subzeroInstanceName: '__subzero__',
        // dbPoolInstanceName: '__dbPool__',
        // contextEnvInstanceName: '__contextEnv__',
    }
);

Handling requests

const restHandler = rest(dbSchemas, {
    // optional configuration
    debugFn: debug('subzero:rest'),

    // the search path to use when executing queries (postgresql only)
    // dbExtraSearchPath: ['public'],

    // execute the query in a transaction
    // wrapInTransaction: true,

    // execute the query that sets the
    // request environment variables in a transaction
    // setDbEnv: true,

    // the names of the instances in express app context to use
    // subzeroInstanceName: '__subzero__',
    // dbPoolInstanceName: '__dbPool__',
    // contextEnvInstanceName: '__contextEnv__',

    // the name of the anonymous role
    // (requests coming in without a JWT token)
    // dbAnonRole: 'anonymous',
});
app.use('/rest', restHandler);

You will most probably want to allow calls to the rest api only to authenticated users, as such you will have a middleware that will check the JWT token and set the user id in the request environment variables. You can chain the middleware with the rest handler like this:

const isAuthenticated = passport.authenticate('jwt', { session: false });
app.use('/rest', isAuthenticated, restHandler);

If in the frontend you use the subzero SDK to call the rest api, attach the same handler to the following path:

app.use('/rest/v1', isAuthenticated, restHandler);

Initializing the client

In the frontend code, you can use the SDK client to call the rest api. The advantage of using the client is that it will automatically add the JWT token to the request headers and also auto refresh the token when it expires.

import {createClient, PostgrestResponse} from '@subzerocloud/ra-subzero';
// notice there is no /rest in the url
// you can use window.location.origin
// when the static files are served from the same server
const client = createClient('http://localhost:5173');