How to Use MySQL Database in Total.js with QueryBuilderMySQL?

Total.js, a powerful web framework for Node.js, simplifies web application development. Integrating databases like MySQL is crucial for building dynamic applications. In this tutorial, we'll explore how to seamlessly combine MySQL with Total.js using QueryBuilderMySQL. This intuitive tool streamlines database interactions, making it ideal for both beginners and experienced developers.

Let's delve into the integration of MySQL with Total.js using QueryBuilderMySQL.

Table of Contents

Prerequisites
Creating a Total.js Application
Setting up QueryBuilderMySQL
Configuring MySQL Database Connection
Using QueryBuilderMySQL in Total.js

1. Prerequisites

Before you start integrating MySQL with Total.js using QueryBuilderMySQL, make sure you have the following prerequisites in place:

Node.js: Ensure that Node.js is installed on your system. If you haven't installed it yet, you can download and install it from the official Node.js website.
Total.js (version 4 or later): You can install Total.js locally in your project directory. To do this, create a new project folder and navigate into it using the terminal or command prompt. Then, run the following npm command inside your project directory to install Total.js locally: npm install total4
MySQL Database: Set up a MySQL database server and make sure it's running. If you haven't installed MySQL, you can download and install it from the official MySQL website.

Having Node.js, a local installation of Total.js version 4 or later, and a running MySQL database server in place ensures a smooth integration process. Now you're ready to proceed with setting up QueryBuilderMySQL in your Total.js application.

2. Creating a Total.js Application

# Open Terminal
cd /path/to/your/totaljs/project

# Navigate to Project Root
touch index.js

# Create index.js
echo "require('total4/debug')({ port: 5000 });" >> index.js

# Add Start Script
node index.js

# Check Output
# The terminal should indicate the server running at http://127.0.0.1:5000/.

3. Setting up QueryBuilderMySQL

QueryBuilder is a versatile tool that provides a simple ORM implementation for various database engines. It operates as an abstraction layer, allowing you to download existing implementations for QueryBuilder or create your own, depending on your specific requirements.

QueryBuilder in General

QueryBuilder encompasses two main implementations:

Database Operations: This implementation focuses on performing general database operations.
Query Operations for Filtering Data: This implementation enables you to filter and manipulate data using query operations.

QueryBuilder supports serialization into JSON format, making it a flexible and powerful tool for handling database interactions. Here are some existing implementations:

NoSQL Embedded (TextDB) Driver: Included in the Total.js core.
QueryBuilder for PostgreSQL
QueryBuilder for MySQL
QueryBuilder for SQLite

QueryBuilderMySQL is a specific implementation designed for MySQL databases. To set it up, install the QueryBuilderMySQL package using npm:

npm install querybuildermysql2

Initialization:

// /definitions/db.js
require('querybuildermysql2').init('default', CONF.database);

// require('querybuildermysql2').init(name, connectionstring, pooling, [errorhandling]);
// name {String} a name of DB (default: "default")
// connectionstring {String} a connection to the [__ MySQL__](https://www.mysql.com)
// pooling {Number} max. clients (default: "0" (disabled))
// errorhandling {Function(err, cmd)}

4. Configuring MySQL Database Connection

Open Configuration File: Edit /config and add your MySQL connection string.
Usage in db.js: In /definitions/db.js, use the connection string with QueryBuilderMySQL:

require('querybuildermysql2').init('default', require('total.js').config.database);

With QueryBuilderMySQL set up and initialized, you can seamlessly perform MySQL database operations within your Total.js application. This integration simplifies the process of querying and manipulating data, enhancing the efficiency and functionality of your web applications.

5. Using QueryBuilderMySQL in Total.js

Now that you have set up Total.js and configured your MySQL database connection, let's dive into using QueryBuilderMySQL for database operations in a Total.js application.

Creating a Total.js Schema

In Total.js, schemas are used to define the structure of your data models and specify actions related to those models. Below is an example of a Total.js schema (/schemas/users.js) that uses QueryBuilderMySQL:

// /schemas/users.js

NEWSCHEMA('Users', function(schema) {

    // Action: List users
    schema.action('list', {
        name: 'List users',
        action: function($) {
            var builder = DB().list('tbl_user');
            // Autoquery using QueryBuilderMySQL
            // - Auto-generates query based on provided parameters
            // - Sorts the result by 'dtcreated' in descending order
            // - Limits the result to 100 records
            builder.autoquery($.query, 'id:String, name:String, phone:String, phone:String, isonline:Boolean, countprovider:Number, dtcreated:Date,dtupdated:Date,countlogin:Number,isonline:Boolean', 'dtcreated_desc', 100);

            // Additional filtering to exclude removed users
            builder.where('isremoved=FALSE');

            // Sorts the result by 'dtcreated' in descending order
            builder.sort('dtcreated', true);

            // Executes the query and provides the result to the callback function
            builder.callback($.callback);
        }
    });

    // Action: Check customer before insert
    schema.action('check', {
        name: 'Check customer before insert',
        action: function($, model) {
            var db = DB();

            // Check operation using QueryBuilderMySQL
            // - Checks if a user with the provided phone number already exists
            // - Provides fields 'id' for further processing
            // - Throws an error if the user already exists
            db.check('tbl_user').where('phone', model.phone).where('isremoved=FALSE').fields('id').error('@(The account already exists)', true).callback($.done());
        }
    });

    // Action: Create new customer
    schema.action('create', {
        name: 'Insert new customer',
        input:  'gender:{male|female},fistname:Capitalize(40),lastname:Capitalize(40),role:{collector|buyer},phone:Phone,password:String,pincode:Number,photo:String', // Schema inline validation.
        action: async function($, model) {
            // ... (implementation details for creating a new customer)

            var db = DB();

            // Insert operation using QueryBuilderMySQL
            // - Inserts the new customer into the 'tbl_user' table
            await db.insert('tbl_user', model).promise($);

            // ... (additional implementation details)

            // Provides a response with a token and user information
            db.callback($.done({ token: token, user: model }));
        }
    });

    // Action: Remove User
    schema.action('remove', {
        name: 'Remove User',
        params: '*id:String',
        action: async function($) {
            var params = $.params;
            var db = DB();
            // Update operation using QueryBuilderMySQL
            // - Marks the user as removed in the 'tbl_user' table
            // - Performs error handling, audit logging, and provides a response
            db.update('tbl_user', { isremoved: true }).id(params.id).where('isremoved=FALSE').error(404).audit($, 'Removed user: ' + params.id).callback($.done());
        }
    });
});

In conclusion, integrating QueryBuilderMySQL into a Total.js application streamlines MySQL database operations. Key steps include creating a Total.js application, configuring the MySQL connection, setting up QueryBuilderMySQL, and utilizing its operations in a Total.js schema. This combination enhances efficiency and developer productivity, making Total.js a powerful choice for database-driven applications. Happy coding!