Two cases for handling relational data in Total.js

This blog post describes a relational view for storing data in Total.js framework. Examples support these operations: insert and update. This blog post is not for developers who use NoSQL databases. For testing you have to install PostgreSQL database, if you use macOS just use https://postgresapp.com.

A PostgreSQL database structure for testing

create your own testing DB

CREATE TABLE public.tbl_user
(
    id character varying(22) NOT NULL,
    firstname character varying(50),
    lastname character varying(50),
    email character varying(120),
    datecreated timestamp without time zone,
    dateupdated timestamp without time zone,
    CONSTRAINT tbl_user_pkey PRIMARY KEY (id)
)
WITH (
    OIDS = FALSE
);

CREATE TABLE public.tbl_user_address
(
    iduser character varying(22),
    type character varying(15),
    street character varying(50),
    zip character varying(20),
    city character varying(50),
    country character varying(50),
    state character varying(50)
)
WITH (
    OIDS = FALSE
);

CREATE TABLE public.tbl_user_address
(
    iduser character varying(22),
    type character varying(15),
    street character varying(50),
    zip character varying(20),
    city character varying(50),
    country character varying(50),
    state character varying(50)
)
WITH (
    OIDS = FALSE
);

CREATE TABLE public.tbl_user_hobby
(
    iduser character varying(22),
    name character varying(50)
)
WITH (
    OIDS = FALSE
);

CREATE TABLE public.tbl_user_job
(
    iduser character varying(22),
    name character varying(50),
    year smallint
)
WITH (
    OIDS = FALSE
);

Workflow: storing data in one method

we store all data using the schema.setSave() delegate

require('total.js');
require('sqlagent/pg').init('postgre://user:password@127.0.0.1/database');

NEWSCHEMA('Hobby').make(function(schema) {
    schema.define('name', 'String(50)', true);
});

NEWSCHEMA('Job').make(function(schema) {
    schema.define('name', 'String(50)', true);
    schema.define('year', Number);
});

NEWSCHEMA('Address').make(function(schema) {
    schema.define('street', 'Capitalize(50)', true);
    schema.define('zip', 'Zip', true);
    schema.define('city', 'Capitalize(50)', true);
    schema.define('country', 'Capitalize(50)');
    schema.define('state', 'Capitalize(50)', true);
});

NEWSCHEMA('User').make(function(schema) {

    schema.define('id', 'UID');
    schema.define('firstname', 'Capitalize(50)');
    schema.define('lastname', 'Capitalize(50)');
    schema.define('billingaddress', 'Address', true);
    schema.define('postaladdress', 'Address', true);
    schema.define('jobs', '[Job]');
    schema.define('hobbies', '[Hobby]');
    schema.define('email', 'Email');

    schema.setSave(function(error, model, options, callback, controller) {

        var insert = model.id ? false : true;
        var sql = DB(error);

        // Is it a new user?
        if (insert)
            model.id = UID();

        // Need a transaction?
        // sql.begin();

        sql.save('save', 'tbl_user', insert, function(builder, isInsert) {

            builder.set('firstname', model.firstname);
            builder.set('lastname', model.lastname);
            builder.set('email', model.email);

            if (isInsert) {
                // insert
                builder.set('id', model.id);
                builder.set('datecreated', F.datetime);
            } else {
                // update
                builder.set('dateupdated', F.datetime);
                builder.where('id', model.id); // IMPORTANT !!!!                
            }
        });

        // Validate a response from the previous query
        sql.validate('save', 'User doesn\'t exist.');

        // We perform update
        if (!insert) {
            // Removes old data
            sql.remove('tbl_user_address').where('iduser', model.id);
            sql.remove('tbl_user_hobby').where('iduser', model.id);
            sql.remove('tbl_user_job').where('iduser', model.id);
        }

        // Insert billing address
        sql.insert('tbl_user_address').make(function(builder) {
            builder.set('type', 'billing');
            builder.set(model.billingaddress);
            builder.set('iduser', model.id);

            // A primary key is "id" (by default), but this table doesn't contain it.
            builder.primary('iduser');
        });

        // Insert postal address
        sql.insert('tbl_user_address').make(function(builder) {
            builder.set('type', 'postal');
            builder.set(model.postaladdress);
            builder.set('iduser', model.id);

            // A primary key is "id" (by default), but this table doesn't contain it.
            builder.primary('iduser');
        });

        // Insert jobs
        model.jobs.forEach(function(item) {
            sql.insert('tbl_user_job').make(function(builder) {
                builder.set(item);
                builder.set('iduser', model.id);

                // A primary key is "id" (by default), but this table doesn't contain it.
                builder.primary('iduser');
            });
        });

        // Insert hobbies
        model.hobbies.forEach(function(item) {
            sql.insert('tbl_user_hobby').make(function(builder) {
                builder.set(item);
                builder.set('iduser', model.id);

                // A primary key is "id" (by default), but this table doesn't contain it.
                builder.primary('iduser');
            });
        });

        // Transaction?
        // sql.commit();

        // Execute all queries
        sql.exec(function(err, response) {
            callback(SUCCESS(true, model.id));
        });
    });
});

