pg

What is pg?

The pg npm package is a PostgreSQL client for Node.js. It provides functionalities to connect to a PostgreSQL database server and execute queries, manage transactions, and listen to notifications from the database.

What are pg's main functionalities?

Connecting to a PostgreSQL database

This code sample demonstrates how to connect to a PostgreSQL database using the pg package. It includes creating a new client instance and connecting to the database with a connection string.

const { Client } = require('pg');
const client = new Client({
  connectionString: 'postgresql://user:password@localhost:5432/database'
});
client.connect();

Executing a query

This code sample shows how to execute a SQL query to select all records from a table and print the results. It also handles any potential errors and closes the connection.

client.query('SELECT * FROM my_table', (err, res) => {
  console.log(err ? err.stack : res.rows);
  client.end();
});

Using async/await for queries

This code sample uses async/await syntax to execute a query and print the results. It's a more modern approach to handling asynchronous operations in Node.js.

async function fetchData() {
  const res = await client.query('SELECT * FROM my_table');
  console.log(res.rows);
  client.end();
}
fetchData();

Managing transactions

This code sample illustrates how to manage a transaction with the pg package. It begins a transaction, attempts to insert data, commits the transaction if successful, or rolls back if an error occurs.

async function transactionExample() {
  await client.query('BEGIN');
  try {
    await client.query('INSERT INTO my_table (col) VALUES ($1)', ['data']);
    await client.query('COMMIT');
  } catch (e) {
    await client.query('ROLLBACK');
    throw e;
  }
}
transactionExample();

Listening to notifications

This code sample demonstrates how to listen for notifications from the PostgreSQL server. It sets up an event listener for 'notification' events and executes the LISTEN command to subscribe to a specific notification.

const client = new Client();
client.connect();
client.on('notification', (msg) => {
  console.log('New notification:', msg);
});
client.query('LISTEN my_notification');

Other packages similar to pg

mysql

The mysql package is a client for MySQL databases. It provides similar functionalities to pg, such as connecting to a database, executing queries, and managing transactions. However, it is designed specifically for MySQL and not PostgreSQL.

sequelize

Sequelize is an ORM (Object-Relational Mapping) library for Node.js. It supports multiple database systems, including PostgreSQL. Unlike pg, which is a lower-level client, Sequelize provides a higher-level abstraction with features like model definition, associations, and migrations.

typeorm

TypeORM is another ORM for TypeScript and JavaScript that runs on Node.js. It supports PostgreSQL among other databases. It provides an even higher level of abstraction compared to Sequelize and includes features like data-mapper patterns, repositories, and automatic schema generation.

knex

Knex.js is a SQL query builder for Node.js that supports PostgreSQL, MySQL, SQLite3, and more. It provides chainable query building capabilities and can be used as a query client without the full ORM features. It's a middle ground between pg and full ORMs like Sequelize and TypeORM.

README

#node-postgres

Non-blocking PostgreSQL client for node.js. Pure JavaScript and optional native libpq bindings.

Install

$ npm install pg

Intro & Examples

Simple example

var pg = require('pg');

// instantiate a new client
// the client will read connection information from
// the same environment varaibles used by postgres cli tools
var client = new pg.Client();

// connect to our database
client.connect(function (err) {
  if (err) throw err;

  // execute a query on our database
  client.query('SELECT $1::text as name', ['brianc'], function (err, result) {
    if (err) throw err;

    // just print the result to the console
    console.log(result.rows[0]); // outputs: { name: 'brianc' }

    // disconnect the client
    client.end(function (err) {
      if (err) throw err;
    });
  });
});

Client pooling

If you're working on something like a web application which makes frequent queries you'll want to access the PostgreSQL server through a pool of clients. Why? For one thing, there is ~20-30 millisecond delay (YMMV) when connecting a new client to the PostgreSQL server because of the startup handshake. Furthermore, PostgreSQL can support only a limited number of clients...it depends on the amount of ram on your database server, but generally more than 100 clients at a time is a very bad thing. :tm: Additionally, PostgreSQL can only execute 1 query at a time per connected client, so pipelining all queries for all requests through a single, long-lived client will likely introduce a bottleneck into your application if you need high concurrency.

