PostgreSQL connection with pg in Node.js - Mini Project: Build & Apply

PostgreSQL connection with pg

📖 Scenario: You are building a simple Node.js app that connects to a PostgreSQL database to fetch user data.

🎯 Goal: Create a Node.js script that connects to PostgreSQL using the pg library, sets up connection configuration, queries the database for all users, and closes the connection properly.

📋 What You'll Learn

Use the pg library to connect to PostgreSQL

Create a client with exact connection parameters

Write an async function to query all rows from the users table

Close the database connection after the query

💡 Why This Matters

🌍 Real World

Connecting Node.js apps to PostgreSQL databases is common for web apps, APIs, and data processing.

💼 Career

Backend developers often use pg to interact with PostgreSQL databases for data storage and retrieval.

1

Import pg and create a client

Write import { Client } from 'pg' at the top and create a new Client instance called client with no parameters yet.

Node.js

import { Client } from 'pg'

// Create a new Client instance
// Your code here

Need a hint?

Use ES module import syntax and create a client with new Client().

2

Add connection configuration

Modify the Client instance to include these exact connection parameters: user: 'testuser', host: 'localhost', database: 'testdb', password: 'testpass', and port: 5432.

Node.js

import { Client } from 'pg'

// Create a new Client instance with connection config
// Your code here

Need a hint?

Pass an object with the exact keys and values to new Client().

3

Write async function to query users

Write an async function called fetchUsers that connects the client, runs SELECT * FROM users query using client.query, stores the result in res, and returns res.rows.

Node.js

import { Client } from 'pg'

const client = new Client({
  user: 'testuser',
  host: 'localhost',
  database: 'testdb',
  password: 'testpass',
  port: 5432
})

// Write async function fetchUsers
// Your code here

Need a hint?

Use await client.connect() before querying and return res.rows.

4

Close the client connection

Add a line inside fetchUsers after getting the rows to close the client connection using await client.end().

Node.js

import { Client } from 'pg'

const client = new Client({
  user: 'testuser',
  host: 'localhost',
  database: 'testdb',
  password: 'testpass',
  port: 5432
})

async function fetchUsers() {
  await client.connect()
  const res = await client.query('SELECT * FROM users')
  // Close the client connection
  // Your code here
  return res.rows
}

Need a hint?

Call await client.end() to close the connection after the query.