[개발일지 #05 | 백엔드] 메신저 DB 스키마 설계 & Go sqlc 연동하기

안녕하세요! 카카오톡 스타일의 가족/친구용 프라이빗 크로스플랫폼 메신저 ‘딩동(DingDong)’ 만들기 프로젝트 5편입니다.

지난 [4편] 에서는 유그린 NAS에서 돌아가는 Go 백엔드 API 서버(chi 라우터, Graceful Shutdown, 멀티스테이지 Dockerfile)를 세팅했습니다.
이번 5편에서는 메신저 백엔드의 핵심인 데이터베이스 스키마(테이블 구조) 를 설계하고, SQL을 Go 코드로 바꿔 주는 도구 sqlc 를 연동하는 과정을 다룹니다.

1. 메신저 데이터베이스 설계 시 고려해야 할 4가지

단순한 게시판이나 쇼핑몰과 달리, 실시간 메신저는 데이터베이스 설계에서 신경 쓸 요구사항이 많습니다.

flowchart TD
    classDef titleStyle fill:#0f172a,stroke:#0284c7,stroke-width:2px,color:#ffffff;
    classDef nodeStyle fill:#1e293b,stroke:#475569,stroke-width:2px,color:#ffffff;

    A["메신저 DB 핵심 설계 원칙"]:::titleStyle --> B["1. 방별 단조 증가 순번 (seq)"]:::nodeStyle
    A --> C["2. 1:1 채팅방 중복 생성 방지"]:::nodeStyle
    A --> D["3. 전화번호는 해시로만 저장"]:::nodeStyle
    A --> E["4. 다중 기기 세션 및 읽음 확인"]:::nodeStyle

① 방별 단조 증가 순번 (seq)

시간(Timestamp)만으로 메시지를 정렬하면 기기 간 시계 오차나, 같은 순간에 여러 메시지가 들어올 때 순서가 뒤틀릴 수 있습니다. 그래서 방마다 1, 2, 3… 순번(seq)을 원자적(Atomic) 으로 부여합니다. 클라이언트는 연결이 끊겼다가 다시 붙었을 때 “5번 이후의 메시지만 주세요!”라고 요청해서 빠진 메시지 없이 이어받을 수 있습니다(증분 동기화).

② 1:1 채팅방 중복 생성 방지 (direct_key)

A가 B에게 대화를 걸고, B도 A에게 대화를 걸 때 1:1 방이 두 개 생기면 안 됩니다. 두 사용자의 UUID를 오름차순으로 정렬해 작은UUID:큰UUID 형태의 키를 만들고, 이 키에 DB의 UNIQUE 제약을 걸어서 중복 생성을 막습니다.

③ 전화번호는 해시로만 저장 (phone_hash)

평문 전화번호는 DB에 저장하지 않고, E.164 국제 표준 형식의 번호를 서버 비밀키로 계산한 HMAC-SHA256 해시로만 저장합니다. 주소록의 번호를 서버가 같은 방식으로 해시해서 가입자와 비교하면, 평문 번호를 보관하지 않고도 친구를 찾을 수 있습니다.

💡 일반 SHA-256만 쓰지 않는 이유: 전화번호는 경우의 수가 작아서, 해시값이 유출되면 모든 번호를 계산해 대입하는 방식으로 쉽게 되돌려집니다. 서버 비밀키가 들어간 HMAC은 키 없이는 계산할 수 없습니다. 구체적인 코드는 [6편]에서 다룹니다.

④ 다중 기기(모바일/태블릿/PC) 세션 관리

한 사용자가 스마트폰, 아이패드, 맥북에서 동시에 접속할 수 있으므로, 기기별(devices) 세션과 푸시 토큰(FCM/APNs)을 따로 관리합니다.

2. 왜 ORM 대신 sqlc를 선택했을까?

Node.js나 Python에서는 TypeORM, Prisma, SQLAlchemy 같은 ORM을 흔히 사용합니다. Go에서도 GORM 같은 ORM이 있지만, SQL을 직접 작성하고 Go 코드를 자동 생성해 주는 sqlc 도 많이 쓰이는 선택지입니다. 이 시리즈에서는 sqlc를 사용합니다.

💡 sqlc의 동작: 개발자가 .sql 파일에 순수한 PostgreSQL 쿼리를 작성하고 터미널에서 sqlc generate를 실행하면, sqlc가 쿼리를 분석해서 Go 구조체와 함수 코드를 자동으로 만들어 줍니다.

3. 전체 데이터베이스 스키마 (DDL) 및 파일 생성

3.1 파일 경로 및 폴더 구조 안내

마이그레이션 파일은 server/ 아래에 migrations/ 폴더를 만들어 관리합니다. golang-migrate와 sqlc가 인식할 수 있도록, 파일 이름 뒤에 .up.sql(적용용)과 .down.sql(되돌리기용)을 붙이는 것이 표준 규칙입니다.

server/
 ├─ migrations/
 │   ├─ 000001_init_schema.up.sql    # 👈 [지금 만들 파일: 테이블 생성 DDL]
 │   └─ 000001_init_schema.down.sql  # [되돌리기용 파일: 테이블 삭제 DROP]
 ├─ queries/                         # 5장에서 만들 SQL 쿼리들
 ├─ sqlc.yaml                        # 4장에서 만들 sqlc 설정
 ├─ cmd/api/main.go
 ├─ Dockerfile
 ├─ go.mod
 └─ go.sum

3.2 테이블 생성 스키마 (server/migrations/000001_init_schema.up.sql)

VS Code에서 server/migrations/ 폴더를 만들고, 000001_init_schema.up.sql 파일을 만들어 아래 DDL을 붙여넣습니다.

💡 탈퇴한 사용자는 행을 지우지 않습니다. 일반적인 탈퇴는 users.status를 'deleted'로 바꾸는 방식(소프트 삭제)으로 처리합니다. 아래의 ON DELETE SET NULL 같은 규칙은 관리자가 사용자 행을 물리적으로 삭제하는 경우에도 외래키 오류 없이 동작하게 하려는 안전장치입니다.

-- 1. 사용자 (Users)
CREATE TABLE users (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    display_name    VARCHAR(50)  NOT NULL,
    status_message  VARCHAR(100),
    avatar_file_id  UUID,                                   -- files와 서로 참조하므로 외래키는 걸지 않음
    phone_hash      VARCHAR(64)  UNIQUE,                    -- E.164 번호의 HMAC-SHA256 해시(16진수 64자). UNIQUE가 인덱스를 자동 생성
    role            VARCHAR(10)  NOT NULL DEFAULT 'user',   -- 'admin' | 'user'
    status          VARCHAR(10)  NOT NULL DEFAULT 'active', -- 'active' | 'suspended' | 'deleted'
    invited_by      UUID REFERENCES users(id) ON DELETE SET NULL,
    created_at      TIMESTAMPTZ  NOT NULL DEFAULT now(),
    updated_at      TIMESTAMPTZ  NOT NULL DEFAULT now()
);

-- 2. 로그인 계정 수단 (Auth Identities)
CREATE TABLE auth_identities (
    id             UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id        UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    provider       VARCHAR(10) NOT NULL,          -- 'password' | 'google' | 'apple'
    provider_uid   VARCHAR(255) NOT NULL,         -- 로그인 아이디 또는 소셜 sub
    password_hash  TEXT,                          -- Argon2id 해시 (비밀번호 로그인 시)
    created_at     TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE (provider, provider_uid)
);

-- 3. 기기 세션 및 푸시 토큰 (Devices)
CREATE TABLE devices (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id             UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    platform            VARCHAR(10) NOT NULL,     -- 'android' | 'ios' | 'macos' | 'windows'
    name                VARCHAR(100),             -- 예: "아빠 Galaxy S24 Ultra"
    refresh_token_hash  TEXT NOT NULL,            -- Refresh Token 원본이 아닌 SHA-256 해시
    refresh_expires_at  TIMESTAMPTZ NOT NULL,
    fcm_token           TEXT,
    voip_token          TEXT,                     -- iOS PushKit 통화용
    app_version         VARCHAR(20),
    last_active_at      TIMESTAMPTZ,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX idx_devices_user_id ON devices(user_id);
-- 토큰 갱신(refresh) 때마다 이 해시로 기기를 찾으므로 고유 인덱스를 둡니다. (6편의 GetDeviceByRefreshToken)
CREATE UNIQUE INDEX idx_devices_refresh_token_hash ON devices(refresh_token_hash);

-- 4. 초대 코드 (Invite Codes)
CREATE TABLE invite_codes (
    code        VARCHAR(32) PRIMARY KEY,          -- 추측하기 어려운 무작위 문자열을 권장
    owner_id    UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    max_uses    INT NOT NULL DEFAULT 10 CHECK (max_uses > 0),
    used_count  INT NOT NULL DEFAULT 0,
    expires_at  TIMESTAMPTZ NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    CHECK (used_count >= 0 AND used_count <= max_uses)
);

-- 5. 친구 관계 (Friendships)
CREATE TABLE friendships (
    user_id     UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    friend_id   UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    tag         VARCHAR(10) NOT NULL DEFAULT 'friend',  -- 'family' | 'friend'
    nickname    VARCHAR(50),
    blocked     BOOLEAN NOT NULL DEFAULT false,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    PRIMARY KEY (user_id, friend_id)
);

-- 6. 채팅방 (Rooms)
CREATE TABLE rooms (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    type            VARCHAR(10) NOT NULL,             -- 'direct' | 'group'
    direct_key      VARCHAR(80) UNIQUE,               -- 1:1 방 중복 방지 키 ("작은UUID:큰UUID")
    name            VARCHAR(50),
    avatar_file_id  UUID,
    last_seq        BIGINT NOT NULL DEFAULT 0,        -- 방 메시지 최신 순번
    created_by      UUID REFERENCES users(id) ON DELETE SET NULL,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- 7. 채팅방 참여 멤버 (Room Members)
CREATE TABLE room_members (
    room_id        UUID NOT NULL REFERENCES rooms(id) ON DELETE CASCADE,
    user_id        UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    role           VARCHAR(10) NOT NULL DEFAULT 'member',  -- 'owner' | 'member'
    last_read_seq  BIGINT NOT NULL DEFAULT 0,              -- 마지막으로 읽은 메시지 순번
    joined_seq     BIGINT NOT NULL DEFAULT 0,              -- 입장 시점 순번 (이전 대화 숨김용)
    muted          BOOLEAN NOT NULL DEFAULT false,
    pinned         BOOLEAN NOT NULL DEFAULT false,
    joined_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
    PRIMARY KEY (room_id, user_id)
);
CREATE INDEX idx_room_members_user ON room_members(user_id);

-- 8. 메시지 (Messages)
CREATE TABLE messages (
    id           UUID PRIMARY KEY,                  -- 클라이언트가 생성한 중복 방지용 UUID v7
    room_id      UUID NOT NULL REFERENCES rooms(id) ON DELETE CASCADE,
    seq          BIGINT NOT NULL,                   -- 방 내 1씩 증가하는 단조 순번
    sender_id    UUID REFERENCES users(id) ON DELETE SET NULL,  -- 시스템 메시지이거나 사용자 행이 삭제되면 NULL
    type         VARCHAR(10) NOT NULL,              -- 'text' | 'image' | 'file' | 'call_log' | 'system'
    text         TEXT CHECK (char_length(text) <= 10000),
    attachments  JSONB,                             -- [{fileId, name, size, mime, width, height, thumbFileId}]
    call_log     JSONB,                             -- {callId, durationSec, result}
    deleted_at   TIMESTAMPTZ,
    created_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE (room_id, seq)                           -- 증분 동기화 쿼리용 인덱스를 겸함
);
CREATE INDEX idx_messages_room_created ON messages(room_id, created_at DESC);

-- 9. 파일 저장소 메타데이터 (Files)
CREATE TABLE files (
    id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    uploader_id   UUID NOT NULL REFERENCES users(id),   -- 파일이 남아 있는 사용자는 물리 삭제할 수 없음
    message_id    UUID REFERENCES messages(id) ON DELETE SET NULL,
    purpose       VARCHAR(10) NOT NULL,             -- 'message' | 'avatar'
    storage_path  TEXT NOT NULL,                    -- NAS 저장 상대경로 (예: 2026/10/uuid.jpg)
    name          VARCHAR(255) NOT NULL,
    mime_type     VARCHAR(100),
    size          BIGINT NOT NULL,
    sha256        CHAR(64),
    status        VARCHAR(10) NOT NULL DEFAULT 'uploading',  -- 'uploading' | 'ready'
    created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- 10. 보이스톡 통화 기록 (Calls & Call Participants)
CREATE TABLE calls (
    id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    room_id       UUID NOT NULL REFERENCES rooms(id) ON DELETE CASCADE,
    caller_id     UUID REFERENCES users(id) ON DELETE SET NULL,
    status        VARCHAR(10) NOT NULL,             -- 'ringing'|'accepted'|'declined'|'missed'|'ended'
    created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
    accepted_at   TIMESTAMPTZ,
    ended_at      TIMESTAMPTZ
);

CREATE TABLE call_participants (
    call_id    UUID NOT NULL REFERENCES calls(id) ON DELETE CASCADE,
    user_id    UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    joined_at  TIMESTAMPTZ,
    left_at    TIMESTAMPTZ,
    PRIMARY KEY (call_id, user_id)
);

📌 gen_random_uuid()는 PostgreSQL 13부터 기본으로 들어 있어서, 예전 글에 있던 CREATE EXTENSION pgcrypto는 필요하지 않습니다. 또 phone_hash에는 UNIQUE가 이미 인덱스를 만들어 주므로 별도 인덱스를 따로 만들지 않았습니다.

3.3 되돌리기용 스키마 (server/migrations/000001_init_schema.down.sql)

마이그레이션 도구는 실수를 되돌릴 때를 대비해 테이블을 지우는 .down.sql도 필요로 합니다. 테이블을 만든 순서의 반대로 지우는 코드입니다.

DROP TABLE IF EXISTS call_participants CASCADE;
DROP TABLE IF EXISTS calls CASCADE;
DROP TABLE IF EXISTS files CASCADE;
DROP TABLE IF EXISTS messages CASCADE;
DROP TABLE IF EXISTS room_members CASCADE;
DROP TABLE IF EXISTS rooms CASCADE;
DROP TABLE IF EXISTS friendships CASCADE;
DROP TABLE IF EXISTS invite_codes CASCADE;
DROP TABLE IF EXISTS devices CASCADE;
DROP TABLE IF EXISTS auth_identities CASCADE;
DROP TABLE IF EXISTS users CASCADE;

⚠️ down은 모든 데이터를 지웁니다. 개발 중에 스키마를 처음부터 다시 만들 때만 사용하고, 실제 가족 데이터가 들어 있는 DB에서는 절대 실행하지 마세요.

3.4 PostgreSQL DB에 실제 테이블 생성하기 (마이그레이션 실행)

작성한 스키마 파일을 3편에서 띄워 둔 NAS의 PostgreSQL에 적용합니다. 3편에서 DB 포트를 NAS 자기 자신(127.0.0.1)에만 열어 두었으므로, 먼저 Mac 터미널 하나에서 SSH 터널을 열어 둡니다. (3편 4.4절)

ssh -N -L 5432:127.0.0.1:5432 <NAS계정>@<NAS내부IP>

그다음 다른 터미널 창에서 server/ 폴더로 이동해 접속 주소를 환경변수로 지정하고 마이그레이션을 실행합니다. (비밀번호는 3편에서 만든 값입니다. 영문과 숫자로만 만들었다면 주소에 그대로 넣어도 됩니다.)

cd server

# 비밀번호를 화면과 명령어 기록에 남기지 않고 입력받아 환경변수로 지정 (macOS 기본 셸 zsh 기준)
read -s "DBPW?DB 비밀번호: "; echo
export DATABASE_URL="postgres://dingdong:${DBPW}@localhost:5432/dingdong?sslmode=disable"

# golang-migrate로 DB에 테이블 일괄 생성 (설치가 안 되어 있다면 brew install golang-migrate)
migrate -path ./migrations -database "$DATABASE_URL" up

테이블이 만들어졌는지 NAS 터미널(SSH)에서 확인해 봅니다.

docker exec -it dingdong-postgres psql -U dingdong -d dingdong -c "\dt"

users, rooms, messages 등 10여 개의 테이블과 schema_migrations가 보이면 성공입니다.

⚠️ migrate와 psql로 직접 SQL을 적용하는 방식을 섞어 쓰지 마세요. migrate는 어디까지 적용했는지를 schema_migrations 테이블에 기록합니다. psql로 직접 .up.sql을 실행하면 이 기록이 남지 않아, 나중에 migrate up이 “이미 존재하는 테이블” 에러를 냅니다. 이미 psql로 적용해 버렸다면 아래 명령으로 “1번까지 적용됨”이라고 기록해 주세요.

migrate -path ./migrations -database "$DATABASE_URL" force 1

4. sqlc 설치 및 설정 (sqlc.yaml)

4.1 sqlc CLI 설치 (Mac 기준)

4편에서 이미 설치했다면 건너뛰어도 됩니다. 맥 터미널에서 Homebrew로 설치합니다.

brew install sqlc

4.2 sqlc.yaml 설정 파일 작성

server/sqlc.yaml 경로에 설정 파일을 만듭니다.

💡 알아 두면 좋은 설정 3가지 (sqlc v2 + pgx/v5)

• engine: "postgresql": sqlc v2 설정에서는 사용할 DB 엔진을 반드시 적어야 합니다. 빠지면 engine is required 같은 에러가 납니다.
• emit_pointers_for_null_types: true: sqlc와 pgx/v5를 함께 쓰면 NULL이 가능한 컬럼이 pgtype.Text 같은 다루기 번거로운 타입으로 생성됩니다. 이 옵션을 켜면 Go의 포인터(*string, *int 등)로 생성되어 코드가 훨씬 간단해집니다.
• NULL 가능 UUID의 pointer: true: 타입 문자열 앞에 *를 붙여 쓰면 sqlc가 import 코드를 잘못 만듭니다. 포인터 타입은 반드시 아래처럼 import, type, pointer: true 객체 형태로 선언해야 *uuid.UUID가 올바르게 생성됩니다.

version: "2"
sql:
  - engine: "postgresql"
    schema: "migrations"
    queries: "queries"
    gen:
      go:
        package: "db"
        out: "internal/database/db"
        sql_package: "pgx/v5"
        emit_json_tags: true
        emit_prepared_queries: false
        emit_interface: true
        emit_empty_slices: true
        emit_pointers_for_null_types: true
        overrides:
          - db_type: "uuid"
            go_type: "github.com/google/uuid.UUID"
          - db_type: "uuid"
            nullable: true
            go_type:
              import: "github.com/google/uuid"
              type: "UUID"
              pointer: true

💡 schema: "migrations"는 golang-migrate 형식의 폴더를 읽어 테이블 구조를 파악합니다. sqlc는 이 형식에서 되돌리기용 .down.sql 파일을 건너뛰고 .up.sql만 읽습니다. 만약 sqlc generate에서 스키마 관련 에러가 난다면, schema를 "migrations/000001_init_schema.up.sql"처럼 파일 하나로 지정해 보세요.

5. SQL 쿼리 작성 및 자동 코드 생성 실습

이제 server/queries/ 폴더에 필요한 쿼리를 작성합니다.

5.1 회원 관련 쿼리 (server/queries/users.sql)

💡 sqlc.narg로 선택적 업데이트 만들기
display_name 컬럼이 NOT NULL일 때 COALESCE($2, display_name)처럼 쓰면, sqlc는 $2를 일반 string으로 만듭니다. 그런데 Go의 빈 문자열("")은 SQL의 NULL이 아니어서, “이름을 바꾸지 않겠다”는 뜻으로 빈 값을 넘기면 이름이 빈 문자열로 덮어써지는 버그가 생깁니다.
sqlc.narg('display_name')을 쓰면 Go에서 포인터(*string)로 생성되어, nil을 넘기면 기존 값이 유지됩니다. (다만 이 방식으로는 값을 NULL로 비우는 것은 표현할 수 없습니다. 상태 메시지를 지우는 기능이 필요해지면 쿼리를 따로 만드세요.)

-- name: CreateUser :one
INSERT INTO users (display_name, status_message, phone_hash, role)
VALUES ($1, $2, $3, $4)
RETURNING *;

-- name: GetUserByID :one
-- 로그인/토큰 갱신(6편)에서 쓰이므로 정상(active) 계정만 조회합니다.
-- 정지(suspended)·삭제(deleted)된 계정은 조회되지 않아 로그인할 수 없습니다.
SELECT * FROM users
WHERE id = $1 AND status = 'active';

-- name: GetUserByPhoneHash :one
SELECT * FROM users
WHERE phone_hash = $1 AND status = 'active';

-- name: UpdateUserProfile :one
UPDATE users
SET display_name = COALESCE(sqlc.narg('display_name'), display_name),
    status_message = COALESCE(sqlc.narg('status_message'), status_message),
    avatar_file_id = COALESCE(sqlc.narg('avatar_file_id'), avatar_file_id),
    updated_at = now()
WHERE id = sqlc.arg('id')
RETURNING *;

5.2 채팅 메시지 쿼리 (server/queries/messages.sql)

💡 CTE(WITH 구문)로 순번 증가와 메시지 저장을 한 번에
메시지를 보낼 때 “방의 최신 순번 1 증가”와 “메시지 삽입”을 별도 쿼리로 나누면 네트워크 왕복이 2번 생기고, 그 사이에 서버가 꺼지면 순번만 올라가고 메시지는 사라지는 불일치가 생길 수 있습니다.
PostgreSQL의 CTE(WITH ...)를 쓰면 하나의 SQL 문장으로 순번 증가와 메시지 저장을 처리할 수 있고, SQL 문장 하나는 PostgreSQL 안에서 통째로 성공하거나 통째로 취소됩니다. 같은 방에 동시에 메시지가 들어와도 UPDATE가 방 행을 잠그기 때문에 순번이 겹치지 않습니다.

-- name: SendMessageWithSeq :one
WITH next_seq AS (
    UPDATE rooms
    SET last_seq = last_seq + 1,
        updated_at = now()
    WHERE id = sqlc.arg('room_id')::uuid
    RETURNING last_seq
)
INSERT INTO messages (
    id,
    room_id,
    seq,
    sender_id,
    type,
    text,
    attachments,
    call_log
)
SELECT
    sqlc.arg('id')::uuid,
    sqlc.arg('room_id')::uuid,
    next_seq.last_seq,
    sqlc.narg('sender_id')::uuid,
    sqlc.arg('type')::varchar,
    sqlc.narg('text')::text,
    sqlc.narg('attachments')::jsonb,
    sqlc.narg('call_log')::jsonb
FROM next_seq
RETURNING *;

-- name: GetMessagesAfterSeq :many
SELECT * FROM messages
WHERE room_id = sqlc.arg('room_id')::uuid
  AND seq > sqlc.arg('after_seq')::bigint
ORDER BY seq ASC
LIMIT sqlc.arg('max_count')::int;

-- name: IsRoomMember :one
SELECT EXISTS (
    SELECT 1 FROM room_members
    WHERE room_id = sqlc.arg('room_id')::uuid
      AND user_id = sqlc.arg('user_id')::uuid
) AS is_member;

💡 ::uuid, ::bigint 같은 타입 표기를 붙인 이유: INSERT ... SELECT 안의 파라미터는 sqlc가 타입을 추측하기 어려워 interface{}로 만들 수 있습니다. 타입을 직접 적어 주면 SendMessageWithSeqParams{ID, RoomID, SenderID, Type, Text, ...}처럼 이름과 타입이 분명한 구조체가 생성됩니다.

⚠️ 이 쿼리들은 “이 사람이 이 방의 멤버인가”를 확인하지 않습니다. 위의 IsRoomMember를 서비스 코드에서 먼저 호출해서 멤버가 아니면 요청을 거부해야 합니다. 이 확인을 빼먹으면 로그인한 누구나 다른 방에 메시지를 보내거나 대화를 읽을 수 있게 됩니다. (채팅 편에서 실제로 적용합니다.)

그 밖에 알아 둘 점

• 방이 없으면 SendMessageWithSeq는 결과 행이 없어서 pgx.ErrNoRows가 반환됩니다.
• 같은 메시지 id를 다시 보내면(재전송) 고유 키 에러가 나고, 이때 순번 증가도 함께 취소됩니다. 서비스 코드에서 이 에러를 “이미 저장됨”으로 처리하세요.
• 삭제된 메시지(deleted_at이 있는 행)의 text도 이 쿼리는 그대로 돌려줍니다. 클라이언트에 내려보내기 전에 서비스 코드에서 내용을 비워 주세요.

5.3 코드 생성 실행: sqlc generate

터미널에서 server/ 디렉토리로 이동한 뒤 코드 생성 명령어를 실행하고, 생성된 코드가 사용하는 패키지를 설치합니다.

cd server

# 1. SQL을 분석하여 Go 코드 자동 생성!
sqlc generate

# 2. 생성된 코드에 필요한 패키지 설치 및 정리
go get github.com/jackc/pgx/v5
go get github.com/google/uuid
go mod tidy

명령어가 끝나면 server/internal/database/db/ 폴더에 다음과 같은 Go 파일들이 생성됩니다.

• models.go: 모든 테이블에 대응하는 구조체 (UUID는 uuid.UUID로 매핑됨)
• users.sql.go: 회원 쿼리 함수들 (UpdateUserProfileParams는 포인터 필드를 가짐)
• messages.sql.go: SendMessageWithSeq, GetMessagesAfterSeq, IsRoomMember
• db.go & querier.go: DB 인터페이스 및 쿼리 실행기

⚠️ 이 폴더의 파일들은 자동 생성 파일이므로 직접 수정하지 마세요. 고치고 싶다면 .sql 쿼리나 sqlc.yaml을 바꾸고 sqlc generate를 다시 실행합니다.

6. Go 서버에 DB 연결 풀(pgxpool) 및 트랜잭션(WithTx) 통합하기

단일 쿼리는 위처럼 처리할 수 있지만, “방 생성 + 여러 멤버 초대”나 6편의 회원가입처럼 여러 쿼리를 묶어야 하는 로직은 Go 코드에서 트랜잭션이 필요합니다. 이를 위해 WithTx 래퍼를 만들고 메인 서버에 연결합니다.

⚠️ 주의: 아래 코드의 import 경로에 있는 github.com/yourname/dingdong-server 부분은 4편에서 go mod init을 할 때 입력한 본인의 모듈 이름과 똑같이 맞춰 주세요!

6.1 트랜잭션 래퍼 (server/internal/database/tx.go)

server/internal/database/ 폴더를 만들고 tx.go 파일을 작성합니다.

package database

import (
	"context"
	"fmt"

	"github.com/jackc/pgx/v5"
	"github.com/jackc/pgx/v5/pgxpool"
	"github.com/yourname/dingdong-server/internal/database/db"
)

// WithTx: 트랜잭션 안에서 sqlc 쿼리를 실행하고, 성공하면 Commit, 실패하면 Rollback합니다.
func WithTx(ctx context.Context, pool *pgxpool.Pool, fn func(q *db.Queries) error) error {
	tx, err := pool.BeginTx(ctx, pgx.TxOptions{})
	if err != nil {
		return fmt.Errorf("트랜잭션 시작 실패: %w", err)
	}
	// 함수가 어떤 이유로 끝나든(에러, 패닉 포함) 커밋되지 않은 트랜잭션은 반드시 되돌립니다.
	// 이미 Commit된 뒤에 호출하면 아무 일도 하지 않고 에러만 반환하므로, 결과는 무시해도 안전합니다.
	defer func() { _ = tx.Rollback(ctx) }()

	if err := fn(db.New(tx)); err != nil {
		return err
	}

	if err := tx.Commit(ctx); err != nil {
		return fmt.Errorf("커밋 실패: %w", err)
	}
	return nil
}

6.2 서버 진입점에 DB 연결 풀 통합 (server/cmd/api/main.go)

지난 4편에서 만든 웹서버 코드(main.go)에 PostgreSQL 커넥션 풀(pgxpool)과 sqlc 쿼리 객체를 결합한 버전입니다. /health 엔드포인트는 이제 DB 연결 상태까지 확인하고, DB에 닿지 않으면 503으로 응답합니다.

DB 접속 주소(비밀번호 포함)는 코드에 적어 두지 않고 DATABASE_URL 환경변수로만 받습니다. NAS의 Docker에서는 4편의 compose 파일이 이 값을 넣어 주고, 맥에서 직접 실행할 때는 3.4절의 export DATABASE_URL=...(SSH 터널 필요)를 사용합니다.

package main

import (
	"context"
	"encoding/json"
	"errors"
	"log"
	"net/http"
	"os"
	"os/signal"
	"syscall"
	"time"

	"github.com/go-chi/chi/v5"
	"github.com/go-chi/chi/v5/middleware"
	"github.com/go-chi/cors"
	"github.com/jackc/pgx/v5/pgxpool"
	"github.com/yourname/dingdong-server/internal/database/db"
)

func main() {
	ctx := context.Background()

	port := os.Getenv("PORT")
	if port == "" {
		port = "3000"
	}

	// 비밀번호가 들어 있는 값이라 코드에 기본값을 두지 않습니다.
	dbURL := os.Getenv("DATABASE_URL")
	if dbURL == "" {
		log.Fatal("DATABASE_URL 환경변수가 필요합니다. 예: export DATABASE_URL=\"postgres://dingdong:비밀번호@localhost:5432/dingdong?sslmode=disable\"")
	}

	// 1. PostgreSQL 커넥션 풀 설정
	config, err := pgxpool.ParseConfig(dbURL)
	if err != nil {
		log.Fatalf("DB 설정 파싱 실패: %v", err)
	}
	config.MaxConns = 25                     // 최대 연결 수 (PostgreSQL 기본 한도 100보다 낮게)
	config.MinConns = 5                      // 유휴 상태에서도 유지할 최소 연결 수
	config.MaxConnIdleTime = 5 * time.Minute // 오래 쓰지 않은 연결 회수 시간

	pool, err := pgxpool.NewWithConfig(ctx, config)
	if err != nil {
		log.Fatalf("PostgreSQL 연결 풀 생성 실패: %v", err)
	}
	defer pool.Close()

	if err := pool.Ping(ctx); err != nil {
		log.Fatalf("PostgreSQL Ping 실패: %v", err)
	}
	log.Println("🐘 PostgreSQL 연결 풀 초기화 성공!")

	// 2. sqlc 쿼리 객체 초기화 (다음 6편의 인증 API에서 주입해서 사용합니다)
	queries := db.New(pool)
	_ = queries

	// 3. Chi 라우터 및 미들웨어 설정
	r := chi.NewRouter()
	r.Use(middleware.RequestID)
	r.Use(middleware.RealIP) // Caddy 뒤에서만 접근되는 구성이라 사용 (4편 참고)
	r.Use(middleware.Logger)
	r.Use(middleware.Recoverer)

	// 개발 단계용으로 느슨하게 열어 둔 CORS 설정입니다. 웹 클라이언트를 만들 때 허용 도메인만 남기세요.
	r.Use(cors.Handler(cors.Options{
		AllowedOrigins:   []string{"https://*", "http://*"},
		AllowedMethods:   []string{"GET", "POST", "PUT", "DELETE", "OPTIONS"},
		AllowedHeaders:   []string{"Accept", "Authorization", "Content-Type", "X-CSRF-Token"},
		ExposedHeaders:   []string{"Link"},
		AllowCredentials: false,
		MaxAge:           300,
	}))

	// 4. 일반 REST API 라우터 그룹
	r.Group(func(api chi.Router) {
		api.Use(middleware.Timeout(60 * time.Second))

		api.Get("/health", func(w http.ResponseWriter, r *http.Request) {
			// DB 확인이 오래 걸려 헬스체크 전체가 멈추지 않도록 2초로 제한
			pingCtx, cancel := context.WithTimeout(r.Context(), 2*time.Second)
			defer cancel()

			status, dbStatus, code := "ok", "ok", http.StatusOK
			if err := pool.Ping(pingCtx); err != nil {
				status, dbStatus, code = "degraded", "unreachable", http.StatusServiceUnavailable
			}

			res := map[string]string{
				"status":  status,
				"server":  "dingdong-go",
				"db":      dbStatus,
				"time":    time.Now().Format(time.RFC3339),
				"version": "v0.2.0",
			}
			w.Header().Set("Content-Type", "application/json")
			w.WriteHeader(code)
			json.NewEncoder(w).Encode(res)
		})
	})

	// 5. 실시간 WebSocket 엔드포인트 그룹 (아직 자리만 잡아 둔 코드. 구현할 때 반드시 인증을 붙입니다)
	r.Group(func(ws chi.Router) {
		ws.Get("/ws", func(w http.ResponseWriter, r *http.Request) {
			w.Write([]byte("WebSocket 엔드포인트 (추후 구현)"))
		})
	})

	// 6. 서버 실행 및 Graceful Shutdown
	server := &http.Server{
		Addr:              ":" + port,
		Handler:           r,
		ReadHeaderTimeout: 10 * time.Second, // 4편 참고: 본문 읽기 시간은 제한하지 않음 (대용량 업로드)
		WriteTimeout:      0,
		IdleTimeout:       60 * time.Second,
	}

	go func() {
		log.Printf("🚀 딩동 Go 백엔드 서버가 포트 %s에서 시작되었습니다!", port)
		if err := server.ListenAndServe(); err != nil && !errors.Is(err, http.ErrServerClosed) {
			log.Fatalf("서버 시작 실패: %v", err)
		}
	}()

	quit := make(chan os.Signal, 1)
	signal.Notify(quit, syscall.SIGINT, syscall.SIGTERM)
	<-quit
	log.Println("서버 종료 신호를 감지했습니다. 안전하게 종료합니다...")

	shutdownCtx, cancel := context.WithTimeout(context.Background(), 10*time.Second)
	defer cancel()
	if err := server.Shutdown(shutdownCtx); err != nil {
		log.Printf("종료 중 에러: %v", err)
	}
	log.Println("서버가 종료되었습니다.")
}

실행해서 확인해 봅니다. (SSH 터널이 열려 있고 DATABASE_URL이 지정된 상태에서)

go run ./cmd/api
curl http://localhost:3000/health

"db":"ok"가 보이면 성공입니다. NAS에 반영하려면 수정한 server/ 폴더를 NAS로 다시 복사한 뒤 4편처럼 docker compose up -d --build를 실행하고, https://api.myfamily.com/health(본인 도메인)로 확인하세요.

7. 이번 편 요약 및 다음 단계

오늘 구축한 DB 레이어로 딩동 메신저는 다음과 같은 기반을 갖추었습니다.

1. 타입이 있는 SQL: 컬럼 이름 오타나 타입 불일치를 sqlc generate와 컴파일 단계에서 대부분 잡을 수 있습니다.
2. 증분 동기화의 토대: 방별 단조 증가 seq 덕분에 네트워크가 끊겨도 “이 번호 이후”만 요청해서 대화를 이어받을 수 있습니다.
3. ORM 없이 가벼운 DB 접근: sqlc가 생성한 코드가 pgx 드라이버를 직접 호출합니다. (메모리 사용량은 4편의 docker stats로 직접 확인해 보세요.)
4. 안전한 기본 설계: 전화번호는 HMAC 해시로, 사용자 삭제 시의 외래키 규칙과 중복 방지(UNIQUE)는 DB 레벨에서 처리합니다.

아직 남은 숙제

• 권한 확인: 메시지 쿼리는 방 멤버인지 확인하지 않으므로, 서비스 코드에서 IsRoomMember를 먼저 호출해야 합니다.
• 초대코드 보호: 가입 API에 요청 횟수 제한(rate limit)이 필요합니다. ([6편] 마지막 목록 참고)

다음 [개발일지 #06 | 백엔드]에서는 이 데이터베이스 위에 초대코드 기반 회원가입, Argon2id 비밀번호 암호화, JWT Access/Refresh 토큰 발급 및 보안 미들웨어를 구현해 보겠습니다!