Stored Procedure VS ORM?

서비스 개편을 하면서 디비 스키마를 새로 바꿨어야 했다.
(바꿨다라기 보다 그냥 새롭게 만들었다 가 더 맞는 표현인 것 같다.)
새롭게 설계를 할 때 중점으로 생각한 것들은 다음과 같다.

• 어느정도 정규화 규칙을 지켜야 한다.
• 관계 테이블을 만들자.
• 기존 스키마를 고려하여 마이그레이션 할 수 있어야 한다.

디비를 설계 하면서 동시에 API 개발을 하려고 하니 인력 분배하는 것이 중요했다.

나는 디비 스키마 설계 및 stored procedure, user-defined-function (편하게 sp 및 fn 이라고 말하겠다.) 을 개발하는 일종의 DBA 역할을 담당하게 되었다.

회사에서 디비엔진으로 postgresql을 사용하고 있었는데, 개편 전에는 한번도 procedure 를 사용하지 않았고, 전부 orm 및 raw query 를 사용했었다.

둘다 써본 입장에서 두가지의 장점 및 단점을 정리하자면...

orm 의 장점 및 단점

장점
1. 코드를 빠르게 작성할 수 있다 → 생산성을 높일 수 있다.
2. 디버깅을 하기 쉽다.
3. 소스코드의 가독성이 좋다.

단점
1. 테이블간의 관계를 정확하게 맺어줘야 한다 → 스키마를 설계할 때는 물론이고, 소스코드 상에서도 @OneToMany 및 @ManyToOne 를 정확하게 선언해줘야 사용하기 좋다.
2. 소스코드에서도 스키마의 변화를 관리해줘야 하고, 디비 서버에서도 관리를 해줘야 한다. 두 환경에서의 싱크가 맞지 않는 경우, 골치 아픈 상황이 발생한다.
3. 서비스 함수 혹은 어떠한 로직을 짤 때, 디비에 접근하는 횟수가 너무 많다.
→ 디비 네트워크 비용이 너무 많다.

sp 및 fn 의 장점 및 단점

장점
1. 스키마 변화의 관리포인트가 한 곳이다. 디비서버에서만 관리하면 돼.
→ 소스코드에서는 스키마가 어떻게 생겼고, 어떤 변화가 있었는지에 대해 전혀 알 필요가 없다.
2. 서비스 함수 혹은 어떠한 로직을 짤 때, 디비에 한번만 접근하면 된다.
→ 필요한 테이블들을 하나의 sp 혹은 fn에서 한번에 접근할 수 있다.
→ 디비 네트워크 비용이 현저히 낮다.
3. API 개발자는 온전히 어플리케이션 개발에만 신경 쓸 수 있다.
4. 로직이 변경하는 경우, 서버 배포 없이 sp, fn만 수정하면 된다.
→ 가장 큰 장점이라고 생각된다.
5. transaction 관리가 수월해진다.
→ sp안에서 핸들링 가능

단점
1. 생산성이 떨어진다.
2. sp, fn 의 가독성이 떨어진다.
3. 디버깅 하기 어렵다. raise 이 유일하다.
4. 전문 DBA 가 없는 회사에서는 운용하기 쉽지 않다.

이 정도인 것 같다.
지금 우리회사에는 전문 DBA가 없어서, procedure 을 사용하기 위해 내가 담당하고 공부했었는데, 생각보다 어려워서 애를 먹었다.
그래도 지금은 어느정도는 사용한다고 생각해서 fn, sp에 대한 설명 및 예시를 기록하려고 한다.

fn, sp 차이

procedure 와 function 간의 차이는 다음과 같다.

기준	function	procedure
transaction(commit, rollback, etc...) 처리	불가능	가능
return type	value, record, set, etc...	inout parameter 사용 필요
parameter	in, out, inout	inout(14 버전부터 out 가능)
compile time	실행할 때 마다	처음에만 한번
execute command	select, perform	call

transaction 관리

