Express with pug, Postgres and proper MVC

Question

I recently started using Node.js + Express.js (generated with pug) + pg-promise for handling db.

My first target is to obtain data from Postgres (already set up) and display it pretty using render and pug. Let's say it is user list from Users table.

On this restful tutorial I have learned how to get data and return it as JSON - it worked.

Based on Mozilla's tutorial I seperated my code:

routes/users.js: where for '/' I call user_controller.user_list method (using router.get)
controllers/userController.js I have exported user_list where I would like to ask model for data and call render if I have results
queries.js which is kinda my model? But I'm not sure. It has API: connection to db with promises and one function for every query I am going to use in Controllers. I believe I should have like one Model file per table (or any logical entity) but where to store pgp connections? This file is based on first tutorial I mentioned

    // queries.js (connectionString is set properly to my postgres)
    var pgp = require('pg-promise')(options);
    var db = pgp(connectionString);
    function getUsers(req, res, next) {
        db.any('SELECT (user_id, username) FROM public.users ORDER BY user_id ASC LIMIT 1000')
        .then(function (data) {
            res.json({ data: data });
        })
        .catch(function (err) {
            return next(err);
        });    
    }
    module.exports = {
        getUsers: getUsers
    };

Here starts my problem as most tutorials uses mongoose which is very model-db-schema-friendly and what I have is simple 'SELECT ...' string I pass to pg-promise's any() function. Therefore I have no model class like User.

In userControllers.js I don't know how to call getUsers() to handle its data. Returning JS object from getUsers() would be nice.

Also: where should I call render? In controller or only in

db.any(...).then(function (data) { <--here--> })

Before, I also tried to embed whole Postgres handling into Controller but from db.any() I got this array for handling:

[{ row: '(1,John)' },{ row: '(2,Amy)' },{ row: '(50,Peter)' } ]

Didn't know how go from there as I probably lost my API functionality as well ;-)

I am browsing through multiple tutorials how to handle MVC but usually they handle MongoDB and satisfy readers with res.send() not render().

The Once-ler · Accepted Answer · 2019-09-25 20:13:16Z

I am not sure that I understand what your question is exactly about, but since I do not have enough reputation to comment, I'll do my best to help you with your interrogations. :)

First, regarding the queries.js file, it is IMO not exactly a model, but rather a DAO (Data Access Object) file. DAO comes between you Model (which is actually you database) and your Controller layers. There usually is a DAO file per object (User, Pet, whatever you want) in your data model.

When the data model is rather complex, it can be useful to use an Object Relational Mapping (ORM) such as Mongoose to map your database and execute complexe processes on your objects. In such a case, you might need a specific file per object so as to describe your model and store your queries. But since you don't need an ORM, you DAO can directly interact with your database. That is why you do not have a User.js file.

Regarding the way the db object should be used, I think you should refer directly to pg-promise documentation on the matter.

IMPORTANT: For any given connection, you should only create a single Database object in a separate module, to be shared in your application (see the code example below). If instead you keep creating the Database object dynamically, your application will suffer from loss in performance, and will be getting a warning in a development environment (when NODE_ENV = development)

As a matter of fact, a db object in pg-promise sort of represents the database itself and is actually designed for the simultaneous use of several databases, which does not seem to be your case for the moment.

Finally, when it comes to the render function, I believe it should be in the controller, as your DAO is not supposed to know how the data it has gathered is going to be used. Modularity is always a time-saving choice on the long-term.

Furthermore, note that you might later need a Business Layer between your DAO and your controller, in order to preprocess and postprocess data you are going to persist or to display. In such a case, if you need for instance to ask for data from your database, you will need to render data after it is processed by the Business layer. If the render is made in the DAO layer, it will not be possible.

In the link I provided earlier to pg-promise's db object connection, you will also find documentation on the any() method. You might already have looked it up. It specifically states that it returns

A promise object that represents the query result:

When no rows are returned, it resolves with an empty array.

When 1 or more rows are returned, it resolves with the array of rows.

so your returned data is a JS Array. If you want to make it a JS object, just use JSON.stringify(yourArray) to process your data before rendering it in your controller. But I wonder if Pug is not able to use your data directly.

Also, if you cannot get any data out of your DAO, maybe you should check that your data object is not empty, as such a case is tolerated by the any() method. If you expect your query to always return something, you might want to consider using the many() or the one() methods.

I hope this helps you.

Collectives™ on Stack Overflow

Express with pug, Postgres and proper MVC

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related