w3resource

How to Use PostgreSQL with Node.js: Complete Guide with Examples


Using PostgreSQL with Node.js: Setup and Guide

Connecting PostgreSQL with Node.js enables you to develop scalable applications with a powerful database backend. This guide explains how to configure PostgreSQL for Node.js, manage database connections, perform CRUD operations, and offers helpful examples using the popular pg Node.js library.

Step 1: Install pg Library

The pg package is a widely-used PostgreSQL client for Node.js. Install it with npm:

npm install pg

Step 2: Set Up PostgreSQL Database Configuration

Create a configuration file to store the connection details. Here’s an example of how to set up a basic connection in index.js:

Code:

// Import the 'pg' library
const { Client } = require('pg');

// Set up database connection details
const client = new Client({
  user: 'your_username',      // PostgreSQL username
  host: 'localhost',           // Database host
  database: 'your_database',   // Database name
  password: 'your_password',   // PostgreSQL password
  port: 5432,                  // PostgreSQL default port
});

// Connect to the database
client.connect()
  .then(() => console.log('Connected to PostgreSQL'))
  .catch(err => console.error('Connection error', err.stack));

Step 3: Create a Table Using Node.js

With the database connection set up, let’s create a table in PostgreSQL:

Code:

// Define a SQL query to create a table
const createTableQuery = `
  CREATE TABLE IF NOT EXISTS users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(50) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
  );
`;

// Execute the query
client.query(createTableQuery)
  .then(() => console.log('Table created successfully'))
  .catch(err => console.error('Error creating table', err.stack));

Step 4: CRUD Operations with PostgreSQL and Node.js

Django uses migrations to apply model changes to the database schema. Run the following commands to create and apply migrations.

Insert Data

Code:

// Function to insert a new user
const insertUser = async (name, email) => {
  const insertQuery = `INSERT INTO users (name, email) VALUES ($1, $2) RETURNING *`;
  try {
    const res = await client.query(insertQuery, [name, email]);
    console.log('User inserted:', res.rows[0]);
  } catch (err) {
    console.error('Error inserting user', err.stack);
  }
};

// Example usage
insertUser(‘Jeff Tresha’, ' [email protected]');

Query Data

Code:

// Function to fetch all users
const fetchUsers = async () => {
  try {
    const res = await client.query('SELECT * FROM users');
    console.log('All users:', res.rows);
  } catch (err) {
    console.error('Error fetching users', err.stack);
  }
};

// Example usage
fetchUsers();

Update Data

Code:

// Function to update a user’s email
const updateUser = async (id, newEmail) => {
  const updateQuery = `UPDATE users SET email = $1 WHERE id = $2 RETURNING *`;
  try {
    const res = await client.query(updateQuery, [newEmail, id]);
    console.log('User updated:', res.rows[0]);
  } catch (err) {
    console.error('Error updating user', err.stack);
  }
};

// Example usage
updateUser(1, '[email protected]');

Delete Data

// Function to delete a user by ID
const deleteUser = async (id) => {
  const deleteQuery = `DELETE FROM users WHERE id = $1 RETURNING *`;
  try {
    const res = await client.query(deleteQuery, [id]);
    console.log('User deleted:', res.rows[0]);
  } catch (err) {
    console.error('Error deleting user', err.stack);
  }
};

// Example usage
deleteUser(1);

Explanation of Code:

  • Install pg: Installs the Node.js PostgreSQL library for managing database connections.
  • Database Configuration: Configures database credentials, such as username, password, and host.
  • Table Creation: Creates a table with columns for user ID, name, email, and timestamp.
  • CRUD Functions: Defines separate functions for INSERT, SELECT, UPDATE, and DELETE operations on the users table. Each function uses parameterized queries to prevent SQL injection.

Additional Information:

  • Connection Pooling: For larger applications, use Pool instead of Client from the pg library for optimized connection handling.
  • Error Handling: Use try-catch blocks to manage errors gracefully and avoid exposing sensitive information.
  • Environment Variables: Store database credentials in environment variables for better security.

All PostgreSQL Questions, Answers, and Code Snippets Collection.



Become a Patron!

Follow us on Facebook and Twitter for latest update.

It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.

https://www.w3resource.com/PostgreSQL/snippets/postgresql-node-setup.php