andehlu andehlu - 1 month ago 19
Node.js Question

Relational calls in Nodejs + PostgreSQL

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?

Answer

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