How to Use MySQL Database in Total.js with QueryBuilderMySQL?
Tags: mysql, node, totaljs, database
Published: 2023-11-14 16:47:12

**Total.js**, a powerful web framework for Node.js, simplifies web application development. Integrating databases like [MySQL](https://www.mysql.com) is crucial for building dynamic applications. In this tutorial, we'll explore how to seamlessly combine [MySQL](https://www.mysql.com) with [ Total.js](https://totaljs.com) 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](https://www.mysql.com) with [ Total.js](https://totaljs.com) using QueryBuilderMySQL.

---

**Table of Contents**

1. [Prerequisites](#prerequisites)
2. [Creating a ** Total.js** Application](#creating-a-totaljs-application)
3. [Setting up QueryBuilderMySQL](#setting-up-querybuildermysql)
4. [Configuring **MySQL** Database Connection](#configuring-mysql-database-connection)
5. [Using QueryBuilderMySQL in ** Total.js](#using-querybuildermysql-in-totaljs)

## 1. Prerequisites

Before you start integrating [MySQL](https://www.mysql.com) with [ Total.js](https://totaljs.com) 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](https://nodejs.org/).

- **Total.js (version 4 or later):** You can install [ Total.js](https://totaljs.com) 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](https://totaljs.com) 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](https://www.mysql.com/).

Having Node.js, a local installation of [ Total.js](https://totaljs.com) version 4 or later, and a running [ MySQL](https://www.mysql.com) database server in place ensures a smooth integration process. Now you're ready to proceed with setting up QueryBuilderMySQL in your [ Total.js](https://totaljs.com) application.

## 2. Creating a [ Total.js](https://totaljs.com) Application

```sh
# 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:

1. **Database Operations:** This implementation focuses on performing general database operations.
2. **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](https://totaljs.com) core.
- [QueryBuilder for PostgreSQL](https://github.com/totaljs/querybuilderpg)
- [QueryBuilder for MySQL](https://github.com/totaljs/querybuildermysql2)
- [QueryBuilder for SQLite](https://github.com/totaljs/querybuildersqlite3)

QueryBuilderMySQL is a specific implementation designed for [ MySQL](https://www.mysql.com) databases. To set it up, install the QueryBuilderMySQL package using npm:

```sh
npm install querybuildermysql2
```

#### Initialization:
```js
// /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](https://www.mysql.com) Database Connection

- **Open Configuration File:**
   Edit `/config` and add your [ MySQL](https://www.mysql.com) connection string.

- **Usage in db.js:**
In `/definitions/db.js`, use the connection string with QueryBuilderMySQL:

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

With QueryBuilderMySQL set up and initialized, you can seamlessly perform [ MySQL](https://www.mysql.com) database operations within your [ Total.js](https://totaljs.com) 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](https://totaljs.com)

Now that you have set up [ Total.js](https://totaljs.com) and configured your [ MySQL](https://www.mysql.com) database connection, let's dive into using QueryBuilderMySQL for database operations in a [ Total.js](https://totaljs.com) application.

### Creating a [ Total.js](https://totaljs.com) Schema

In [ Total.js](https://totaljs.com), 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](https://totaljs.com) schema (/schemas/users.js) that uses QueryBuilderMySQL:
```js
// /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](https://totaljs.com) application streamlines [ MySQL](https://www.mysql.com) database operations. Key steps include creating a [ Total.js](https://totaljs.com) application, configuring the [ MySQL](https://www.mysql.com) connection, setting up QueryBuilderMySQL, and utilizing its operations in a [ Total.js](https://totaljs.com) schema. This combination enhances efficiency and developer productivity, making [ Total.js](https://totaljs.com) a powerful choice for database-driven applications. Happy coding!