create procedure()
$
begin
	// A transaction
	begin
		[statement1]
	end
  // B transaction
	begin
		[statement2]
	exception // 여기 있으면 B 만 rollback
	end
	exception // 여기 있으면 A,B 트랜잭션 모두 rollback
end
$

SP 예시코드

1. insert sample

create procedure sample_sp_insert
(
	_email character varying, 
	INOUT _inout character varying DEFAULT NULL::character varying
)
  language plpgsql
as
$$
declare -- 사용할 변수를 미리 선언해줘야 함.
  _RETURNED_SQLSTATE text;
  _MESSAGE_TEXT text;
  _PG_EXCEPTION_DETAIL text;
  _PG_EXCEPTION_HINT text;
  _PG_EXCEPTION_CONTEXT text;
  _stack text;
  begin
  	if exists(select * from [table_name] t where t.email = _email) then
	  raise exception '400'; -- error code
	end if;
	insert into [table_name](email)
	values
	  (_email);
    call sample_other_sp();

  exception 
	when sqlstate 'P0001' then -- user defined error
		GET STACKED DIAGNOSTICS
		_MESSAGE_TEXT = MESSAGE_TEXT;
		_inout = _MESSAGE_TEXT;
	    raise exception '%', _MESSAGE_TEXT;
    when others then
		GET STACKED DIAGNOSTICS -- get error stack
			_RETURNED_SQLSTATE = RETURNED_SQLSTATE,
			_MESSAGE_TEXT = MESSAGE_TEXT,
			_PG_EXCEPTION_DETAIL = PG_EXCEPTION_DETAIL,
			_PG_EXCEPTION_HINT = PG_EXCEPTION_HINT,
			_PG_EXCEPTION_CONTEXT = PG_EXCEPTION_CONTEXT;
		GET DIAGNOSTICS _stack = PG_CONTEXT;
		call sp_get_curr_name_custom(_stack, _inout);
		call sp_save_procedure_error(_inout, _RETURNED_SQLSTATE, _MESSAGE_TEXT, _PG_EXCEPTION_DETAIL, _PG_EXCEPTION_HINT, _PG_EXCEPTION_CONTEXT);
		commit;
		raise exception 'SP_ERROR';
  end
$$;

• begin ~ end : 하나의 transaction block 을 뜻한다.
  에러가 발생하는 경우 exception block 으로 이동하며 자동으로 rollback 된다.

• exception block(A) 안에서 다른 sp 혹은 fn 을 호출하는 경우에 반드시 commit 을 해줘야 한다.
  다른 sp를 호출하면 호출당한 sp에서 또 하나의 트랜잭션(B)을 만든다.
  그러면 B는 A의 sub transaction이 된다. B에서 커밋을 했다고 해도 부모 트랜잭션에서도 커밋을 해야 저장이 된다.

• sample_other_sp() : 이 sp에서 에러가 발생(raise exception을 하는 경우 포함)해서 rollback을 해야 하는 경우에는 해당 sp 를 호출한 transaction block안에서 전부 rollback 이 된다 → insert 문도 rollback 됨.

• when sqlstate 'P0001' then : 사용자가 선언한 raise exception 경우이다.
  그외의 에러들(raise exception이 아닌, 사용자가 핸들링 할 수 없는 에러들)은 when others then 쪽으로 오게 된다.
  [REF]postgres error code

• stack error code

  command	description
  GET STACKED DIAGNOSTICS	에러가 났을 경우(exception block에 도달하는 경우)발생한 에러에 대한 stack의 정보를 가져온다.
  RETURNED_SQLSTATE	the SQLSTATE error code of the exception
  MESSAGE_TEXT	the text of the exception's primary message
  PG_EXCEPTION_DETAIL	the text of the exception's detail message, if any
  PG_EXCEPTION_CONTEXT	line(s) of text describing the call stack at the time of the exception
  PG_CONTEXT	returns a text string with line(s) of text describing the call stack.

  [REF]postgres error stack code

2. 현재 실행중인 sp의 이름을 가져오는 sp

create or replace procedure sp_get_curr_name(_stack text, inout _inout varchar)
  language plpgsql
