Setup PostgreSQL With Node.js on Windows: Step-by-Step Guide

PostgreSQL and Node.js install separately, and the only thing joining them is a connection string you write by hand. The installer never shows it to you, so a first failure looks like a broken database when the port number is the problem.

I ran the Node.js half against a live PostgreSQL server, from the connection string through a pool that returns rows, and kept the messages my wrong strings produced.

What PostgreSQL Brings to a Node.js Project

PostgreSQL stores rows in tables you define, and a Node.js process reads them over a TCP connection. The two are separate programs with separate configuration, so until you hand the driver a connection string your code has no way to reach the database.

The driver sits in between. node-postgres, published on npm as pg, speaks the PostgreSQL protocol from JavaScript and hands you back plain JavaScript objects you can loop over.

Each of the three pieces keeps its settings somewhere different, which is why a failure can look like it belongs to the wrong one. A query only returns a row once all three agree.

What You Need Before You Start

The installer asks for two decisions you cannot change afterwards without reinstalling, so settle them before you download anything.

• 64-bit Windows. The EDB installers list 18.6, 17.11, 16.15, 15.19 and 14.24 for Windows x86-64, and no 32-bit build.
• Administrator rights on the account you install with, because the installer registers the PostgreSQL service.
• Node.js already installed. Run node –version in a new terminal and confirm it answers with a version number.
• A version decision. PostgreSQL 18.6 is the current release, and 18 is the line to install on a new machine.
• A port decision. The default is 5432, and you should keep it unless something else on the machine already listens there.
• A password for the postgres superuser that you will not lose, because the wizard asks for it once and never shows it again.

If node –version answers with an error instead, work through the Node.js installation guide first, because nothing below runs without it.

Install PostgreSQL on Windows

The Windows build comes from EnterpriseDB rather than from the PostgreSQL project’s own servers. Accept the defaults unless a default conflicts with something already running on the machine.

Download the installer

Open the PostgreSQL downloads page on EnterpriseDB and take the Windows x86-64 build for the version you picked. The download is a few hundred megabytes, so it takes a minute on a slow line.

Run the installer wizard

Work through the screens in this order. The wizard writes nothing to disk until the last one completes, so you can step back while you decide.

Welcome. The first screen names the version you are about to install, so check that number against the build you downloaded before you click Next.

Installation directory. This screen sets where the program files go rather than where the databases go, and the default path is fine on a machine with one drive. On a machine with a small system partition, point it at a larger drive.

Components. The four checkboxes on this screen decide what lands on the machine.

• PostgreSQL Server is the database itself. You need it.
• Command Line Tools installs psql and pg_restore. You need these too, because the verification step below uses psql.
• pgAdmin 4 is a graphical client. Install it if you would rather click through tables than type SQL.
• Stack Builder downloads extra drivers and add-ons after the install. Leave it out unless you already know you need something from it.

Data directory. This second directory screen sets where your databases live. Every table you create from here on lives inside it, so give it room.

Superuser password. Type the password for the postgres account here and keep it safe, because this is the account that creates databases and roles.

Port. Whatever number ends up in this box has to appear in your connection string later, so note it down if you change it. Leave the default of 5432 unless something else already listens there.

Locale. The locale decides how the server sorts and compares text, and the default follows the operating system.

Summary. This screen repeats every choice you just made in one place. If the port or the data directory is wrong, this is the last point where going back costs nothing.

Install. Clicking Next starts a copy that is the only long wait in the process, so let it finish rather than cancelling. A half-copied install leaves a service that will not start.

Finish. Clear the checkbox that offers to launch Stack Builder, then click Finish, because nothing later in these steps needs it.

Verify the installation with psql

Open SQL Shell (psql) from the PostgreSQL folder in the Start menu, and press Enter through the server, database, port and username prompts to accept the defaults.

Type the superuser password when it asks and you land at a postgres=# prompt.

You can also skip the interactive shell and pass the whole connection as one argument. This is the form worth learning, because the Node.js code later uses the same string.

