How to connect to PostgreSQL using Node.js

Node.js applications that need to read or write data from a PostgreSQL database require a driver to handle the connection. The node-postgres package (pg) provides that driver, along with support for queries, connection pooling and async/await syntax.

This guide covers installing the pg package over SSH and writing a Node.js script that connects to your PostgreSQL database and runs a query.

Install the node-postgres package

The pg package is installed via npm and must be present in your project before you can import it. These steps install it into your home directory or project folder over SSH.

1. Connect to your server via SSH.
   Open a terminal and log in to your server. See how to connect and use SSH if you need help with this step.
2. Navigate to your project directory.
   Change into the directory where your Node.js application lives, for example: cd ~/myapp
3. Install the pg package.
   Run the following command to add pg as a dependency:

npm install pg

npm downloads and installs the package into a node_modules folder in your project directory. You are now ready to use it in your application code.

Connect to PostgreSQL and run a query

The pg package exposes a Client class that manages a single database connection. You create a client with your connection details, call connect(), run your queries, then call end() to close the connection cleanly. The example below uses async/await, which is the current recommended approach.

Create a file called db.mjs in your project directory and add the following code. Replace the placeholder values with your actual database credentials:

import { Client } from 'pg';

const client = new Client({
    host: 'localhost',
    database: 'your_database_name',
    user: 'your_database_user',
    password: 'your_database_password',
});

await client.connect();

try {
    const res = await client.query('SELECT * FROM your_table_name');
    console.log(res.rows);
} catch (err) {
    console.error('Query error:', err);
} finally {
    await client.end();
}

Replace your_database_name, your_database_user, your_database_password and your_table_name with your actual values. The host value of localhost is correct when your application and database run on the same server.

1. Run the script.
   Execute the file from your project directory:

node db.mjs

The query results print to the terminal as an array of row objects. If the output is empty, the table exists but contains no rows. If you see an authentication error, double-check your database username and password.

Troubleshooting

SyntaxError: Cannot use import statement outside a module

This error appears when Node.js does not recognise the import syntax because the project is not configured as an ES module. You have two options to fix this.

Option 1: Save your file with a .mjs extension instead of .js. Node.js treats .mjs files as ES modules automatically.

Option 2: Add a package.json file to your project directory with the following content:

{
    "type": "module"
}

This tells Node.js to treat all .js files in the project as ES modules. Re-run your script after making either change.

Connection refused or timeout errors

These errors indicate the application cannot reach the PostgreSQL server. Common causes include:

• The host value is incorrect. Use localhost when the database is on the same server as your application.
• PostgreSQL is not running. Check the service status on your VPS.
• Your application is on a different server to the database. Remote SQL connections are not supported on shared hosting, use an SSH tunnel instead.
• A firewall rule is blocking the PostgreSQL port (5432). See our guide on opening ports in UFW if you need to allow access.

Authentication failed for user

PostgreSQL rejected the credentials supplied in your connection object. Check the following:

• The user and password values match the credentials you set when creating the database user.
• The database user has been granted access to the target database.
• There are no extra spaces or quote characters in the credential strings.

Wrapping up

You installed the pg package via npm and wrote a Node.js script that connects to a PostgreSQL database, runs a query and closes the connection. The try/catch/finally pattern in the example keeps error handling and connection cleanup separate from your query logic.

From here you can extend the script to run INSERT, UPDATE or DELETE statements using the same client.query() method. For applications that handle multiple concurrent requests, consider switching from a single Client to a connection pool. The pg package includes a Pool class for this purpose. See our related guides on connecting to PostgreSQL using psql and connecting to a database over an SSH tunnel for further reading.

Running Node.js applications in production works best on a server where you control the runtime environment. See our Node.js hosting plans for a platform built around Node.js workloads.