How to connect Node.js to PostgreSQL

Application code usually reaches PostgreSQL through a small client layer rather than scattering connection details and SQL across route handlers or scripts. In Node.js, the pg package provides the node-postgres client used to open a pool and run queries against the database supplied at startup.

A Pool is the normal entry point for web apps and background workers because it reuses database connections instead of creating a new PostgreSQL session for every query. pg can read the same PGHOST, PGPORT, PGDATABASE, PGUSER, and PGPASSWORD environment variables used by libpq tools.

The smoke test assumes the database, role, and password already exist. It installs the client package, creates a small database check script, and queries the active database with a bound parameter. Use shell-set password values only for a local check; long-running services should receive credentials from the process manager, container runtime, secret store, or a PostgreSQL passfile.

1. Open the Node.js project root that contains package.json.

   If the project does not have package metadata yet, initialize it before adding database dependencies.
   Related: How to create a Node.js project with npm

2. Install the PostgreSQL client package.

   $ npm install pg
   
   added 14 packages, and audited 15 packages in 2s
   
   found 0 vulnerabilities

   pg is the npm package for node-postgres. It installs the runtime client used by Pool and Client.
   Related: How to install a Node.js dependency

3. Create the connection smoke-test script.

   db-check.mjs

   import pg from "pg";
    
   const { Pool } = pg;
   const pool = new Pool();
    
   try {
     const result = await pool.query(
       `SELECT current_database() AS database,
               current_user AS user_name,
               $1::text AS app_message`,
       ["Node.js connected to PostgreSQL"],
     );
    
     const row = result.rows[0];
     console.log(`database=${row.database}`);
     console.log(`user=${row.user_name}`);
     console.log(`message=${row.app_message}`);
   } finally {
     await pool.end();
   }

   Keep one Pool for the life of a web server or worker process, and call pool.end() during shutdown. The finally block is appropriate for this one-off smoke test because the script exits after one query.

4. Set the PostgreSQL connection variables for the current shell.

   $ export PGHOST=localhost
   $ export PGPORT=5432
   $ export PGDATABASE=appdb
   $ export PGUSER=app_user
   $ export PGPASSWORD='app_password'

   Replace these local smoke-test values with the database host, name, user, and password for the target environment. Avoid committing passwords to source files, shell scripts, screenshots, or saved transcripts.

5. Run the Node.js database smoke test.

   $ node db-check.mjs
   database=appdb
   user=app_user
   message=Node.js connected to PostgreSQL

   The output should show the selected database, expected role, and bound query message.