🔌 BD03 — PostgreSQL & Node.js

1. Installation et configuration

npm install pg
npm install --save-dev @types/pg  # TypeScript

// db.js
const { Pool } = require('pg');

const pool = new Pool({
  host:     process.env.PG_HOST     || 'localhost',
  port:     parseInt(process.env.PG_PORT || '5432'),
  database: process.env.PG_DATABASE || 'myapp',
  user:     process.env.PG_USER     || 'myuser',
  password: process.env.PG_PASSWORD,
  max:      20,           // connexions max dans le pool
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,
});

pool.on('error', (err) => {
  console.error('Unexpected error on idle client', err);
  process.exit(-1);
});

module.exports = pool;

2. Pool de connexions

const pool = require('./db');

// Requête simple via le pool (connexion auto-release)
async function getUsers() {
  const result = await pool.query('SELECT * FROM users ORDER BY created_at DESC');
  return result.rows;
}

// Connexion dédiée (transactions, verrous)
async function withClient(fn) {
  const client = await pool.connect();
  try {
    return await fn(client);
  } finally {
    client.release();
  }
}

3. Requêtes paramétrées

// ✅ Correct — paramètres positionnels
async function getUserByEmail(email) {
  const { rows } = await pool.query(
    'SELECT id, email, role FROM users WHERE email = $1 AND active = true',
    [email]
  );
  return rows[0] || null;
}

// ❌ Dangereux — injection SQL possible
// pool.query(`SELECT * FROM users WHERE email = '${email}'`);

// INSERT avec RETURNING
async function createUser(email, passwordHash) {
  const { rows } = await pool.query(
    `INSERT INTO users (email, password_hash, created_at)
     VALUES ($1, $2, NOW())
     RETURNING id, email, created_at`,
    [email, passwordHash]
  );
  return rows[0];
}

// UPDATE avec plusieurs paramètres
async function updateProduct(id, name, price) {
  const { rowCount } = await pool.query(
    'UPDATE products SET name = $1, price = $2 WHERE id = $3',
    [name, price, id]
  );
  return rowCount > 0;
}

// Prepared statement (réutilisation du plan de requête)
await pool.query({
  name: 'fetch-user',
  text: 'SELECT * FROM users WHERE id = $1',
  values: [42]
});

4. Transactions en Node.js

async function transferMoney(fromId, toId, amount) {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');

    const { rows } = await client.query(
      'SELECT balance FROM accounts WHERE id = $1 FOR UPDATE',
      [fromId]
    );
    if (!rows[0] || rows[0].balance < amount) {
      throw new Error('Solde insuffisant');
    }

    await client.query(
      'UPDATE accounts SET balance = balance - $1 WHERE id = $2',
      [amount, fromId]
    );
    await client.query(
      'UPDATE accounts SET balance = balance + $1 WHERE id = $2',
      [amount, toId]
    );

    await client.query('COMMIT');
    return { success: true };
  } catch (err) {
    await client.query('ROLLBACK');
    throw err;
  } finally {
    client.release();
  }
}

// Helper générique de transaction
async function withTransaction(fn) {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    const result = await fn(client);
    await client.query('COMMIT');
    return result;
  } catch (err) {
    await client.query('ROLLBACK');
    throw err;
  } finally {
    client.release();
  }
}

5. Curseurs & Streaming

const { QueryStream } = require('pg-query-stream');
// npm install pg-query-stream

// Streaming de millions de lignes sans saturer la RAM
async function exportLargeDataset(res) {
  const client = await pool.connect();
  try {
    const query = new QueryStream(
      'SELECT id, email, created_at FROM users ORDER BY id',
      [],
      { highWaterMark: 100 }
    );
    const stream = client.query(query);
    stream.on('data', (row) => {
      res.write(JSON.stringify(row) + '\n');
    });
    stream.on('end', () => {
      res.end();
      client.release();
    });
    stream.on('error', (err) => {
      client.release();
      throw err;
    });
  } catch (err) {
    client.release();
    throw err;
  }
}

6. LISTEN / NOTIFY

// PostgreSQL → Node.js via notification asynchrone
const client = await pool.connect();
await client.query('LISTEN new_order');

client.on('notification', (msg) => {
  const payload = JSON.parse(msg.payload);
  console.log('Nouvelle commande :', payload);
  // Déclencher traitement asynchrone, WebSocket, email...
});

// Dans PostgreSQL (trigger ou application)
await pool.query(
  "SELECT pg_notify('new_order', $1)",
  [JSON.stringify({ order_id: 42, total: 99.99 })]
);

7. Bonnes pratiques de sécurité

• Utiliser un utilisateur PostgreSQL dédié avec les droits minimum nécessaires
• Stocker les credentials dans des variables d'environnement (jamais en dur)
• Toujours utiliser des requêtes paramétrées ($1, $2)
• Limiter les connexions : max dans le pool + max_connections PG
• Activer SSL : ssl: { rejectUnauthorized: true, ca: fs.readFileSync('ca.pem') }
• Valider les entrées utilisateur avant tout passage en base

// Pool avec SSL
const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  ssl: process.env.NODE_ENV === 'production'
    ? { rejectUnauthorized: false }  // Heroku, Railway
    : false
});