Oct 142022

Author

2 min read

Tutorial series - #5 API routing and QueryBuilder

Discover API routing mechanism and explore wonderful database queries with QueryBuilder

Tutorial series - #5 API Routing and Querybuilder

In this tutorial you will discover two amazing features of Total.js : API routing and Querybuilder. API routing is newly added to total4 as an extra functionality. Querybuilder is a simple ORM that is used within total.js apps with multiple databases compatibility.

API routing

API routing is an internal mechanism used to expose API endpoints in a very simple way. The mechanism is designed to focus on handling maximum number of operations with minimum enpoints. Therefore API routing only works with Total.js Schema and their operations.

How does it work?

You can create one enpoint (e.g /api/) and use it to target infinite number of operations within your application. All you have to do is call that endpoint with POST method and have in the body of the request, a json payload to specify what operation of what schema to target.

Example :

Download empty project

$ git clone https://github.com/totaljs/emptyproject-restservice api
$ cd api
$ npm install total4
$ node index.js

Declare your API routes

// in /controllers/api.js


// Users
ROUTE('API  /api/   -users_query            *Users    --> query');
ROUTE('API  /api/   -users_read/{id}        *Users    --> read');
ROUTE('API  /api/   +users_insert           *Users    --> check insert (response)');
ROUTE('API  /api/   +users_update/{id}      *Users    --> update');
ROUTE('API  /api/   -users_remove/{id}      *Users    --> remove');
ROUTE('API  /api/   -users_refresh          *Users    --> refresh');

// ...

Create Schema and operations


// in /schemas/users.js

NEWSCHEMA('Users', function(schema) {

    // Fields definition
   
    schema.setQuery(function($) { 
        $.callback([]);
    });
    
    schema.setRead(function($) { 
        $.callback({}); 
    });
    
    schema.setInsert(function($, model) { 
        $.done(model.id) 
    });
    
    schema.setUpdate(function($, model) { 
        $.done(model.id) 
    });
    
    schema.setRemove(function($) { 
        $.success();
    });

    // Async/Await workflow
    
    schema.addWorkflow('refresh', async function ($) {
        var data = await FUNC.fetchData();
        
        if (!data) {
            $.invalid(404);
            return;
        }
        
        $.callback(data);
        
    });
    // Custom workflows
});

Call endpoint from external

POST /api/
Content-Type: application/json

{
    "schema": "users_query?page=1",
    "data": {}
}

Output

[]

*Required

The request body is JSON format and expects two parameters:

schema ---> (schema_name/{dynamic_arg_1}/{dynamic_arg_2}?query=arguments)

to specify schema (supports params and query).

data ---> data payload (optional).

Good to know

The routing can contain two times +/- characters.

First : ROUTE('+API ...') or ROUTE('-API ...') for authorization purpose. (Read more here).
Second +schema_name or -schema_name to specify if data reception is expected or not.

Websocket API

Yes. The API Routing supports the websocket protocol and we recommand it too. Its declaration is same like the standard API Routing, except that the route must contain identifiers in the form @YOUR_NAME instead of relative URL address. Let us try it:

Declare your Websocket API routes

// Users
ROUTE('API  @api   -users_remove/{id}   *Users --> remove');

// ...

// IMPORTANT: socket route
ROUTE('SOCKET / @api');

Websocket message:

{
    "TYPE": "api",
    "callbackid": "CUSTOM_CALLBACK_ID", // The value will be returned back
    "data": {
        "schema": "schema_name/{dynamic_arg_1}/{dynamic_arg_2}?query=arguments",
        "data": {}
    }
}

Documentation

QueryBuilder

Querybuilder is a lightweight ORM (Object Relational Mapping) that supports various database engines. QueryBuilder acts like an abstraction layer of the database management system in order to let you manipulate data in a comfortable way. Without ORM in your application, all your database queries would look like this 😢 :

SELECT id, value, type, name FROM cl_config WHERE id = 'openmail';

Instead of that, you can use Querybuilder 😋 which gives you many benefits like:

database operations
query operations for filtering data
automatic error handling operations
operations audits
etc

Querybuilder has many implementations and support many database engines:

Nosql embedded (TextDB) driver included in the Total.js core
QueryBuilder for PostgresSQL
QueryBuilder for MySQL
QueryBuilder for SQLite

Example

Let us move quickly to the example. We are taking our API Routing example above and we are updating schema file.

QueryBuilder with NOSQL

Replace with the following

// in /schemas/users.js

NEWSCHEMA('Users', function(schema) {

    // Fields definition
   
    schema.setQuery(function($) { 
        var builder = DB().find('nosql/users'); // querybuilder instance
        builder.autoquery($, 'id:String, name:String, isconfirmed:Boolean, dtcreated:Date, ')
        builder.fields('id,name,email,phone').where('isremoved', false).take(100);
        builder.callback($.callback);
    });
    
    schema.setRead(function($) { 
        var builder = DB().one('nosql/users'); // database operations
        builder.id($.id).where('isremoved', false); // filtering operations
        builder.error(404) // automatic error handling
        builder.callback($.callback);
    });

    schema.setInsert(function($, model) { 
        model.id = UID();
        var builder = DB().insert('nosql/users', model);
        builder.audit($, 'Inserted User : {data.name}'); // audit
        builder.callback($.done(model.id));
    });
    schema.setUpdate(function($, model) { $.done(model.id) });
    schema.setRemove(function($) { $.success() });

    // Custom workflows
});

QueryBuilder with Postgresql

First: install QueryBuilder inplementation for postgresql

$ npm install querybuilderpg

Second: Connect to your postgresql database

// in /definitions/db.js
require('querybuilderpg').init('default', 'postgres://user:password@localhost:port/database_name', ERROR('Postgres'));

Replace with the following

// in /schemas/users.js

NEWSCHEMA('Users', function(schema) {

    // Fields definition
   
    schema.setQuery(function($) { 
        var builder = DB().find('tbl_user'); // querybuilder instance
        builder.autoquery($, 'id:String, name:String, isconfirmed:Boolean, dtcreated:Date, ')
        builder.fields('id,name,email,phone').where('isremoved', false).take(100);
        builder.callback($.callback);
    });
    
    schema.setRead(function($) { 
        var builder = DB().one('tbl_user'); // database operations
        builder.id($.id).where('isremoved', false); // filtering operations
        builder.error(404) // automatic error handling
        builder.callback($.callback);
    });

    schema.setInsert(function($, model) { 
        model.id = UID();
        var builder = DB().insert('tbl_user', model);
        builder.audit($, 'Inserted User : {data.name}'); // audit operation
        builder.callback($.done(model.id));
    });

    schema.setUpdate(function($, model) {
        $.done(model.id);
    });

    schema.setRemove(function($) {
        $.success();
    });

    // Custom workflows
});

Documentation

Tutorial series - #5 API Routing and Querybuilder

API routing

QueryBuilder

Other posts from Total.js Platform