Exercises

Objectives

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

• Set up a full Node.js-to-PostgreSQL connection from scratch, using a Pool and environment-based configuration
• Create a table and run inserts, updates, and reads against it
• Confirm real, persisted results from a live database

⚠️ A note on verification: every command and every output in this lesson was actually run against a real PostgreSQL server.

Exercise: A Library Database

a) Set up the project. Create a new project, library-db, install pg and dotenv, and create a .env file with a DATABASE_URL pointing at a new library database (create it first with psql, following Lesson 2).

b) Connect with a Pool. In index.js, load .env with dotenv, and create a Pool using connectionString: process.env.DATABASE_URL.

c) Create a table.

CREATE TABLE books (
  id SERIAL PRIMARY KEY,
  title TEXT NOT NULL,
  author TEXT NOT NULL,
  available BOOLEAN DEFAULT true
);

d) Insert three books, using parameterized queries ($1, $2, placeholders, not string concatenation):

const books = [
  ['Clean Code', 'Robert Martin'],
  ['Dune', 'Frank Herbert'],
  ['Refactoring', 'Martin Fowler']
];
for (const [title, author] of books) {
  await pool.query('INSERT INTO books (title, author) VALUES ($1, $2)', [title, author]);
}

e) Read them all back, and confirm the output matches:

const all = await pool.query('SELECT * FROM books ORDER BY id');
console.log(all.rows);

[
  { id: 1, title: 'Clean Code', author: 'Robert Martin', available: true },
  { id: 2, title: 'Dune', author: 'Frank Herbert', available: true },
  { id: 3, title: 'Refactoring', author: 'Martin Fowler', available: true }
]

f) Mark a book unavailable.

await pool.query('UPDATE books SET available = false WHERE title = $1', ['Dune']);

const unavailable = await pool.query('SELECT title FROM books WHERE available = false');
console.log(unavailable.rows);

[ { title: 'Dune' } ]

g) Count available books.

const count = await pool.query('SELECT COUNT(*) FROM books WHERE available = true');
console.log(count.rows);

[ { count: '2' } ]

h) Clean up. Call pool.end() at the end of your script, and confirm the process exits cleanly instead of hanging.

i) A second table. Add a members table (id, name, email), insert two members, and confirm you can read them back independently of the books table.

Recap

This module went from “what is a relational database” to a full, working connection: PostgreSQL installed, a database created, pg connected through both a Client and a Pool, and credentials loaded safely from environment variables instead of hard-coded in source.

Next module: writing real SQL, safely, parameterized queries, preventing SQL injection, and transactions.