Connecting Node.js to PostgreSQL with `pg`

1. The Bridge: node-postgres (pg)

To allow our JavaScript code to "talk" to the PostgreSQL server, we need a translator or a client. For Node.js, the standard and most popular client is node-postgres, which is available on npm as the package pg.

This library is the key to executing SQL queries, retrieving data, and managing our connection from within our Node.js application.

2. Setting Up Your Project

Let's get your project ready to connect to the database. This involves installing the necessary packages and, most importantly, managing your database credentials securely.

Step 1: Install Dependencies

In your project's terminal, install two packages: pg for the database connection and dotenv to manage environment variables.

npm install pg dotenv

Why dotenv?
We use dotenv to load sensitive information, like database passwords, from a .env file into our application's environment. This prevents us from hardcoding secrets directly into our source code, which is a major security risk.

Step 2: Create a .env File

In the root directory of your project, create a new file named .env. This file is where you'll store the connection details for the database and user you created in the last lesson.

Add the following content to your .env file, replacing 'your_password_here' with the actual password you set for task_manager_user:

# .env

DB_HOST=localhost
DB_PORT=5432
DB_DATABASE=task_manager
DB_USER=task_manager_user
DB_PASSWORD='your_password_here'

Step 3: Update .gitignore

This is a critical security step. You must ensure your .env file is never committed to version control (like Git). Add it to your .gitignore file.

# .gitignore

node_modules
.env

3. Creating the Connection Pool

For a web application that will handle multiple user requests, creating a new database connection for every single request is highly inefficient. The process of establishing a connection has significant overhead.

The professional approach is to use a connection pool. A pool is a cache of database connections that your application can borrow from and return to. This dramatically improves performance and makes your application more resilient.

Now, let's implement this. We will create a dedicated module in our application responsible for configuring and exporting our database connection pool.

Following the guide, you should have a db.js file that looks something like this:

// db.js
const { Pool } = require('pg');
require('dotenv').config();

const pool = new Pool({
  host: process.env.DB_HOST,
  port: process.env.DB_PORT,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_DATABASE,
});

module.exports = pool;

4. Verifying the Connection

With the pool configured, we need to test it to make sure everything is working. The pg library gives us a simple way to execute queries directly on the pool object. The library conveniently handles the process of checking out a client from the pool, running the query, and releasing the client back to the pool.

Let's apply this to our project. To keep our code organized, we'll modify db.js to export a query function instead of the entire pool object. This is a good practice that encapsulates the database logic.

Update your db.js file to look like this:

// db.js
const { Pool } = require('pg');
require('dotenv').config();

const pool = new Pool({
  user: process.env.DB_USER,
  host: process.env.DB_HOST,
  database: process.env.DB_DATABASE,
  password: process.env.DB_PASSWORD,
  port: process.env.DB_PORT,
});

// Export a query function that uses the pool
module.exports = {
  query: (text, params) => pool.query(text, params),
};

Now, in your main index.js file (or a temporary test file), you can import this query function and use it to verify the connection. Add this code to index.js:

// At the top of index.js, with your other requires
const db = require('./db'); 

// ... your existing Express app setup ...

// Add a simple async function to test the connection
async function testDbConnection() {
  try {
    const result = await db.query('SELECT NOW()');
    console.log('Database connection successful. Current time:', result.rows[0].now);
  } catch (error) {
    console.error('Database connection failed:', error);
  }
}

// Call the function
testDbConnection();

Run your application with node index.js or nodemon index.js. If everything is configured correctly, you should see a message in your console confirming the successful database connection along with the current timestamp from the database server.

Conclusion

Congratulations! You have successfully connected your Node.js application to your PostgreSQL database. This is a cornerstone skill for any backend developer.

Key Takeaways:

• The pg library is the standard Node.js client for connecting to PostgreSQL.
• Connection pools are essential for building performant and scalable web applications.
• Storing database credentials in a .env file and keeping it out of version control with .gitignore is a non-negotiable security practice.
• You can create a dedicated module (db.js) to encapsulate connection logic and export a query function for use throughout your application.

Preview of the Next Lesson:
The bridge is built. Now it's time to define what kind of data will cross it. In the next lesson, you will design a database schema and create tables with appropriate data types and constraints. We'll move from simple test queries to writing the actual SQL commands that will form the structure of our task_manager application.