Node.js PostgreSQL 연동

 

Node.js 애플리케이션에서 PostgreSQL 데이터베이스를 연동하는 방법을 알아봅니다. pg 패키지를 사용한 연결 설정부터 CRUD 작업, 트랜잭션, JSON 데이터 타입 활용까지 실무에서 바로 적용할 수 있는 내용을 다룹니다.

1. PostgreSQL 소개와 특징

PostgreSQL은 오픈소스 객체-관계형 데이터베이스 관리 시스템(ORDBMS)입니다. 1986년 버클리 대학교의 POSTGRES 프로젝트에서 시작되어 현재까지 활발하게 개발되고 있습니다.

PostgreSQL의 주요 특징

ACID 준수: 트랜잭션의 원자성(Atomicity), 일관성(Consistency), 격리성(Isolation), 지속성(Durability)을 완벽하게 지원합니다.

다양한 데이터 타입: 기본 타입 외에도 배열, hstore, JSON, JSONB, 기하학적 타입, 네트워크 주소 타입 등을 지원합니다.

확장성: 사용자 정의 함수, 데이터 타입, 연산자, 인덱스 메서드를 추가할 수 있습니다.

동시성 제어: MVCC(Multi-Version Concurrency Control)를 사용하여 읽기 작업이 쓰기 작업을 차단하지 않습니다.

2. pg 패키지 설치 및 설정

Node.js에서 PostgreSQL을 사용하려면 node-postgres(pg) 패키지를 설치합니다. 이 패키지는 PostgreSQL 공식 프로토콜을 구현한 순수 JavaScript 클라이언트입니다.

npm install pg

TypeScript를 사용하는 경우 타입 정의도 함께 설치합니다.

npm install pg @types/pg

기본 연결 설정

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

const client = new Client({
  host: 'localhost',
  port: 5432,
  database: 'mydb',
  user: 'postgres',
  password: 'password'
});

async function connect() {
  try {
    await client.connect();
    console.log('PostgreSQL 연결 성공');

    const result = await client.query('SELECT NOW()');
    console.log('현재 시간:', result.rows[0].now);
  } catch (error) {
    console.error('연결 오류:', error.message);
  } finally {
    await client.end();
  }
}

connect();

환경 변수를 활용한 설정

보안을 위해 데이터베이스 접속 정보는 환경 변수로 관리하는 것이 좋습니다.

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

const client = new Client({
  connectionString: process.env.DATABASE_URL,
  ssl: process.env.NODE_ENV === 'production' ? { rejectUnauthorized: false } : false
});

3. 연결 풀(Pool) 구성과 관리

실제 애플리케이션에서는 Client 대신 Pool을 사용합니다. Pool은 여러 클라이언트 연결을 관리하며, 요청마다 새 연결을 생성하는 오버헤드를 줄여줍니다.

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

const pool = new Pool({
  host: 'localhost',
  port: 5432,
  database: 'mydb',
  user: 'postgres',
  password: 'password',
  max: 20,                      // 최대 연결 수
  idleTimeoutMillis: 30000,     // 유휴 연결 타임아웃 (30초)
  connectionTimeoutMillis: 2000 // 연결 타임아웃 (2초)
});

// 풀 이벤트 리스너
pool.on('connect', () => {
  console.log('새 클라이언트 연결됨');
});

pool.on('error', (err) => {
  console.error('풀 오류:', err.message);
});

풀을 사용한 쿼리 실행

async function queryWithPool() {
  try {
    // 방법 1: pool.query() - 간단한 쿼리에 적합
    const result = await pool.query('SELECT * FROM users WHERE id = $1', [1]);
    console.log(result.rows);

    // 방법 2: 클라이언트 체크아웃 - 여러 쿼리를 순차 실행할 때
    const client = await pool.connect();
    try {
      const res1 = await client.query('SELECT * FROM users');
      const res2 = await client.query('SELECT * FROM orders');
      return { users: res1.rows, orders: res2.rows };
    } finally {
      client.release(); // 반드시 클라이언트 반환
    }
  } catch (error) {
    console.error('쿼리 오류:', error.message);
  }
}

애플리케이션 종료 시 풀 정리

process.on('SIGINT', async () => {
  await pool.end();
  console.log('풀 연결 종료');
  process.exit(0);
});

4. 파라미터화된 쿼리(Parameterized Query)

