[node.js] Procedure 사용하여 API 생성하기

개인적으로 공부하면서 기록하는 공간으로
잘못된 정보는 댓글을 통해 알려주시면 감사하겠습니다 :-)

▪ ▪ ▪ ▪ ▪

 

저장 프로시저 (Stored Procedure)

• 일련의 쿼리를 하나의 함수처럼 실행하기 위한 쿼리의 집합이다.
• 데이터베이스에 대한 작업을 정라한 절차를 관계형 데이터베이스 관리 시스템에 저장한 것이다.

장점
① 트래픽 감소
    → 프로시저를 실행 할 호출만 네트워크에서 전송된다.
② 보안 강화
    → 데이터베이스 개체에 대한 직접적인 사용 권한이 없는 경우에도 프로시저를 통해 작업을 수행 가능하다.
    → 네트워크를 통해 프로시저를 호출하면 사용자가 테이블 및 데이터베이스 개체 이름을 보거나 중요한 데이터를 검색할 수 없다.
    → 프로시저 매개 변수를 사용하면 SQL 삽입 공격으로부터 보호하는 데 도움이 된다.
    → 프로시저를 암호화 하여 원본 코드를 난독 처리 할 수 있다.
③ 코드 재사용
    → 반복적인 데이터베이스 작업의 코드를 프로시저에서 캡슐화하여 사용하면 코드 재작성의 필요가 없으며, 코드 불일치가 감소된다.
④ 간편한 유지 관리
    → 클라이언트 애플리케이션에서 프로시저를 호출하고 데이터베이스 작업을 데이터 계층에 유지하면 기본 데이터베이스의 모든 변경 내용에 대해 프로시저만 업데이트하면 된다. 
⑤ 성능 향상
    → 프로시저는 처음 실행할 때 컴파일되며 이후 실행에 다시 사용되는 실행계획을 만드므로, 쿼리 프로세서는 새 계획을 만들 필요가 없어 일반적으로 프로시저 처리 시간이 줄어든다.

 

MSSQL Stored Procedure 사용하기

MSSQL 연동하기

[MSSQL] node.js에서 MSSQL 연동하기

 

프로시저 생성

• 이름 또는 나이 조건을 주고 그에 해당되는 데이터를 조회하는 쿼리를 작성한다.

CREATE PROCEDURE [dbo].[SP_TEST_TABLE_SELECT]	
	@NAME				varchar(10),
	@AGE				int,
	@TOTAL				int= NULL  output 
AS
BEGIN
	SET NOCOUNT ON;

	-- 조회 총 count 수
	SELECT	@TOTAL = COUNT(*)
	FROM	DBO.TEST_TABLE
	WHERE	(1=1)
	AND		(@NAME IS NULL OR NAME = @NAME)
	AND		(@AGE IS NULL OR AGE >= @AGE)

	
	-- 해당 조건에 만족하는 데이터 조회
	SELECT
			IDX
	,		NAME
	,		AGE

	FROM	
			DBO.TEST_TABLE

	WHERE	(1=1)
	AND		(@NAME IS NULL OR NAME = @NAME)
	AND		(@AGE IS NULL OR AGE >= @AGE)
	
	
END

 

라우터 (/src/api/routes/sp_test/sp_test.router.js)

import express from 'express';
import controller from './sp_test.controller.js';

export const testRouter = express.Router();

testRouter.post('/testList', controller.getTestList);

 

컨트롤러 (/src/api/routes/sp_test/sp_test.controller.js)

컨트롤러는 들어오는 클라이언트의 요청을 받고 서비스에 전달하며, 서비스에서 작업을 마친 데이터를 받아 클라이언트에게 응답한다.

import testService from './sp_test.service.js';

export default {
  getTestList: async (req, res) => {
    try {
      let result = await testService.getTestList(req.body);
      res.json(result);
    } catch (err) {
      console.log(err);
    }
  },
};

컨트롤러에서 데이터를 json형태로 전달 받을 때,
HTTP 메소드가 POST일 경우 req.body로 데이터를 받고,  GET일 경우 req.params로 데이터를 전달 받는다.

서비스 (/src/api/routes/sp_test/sp_test.service.js)

프로시저를 활용하여 데이터베이스와 상호 작용하며 컨트롤러에 전달할 데이터를 리턴한다.

import { sql, connPool } from '../../../config/server.js';

export default {
  getTestList: async (param) => {
    try {
      const pool = await connPool;
      const result = await pool
        .request()
        .input('NAME', sql.VarChar, param.name)
        .input('AGE', sql.Int, param.age)
        .output('TOTAL', 0)
        .execute('dbo.SP_TEST_TABLE_SELECT')
        .then((result) => {
          const result_data = {
            total: result.output.TOTAL,
            result: result.recordset,
          };
          return result_data;
        })
        .catch((err) => {
          console.log('err', err);
        });

      return result;
    } catch (err) {
      console.log(err);
    }
  },
};

앞서 설정한 DB 커넥션풀을 호출하고, 실행할 프로시저의 input값과 output 값을 입력해준다.
이 때 주의할 점은 값을 입력해주는 .output('PARAM', '') 부분의 PARAM과 output 값을 받아오는 result.output.PARAM 부분의 대소문자도 일치해야 정상적으로 값을 받아올 수 있다.

API 호출하기

 

Reference

✔ https://ko.wikipedia.org/wiki/%EC%A0%80%EC%9E%A5_%ED%94%84%EB%A1%9C%EC%8B%9C%EC%A0%80
✔ https://docs.microsoft.com/ko-kr/sql/relational-databases/stored-procedures/stored-procedures-database-engine?view=sql-server-ver15
✔ https://velog.io/@ju_h2/Node-express-%EC%84%9C%EB%B2%84%EC%97%90-3-Layer-Architecture-%EC%A0%81%EC%9A%A9%ED%95%98%EA%B8%B0