In order to work with Postgres and node, Pg is the way to go. First, you need to install the pg module by running npm i pg
. After that create a folder in your project and for now, let it name db. Inside that folder create an index.js file and add the given code to it.
const { Pool } = require('pg'); const pool = new Pool({ connectionString: process.env.DATABASE_URL,//this is your connection string discussed in details below ssl: false, max: 20, // some sane value as per your project requirement idleTimeoutMillis: 30000, connectionTimeoutMillis: 2000, }); module.exports = { query: (text, params) => pool.query(text, params) }
The connection string
PostgreSQL connection string is made in following format.
postgres://<username e.g postgres>:<password>@<db url eg localhost>:<port eg 5432>/<database name> an example connection string from one of my projects on local host is postgres://postgres:iwillnottell@127.0.0.1:5432/customer_query_vo
Using The Pool
Once you have created a index.js file inside db folder as discussed above you can easily import it and use it in your js files. An example import and use is given below
var db = require('../db'); // inside your async function const resultProperty = await db.query('select * from property where property_id=$1', [propertyId]); // transaction examples await db.query('BEGIN') // begin transaction //do your stuff await db.query('COMMIT') //commit transaction // if some thing fails then rollback await db.query('ROLLBACK')
PG module is a very versatile and easy to use module though you might need pg-format
If you want to insert multiple values or deal with collections.