Integrating Node.js with PostgreSQL

1. Introduction

Integrating Node.js with PostgreSQL allows developers to build robust and scalable applications that leverage the power of a relational database. This lesson will cover the necessary steps to successfully connect Node.js with PostgreSQL and perform basic CRUD operations.

2. Setup

CREATE DATABASE mydatabase;
CREATE USER myuser WITH PASSWORD 'mypassword';
GRANT ALL PRIVILEGES ON DATABASE mydatabase TO myuser;

2.3 Initialize Node.js Project

mkdir myproject
cd myproject
npm init -y
npm install pg

3. Connecting to PostgreSQL

To connect to PostgreSQL from your Node.js application, you will use the pg library.

const { Pool } = require('pg');

const pool = new Pool({
    user: 'myuser',
    host: 'localhost',
    database: 'mydatabase',
    password: 'mypassword',
    port: 5432,
});

pool.connect()
    .then(() => console.log('Connected to PostgreSQL'))
    .catch(err => console.error('Connection error', err.stack));

4. Performing Database Operations

4.1 Inserting Data

const insertData = async (name) => {
    const res = await pool.query('INSERT INTO users(name) VALUES($1) RETURNING *', [name]);
    console.log('Inserted:', res.rows[0]);
};

insertData('John Doe');

4.2 Querying Data

const getUsers = async () => {
    const res = await pool.query('SELECT * FROM users');
    console.log('Users:', res.rows);
};

getUsers();

4.3 Updating Data

const updateUser = async (id, name) => {
    const res = await pool.query('UPDATE users SET name = $1 WHERE id = $2', [name, id]);
    console.log('Updated:', res.rowCount);
};

updateUser(1, 'Jane Doe');

4.4 Deleting Data

const deleteUser = async (id) => {
    const res = await pool.query('DELETE FROM users WHERE id = $1', [id]);
    console.log('Deleted:', res.rowCount);
};

deleteUser(1);

5. Best Practices

• Use Connection Pooling: Always use connection pooling for better performance.
• Handle Errors Properly: Always include error handling in your database queries.
• Keep Secrets Safe: Avoid hardcoding sensitive information such as passwords in your code.
• Use Parameterized Queries: To prevent SQL injection attacks, always use parameterized queries.
• Close Connections: Ensure to close your database connections when not in use.

6. FAQ