QueryBuilder in Action Part 1

When building applications with Node.js in general or Total.js specifically, querying and managing data efficiently is essential. In this blog, we’ll cover how to perform basic data operations using the Total.js QueryBuilder. This first part will introduce fundamental concepts, demonstrate core actions like inserting and retrieving data, and showcase practical examples to get you started.

Getting Started: Exploring QueryBuilder in Total.js

Total.js’s QueryBuilder offers a powerful abstraction layer for interacting with various databases through operations such as filtering, inserting, updating, and retrieving data. Whether you're working on a lightweight application or managing complex queries, QueryBuilder provides a simple yet flexible ORM solution for Node.js applications.

Database Support and Implementations

QueryBuilder acts as a middle layer, abstracting the specifics of database engines. To utilize it effectively, you’ll need to integrate an existing implementation or build your own that meets your requirements. Total.js supports multiple database engines, ensuring flexibility for different use cases.

Here are the currently available implementations:

• NoSQL Embedded Database (TextDB): This is a lightweight, file-based database included in the Total.js core, perfect for small applications or prototyping.
• PostgreSQL: Utilize QueryBuilder with one of the most robust and feature-rich relational databases.
• MySQL: Integrate with this widely used database for scalable and high-performance solutions.
• SQLite: Leverage QueryBuilder with this lightweight, serverless database for quick deployments or embedded applications.

Setting Up QueryBuilder

In this guide, we’ll start with an embedded NoSQL (TextDB) database to demonstrate how to define schemas and perform basic actions like querying, inserting, and updating data. By the end, you’ll have the skills to adapt these operations for other supported database engines.

Setting up the Database

To learn effectively, we'll create a test NoSQL database file with sample user data.

Database Setup: Create a databases/users.nosql file to store sample user records:

{"id": 2, "name": "Bob", "age": 30}
{"id": 3, "name": "Charlie", "age": 28}
{"id": 4, "name": "Diana", "age": 22}
{"id": 5, "name": "Edward", "age": 35}
{"id": 6, "name": "John", "age": 45}
{"id": 7, "name": "Fiona", "age": 27}
{"id": 8, "name": "George", "age": 29}
{"id": 9, "name": "Hannah", "age": 24}
{"id": 10, "name": "Isaac", "age": 31}
{"id": 11, "name": "Julia", "age": 26}

Good to know: You don't need to manually create the .nosql file in the databases folder. The framework is able to create it during the insert operation if it does not exists. But the .nosql file content is plaintext and it is important to understand its structure. Maybe it will be usefull to fix certain cases.

Schema Definition: Create a schema with actions in schemas/users.js. We’ll define actions for listing, retrieving, and inserting users.

Basic QueryBuilder Actions with Practical Examples

In schemas/users.js, we'll define actions for listing and inserting users. This schema uses QueryBuilder methods to filter data, retrieve specific records, and add new users to the database.

1. Listing Users with Filters
We start by defining a list action to retrieve users based on criteria like name or age.

NEWSCHEMA('Users', function(schema) {

    schema.action('list', {
        name: 'List users',
        query: 'page:Number, sort:String, name:String',
        params: 'id:String',
        action: async function($) {
            // Example: filter by name
            var users = await DATA.find('nosql/users').where('name', $.query.name).promise();
            $.callback(users); 
        }
    });
});

Usage Examples:

Here are some variations of using list to filter users.

• Filter by Name:

var users = await ACTION('Users/list').where('name', 'John').promise();
console.log(users);

• Retrieve First User Only:

var user = await DATA.find('nosql/users').first().promise();
console.log(user);

2. Retrieving a User by ID
Another useful feature is retrieving a user by their ID. Using QueryBuilder's .id() method makes this straightforward:

var builder = DATA.find('nosql/users');
builder.id($.params.id);
// or builder.where('id', $.params.id);
$.callback(await builder.promise());

Example Usage:

var user = await ACTION('Users/list').params({ id: 5 }).promise();
console.log(user);  // Retrieves user with id 5

3. Inserting a New User
Next, let’s define an insert action to add new users to the database.

schema.action('insert', {
    name: 'Inserting new users',
    input: '*name:String, age:Number',
    output: 'success:Boolean',
    action: function($, model) {
        DATA.insert('nosql/users', model).callback(function(err) {
            if (err) {
                console.error(err);
            } else {
                $.success(true);
            }
        });
    }
});

This action adds a user to databases/users.nosql. Here’s how you can call the insert action.

Usage Example:

// in `definitions/test.js`
ON('ready', function() {
  var result = await ACTION('Users/insert', { name: 'Alice', age: 25 }).promise();
    console.log(result);
});

Practical Usage Cases: QueryBuilder in Action

1. Retrieve Users by Filtered Name

async function getUsersByName() {
    var users = await ACTION('Users/list').query({ name: 'Hannah' }).promise();
    console.log(users);
}
getUsersByName();

2. Retrieve User by ID

async function getUserByID() {
    var user = await ACTION('Users/list').params({ id: 4 }).promise();
    console.log(user);
}
getUserByID();

3. Insert a New User and Retrieve the Updated List

async function insertAndRetrieve() {
    await ACTION('Users/insert', { name: 'James', age: 32 }).promise();
    var users = await ACTION('Users/list').promise();
    console.log(users);
}
insertAndRetrieve();

Conclusion

In this first part of QueryBuilder in Action, we covered the essentials: creating a basic NoSQL database, defining schema actions, and using QueryBuilder methods to list, filter, retrieve, and insert users. We explored practical code examples for each operation to help you understand how these concepts work in a real-world scenario.
In the next part, we’ll dive into more advanced QueryBuilder methods, and complex filtering to give you a complete toolkit for managing data efficiently in Total.js.
Stay tuned for QueryBuilder in Action Part 2, where we'll go deeper into advanced querying techniques to supercharge your Total.js applications!