With that in mind we can imagine a situation where you have a web server which connects and disconnects a new client for every web request or every query (don't do this!). If you get only 1 request at a time everything will seem to work fine, though it will be a touch slower due to the connection overhead. Once you get >100 simultaneous requests your web server will attempt to open 100 connections to the PostgreSQL backend and :boom: you'll run out of memory on the PostgreSQL server, your database will become unresponsive, your app will seem to hang, and everything will break. Boooo!

Good news: node-postgres ships with built in client pooling. Client pooling allows your application to use a pool of already connected clients and reuse them for each request to your application. If your app needs to make more queries than there are available clients in the pool the queries will queue instead of overwhelming your database & causing a cascading failure. :thumbsup:

var pg = require('pg');

// create a config to configure both pooling behavior
// and client options
// note: all config is optional and the environment variables
// will be read if the config is not present
var config = {
  user: 'foo', //env var: PGUSER
  database: 'my_db', //env var: PGDATABASE
  password: 'secret', //env var: PGPASSWORD
  port: 5432, //env var: PGPORT
  max: 10, // max number of clients in the pool
  idleTimeoutMillis: 30000, // how long a client is allowed to remain idle before being closed
};


//this initializes a connection pool
//it will keep idle connections open for a 30 seconds
//and set a limit of maximum 10 idle clients
var pool = new pg.Pool(config);

// to run a query we can acquire a client from the pool,
// run a query on the client, and then return the client to the pool
pool.connect(function(err, client, done) {
  if(err) {
    return console.error('error fetching client from pool', err);
  }
  client.query('SELECT $1::int AS number', ['1'], function(err, result) {
    //call `done()` to release the client back to the pool
    done();

    if(err) {
      return console.error('error running query', err);
    }
    console.log(result.rows[0].number);
    //output: 1
  });
});

pool.on('error', function (err, client) {
  // if an error is encountered by a client while it sits idle in the pool
  // the pool itself will emit an error event with both the error and
  // the client which emitted the original error
  // this is a rare occurrence but can happen if there is a network partition
  // between your application and the database, the database restarts, etc.
  // and so you might want to handle it and at least log it out
  console.error('idle client error', err.message, err.stack)
})

node-postgres uses pg-pool to manage pooling. It bundles it and exports it for convenience. If you want, you can require('pg-pool') and use it directly - it's the same as the constructor exported at pg.Pool.

It's highly recommended you read the documentation for pg-pool.

Here is an up & running quickly example

More Documentation

Native Bindings

To install the native bindings:

$ npm install pg pg-native

node-postgres contains a pure JavaScript protocol implementation which is quite fast, but you can optionally use native bindings for a 20-30% increase in parsing speed (YMMV). Both versions are adequate for production workloads. I personally use the pure JavaScript implementation because I like knowing what's going on all the way down to the binary on the socket, and it allows for some fancier use cases which are difficult to do with libpq. :smile:

To use the native bindings, first install pg-native. Once pg-native is installed, simply replace var pg = require('pg') with var pg = require('pg').native. Make sure any exported constructors from pg are from the native instance. Example:

var pg = require('pg').native
var Pool = require('pg').Pool // bad! this is not bound to the native client
var Client = require('pg').Client // bad! this is the pure JavaScript client

var pg = require('pg').native
var Pool = pg.Pool // good! a pool bound to the native client
var Client = pg.Client // good! this client uses libpq bindings

node-postgres abstracts over the pg-native module to provide exactly the same interface as the pure JavaScript version. Care has been taken to keep the number of api differences between the two modules to a minimum; however, it is recommended you use either the pure JavaScript or native bindings in both development and production and don't mix & match them in the same process - it can get confusing!

Features

• pure JavaScript client and native libpq bindings share the same api
• connection pooling
• extensible js<->postgresql data-type coercion
• supported PostgreSQL features
  • parameterized queries
  • named statements with query plan caching
  • async notifications with LISTEN/NOTIFY
  • bulk import & export with COPY TO/COPY FROM

Extras

node-postgres is by design pretty light on abstractions. These are some handy modules we've been using over the years to complete the picture. Entire list can be found on wiki

Contributing

:heart: contributions!

If you need help getting the tests running locally or have any questions about the code when working on a patch please feel free to email me or gchat me.

I will happily accept your pull request if it:

• has tests
• looks reasonable
• does not break backwards compatibility

Information about the testing processes is in the wiki.

Open source belongs to all of us, and we're all invited to participate!

Support

If at all possible when you open an issue please provide

• version of node
• version of postgres
• smallest possible snippet of code to reproduce the problem

Usually I'll pop the code into the repo as a test. Hopefully the test fails. Then I make the test pass. Then everyone's happy!

If you need help or run into any issues getting node-postgres to work on your system please report a bug or contact me directly. I am usually available via google-talk at my github account public email address. Remember this is a labor of love, and though I try to get back to everything sometimes life takes priority, and I might take a while. It helps if you use nice code formatting in your issue, search for existing answers before posting, and come back and close out the issue if you figure out a solution. The easier you can make it for me, the quicker I'll try and respond to you!

If you need deeper support, have application specific questions, would like to sponsor development, or want consulting around node & postgres please send me an email, I'm always happy to discuss!

I usually tweet about any important status updates or changes to node-postgres on twitter. Follow me @briancarlson to keep up to date.

License

Copyright (c) 2010-2016 Brian Carlson ([e-mail])

Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.