as
$$
DECLARE
  curr_sp text;
BEGIN
  curr_sp := substring(_stack from 'function (.*?) line');
  _inout := curr_sp::regprocedure::regproc::text;
END
$$;

여기서 사용하는 stack이 위에서 가져온 PG_CONTEXT 이다.

3. how to call sp in source code

call sample_sp_insert('sampleEmail@email.com');
// 여기서 에러가 나서 raise exception 하면 
// 소스코드에서는 throw error 와 같은 현상이 발생한다. 
// -> try catch 문에서 잡으면 됨.
return true;

---

REF

혹시나 sp 및 fn 이 날라갈 수 있을 수도...?
방지하기 위해 백업하는 sp 도 같이 기록하자. 이게 날라가면 무용지물이기 때문에.
stored procedure, user defined function, user defined type 들을 s3 bucket에 저장하는 SP이다

create procedure sp_backup_db_to_s3()
  language plpgsql
as
$$
  declare back_up_function text;
  declare back_up_procedure text;
  declare back_up_cron text;
  declare back_up_user_defined_type text;
  declare i text;
  declare tables record;
  declare routines record;
  declare types record;
  BEGIN

    for i in
      SELECT
  		t.table_name
	  FROM
		capa.INFORMATION_SCHEMA.TABLES as t
	  WHERE
  		table_schema = 'public'
    loop
	  select
	    *
	  into
	    tables
	  from
	    aws_s3.query_export_to_s3(format('select * from %I', i),
      	aws_commons.create_s3_uri('[s3_bucket_name]', concat('[버킷안에서의 디렉토리 이름]', to_char(now(), 'YYYYMMDD'), concat('/', i)), 'ap-northeast-2'),
      	options := 'format csv');
	end loop;

    back_up_function :=
      'select
		pg_get_functiondef(oid),
		proname
	  from (
		  SELECT oid, proname
		  FROM pg_proc
		  WHERE proname in (
			  SELECT r.routine_name
			  FROM capa.INFORMATION_SCHEMA.ROUTINES as r
			  where LEFT(ROUTINE_NAME, 3) IN (''fn_'', ''_fn'')
		  )
	  )as a;';

    back_up_procedure :=
      'select
		pg_get_functiondef(oid),
		proname
	  from (
		  SELECT oid, proname
		  FROM pg_proc
		  WHERE proname in (
			  SELECT r.routine_name
			  FROM capa.INFORMATION_SCHEMA.ROUTINES as r
			  where LEFT(ROUTINE_NAME, 3) IN (''sp_'', ''_sp'')
		  )
	  )as a;';


    back_up_cron :=
      'select
		pg_get_functiondef(oid),
		proname
	  from (
		  SELECT oid, proname
		  FROM pg_proc
		  WHERE proname in (
			  SELECT r.routine_name
			  FROM capa.INFORMATION_SCHEMA.ROUTINES as r
			  where LEFT(ROUTINE_NAME, 3) IN (''cr_'', ''_cr'')
		  )
	  )as a;';
    
    back_up_user_defined_type :=
      'WITH types AS (
    	SELECT n.nspname,
            pg_catalog.format_type ( t.oid, NULL ) AS obj_name,
            CASE
                WHEN t.typrelid != 0 THEN CAST ( ''tuple'' AS pg_catalog.text )
                WHEN t.typlen < 0 THEN CAST ( ''var'' AS pg_catalog.text )
                ELSE CAST ( t.typlen AS pg_catalog.text )
                END AS obj_type,
            coalesce ( pg_catalog.obj_description ( t.oid, ''pg_type'' ), '''' ) AS description
        FROM pg_catalog.pg_type t
        JOIN pg_catalog.pg_namespace n
            ON n.oid = t.typnamespace
        WHERE ( t.typrelid = 0
                OR ( SELECT c.relkind = ''c''
                        FROM pg_catalog.pg_class c
                        WHERE c.oid = t.typrelid ) )
            AND NOT EXISTS (
                    SELECT 1
                        FROM pg_catalog.pg_type el
                        WHERE el.oid = t.typelem
                        AND el.typarray = t.oid )
            AND n.nspname <> ''pg_catalog''
            AND n.nspname <> ''information_schema''
            AND n.nspname !~ ''^pg_toast''
	  ),
	  cols AS (
		  SELECT n.nspname::text AS schema_name,
				  pg_catalog.format_type ( t.oid, NULL ) AS obj_name,
				  a.attname::text AS column_name,
				  pg_catalog.format_type ( a.atttypid, a.atttypmod ) AS data_type,
				  a.attnotnull AS is_required,
				  a.attnum AS ordinal_position,
				  pg_catalog.col_description ( a.attrelid, a.attnum ) AS description
			  FROM pg_catalog.pg_attribute a
			  JOIN pg_catalog.pg_type t
				  ON a.attrelid = t.typrelid
			  JOIN pg_catalog.pg_namespace n
				  ON ( n.oid = t.typnamespace )
			  JOIN types
				  ON ( types.nspname = n.nspname
					  AND types.obj_name = pg_catalog.format_type ( t.oid, NULL ) )
			  WHERE a.attnum > 0
				  AND NOT a.attisdropped
	  )
	  SELECT
      	cols.obj_name
		,json_agg(json_build_object(''column_name'', cols.column_name, ''data_type'', cols.data_type, ''ordinal_position'', cols.ordinal_position,
		''is_required'', cols.is_required, ''description'', coalesce ( cols.description, '''' )) order by cols.ordinal_position)
	  FROM cols
	  where cols.schema_name <> ''aws_commons''
	  group by cols.obj_name
	  ORDER BY cols.obj_name';

	select
	  *
	into
	  routines
	from
	  aws_s3.query_export_to_s3(
	    back_up_function,
		aws_commons.create_s3_uri('[s3_bucket_name]', concat('[버킷안에서의 디렉토리 이름]', to_char(now(), 'YYYYMMDD'), '[해당 디렉토리 안의 디렉토리]'), 'ap-northeast-2'),
		options :='format csv');

    select
	  *
	into
	  routines
	from
	  aws_s3.query_export_to_s3(
	    back_up_procedure,
		aws_commons.create_s3_uri('[s3_bucket_name]', concat('[버킷안에서의 디렉토리 이름]', to_char(now(), 'YYYYMMDD'), '[해당 디렉토리 안의 디렉토리]'), 'ap-northeast-2'),
		options :='format csv');
    
    select
	  *
	into
	  routines
	from
	  aws_s3.query_export_to_s3(
	    back_up_cron,
		aws_commons.create_s3_uri('[s3-bucket_name]', concat('[버킷안에서의 디렉토리 이름]', to_char(now(), 'YYYYMMDD'), '[해당 디렉토리 안의 디렉토리]'), 'ap-northeast-2'),
		options :='format csv');

    select
	  *
	into
	  types
	from
	  aws_s3.query_export_to_s3(
	   back_up_user_defined_type,
		aws_commons.create_s3_uri('[s3_bucket_name]', concat('[버킷안에서의 디렉토리 이름]', to_char(now(), 'YYYYMMDD')), 'ap-northeast-2'),
		options :='format csv');

END
$$;

이렇게 sp를 사용하면 소스코드에서는 스키마의 정보를 알필요가 없다.
하지만 혹시 알아야 하는 경우가 생길수도 있기 때문에...
그럴때는 typeorm-model-generator 를 사용하면 된다.

$ npm i --save typeorm-model-generator

$ npx typeorm-model-generator -h [dbhost주소] -d [데이터베이스이름] -p [port] -u [유저] -x [비밀번호] -e [db종류] -o [entity 파일 위치] --case-file none

위 command 를 실행하면 스키마에 있는 모든 테이블, procedure, function, type, 등등 필요한 모든 정보를 특정 폴더 위치에 가져올 수 있게 된다.

❗ 그냥 가져오게 되면 테이블 명이 camel case가 아닌, snake case 로 가져오게 되는데 뒤에 --case-file none 옵션을 달면 camelCase 로 가져오게 된다.