psql "postgresql://postgres:YOUR-PASSWORD@localhost:5432/postgres" -c "SELECT current_user, current_database();"

 current_user | current_database
--------------+------------------
 postgres     | postgres
(1 row)

The headings and a row of values mean the server answered, it accepted the password, and the client reached the database you named. Any other outcome prints an error instead of that little table.

I checked the server side with that command on its own, because the installer only proves it copied files.

Create the Database and a Table

I gave the application its own login rather than using the postgres superuser, because that account can drop anything on the server. It needs a database of its own and one table to query.

Create the role and the database

Run both statements as the postgres superuser.

Each statement ends in a semicolon, and the client will not run anything until it sees one. The server answers with a line per statement it accepted, so a missing line means the statement above it did not run.

CREATE ROLE shopuser LOGIN PASSWORD 'shopdevpass';
CREATE DATABASE shop OWNER shopuser;

CREATE ROLE
CREATE DATABASE

Create the table and seed rows

Connect as the new role and create a single table. Seeding a few rows gives the next SELECT something to recognise on screen.

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  price NUMERIC(10,2) NOT NULL,
  in_stock BOOLEAN NOT NULL DEFAULT true
);

INSERT INTO products (name, price, in_stock) VALUES
  ('Mechanical Keyboard', 89.00, true),
  ('USB-C Hub', 45.50, true),
  ('Laptop Stand', 32.00, false);

CREATE TABLE
INSERT 0 3

I seeded the Laptop Stand row as out of stock, so the table holds a mix of states.

A listing confirms both facts at once. The Owner column should read shopuser, which means the role can read and write the table without the superuser account.

psql "postgresql://shopuser:shopdevpass@localhost:5432/shop" -c "\dt"

            List of tables
 Schema |   Name   | Type  |  Owner
--------+----------+-------+----------
 public | products | table | shopuser
(1 row)

That role is the one your Node.js code will use.

Install the pg Driver in a Node.js Project

The driver is a single npm package, and the project around it is four commands. Run them in a new terminal, in whatever folder you keep code.

mkdir shop-app
cd shop-app
npm init -y
npm install pg

The init command writes a package.json with the default values, and the install pulls in pg plus the modules it depends on. A clean run looks like this, with no vulnerabilities reported.

A single line has to be added to package.json before any of the code below will run. The examples here use import statements, and Node.js treats a plain .js file as CommonJS until you tell it otherwise.

{
  "name": "shop-app",
  "version": "1.0.0",
  "type": "module",
  "dependencies": {
    "pg": "^8.23.0"
  }
}

Without that type line, the import in my file stopped with a SyntaxError saying it cannot use an import statement outside a module. Adding the line is the whole fix, and no file has to be renamed.

Connect With a Pool and Run a Query

Everything up to this point was setup.

The connection string is the piece you will edit when something breaks, and it is the one line I kept editing while getting this working.

Build the connection string

The string is a URI with five values packed into it. Each value has a default, and each one also has a failure message attached to it.

postgresql://shopuser:shopdevpass@localhost:5432/shop

The driver also reads PGHOST, PGPORT, PGUSER, PGPASSWORD and PGDATABASE from the environment. Supplying those instead of the string lets the same code run against a different server with no edit.

Open a pool instead of a client

A single Client is one connection you open and close yourself.

A Pool keeps a set of connections open and lends one to each query, which matters because the opening handshake costs 20 to 30 milliseconds every time. Creating a pool inside a request handler throws that saving away, so aim for one pool per process.

import pg from 'pg';

const { Pool } = pg;

export const pool = new Pool({
  connectionString: 'postgresql://shopuser:shopdevpass@localhost:5432/shop',
});

pool.on('error', (err) => {
  console.error('Idle client error:', err.message);
});

I put the pool in db.js and export it from there, so every file in the project imports the same one.

Run your first query

index.js imports that pool, sends one statement, prints what comes back and closes the pool. Save it next to db.js and run it with node index.js.

import { pool } from './db.js';

const { rows } = await pool.query(
  'SELECT id, name, price FROM products WHERE in_stock = $1 ORDER BY id',
  [true]
);

console.table(rows);

