🏠 | 데이터 무결성을 위한 Supabase Trigger 설정

프로젝트를 진행하며 데이터베이스의 updated_at 필드를 갱신하는 작업을 수동으로 처리하던 중, 예상치 못한 에러로 인해 데이터 변경 기록이 정확히 반영되지 않는 문제를 발견했습니다. 이는 데이터 변경 작업에서 무결성과 일관성을 보장하지 못하는 치명적인 약점으로 작용할 수 있었습니다.

이 문제를 해결하기 위해 데이터베이스 수준에서 변경 작업과 수정 기록을 자동으로 처리하는 방안을 모색했고, PostgreSQL 기반의 Supabase에서 제공하는 트리거(Trigger) 기능을 도입했습니다. 트리거는 데이터 변경 이벤트와 결합되어 필요한 로직을 자동으로 실행함으로써, 데이터 무결성을 보장하고 개발자의 추가적인 관리 부담을 최소화할 수 있는 강력한 도구입니다.

Supabase Trigger

Trigger

PostgreSQL의 트리거는 데이터베이스 테이블에서 행이 삽입(INSERT), 수정(UPDATE), 삭제(DELETE)되는 이벤트가 발생할 때, 자동으로 지정된 로직을 실행하는 기능입니다. 개발자는 사용자 정의 트리거 함수를 생성하고 이를 트리거와 연결하여, 데이터 무결성을 보장하고 데이터베이스 작업을 자동화할 수 있습니다.

트리거는 지정된 이벤트와 동일한 트랜잭션(transaction) 내에서 실행됩니다. 트리거가 실패하면 전체 트랜잭션이 롤백(rollback)되며, 반대로 트리거와 작업이 모두 성공해야만 트랜잭션이 커밋(commit)됩니다. 이러한 동작은 데이터의 정확성과 안정성을 보장하는 데 중요한 역할을 합니다.

📍

트랜잭션(transaction)

• 데이터베이스에서 하나의 작업 단위로 간주되는 연산들의 집합입니다.
• 트랜잭션은 모두 성공적으로 실행(commit)되거나, 하나라도 실패하면 전체가 취소(rollback)됩니다.

Trigger SQL 구문

트리거는 트리거 함수와 이를 호출하는 트리거 설정으로 구성됩니다. 트리거 함수를 작성한 후, 트리거를 생성하여 특정 이벤트 발생 시 함수가 실행되도록 설정할 수 있습니다.

1. 트리거 함수(Trigger Function)
   트리거 함수는 PostgreSQL의 CREATE FUNCTION 문으로 작성됩니다. 함수 본문은 $$ 기호로 묶인 문자열로 정의되며, 트리거와 연결되어 데이터베이스 작업 시 호출됩니다.

-- 함수 이름을 정의합니다.
CREATE FUNCTION {function_name}

-- 함수가 트리거용 함수임을 명시합니다.
-- '$$'는 (작은따옴표 대신) 여러 줄로 구성된 코드를 문자열로 전달할 때 사용됩니다.
RETURNS TRIGGER AS $$
BEGIN
    -- 'NEW'는 데이터베이스에 삽입 또는 수정될 새로운 데이터를 나타냅니다.
    NEW.{table_columns} = {value};
    
    -- 트리거 함수는 반드시 데이터 행을 반환해야 합니다.
    -- NEW는 수정된 데이터를 반환하고, OLD는 수정 전 데이터를 반환합니다.
    RETURN {NEW | OLD};
END;
-- 트리거 함수에서 사용하는 언어를 지정한 것으로, PostgreSQL의 프로시저 언어를 대개 사용합니다.
$$ LANGUAGE plpgsql;

2. 트리거 설정(Trigger)
   트리거는 CREATE TRIGGER 문을 통해 생성되며, 특정 이벤트 발생 시 실행될 함수를 호출합니다.

-- CREATE TRIGGER를 통해 생성합니다.
CREATE TRIGGER {trigger_name} 

-- 실행 유형을 지정합니다.
-- AFTER는 트리거 데이터가 변경된 이후에 실행됩니다.
-- BEFORE는 트리거 데이터가 변경되기 이전에 실행됩니다.
{AFTER|BEFORE} {INSERT|UPDATE|DELETE} ON {table_name} 

-- 실행빈도를 정하는 옵션입니다. 
-- row라면 각 행에 한 번씩 실행되어야 하고, 
-- statement는 전체 삽입 시 한 번 실행됩니다.
FOR EACH {ROW|STATEMENT} 

-- 호출할 동작 함수를 등록합니다.
EXECUTE FUNTION {trigger_function()}

Trigger 설정시 주의사항

• 트리거는 데이터 변경 이벤트마다 실행되므로, 트리거 로직에 과도한 계산이나 복잡한 연산이 포함될 경우 데이터베이스 성능에 영향을 미칠 수 있습니다. 로직은 최소한의 연산으로 간결하게 작성해야 합니다.

• 트리거는 데이터베이스 내부에서 비동기적으로 실행되지 않고, 트랜잭션 내에서 즉시 실행되므로, 실패 시 전체 트랜잭션이 롤백됩니다. 이러한 특성으로 인해 예상치 못한 동작이나 오류를 디버깅하기가 어렵습니다.