SQL 인젝션 공격을 방지하려면 반드시 파라미터화된 쿼리를 사용해야 합니다. pg 패키지는 $1, $2 형식의 위치 기반 파라미터를 지원합니다.

잘못된 방법 (SQL 인젝션 취약)

// 절대 사용하지 마세요
const query = `SELECT * FROM users WHERE name = '${userName}'`;

올바른 방법 (파라미터화된 쿼리)

async function findUserByName(name) {
  const query = 'SELECT * FROM users WHERE name = $1';
  const values = [name];

  const result = await pool.query(query, values);
  return result.rows;
}

async function insertUser(name, email, age) {
  const query = `
    INSERT INTO users (name, email, age)
    VALUES ($1, $2, $3)
    RETURNING id, name, email, age, created_at
  `;
  const values = [name, email, age];

  const result = await pool.query(query, values);
  return result.rows[0];
}

Named Parameters 스타일 사용

pg-format 또는 sql-template-strings 패키지를 사용하면 더 직관적인 쿼리 작성이 가능합니다.

const format = require('pg-format');

async function findUsers(names) {
  // 배열 값을 안전하게 처리
  const query = format('SELECT * FROM users WHERE name IN (%L)', names);
  const result = await pool.query(query);
  return result.rows;
}

5. CRUD 작업 예제

실무에서 자주 사용하는 CRUD(Create, Read, Update, Delete) 작업을 구현합니다.

테이블 생성

async function createTable() {
  const query = `
    CREATE TABLE IF NOT EXISTS users (
      id SERIAL PRIMARY KEY,
      name VARCHAR(100) NOT NULL,
      email VARCHAR(255) UNIQUE NOT NULL,
      age INTEGER,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )
  `;

  await pool.query(query);
  console.log('users 테이블 생성 완료');
}

Create - 데이터 삽입

async function createUser(userData) {
  const { name, email, age } = userData;
  const query = `
    INSERT INTO users (name, email, age)
    VALUES ($1, $2, $3)
    RETURNING *
  `;

  const result = await pool.query(query, [name, email, age]);
  return result.rows[0];
}

// 다중 삽입
async function createUsers(usersData) {
  const query = `
    INSERT INTO users (name, email, age)
    SELECT * FROM UNNEST($1::varchar[], $2::varchar[], $3::int[])
    RETURNING *
  `;

  const names = usersData.map(u => u.name);
  const emails = usersData.map(u => u.email);
  const ages = usersData.map(u => u.age);

  const result = await pool.query(query, [names, emails, ages]);
  return result.rows;
}

Read - 데이터 조회

async function getUserById(id) {
  const query = 'SELECT * FROM users WHERE id = $1';
  const result = await pool.query(query, [id]);
  return result.rows[0] || null;
}

async function getUsers(options = {}) {
  const { limit = 10, offset = 0, orderBy = 'created_at', order = 'DESC' } = options;

  // orderBy와 order는 허용된 값만 사용 (SQL 인젝션 방지)
  const allowedColumns = ['id', 'name', 'email', 'created_at'];
  const allowedOrders = ['ASC', 'DESC'];

  const safeOrderBy = allowedColumns.includes(orderBy) ? orderBy : 'created_at';
  const safeOrder = allowedOrders.includes(order.toUpperCase()) ? order.toUpperCase() : 'DESC';

  const query = `
    SELECT * FROM users
    ORDER BY ${safeOrderBy} ${safeOrder}
    LIMIT $1 OFFSET $2
  `;

  const result = await pool.query(query, [limit, offset]);
  return result.rows;
}

Update - 데이터 수정

async function updateUser(id, updates) {
  const fields = [];
  const values = [];
  let paramIndex = 1;

  Object.keys(updates).forEach(key => {
    if (updates[key] !== undefined) {
      fields.push(`${key} = $${paramIndex}`);
      values.push(updates[key]);
      paramIndex++;
    }
  });

  if (fields.length === 0) {
    throw new Error('수정할 필드가 없습니다');
  }

  fields.push(`updated_at = CURRENT_TIMESTAMP`);
  values.push(id);

  const query = `
    UPDATE users
    SET ${fields.join(', ')}
    WHERE id = $${paramIndex}
    RETURNING *
  `;

  const result = await pool.query(query, values);
  return result.rows[0];
}

