How to use the PostgreSQL database in Total.js? QueryBuilderPG

Total.js is a powerful Node.js framework that provides seamless integration with various databases, including the robust and feature-rich PostgreSQL. Leveraging the capabilities of PostgreSQL can greatly enhance your Total.js applications, enabling you to build scalable and efficient database-driven solutions. In this blog post, we will explore how to effectively use the PostgreSQL database in Total.js using QueryBuilderPG, a convenient query builder library.

Installation and Setup:

Before diving into PostgreSQL, let's ensure that we have everything set up correctly. Begin by installing PostgreSQL on your machine, following the appropriate steps for your operating system. Visit the official website, to get it set up. Once PostgreSQL is installed, create a new database and user specifically for your Total.js application. This ensures proper isolation and security.

CREATE USER totaluser WITH SUPERUSER;
ALTER USER totaluser WITH PASSWORD 'password';

Creating total.js app and dependencies

For the sake of an example, we'll create an empty project with npm and set it to run the total.js application.

mkdir pgtotalapp && cd pgtotalapp && npm init -y && npm i total4 && echo "require('total4/debug')({ port:8000 });
" >> index.js

Also, we need to install querybuilderpg for communicating with PostgreSQL in total.js.

npm i querybuilderpg

Connecting to the Database:

Establishing a connection to the PostgreSQL database from your Total.js application is straightforward. Use the configuration options to specify the host, port, database name, username, and password. As a convention in total.js, we do create a config (it's like a .env file in Laravel, just for holding global configurations) file in the root of the project and add the database credentials like the following:

database  :  postgresql://totaluser:password@127.0.0.1:5432/pgtotalapp

Almost done, but not yet. Now we need to create the definitions folder and set the link with the database. Inside that definitions folder, we are going to create a file db.js and paste the following code inside:

mkdir definitions && echo "require('querybuilderpg').init('', CONF.database, 1, ERROR('DB'));" >> ./definitions/db.js

With the connection successfully established, you are ready to start interacting with the database.

Creating Database Tables:

To begin working with data in PostgreSQL, you need to create the necessary database tables. For that purpose, as a convention in total.js, we create a database.sql in the root of the project and create the schema of the database. Here is a simple example for creating a users table in SQL :

-- /database.sql
CREATE TABLE public.tbl_user(
    id text NOT NULL,
    firstname text NOT NULL,
    lastname text NOT NULL,
    isremoved bool NOT NULL DEFAULT FALSE,
    dtcreated timestamp NOT NULL DEFAULT now(),
    dtupdated timestamp,
    PRIMARY KEY ("id")
);

INSERT INTO public.tbl_user (id, firstname, lastname) 
VALUES ('123qe001uw51d', 'Peter', 'Sirka'),
       ('123qf001uw51d', 'Chris', 'Kid'),
       ('123qf003uw51d', 'Louis', 'Bertson'),
       ('123qj001uw51d', 'John', 'Doe');

Verify the successful creation of tables by checking the database schema.

Retrieving data

After that, Let's create a simple view to retrieve all the users in the database. We create a views folder and add an index.html inside this folder where we add the following content

@{layout('')}
<!DOCTYPE html>
<html>
<head>
    <title>My Site</title>
    <meta charset="utf-8" />
</head>
<body>
<div id="company" style="width:100px;background-color: #ff6a00">Below is a list of all users</div>

<table width="100%" class="table" cellpadding="0" cellspacing="0" border="0">
    <colgroup>
        <col style="width:200px" />
        <col style="width:800px" />
    </colgroup>
    @{foreach var m in model}
    <tr>
        <td>@{m.id}</td>
        <td>@{m.age}</td>
        <td>@{m.name}</td>
    </tr>
    @{end}
</table>
</body>
</html>

With Total.js and QueryBuilderPG, developers can create data-driven applications that excel in performance, security, and flexibility.

If you type node index.js and open your browser at localhost:8000 you will all the users we inserted in the database.

Check this GitHub link for the code