Connecting to PostgreSQL with pg

// npm install pg
const { Pool } = require('pg');

const pool = new Pool({
    host:     process.env.DB_HOST     || 'localhost',
    port:     parseInt(process.env.DB_PORT || '5432', 10),
    database: process.env.DB_NAME     || 'myapp',
    user:     process.env.DB_USER     || 'postgres',
    password: process.env.DB_PASS     || '',
    max:      10,           // max connections in pool
    idleTimeoutMillis: 30_000,
    connectionTimeoutMillis: 2_000,
});

// Test the connection:
pool.query('SELECT NOW()', (err, res) => {
    if (err) console.error('DB connection error:', err);
    else console.log('DB connected at:', res.rows[0].now);
});

// Query with parameterised values (prevents SQL injection):
async function getUserById(id) {
    const result = await pool.query(
        'SELECT id, username, email FROM users WHERE id = $1',
        [id]
    );
    return result.rows[0] || null;
}

// Insert and get the new row:
async function createUser(username, email, passwordHash) {
    const result = await pool.query(
        'INSERT INTO users (username, email, password) VALUES ($1, $2, $3) RETURNING *',
        [username, email, passwordHash]
    );
    return result.rows[0];
}

// Update:
async function updateEmail(userId, newEmail) {
    await pool.query(
        'UPDATE users SET email = $1 WHERE id = $2',
        [newEmail, userId]
    );
}

module.exports = { pool, getUserById, createUser, updateEmail };