await pool.end();

The dollar-one marker is not text substitution. The driver sends the statement and the values as two separate fields, and PostgreSQL puts the value in after it has parsed the query.

I printed the rows with console.table instead of console.log, because a row logged as a plain object drops the column names. The result holds the two in-stock rows, because the third row is stored as out of stock.

Insert without concatenating strings

The same placeholder works on a write, and RETURNING hands you the row the server stored rather than a count of rows touched.

import { pool } from './db.js';

const name = 'Desk Lamp';
const price = 24.99;

const { rows } = await pool.query(
  'INSERT INTO products (name, price) VALUES ($1, $2) RETURNING id, name, price',
  [name, price]
);

console.log('Inserted:', rows[0]);

await pool.end();

Building the same statement with a template literal is where a quote inside the value stops being your data. I tried it with a name containing an apostrophe and the server answered with error 42601, a syntax error at or near the rest of the name, and the row was never written.

I passed the same name through the values array and it inserted without complaint. The parameterized version never gives the value to the parser, so there is nothing for a quote to close early.

When the Connection Fails

The failures below stop a first attempt, and each one points at a different part of the string you just built. The first two come from the server refusing, and the last one comes from your own process staying alive.

The server is not listening on that port

I pointed the connection string at port 5433 to see what a wrong port produces, so save this as bad-port.js and run it.

import pg from 'pg';

const { Client } = pg;
const client = new Client({
  connectionString: 'postgresql://shopuser:shopdevpass@localhost:5433/shop',
});

try {
  await client.connect();
} catch (err) {
  console.error(err.code, err.message);
  process.exitCode = 1;
}

ECONNREFUSED is a TCP error, which is why the database never gets a chance to say anything. The message names the address and the port, so compare that port against the number you chose in the wizard.

The same message arrives when the port is right and the PostgreSQL service is stopped. A closed port and a missing listener look identical from outside, so check the service is running before you edit the string.

The password or the role is wrong

Change the password in the string to something wrong and the failure moves to the server, because the TCP connection now succeeds.

import pg from 'pg';

const { Client } = pg;
const client = new Client({
  connectionString: 'postgresql://shopuser:wrongpassword@localhost:5432/shop',
});

try {
  await client.connect();
} catch (err) {
  console.error(err.code, err.message);
  process.exitCode = 1;
}

Code 28P01 means the server was reached and the login was rejected. The message repeats the role name it tried, so a typo in the user field shows up here as well as a wrong password.

A message about no pg_hba.conf entry for host is different, because the password was fine and the server’s own rules refused your address. That file lives in the data directory you chose in the wizard, on the server, not inside your Node.js project.

The process never exits

A pool holds open sockets, and Node.js will not leave the event loop while one of them is alive. A script that runs a query and skips the last line looks like it has hung.

import { pool } from './db.js';

const { rows } = await pool.query('SELECT count(*)::int AS products FROM products');
console.log(rows[0]);

{ products: 4 }
[exit 124]

The count prints, and then the command had to be cut off with a timeout, because exit code 124 is how a timeout reports a process that never finished on its own.

Adding the pool.end() line from index.js is the fix. A long-running server should call it once on shutdown rather than after every query.

Size the Pool Before Your App Gets Busy

The pool opens clients as queries arrive and stops at ten by default. I left max at that number while testing, and it is the setting to move once an API sits behind the app.

export const pool = new Pool({
  connectionString: 'postgresql://shopuser:shopdevpass@localhost:5432/shop',
  max: 10,
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 5000,
});

The max option caps how many clients this pool will open, and idleTimeoutMillis closes one that has sat unused for that long. connectionTimeoutMillis is the wait a query allows for a free client before it gives up.

A Node.js cluster of four processes, each holding ten connections, adds up to forty, and PostgreSQL refuses the forty-first once its max_connections limit is reached.

Start the app again with node index.js and watch the server while it runs. The connection count in pg_stat_activity against max_connections is the pair of numbers that tells you whether the pool is sized for the machine.

FAQ

The questions below come up once the first query works and the local setup has to turn into something you deploy.