Relational calls in Nodejs + PostgreSQL

Question

I am new to Node and need some advice. I am trying to build a relational SQL query. For example I have a table named BOOKS and another named PAGES. PAGES has a foreign key pointing back to a row on BOOKS. I am trying to return a BOOK row and all the PAGES that are relational, then render to a view.

I have tried nested sql calls. Quick example below...

client.query('SELECT * FROM books WHERE id=($1) ',[bookid], function(err, result1) {
    client.query('SELECT * FROM pages WHERE bookid=($1) ',[bookid], function(err, result2) {

        res.render('book',{ book: result1.rows, pages: result2.rows});
        done();

    });
});

I have also tried chaining like below:

    client.query('SELECT * FROM books WHERE id=($1); SELECT * FROM pages WHERE bookid=($2) ',[bookid, bookid], function(err, result) {
        res.render('book',{ book: JSON.stringify(result.rows)});
        done();
    });

And both explode at runtime. Have I missed something pretty easy?

One of the solutions: http://stackoverflow.com/questions/37664258/get-a-parents-children-tree-with-pg-promise — vitaly-t, Oct 20 '16 at 11:27
The best solution is via a single query, see: http://stackoverflow.com/questions/39805736/get-join-table-as-array-of-results-with-postgresql-nodejs — vitaly-t, Oct 20 '16 at 11:29

ardilgulez · Accepted Answer · 2016-10-20T07:59:44.537

Good for you that the nested example worked :) but unfortunately it's not a really good way, because client.query function has a large overhead and nesting it while you can get away with not nesting will cause issues (not now but definitely when/if you push to production). The better way to do this is to join the tables. Here's how you can do it:

client.query('SELECT * FROM books, pages WHERE books.bookid=($1) and books.bookid = pages.bookid ',[bookid], function(err, result) {

    var bookrows = result.map(function(item){
        getColumnsFromBooksTable(item);
    });

    var pagerows = result.map(function(item){
        getColumnsFromPagesTable(item);
    });

    res.render('book',{ book: bookrows, pages: pagerows});
    done();

});

If you only want some of the rows from each table and not all of them, you can use client.query with this type of an SQL query:

SELECT books.bookname, books.frontcover, books.backcover, pages.pagenumber, pages.pagecontent FROM books, pages WHERE books.bookid=($1) and books.bookid = pages.bookid

Or better yet, if you want to search with book name (a column that pages table most probably doesn't have):

SELECT books.bookname, books.frontcover, books.backcover, pages.pagenumber, pages.pagecontent FROM books, pages WHERE books.bookname=($1) and books.bookid = pages.bookid

One question - are getColumnsFromBooksTable and getColumnsFromPagesTable functions that need to be created to parse out the relevant data? — barrylachapelle, Oct 20 '16 at 09:09
Yes. I don't know the column names you have and thus what the result has, so I left them as stubs. — ardilgulez, Oct 20 '16 at 09:20

Relational calls in Nodejs + PostgreSQL

1 Answers1