Node.js — pg moduli, connection pool, CRUD

~25 daq · PostgreSQL · PostgreSQL va Node.js

PostgreSQL ni Node.js dan ishlatish uchun pg moduli — eng mashhur va keng tarqalgan kutubxona.

O'rnatish

npm install pg

Ulanish — Pool

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

const pool = new Pool({
  host:     process.env.DB_HOST     || 'localhost',
  port:     process.env.DB_PORT     || 5432,
  database: process.env.DB_NAME     || 'myapp',
  user:     process.env.DB_USER     || 'postgres',
  password: process.env.DB_PASSWORD || 'parol',
  max:      20,                  // maksimal ulanishlar
  idleTimeoutMillis:    30_000,  // bo'sh ulanish yopilish vaqti
  connectionTimeoutMillis: 2000, // ulanish kutish vaqti
});

module.exports = pool;

.env faylida:

DB_HOST=localhost
DB_PORT=5432
DB_NAME=myapp
DB_USER=api_user
DB_PASSWORD=kuchli_parol

SELECT — ma'lumot olish

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

// Barcha foydalanuvchilar
async function barcha() {
  const { rows } = await pool.query('SELECT * FROM foydalanuvchilar');
  return rows;
}

// ID bo'yicha — parameterized query (SQL Injection yo'q)
async function bittasiId(id) {
  const { rows } = await pool.query(
    'SELECT * FROM foydalanuvchilar WHERE id = $1',
    [id]
  );
  return rows[0] || null;
}

// Filtrlash
async function faollar() {
  const { rows } = await pool.query(
    'SELECT id, ism, email FROM foydalanuvchilar WHERE faol = $1 ORDER BY ism',
    [true]
  );
  return rows;
}

INSERT — qo'shish

async function yaratish(ism, email, parol_hash) {
  const { rows } = await pool.query(
    `INSERT INTO foydalanuvchilar (ism, email, parol_hash)
     VALUES ($1, $2, $3)
     RETURNING id, ism, email, yaratilgan`,
    [ism, email, parol_hash]
  );
  return rows[0]; // yangi yaratilgan foydalanuvchi
}

UPDATE — yangilash

async function yangilash(id, ism, email) {
  const { rows, rowCount } = await pool.query(
    `UPDATE foydalanuvchilar
     SET ism = $2, email = $3, yangilangan = NOW()
     WHERE id = $1
     RETURNING *`,
    [id, ism, email]
  );
  if (rowCount === 0) throw new Error('Topilmadi');
  return rows[0];
}

DELETE — o'chirish

async function o_chirish(id) {
  const { rowCount } = await pool.query(
    'DELETE FROM foydalanuvchilar WHERE id = $1',
    [id]
  );
  return rowCount > 0; // true = o'chirildi, false = topilmadi
}

Xatolarni boshqarish

async function bittasiEmail(email) {
  try {
    const { rows } = await pool.query(
      'SELECT * FROM foydalanuvchilar WHERE email = $1',
      [email]
    );
    return rows[0] || null;
  } catch (err) {
    // Ulanish xatosi, so'rov xatosi
    console.error('DB xato:', err.message);
    throw err;
  }
}

Express bilan to'liq misol

const express = require('express');
const pool    = require('./db');
const app     = express();
app.use(express.json());

// GET /users
app.get('/users', async (req, res) => {
  try {
    const { rows } = await pool.query(
      'SELECT id, ism, email FROM foydalanuvchilar ORDER BY id'
    );
    res.json(rows);
  } catch (err) {
    res.status(500).json({ xato: err.message });
  }
});

// POST /users
app.post('/users', async (req, res) => {
  const { ism, email } = req.body;
  try {
    const { rows } = await pool.query(
      'INSERT INTO foydalanuvchilar (ism, email) VALUES ($1, $2) RETURNING *',
      [ism, email]
    );
    res.status(201).json(rows[0]);
  } catch (err) {
    if (err.code === '23505') { // unique violation
      res.status(409).json({ xato: 'Bu email allaqachon mavjud' });
    } else {
      res.status(500).json({ xato: err.message });
    }
  }
});

Xulosa

const { Pool } = require('pg');
const pool = new Pool({ /* sozlamalar */ });

// So'rov
const { rows, rowCount } = await pool.query(SQL, [param1, param2]);

// Parametrlar: $1, $2, $3 ... — SQL Injection yo'q
// rows      — natija massivi
// rowCount  — ta'sirlangan qatorlar soni
// rows[0]   — birinchi natija

// Xatolar
// err.code = '23505' — unique violation
// err.code = '23503' — foreign key violation
// err.code = '23502' — not null violation

Keyingi dars

63-dars: Node.js — Transaction, xatolarni boshqarish