[nodeJS] Node.js에서 DB 프로시저 활용하기

데이터베이스 프로시저는 반복적인 작업이나 복잡한 비즈니스 로직을 데이터베이스 레벨에서 처리할 수 있게 해주는 강력한 도구입니다. Node.js와 MariaDB를 연동하면 애플리케이션의 효율성과 유지 보수성을 높일 수 있습니다.

 

1. 프로시저란

프로시저(Stored Procedure)는 데이터베이스에 저장된 SQL 쿼리의 집합으로, 미리 정의된 작업 수행 가능

• 효율성: 클라이언트-서버 간 데이터 전송 감소.
• 재사용성: 동일한 로직을 여러 애플리케이션에서 재사용 가능.
• 보안성: SQL 쿼리를 애플리케이션 외부에 노출하지 않음.
• 유지 보수성: 비즈니스 로직을 데이터베이스 레벨에서 관리.

 

2. MariaDB와 Node.js 설정

1) MariaDB 설치 및 초기화

MariaDB를 설치한 후 데이터베이스와 사용자 계정 생성

CREATE DATABASE my_database;
CREATE USER 'my_user'@'localhost' IDENTIFIED BY 'my_password';
GRANT ALL PRIVILEGES ON my_database.* TO 'my_user'@'localhost';
FLUSH PRIVILEGES;

 

2) Node.js와 MariaDB 연결

mysql2 라이브러리를 사용하여 MariaDB와 Node.js를 연결

npm install mysql2

// database.js

const mysql = require('mysql2/promise');

const pool = mysql.createPool({
  host: 'localhost',
  user: 'my_user',
  password: 'my_password',
  database: 'my_database',
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0
});

module.exports = pool;

 

3. 프로시저 생성 및 실행

1) 프로시저 생성

MariaDB에서 프로시저 생성

// 특정 범위의 사용자 데이터를 반환하는 프로시저

DELIMITER //
CREATE PROCEDURE GetUsersByAge(IN min_age INT, IN max_age INT)
BEGIN
  SELECT * FROM users WHERE age BETWEEN min_age AND max_age;
END //
DELIMITER ;

 

2) 프로시저 실행

MariaDB CLI 또는 다른 SQL 클라이언트를 사용하여 프로시저 실행

CALL GetUsersByAge(20, 30);

 

4. Node.js에서 프로시저 호출

mysql2의 execute 메서드를 사용하여 프로시저 호출

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

async function getUsersByAge(minAge, maxAge) {
  try {
    const [rows] = await pool.execute('CALL GetUsersByAge(?, ?)', [minAge, maxAge]);
    return rows;
  } catch (err) {
    console.error('Error executing procedure:', err.message);
    throw err;
  }
}

// 예제 호출
(async () => {
  const users = await getUsersByAge(20, 30);
  console.log('Users:', users);
})();

 

출력 결과

Users: [
  { id: 1, name: 'Alice', age: 25 },
  { id: 2, name: 'Bob', age: 29 }
]

 

5. 프로시저 활용 예제: 사용자 관리 시스템

1) 사용자 추가 프로시저

DELIMITER //
CREATE PROCEDURE AddUser(IN user_name VARCHAR(100), IN user_email VARCHAR(100), IN user_age INT)
BEGIN
  INSERT INTO users (name, email, age) VALUES (user_name, user_email, user_age);
END //
DELIMITER ;

 

2) Node.js에서 사용자 추가

async function addUser(name, email, age) {
  try {
    const [result] = await pool.execute('CALL AddUser(?, ?, ?)', [name, email, age]);
    console.log('User added:', result);
  } catch (err) {
    console.error('Error adding user:', err.message);
    throw err;
  }
}

// 예제 호출
(async () => {
  await addUser('Charlie', 'charlie@example.com', 35);
})();

 

6. 트랜잭션과 프로시저

복잡한 작업에서는 트랜잭션과 프로시저를 함께 사용하는 것이 유용함.

DELIMITER //
CREATE PROCEDURE TransferFunds(IN from_user INT, IN to_user INT, IN amount DECIMAL(10, 2))
BEGIN
  DECLARE insufficient_funds CONDITION FOR SQLSTATE '45000';

  START TRANSACTION;

  UPDATE accounts SET balance = balance - amount WHERE user_id = from_user;
  IF ROW_COUNT() = 0 THEN
    SIGNAL insufficient_funds SET MESSAGE_TEXT = 'Insufficient funds';
  END IF;

  UPDATE accounts SET balance = balance + amount WHERE user_id = to_user;
  COMMIT;
END //
DELIMITER ;

 

Node.js에서 호출

async function transferFunds(fromUser, toUser, amount) {
  try {
    const [result] = await pool.execute('CALL TransferFunds(?, ?, ?)', [fromUser, toUser, amount]);
    console.log('Transfer successful:', result);
  } catch (err) {
    console.error('Error transferring funds:', err.message);
    throw err;
  }
}

// 예제 호출
(async () => {
  await transferFunds(1, 2, 100.00);
})();

 

7. 프로시저 활용의 장단점

장점

• 효율성: 클라이언트와 데이터베이스 간의 트래픽 감소.
• 일관성: 동일한 로직을 데이터베이스 레벨에서 관리 가능.
• 보안: SQL 쿼리 노출 감소.

단점

• 유지 보수 어려움: 데이터베이스 내의 복잡한 로직 수정 시 문제가 발생할 수 있음.
• 이식성 제한: 다른 DBMS로 이전 시 재작성 필요.
• 디버깅 어려움: 데이터베이스 내부에서 디버깅이 어렵거나 제한적임.

 

Node.js와 MariaDB를 사용하여 프로시저를 활용하면 복잡한 비즈니스 로직을 효율적으로 처리할 수 있습니다.

프로시저는 특히 데이터 무결성과 성능이 중요한 애플리케이션에서 강력한 도구로 작용합니다.

이번 포스팅에서 다룬 내용을 바탕으로 실습을 진행해보고, MariaDB 프로시저를 여러분의 프로젝트에 활용해 보세요!