function save() {
    var user = {};

    // user.id = 'IF YOU WANT TO MODIFY EXISTING USER';
    user.firstname = 'Peter';
    user.lastname = 'Sirka';

    user.billingaddress = {};
    user.billingaddress.street = 'Viestova 28';
    user.billingaddress.zip = '97401';
    user.billingaddress.city = 'Banska Bystrica';
    user.billingaddress.state = 'Slovakia';

    user.postaladdress = {};
    user.postaladdress.street = 'Viestova 28';
    user.postaladdress.zip = '97401';
    user.postaladdress.city = 'Banska Bystrica';
    user.postaladdress.state = 'Slovakia';

    user.jobs = [];
    user.jobs.push({ name: 'Web Developer', year: 2006 });
    user.jobs.push({ name: 'Student', year: 2000 });

    user.hobbies = [];
    user.hobbies.push({ name: 'Programming'});
    user.hobbies.push({ name: 'Motocycles'});
    user.hobbies.push({ name: 'Airsoft'});
    user.hobbies.push({ name: 'Mountains'});

    var schema = GETSCHEMA('User').make(user);
    schema.$save((err, response) => console.log(err, response));
}

// Save the data
save();

Workflow: each schema stores own data

each schema stores the data using schema.setSave() delegate
we have to add iduser field to each schema
this example uses schema.$repository(name, [value]) function as a session store for schemas

require('total.js');
require('sqlagent/pg').init('postgre://user:password@127.0.0.1/database');

NEWSCHEMA('Hobby').make(function(schema) {

    schema.define('name', 'String(50)', true);

    schema.setSave(function(error, model, options, callback, controller) {

        // We expect: model.iduser

        var sql = DB(error);

        if (model.$repository('update'))
            sql.remove('tbl_user_hobby').where('iduser', model.iduser);

        sql.insert('tbl_user_hobby').make(function(builder) {
            builder.set(model);
            builder.set('iduser', model.iduser);

            // A primary key is "id" (by default), but this table doesn't contain it.
            builder.primary('iduser');
        });

        sql.exec(() => callback(SUCCESS(true)));
    });

});

NEWSCHEMA('Job').make(function(schema) {

    schema.define('name', 'String(50)', true);
    schema.define('year', Number);

    schema.setSave(function(error, model, options, callback, controller) {

        // We expect: model.iduser

        var sql = DB(error);

        if (model.$repository('update'))
            sql.remove('tbl_user_job').where('iduser', model.iduser);

        sql.insert('tbl_user_job').make(function(builder) {
            builder.set(model);
            builder.set('iduser', model.iduser);

            // A primary key is "id" (by default), but this table doesn't contain it.
            builder.primary('iduser');
        });

        sql.exec(() => callback(SUCCESS(true)));
    });

});

