Restful Api express postgres database

Question

I´m developing a rest full api with node and exrpess, my database is postgresql, I need to use the postgres package pg-promise.

I know that I need to connect my app with the database in the app.js file, but my question is, How I should use this connection in my endpoints.

I have routes and I am using controllers.

For example

app.js

//in this file, suppously I have to to the connection 
const db = pgp('postgres://john:pass123@localhost:5432/products');

app.use('/products', productsRoute);

products.js (route)

 router.get('/', ProductsController.get_all_products);

products.js (controller)

    exports.get_all_products = (req, res, next ) => {
        // Here i want to use de database connection to do the query to find all
        //products in the database
    }

How do I get access to the connection to do something like

db.any('SELECT * FROM products WHERE active = $1', [true])
    .then(function(data) {
        // success;
    })
    .catch(function(error) {
        // error;
    });

From the controller.

Update

Ok, I´m using now node-prostgres, pg. I saw is better, Thanks for the advice people.

I want to create one time de db instance, and call it anywhere, in specific in the controllers

Could I use app.local to save my client?, connect, do a query and then close it. Do this anywhere

Prasad · Accepted Answer · 2019-04-10 15:32:27Z

2

I haven't used pg-promise.

If it helps, you can use PostgreSQL client for Node.js. You can also use async/await with it.

Instead of a router, you can use Express middle-ware straightaway as follows.

//app.js:

const express = require('express')
const bodyParser = require('body-parser')
const app = express()
const port = 1234

const db = require('./dbconnector')

//...omitted for brevity`
// 'db' is exported from a file such as 
// dbconnector.js.
app.get('/products', db.getProducts) 


//In dbconnector.js:
const Pool = require('pg').Pool
const pool = new Pool({
  user: 'postgres',
  host: 'localhost',
  database: 'mydb',
  password: 'mypwd',
  port: 5432,
})

const getProducts = (request, response) => {
    pool.query('SELECT * FROM products ORDER BY id 
ASC', (error, results) => {
      if (error) {
        throw error
      }
      response.status(200).json(results.rows)
    })
  }

// ...omitted for brevity

module.exports = {
 getProducts 

}

For modular design, please use a separate file (not app.js/index.js/server.js) for db connections as best practice and require that in your main app.js.

Here is help on pg module.

answered Apr 10, 2019 at 15:32

Prasad

3692 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ernesto petit Over a year ago

Could you explain me this please please use a separate file (not app.js/index.js/server.js)

Prasad Over a year ago

Use a separate file such as dbconnector.js to handle connections to the db rather than handling connections, errors in the main file (app.js).

1565986223 · Accepted Answer · 2019-04-10 17:26:06Z

Here's an example how to use it:

// mydb.js
async function someDbQuery() {
  let result;
  try {
    result = db.any('SELECT * FROM products WHERE active = $1', [true])
  } catch (e) {
    throw e
  }
  return result;
}
module.exports = someDbQuery;

// in your controller after importing
const { someDbQuery } = require('./mydb.js')
exports.get_all_products = async (req, res, next ) => {
  // Here i want to use de database connection to do the query to find all
  //products in the database
  try {
    const result = await someDbQuery();
    // use result here
  } catch (e) {
    // handle error
    console.error(e)
  }
}

Side note:

From the docs pg-promise

Built on top of node-postgres

node-postgres now supports promise too.

vitaly-t · Accepted Answer · 2019-04-11 13:27:50Z

0

You do not need to do anything, pg-promise manages connections automatically. It will be allocated for the query and released right after. See examples.

answered Apr 11, 2019 at 13:27

vitaly-t

26.1k17 gold badges129 silver badges151 bronze badges

Collectives™ on Stack Overflow

Restful Api express postgres database

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related