Delete - 데이터 삭제

async function deleteUser(id) {
  const query = 'DELETE FROM users WHERE id = $1 RETURNING *';
  const result = await pool.query(query, [id]);
  return result.rows[0] || null;
}

async function deleteUsersByAge(minAge) {
  const query = 'DELETE FROM users WHERE age < $1 RETURNING id';
  const result = await pool.query(query, [minAge]);
  return result.rowCount; // 삭제된 행 수
}

6. 트랜잭션 처리

여러 쿼리를 하나의 작업 단위로 처리해야 할 때 트랜잭션을 사용합니다. 트랜잭션 내의 모든 쿼리가 성공해야 커밋되고, 하나라도 실패하면 전체가 롤백됩니다.

기본 트랜잭션 패턴

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

  try {
    await client.query('BEGIN');

    // 출금 계좌에서 차감
    const withdrawQuery = `
      UPDATE accounts SET balance = balance - $1
      WHERE id = $2 AND balance >= $1
      RETURNING balance
    `;
    const withdrawResult = await client.query(withdrawQuery, [amount, fromId]);

    if (withdrawResult.rowCount === 0) {
      throw new Error('잔액이 부족하거나 계좌가 존재하지 않습니다');
    }

    // 입금 계좌에 추가
    const depositQuery = `
      UPDATE accounts SET balance = balance + $1
      WHERE id = $2
      RETURNING balance
    `;
    const depositResult = await client.query(depositQuery, [amount, toId]);

    if (depositResult.rowCount === 0) {
      throw new Error('입금 계좌가 존재하지 않습니다');
    }

    // 거래 기록 저장
    await client.query(
      'INSERT INTO transactions (from_id, to_id, amount) VALUES ($1, $2, $3)',
      [fromId, toId, amount]
    );

    await client.query('COMMIT');

    return {
      success: true,
      fromBalance: withdrawResult.rows[0].balance,
      toBalance: depositResult.rows[0].balance
    };
  } catch (error) {
    await client.query('ROLLBACK');
    throw error;
  } finally {
    client.release();
  }
}

트랜잭션 헬퍼 함수

반복적인 트랜잭션 패턴을 헬퍼 함수로 추상화하면 코드가 간결해집니다.

async function withTransaction(callback) {
  const client = await pool.connect();

  try {
    await client.query('BEGIN');
    const result = await callback(client);
    await client.query('COMMIT');
    return result;
  } catch (error) {
    await client.query('ROLLBACK');
    throw error;
  } finally {
    client.release();
  }
}

// 사용 예시
async function createOrderWithItems(orderData, items) {
  return withTransaction(async (client) => {
    // 주문 생성
    const orderResult = await client.query(
      'INSERT INTO orders (user_id, total) VALUES ($1, $2) RETURNING id',
      [orderData.userId, orderData.total]
    );
    const orderId = orderResult.rows[0].id;

    // 주문 항목 추가
    for (const item of items) {
      await client.query(
        'INSERT INTO order_items (order_id, product_id, quantity, price) VALUES ($1, $2, $3, $4)',
        [orderId, item.productId, item.quantity, item.price]
      );

      // 재고 감소
      await client.query(
        'UPDATE products SET stock = stock - $1 WHERE id = $2',
        [item.quantity, item.productId]
      );
    }

    return orderId;
  });
}

7. JSON/JSONB 데이터 타입 활용

PostgreSQL은 JSON과 JSONB 데이터 타입을 지원합니다. JSONB는 바이너리 형태로 저장되어 인덱싱과 검색 성능이 우수합니다.

JSONB 컬럼이 있는 테이블 생성

async function createProductTable() {
  const query = `
    CREATE TABLE IF NOT EXISTS products (
      id SERIAL PRIMARY KEY,
      name VARCHAR(255) NOT NULL,
      price DECIMAL(10, 2) NOT NULL,
      metadata JSONB DEFAULT '{}',
      tags JSONB DEFAULT '[]',
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )
  `;

  await pool.query(query);

  // JSONB 컬럼에 GIN 인덱스 생성 (검색 성능 향상)
  await pool.query(`
    CREATE INDEX IF NOT EXISTS idx_products_metadata ON products USING GIN (metadata)
  `);
}

JSONB 데이터 삽입

