Node-Pg is a library used to connect your javascript application to a postgres database.
Installation
First of all you need to install it with npm.
$ npm install pg
How to use it
You have two ways of connecting to a database. Either with a single client connection, which is opened and closed for every transaction or a pool of connections, which remain open the whole time. Pools are faste since you do not authenticate for every connection, but you have to pass the variable around to use them anywhere in your application. They also take up more connection slots on your database.
Using Client Connections
The most efficient way to manage client connections is to write your own wrapper functions, for establishing the connection, executing the query and closing the connection again.
Lets start with creating the connection.
import { Client } from "pg";
import fs from "fs"; /* only needed for ssl cert */
function createClient() {
const client = new Client ({
host: process.env.PG_HOST,
port: Number(process.env.PG_PORT) || 5432,
database: process.env.PG_DATABASE,
user: process.env.PG_USER,
password: process.env.PG_PASSWORD,
application_name: process.env.PG_APP
/* if you want to use ssl add this
ssl: {
rejectUnauthorized: false,
cert: fs.readFileSync("./mycert.crt").toString()
}
*/
});
return client;
}
I am using environment variables for the connection-details. You could also write them here as a string but that is not a good practice, since you will risk to expose your credentials if you publish your code somewhere.
Now that we have our client object we need to initialize the connection with it.
I will also start creating the wrapper function executeQuery which I can use from everywhere in my codebase to execute queries without thinking about the connections.
export async function executeQuery() {
const client = createClient();
await client.connect();
/* Check if the connection got an error */
client.on("error", (err) => {
console.log("terminated pg client due to error: " + err);
client.end();
});
/* Close the database connection. Otherwise you will run out of connection-ports on your database */
client.end();
}
Next we will pass a query object to this function and execute it.
export async function executeQuery(sql) {
const client = createClient();
await client.connect();
/* Check if the connection got an error */
client.on("error", (err) => {
console.error("terminated pg client due to error: " + err);
client.end();
});
let res;
try {
res = await client.query(sql);
} catch (e) {
console.error(e)
} finally {
client.end()
}
return res
}
Now we can use this function to execute queries by passing the query object to it. It takes care about connecting, checking for errors and closing the connection once done.
Using Pool Connections
Pool connections work kind of similar. You first create a pool, then you take a connection from the pool, to execute your query and release it back to the pool once your done.
This is faster since the connection in the pool remains open and idle instead of closing it. It also makes your database load more predictable since you will never have more connections than in the pool. If there are no free connections left in the pool, your request will wait in a queue for one to finish.
The basic procedure looks like this.
import pg from 'pg'
const { Pool } = pg
const pool = new Pool({
host: process.env.PG_HOST,
port: Number(process.env.PG_PORT) || 5432,
database: process.env.PG_DATABASE,
user: process.env.PG_USER,
password: process.env.PG_PASSWORD,
application_name: process.env.PG_APP
/* if you want to use ssl add this
ssl: {
rejectUnauthorized: false,
cert: fs.readFileSync("./mycert.crt").toString()
}
*/
})
const client = await pool.connect()
await client.query('SELECT NOW()')
client.release()
You can also use a wrapper function with connection pools. The key is to create a variable outside the function to keep it on a module level. Otherwise, you would create a new pool for every query, which is even more ineffective than using single connections.
import { Pool } from 'pg'
/* store the connection in a variable outside the function scope */
let pool: Pool = createPool();
function createPool() {
const pool = new Pool({
host: process.env.PG_HOST,
port: Number(process.env.PG_PORT) || 5432,
database: process.env.PG_DATABASE,
user: process.env.PG_USER,
password: process.env.PG_PASSWORD,
application_name: process.env.PG_APP
/* if you want to use ssl add this
ssl: {
rejectUnauthorized: false,
cert: fs.readFileSync("./mycert.crt").toString()
}
*/
})
return pool
}
export async function executeQuery(sql) {
/* create a pool if there is none */
if (!pool) {
console.log("create a new connection-pool.");
pool = createPool();
}
const client = await pool.connect();
/* Check if the connection got an error */
client.on("error", (err) => {
console.error("terminated pg client due to error: " + err);
client.end();
});
let res;
try {
res = await client.query(sql);
} catch (e) {
console.error(e)
} finally {
/* release connection back to the pool to be reused */
client.release()
}
return res
}
As you can see it is pretty similar to the single client connection, but it is much faster.