NEWSCHEMA('Address').make(function(schema) {

    schema.define('street', 'Capitalize(50)', true);
    schema.define('zip', 'Zip', true);
    schema.define('city', 'Capitalize(50)', true);
    schema.define('country', 'Capitalize(50)');
    schema.define('state', 'Capitalize(50)', true);

    schema.setSave(function(error, model, options, callback, controller) {

        // We expect: model.iduser, model.type

        var sql = DB(error);

        if (model.$repository('update'))
            sql.remove('tbl_user_address').where('iduser', model.iduser);

        sql.insert('tbl_user_address').make(function(builder) {
            builder.set(model);
            builder.set('iduser', model.iduser);

            // A primary key is "id" (by default), but this table doesn't contain it.
            builder.primary('iduser');
        });

        sql.exec(() => callback(SUCCESS(true)));
    });

});

NEWSCHEMA('User').make(function(schema) {

    schema.define('id', 'UID');
    schema.define('firstname', 'Capitalize(50)');
    schema.define('lastname', 'Capitalize(50)');
    schema.define('billingaddress', 'Address', true);
    schema.define('postaladdress', 'Address', true);
    schema.define('jobs', '[Job]');
    schema.define('hobbies', '[Hobby]');
    schema.define('email', 'Email');

    schema.setSave(function(error, model, options, callback, controller) {

        var insert = model.id ? false : true;

        // Is it a new user?
        if (insert)
            model.id = UID();

        model.$repository('update', !insert);

        var sql = DB(error);

        sql.save('save', 'tbl_user', insert, function(builder, is) {

            builder.set('firstname', model.firstname);
            builder.set('lastname', model.lastname);
            builder.set('email', model.email);

            if (is) {
                // insert
                builder.set('id', model.id);
                builder.set('datecreated', F.datetime);
            } else {
                // update
                builder.set('dateupdated', F.datetime);
                builder.where('id', model.id); // IMPORTANT !!!!
            }
        });

        sql.exec(function(err, response) {

            var async = [];

            // Insert billing address
            async.push(function(next) {
                model.billingaddress.type = 'billing';
                model.billingaddress.iduser = model.id;
                model.billingaddress.$save(function(err, response) {
                    err && error.push(err);
                    next();
                });
            });

            // Insert postal address
            async.push(function(next) {
                model.postaladdress.type = 'postal';
                model.postaladdress.iduser = model.id;
                model.postaladdress.$save(function(err, response) {
                    err && error.push(err);
                    next();
                });
            });

            // Insert jobs
            async.push(function(next) {
                model.jobs.wait(function(item, resume) {
                    item.iduser = model.id;
                    item.$save(function(err, response) {
                        err && error.push(err);
                        resume();
                    });
                }, next);
            });

            // Insert hobbies
            async.push(function(next) {
                model.hobbies.wait(function(item, resume) {
                    item.iduser = model.id;
                    item.$save(function(err, response) {
                        err && error.push(err);
                        resume();
                    });
                }, next);
            });

            // Execute all async operations
            async.async(() => callback(SUCCESS(true, model.id)));
        });
    });
});

function save() {

    var user = {};

    user.id = '17020109510001yxw1';
    user.firstname = 'Peter';
    user.lastname = 'Sirka';

    user.billingaddress = {};
    user.billingaddress.street = 'Viestova 28';
    user.billingaddress.zip = '97401';
    user.billingaddress.city = 'Banska Bystrica';
    user.billingaddress.state = 'Slovakia';

    user.postaladdress = {};
    user.postaladdress.street = 'Viestova 28';
    user.postaladdress.zip = '97401';
    user.postaladdress.city = 'Banska Bystrica';
    user.postaladdress.state = 'Slovakia';

    user.jobs = [];
    user.jobs.push({ name: 'Web Developer', year: 2006 });
    user.jobs.push({ name: 'Student', year: 2000 });

    user.hobbies = [];
    user.hobbies.push({ name: 'Programming'});
    user.hobbies.push({ name: 'Motocycles'});
    user.hobbies.push({ name: 'Airsoft'});
    user.hobbies.push({ name: 'Mountains'});

    var schema = GETSCHEMA('User').make(user);
    schema.$save((err, response) => console.log(err, response));
}

save();

Two cases for handling relational data in Total.js

A PostgreSQL database structure for testing

Workflow: storing data in one method

Workflow: each schema stores own data

Other posts