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": {}
}
}
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:
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
