PostgreSQL connection with pg in Node.js - Step-by-Step Execution

Concept Flow - PostgreSQL connection with pg

Import pg module

↓

Create Client with config

↓

Call client.connect()

↓

Connection established?

No→Error Handling

Yes↓

Use client to query database

↓

Process query results

↓

Close connection with client.end()

This flow shows how to import the pg module, create a client, connect to PostgreSQL, run queries, and close the connection.

Execution Sample

Node.js

import { Client } from 'pg';

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

await client.connect();

This code imports pg, creates a client with connection info, and connects to the PostgreSQL server.

Execution Table

Step	Action	Evaluation	Result
1	Import pg Client	Module 'pg' imported	Client class ready
2	Create Client instance	Client config set	Client object created
3	Call client.connect()	Attempt connection to DB	Connection successful
4	Run query client.query('SELECT 1')	Send query to DB	Query result received
5	Process query result	Extract rows	Rows available
6	Call client.end()	Close connection	Connection closed

💡 Connection closed after query and processing

Variable Tracker

Variable	Start	After Step 2	After Step 3	After Step 6
client	undefined	Client object with config	Connected client instance	Closed client instance

Key Moments - 2 Insights

Why do we need to call client.connect() before querying?

Because the client must establish a connection to the database server first, as shown in step 3 of the execution_table. Without connecting, queries cannot be sent.

What happens if client.end() is not called?

The connection stays open, which can cause resource leaks. Step 6 shows closing the connection to free resources.

Visual Quiz - 3 Questions

Look at the execution_table, what is the client state after step 3?

A Connection successful and client ready

B Connection failed

C Client object created but not connected

D Connection closed

💡 Hint

Refer to row 3 in execution_table where client.connect() is called and connection is successful

At which step is the query sent to the database?

A Step 2

B Step 3

C Step 4

D Step 6

💡 Hint

Look at the 'Action' column in execution_table row 4 for query sending

If client.end() is skipped, what changes in variable_tracker after step 6?

A client becomes undefined

B client remains connected

C client is closed anyway

D client throws error

💡 Hint

Check variable_tracker for client state after step 6 and consider what happens if end() is not called

Concept Snapshot

PostgreSQL connection with pg:
1. Import Client from 'pg'.
2. Create Client with config (user, host, db, password, port).
3. Call client.connect() to open connection.
4. Use client.query() to run SQL.
5. Process results.
6. Call client.end() to close connection.

Full Transcript

This lesson shows how to connect to a PostgreSQL database using the 'pg' module in Node.js. First, we import the Client class from 'pg'. Then, we create a new Client instance with connection details like user, host, database, password, and port. Next, we call client.connect() to establish the connection. After connecting, we can run queries using client.query(). Once done, we process the results and finally call client.end() to close the connection and free resources. Each step is important to ensure proper communication with the database and resource management.