async function createProduct(productData) {
  const { name, price, metadata, tags } = productData;

  const query = `
    INSERT INTO products (name, price, metadata, tags)
    VALUES ($1, $2, $3, $4)
    RETURNING *
  `;

  const result = await pool.query(query, [
    name,
    price,
    JSON.stringify(metadata),
    JSON.stringify(tags)
  ]);

  return result.rows[0];
}

// 사용 예시
await createProduct({
  name: '무선 이어폰',
  price: 89000,
  metadata: {
    brand: 'TechBrand',
    color: 'black',
    specs: {
      battery: '24시간',
      bluetooth: '5.0',
      weight: '5g'
    }
  },
  tags: ['전자기기', '오디오', '무선']
});

JSONB 쿼리 연산자

// 특정 키 값으로 검색 (-> 연산자: JSON 객체 반환)
async function getProductsByBrand(brand) {
  const query = `
    SELECT * FROM products
    WHERE metadata->>'brand' = $1
  `;
  const result = await pool.query(query, [brand]);
  return result.rows;
}

// 중첩된 값 검색 (#>> 연산자: 경로로 텍스트 값 추출)
async function getProductsByBluetooth(version) {
  const query = `
    SELECT * FROM products
    WHERE metadata#>>'{specs,bluetooth}' = $1
  `;
  const result = await pool.query(query, [version]);
  return result.rows;
}

// 키 존재 여부 확인 (? 연산자)
async function getProductsWithColor() {
  const query = `
    SELECT * FROM products
    WHERE metadata ? 'color'
  `;
  const result = await pool.query(query);
  return result.rows;
}

// 배열에 값 포함 여부 (@> 연산자: 포함 관계)
async function getProductsByTag(tag) {
  const query = `
    SELECT * FROM products
    WHERE tags @> $1::jsonb
  `;
  const result = await pool.query(query, [JSON.stringify([tag])]);
  return result.rows;
}

JSONB 데이터 업데이트

// 특정 키 값 업데이트 (jsonb_set 함수)
async function updateProductColor(id, color) {
  const query = `
    UPDATE products
    SET metadata = jsonb_set(metadata, '{color}', $1::jsonb)
    WHERE id = $2
    RETURNING *
  `;
  const result = await pool.query(query, [JSON.stringify(color), id]);
  return result.rows[0];
}

// 여러 키 병합 (|| 연산자)
async function mergeProductMetadata(id, newMetadata) {
  const query = `
    UPDATE products
    SET metadata = metadata || $1::jsonb
    WHERE id = $2
    RETURNING *
  `;
  const result = await pool.query(query, [JSON.stringify(newMetadata), id]);
  return result.rows[0];
}

// 키 삭제 (- 연산자)
async function removeProductMetadataKey(id, key) {
  const query = `
    UPDATE products
    SET metadata = metadata - $1
    WHERE id = $2
    RETURNING *
  `;
  const result = await pool.query(query, [key, id]);
  return result.rows[0];
}

// 배열에 요소 추가
async function addProductTag(id, tag) {
  const query = `
    UPDATE products
    SET tags = tags || $1::jsonb
    WHERE id = $2 AND NOT tags @> $1::jsonb
    RETURNING *
  `;
  const result = await pool.query(query, [JSON.stringify([tag]), id]);
  return result.rows[0];
}

JSONB 집계 함수

// 모든 고유 태그 추출
async function getAllUniqueTags() {
  const query = `
    SELECT DISTINCT jsonb_array_elements_text(tags) as tag
    FROM products
    ORDER BY tag
  `;
  const result = await pool.query(query);
  return result.rows.map(row => row.tag);
}

// 브랜드별 상품 수 집계
async function countByBrand() {
  const query = `
    SELECT metadata->>'brand' as brand, COUNT(*) as count
    FROM products
    WHERE metadata ? 'brand'
    GROUP BY metadata->>'brand'
    ORDER BY count DESC
  `;
  const result = await pool.query(query);
  return result.rows;
}

결론

Node.js에서 PostgreSQL을 연동할 때는 pg 패키지의 Pool을 사용하여 연결을 효율적으로 관리하고, 파라미터화된 쿼리로 SQL 인젝션을 방지해야 합니다. 트랜잭션은 데이터 무결성이 중요한 작업에서 필수이며, JSONB 타입을 활용하면 유연한 데이터 구조를 구현할 수 있습니다.