• 트리거는 테이블 수준에서 설정되므로, 의도하지 않은 부작용(예: 무한 루프 트리거 호출)이 발생하지 않도록 설계해야 합니다. 트리거 실행 조건과 적용 범위를 명확히 정의하는 것이 중요합니다.

• 데이터 변경이 자주 발생하는 테이블에 트리거를 설정할 경우, 이벤트 빈도와 트리거 실행 시간이 쿼리 응답 속도에 영향을 미칠 수 있습니다. 이를 최소화하기 위해 트리거 로직은 데이터베이스 작업(예: UPDATE, INSERT) 외부에서 처리할 수 있는 연산을 배제하고, 데이터베이스 내에서만 처리 가능한 작업에 집중해야 합니다.

---

updated_at trigger SQL (프로젝트 사용례)

위 구문 방식에 따라 updated_at을 자동으로 업데이트 하는 트리거 함수 및 트리거를 작성했습니다. 이를 Supabase Dashboard > SQL Editor 에서 실행하여 설정합니다.

# trigger function
CREATE OR REPLACE FUNCTION update_timestamp()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

# trigger
CREATE TRIGGER update_house_trigger
BEFORE INSERT OR UPDATE ON house
FOR EACH ROW
EXECUTE FUNCTION update_timestamp();

CREATE TRIGGER update_profile_trigger
BEFORE INSERT OR UPDATE ON user_profile
FOR EACH ROW
EXECUTE FUNCTION update_timestamp();

trigger function description

• CREATE OR REPLACE: 함수가 이미 존재하면 덮어쓰고, 존재하지 않으면 새로 생성하는 명령어입니다.
• RETURNS TRIGGER : 이 함수는 트리거 함수임을 알려줍니다. 즉, 테이블의 데이터가 변경될 때 자동으로 호출될 함수임을 알려줍니다.
• AS $$ ... $$ : 함수를 정의하는 구간으로 $$ 는 함수 본문을 둘러싸는 구분자입니다.(일종의 ' '(따옴표)와 같은 겁니다. ' '로도 할 수 있지만, 함수 내부에서 따옴표를 사용하는 경우 \를 붙여서 사용하는 등 불편함이 많아 $$로 감싸서 사용하는 것이 일반적입니다.)
• BEGIN ... END : 함수 실행 구간 시작과 끝을 알려줍니다. 함수 본문은 사이에 작성합니다.
• NEW : 트리거가 동작하는 동안 생성・수정되는 데이터의 새 값을 나타냅니다. 즉, 해당 행(column)의 새 데이터를 참조합니다.
  • NEW.updated_at 은 새로 생성되거나 수정되는 데이터 행의 updated_at 열을 나타냅니다.
  • NOW() : 현재 날짜와 시간을 반환하는 supabase postgreSQL의 내장 함수입니다.
• RETURN NEW : 트리거 함수는 항상 생성・수정되는 새로운 데이터 행을 반환 값으로 설정해야 합니다. 이 반환되는 데이터가 데이터베이스에 저장됩니다.

trigger function with supabase dashboard

SQL로 작성하지 않고 supabase dashboard를 통해서도 위와 똑같은 트리거 함수를 생성할 수 있습니다.

대쉬보드에서 Database > functions > Create a new function 을 통해 생성할 수 있습니다.

나타난 사이드 바에 함수 명과, 트리거를 설정하고, 함수 내용을 작성하면 끝입니다.

trigger with supabase dashboard

트리거 설정 또한 supabase dash board를 사용해서도 쉽게 설정할 수 있습니다.

대쉬보드에서 Database > triggers > Create a new trigger 를 통해 생성할 수 있습니다.

나타난 사이드 바에 트리거 명과, 적용할 테이블, 함수를 호출할 이벤트와 실행 시기 등의 옵션을 선택하고 만든 트리거 함수를 연결해주면 됩니다.

---

👩🏻‍💻 ʀᴇꜱᴜʟᴛꜱ

이렇게 수동으로 처리하던 updated_at 필드 갱신 로직을 데이터베이스 트리거로 대체하여, 데이터 수정 시 updated_at이 자동으로 갱신되도록 구현했습니다.

이제는 update 함수를 불러서 데이터를 넘길 때, new Date()로 불러온 현재 시각을 정제해서 넘길 필요가 없어졌습니다. supabase에서 자동으로 업데이트 되는 시간의 타임스탭프를 데이터베이스에 저장합니다.

이를 통해 데이터 변경 기록의 정확성을 데이터베이스 수준에서 보장할 수 있게 되었으며, 어플리케이션 코드에서 발생할 수 있는 갱신 로직의 누락이나 실수를 원천적으로 방지했습니다.

또한, 데이터베이스에서 직접 updated_at 갱신을 처리함으로써 서버-클라이언트 간의 불필요한 데이터 전달 및 코드 중복을 제거하여 전반적인 코드베이스를 간결하고 유지보수하기 쉽게 개선했습니다.