The pg Driver and Connecting

Objectives

By the end of this lesson, you should be able to:

• Install and use pg, the standard PostgreSQL driver for Node.js
• Connect to a PostgreSQL database with a Client
• Run a query and read back the results

💡 Why this matters: pg is the foundation everything else in this course sits on, raw queries in Module 2, and even Prisma later, generates SQL that ultimately runs through a driver just like this one. Understanding it directly means nothing later is a black box.

⚠️ A note on verification: every snippet and every result shown below was actually run against a real PostgreSQL server.

Project File Structure

This lesson’s project is a single script connecting to the school database created in the last lesson:

pg-intro/
├── package.json
└── index.js

Installing pg

mkdir pg-intro && cd pg-intro
npm init -y
npm install pg

Connecting with a Client

pg exports a Client class, a single connection to the database:

// index.js
const { Client } = require('pg');

const client = new Client({
  host: 'localhost',
  port: 5432,
  user: 'postgres',
  password: 'postgres',
  database: 'school'
});

async function main() {
  await client.connect();
  console.log('Connected!');
  await client.end();
}

main();

node index.js

Connected!

Running a Query

Create a table and insert a couple of rows, then read them back:

async function main() {
  await client.connect();

  await client.query('CREATE TABLE students (id SERIAL PRIMARY KEY, name TEXT NOT NULL, grade INTEGER)');
  await client.query("INSERT INTO students (name, grade) VALUES ('Erin', 9), ('Jordan', 10)");

  const result = await client.query('SELECT * FROM students ORDER BY id');
  console.log(result.rows);
  console.log('Row count:', result.rowCount);

  await client.end();
}

main();

[ { id: 1, name: 'Erin', grade: 9 }, { id: 2, name: 'Jordan', grade: 10 } ]
Row count: 2

client.query() returns a result object, rows is an array of plain JavaScript objects, one per row, and rowCount is how many rows were affected or returned. This is the exact same shape every query in this course reads results from.

Always Close the Connection

client.end() closes the connection. Forgetting it leaves the Node process open (and, in a real app, leaks connections), which is part of why the next lesson introduces a connection pool instead of managing single clients by hand.

Try It

1. Build the pg-intro project above, and confirm the exact output shown.
2. Add a third student to the INSERT statement, and confirm it shows up in the SELECT results.
3. Try running a query after calling client.end(), and read the error it produces. Explain, in your own words, why it happens.
4. Change the database field to a name that doesn’t exist, and observe the connection error.

Recap

• pg is the standard, low-level PostgreSQL driver for Node.js.
• A Client represents a single connection, client.connect() opens it, client.end() closes it.
• client.query() returns rows (the data) and rowCount (how many rows were affected or returned).

Next lesson: connection pooling, and why real applications use a Pool instead of a single Client.