{"id":136,"date":"2026-10-14T08:02:59","date_gmt":"2026-10-14T08:02:59","guid":{"rendered":"https:\/\/dingdonghome.duckdns.org\/?p=136"},"modified":"2026-10-31T07:43:45","modified_gmt":"2026-10-31T07:43:45","slug":"%ea%b0%9c%eb%b0%9c%ec%9d%bc%ec%a7%80-05-%eb%a9%94%ec%8b%a0%ec%a0%80-db-%ec%8a%a4%ed%82%a4%eb%a7%88-%ec%84%a4%ea%b3%84%ec%99%80-go%ec%9d%98-%ec%b5%9c%ea%b0%95-%ec%a1%b0%ed%95%a9-sqlc-%ec%97%b0","status":"publish","type":"post","link":"https:\/\/dingdong.goodday.ing\/?p=136","title":{"rendered":"[\uac1c\ubc1c\uc77c\uc9c0 #05 | \ubc31\uc5d4\ub4dc] \uba54\uc2e0\uc800 DB \uc2a4\ud0a4\ub9c8 \uc124\uacc4 & Go sqlc \uc5f0\ub3d9\ud558\uae30"},"content":{"rendered":"\n

\uc548\ub155\ud558\uc138\uc694! \uce74\uce74\uc624\ud1a1 \uc2a4\ud0c0\uc77c\uc758 \uac00\uc871\/\uce5c\uad6c\uc6a9 \ud504\ub77c\uc774\ube57 \ud06c\ub85c\uc2a4\ud50c\ub7ab\ud3fc \uba54\uc2e0\uc800 ‘\ub529\ub3d9(DingDong)’<\/strong> \ub9cc\ub4e4\uae30 \ud504\ub85c\uc81d\ud2b8 5\ud3b8\uc785\ub2c8\ub2e4.<\/p>\n\n\n\n

\uc9c0\ub09c [4\ud3b8<\/strong>] \uc5d0\uc11c\ub294 \uc720\uadf8\ub9b0 NAS\uc5d0\uc11c \ub3cc\uc544\uac00\ub294 Go \ubc31\uc5d4\ub4dc API \uc11c\ubc84(chi \ub77c\uc6b0\ud130, Graceful Shutdown, \uba40\ud2f0\uc2a4\ud14c\uc774\uc9c0 Dockerfile)\ub97c \uc138\ud305\ud588\uc2b5\ub2c8\ub2e4.
\uc774\ubc88 5\ud3b8\uc5d0\uc11c\ub294 \uba54\uc2e0\uc800 \ubc31\uc5d4\ub4dc\uc758 \ud575\uc2ec\uc778 \ub370\uc774\ud130\ubca0\uc774\uc2a4 \uc2a4\ud0a4\ub9c8(\ud14c\uc774\ube14 \uad6c\uc870)<\/strong> \ub97c \uc124\uacc4\ud558\uace0, SQL\uc744 Go \ucf54\ub4dc\ub85c \ubc14\uafd4 \uc8fc\ub294 \ub3c4\uad6c sqlc<\/strong> \ub97c \uc5f0\ub3d9\ud558\ub294 \uacfc\uc815\uc744 \ub2e4\ub8f9\ub2c8\ub2e4.<\/p>\n\n\n\n

\n\n\n\n

1. \uba54\uc2e0\uc800 \ub370\uc774\ud130\ubca0\uc774\uc2a4 \uc124\uacc4 \uc2dc \uace0\ub824\ud574\uc57c \ud560 4\uac00\uc9c0<\/h2>\n\n\n\n

\ub2e8\uc21c\ud55c \uac8c\uc2dc\ud310\uc774\ub098 \uc1fc\ud551\ubab0\uacfc \ub2ec\ub9ac, \uc2e4\uc2dc\uac04 \uba54\uc2e0\uc800\ub294 \ub370\uc774\ud130\ubca0\uc774\uc2a4 \uc124\uacc4\uc5d0\uc11c \uc2e0\uacbd \uc4f8 \uc694\uad6c\uc0ac\ud56d\uc774 \ub9ce\uc2b5\ub2c8\ub2e4.<\/p>\n\n\n\n

flowchart TD\n    classDef titleStyle fill:#0f172a,stroke:#0284c7,stroke-width:2px,color:#ffffff;\n    classDef nodeStyle fill:#1e293b,stroke:#475569,stroke-width:2px,color:#ffffff;\n\n    A[\"\uba54\uc2e0\uc800 DB \ud575\uc2ec \uc124\uacc4 \uc6d0\uce59\"]:::titleStyle --> B[\"1. \ubc29\ubcc4 \ub2e8\uc870 \uc99d\uac00 \uc21c\ubc88 (seq)\"]:::nodeStyle\n    A --> C[\"2. 1:1 \ucc44\ud305\ubc29 \uc911\ubcf5 \uc0dd\uc131 \ubc29\uc9c0\"]:::nodeStyle\n    A --> D[\"3. \uc804\ud654\ubc88\ud638\ub294 \ud574\uc2dc\ub85c\ub9cc \uc800\uc7a5\"]:::nodeStyle\n    A --> E[\"4. \ub2e4\uc911 \uae30\uae30 \uc138\uc158 \ubc0f \uc77d\uc74c \ud655\uc778\"]:::nodeStyle<\/pre>
<\/div><\/div>\n\n\n\n
\u2460 \ubc29\ubcc4 \ub2e8\uc870 \uc99d\uac00 \uc21c\ubc88 (seq<\/code>)<\/h3>\n\n\n\n
\uc2dc\uac04(Timestamp)\ub9cc\uc73c\ub85c \uba54\uc2dc\uc9c0\ub97c \uc815\ub82c\ud558\uba74 \uae30\uae30 \uac04 \uc2dc\uacc4 \uc624\ucc28\ub098, \uac19\uc740 \uc21c\uac04\uc5d0 \uc5ec\ub7ec \uba54\uc2dc\uc9c0\uac00 \ub4e4\uc5b4\uc62c \ub54c \uc21c\uc11c\uac00 \ub4a4\ud2c0\ub9b4 \uc218 \uc788\uc2b5\ub2c8\ub2e4. \uadf8\ub798\uc11c \ubc29\ub9c8\ub2e4 1, 2, 3\u2026 \uc21c\ubc88(seq<\/code>)\uc744 \uc6d0\uc790\uc801(Atomic)<\/strong> \uc73c\ub85c \ubd80\uc5ec\ud569\ub2c8\ub2e4. \ud074\ub77c\uc774\uc5b8\ud2b8\ub294 \uc5f0\uacb0\uc774 \ub04a\uacbc\ub2e4\uac00 \ub2e4\uc2dc \ubd99\uc5c8\uc744 \ub54c “5\ubc88 \uc774\ud6c4\uc758 \uba54\uc2dc\uc9c0\ub9cc \uc8fc\uc138\uc694!”\ub77c\uace0 \uc694\uccad\ud574\uc11c \ube60\uc9c4 \uba54\uc2dc\uc9c0 \uc5c6\uc774 \uc774\uc5b4\ubc1b\uc744 \uc218 \uc788\uc2b5\ub2c8\ub2e4(\uc99d\ubd84 \ub3d9\uae30\ud654).<\/p>\n\n\n\n
\u2461 1:1 \ucc44\ud305\ubc29 \uc911\ubcf5 \uc0dd\uc131 \ubc29\uc9c0 (direct_key<\/code>)<\/h3>\n\n\n\n
A\uac00 B\uc5d0\uac8c \ub300\ud654\ub97c \uac78\uace0, B\ub3c4 A\uc5d0\uac8c \ub300\ud654\ub97c \uac78 \ub54c 1:1 \ubc29\uc774 \ub450 \uac1c \uc0dd\uae30\uba74 \uc548 \ub429\ub2c8\ub2e4. \ub450 \uc0ac\uc6a9\uc790\uc758 UUID\ub97c \uc624\ub984\ucc28\uc21c\uc73c\ub85c \uc815\ub82c\ud574 \uc791\uc740UUID:\ud070UUID<\/code> \ud615\ud0dc\uc758 \ud0a4\ub97c \ub9cc\ub4e4\uace0, \uc774 \ud0a4\uc5d0 DB\uc758 UNIQUE<\/code> \uc81c\uc57d\uc744 \uac78\uc5b4\uc11c \uc911\ubcf5 \uc0dd\uc131\uc744 \ub9c9\uc2b5\ub2c8\ub2e4.<\/p>\n\n\n\n
\u2462 \uc804\ud654\ubc88\ud638\ub294 \ud574\uc2dc\ub85c\ub9cc \uc800\uc7a5 (phone_hash<\/code>)<\/h3>\n\n\n\n
\ud3c9\ubb38 \uc804\ud654\ubc88\ud638\ub294 DB\uc5d0 \uc800\uc7a5\ud558\uc9c0 \uc54a\uace0, E.164 \uad6d\uc81c \ud45c\uc900 \ud615\uc2dd\uc758 \ubc88\ud638\ub97c \uc11c\ubc84 \ube44\ubc00\ud0a4\ub85c \uacc4\uc0b0\ud55c HMAC-SHA256 \ud574\uc2dc<\/strong>\ub85c\ub9cc \uc800\uc7a5\ud569\ub2c8\ub2e4. \uc8fc\uc18c\ub85d\uc758 \ubc88\ud638\ub97c \uc11c\ubc84\uac00 \uac19\uc740 \ubc29\uc2dd\uc73c\ub85c \ud574\uc2dc\ud574\uc11c \uac00\uc785\uc790\uc640 \ube44\uad50\ud558\uba74, \ud3c9\ubb38 \ubc88\ud638\ub97c \ubcf4\uad00\ud558\uc9c0 \uc54a\uace0\ub3c4 \uce5c\uad6c\ub97c \ucc3e\uc744 \uc218 \uc788\uc2b5\ub2c8\ub2e4.<\/p>\n\n\n\n
\n
\ud83d\udca1 \uc77c\ubc18 SHA-256\ub9cc \uc4f0\uc9c0 \uc54a\ub294 \uc774\uc720: \uc804\ud654\ubc88\ud638\ub294 \uacbd\uc6b0\uc758 \uc218\uac00 \uc791\uc544\uc11c, \ud574\uc2dc\uac12\uc774 \uc720\ucd9c\ub418\uba74 \ubaa8\ub4e0 \ubc88\ud638\ub97c \uacc4\uc0b0\ud574 \ub300\uc785\ud558\ub294 \ubc29\uc2dd\uc73c\ub85c \uc27d\uac8c \ub418\ub3cc\ub824\uc9d1\ub2c8\ub2e4. \uc11c\ubc84 \ube44\ubc00\ud0a4\uac00 \ub4e4\uc5b4\uac04 HMAC\uc740 \ud0a4 \uc5c6\uc774\ub294 \uacc4\uc0b0\ud560 \uc218 \uc5c6\uc2b5\ub2c8\ub2e4. \uad6c\uccb4\uc801\uc778 \ucf54\ub4dc\ub294 [6\ud3b8<\/strong>]\uc5d0\uc11c \ub2e4\ub8f9\ub2c8\ub2e4.<\/p>\n<\/blockquote>\n\n\n\n
\u2463 \ub2e4\uc911 \uae30\uae30(\ubaa8\ubc14\uc77c\/\ud0dc\ube14\ub9bf\/PC) \uc138\uc158 \uad00\ub9ac<\/h3>\n\n\n\n
\ud55c \uc0ac\uc6a9\uc790\uac00 \uc2a4\ub9c8\ud2b8\ud3f0, \uc544\uc774\ud328\ub4dc, \ub9e5\ubd81\uc5d0\uc11c \ub3d9\uc2dc\uc5d0 \uc811\uc18d\ud560 \uc218 \uc788\uc73c\ubbc0\ub85c, \uae30\uae30\ubcc4(devices<\/code>) \uc138\uc158\uacfc \ud478\uc2dc \ud1a0\ud070(FCM\/APNs)\uc744 \ub530\ub85c \uad00\ub9ac\ud569\ub2c8\ub2e4.<\/p>\n\n\n\n
\n\n\n\n
2. \uc65c ORM \ub300\uc2e0 sqlc\ub97c \uc120\ud0dd\ud588\uc744\uae4c?<\/h2>\n\n\n\n
Node.js\ub098 Python\uc5d0\uc11c\ub294 TypeORM, Prisma, SQLAlchemy \uac19\uc740 ORM\uc744 \ud754\ud788 \uc0ac\uc6a9\ud569\ub2c8\ub2e4. Go\uc5d0\uc11c\ub3c4 GORM \uac19\uc740 ORM\uc774 \uc788\uc9c0\ub9cc, SQL\uc744 \uc9c1\uc811 \uc791\uc131\ud558\uace0 Go \ucf54\ub4dc\ub97c \uc790\ub3d9 \uc0dd\uc131\ud574 \uc8fc\ub294 sqlc<\/code><\/strong> \ub3c4 \ub9ce\uc774 \uc4f0\uc774\ub294 \uc120\ud0dd\uc9c0\uc785\ub2c8\ub2e4. \uc774 \uc2dc\ub9ac\uc988\uc5d0\uc11c\ub294 sqlc\ub97c \uc0ac\uc6a9\ud569\ub2c8\ub2e4.<\/p>\n\n\n\n
\ube44\uad50 \ud56d\ubaa9<\/th>\uc804\ud1b5\uc801\uc778 ORM (GORM \ub4f1)<\/th>sqlc<\/th><\/tr><\/thead>
\ub3d9\uc791 \ubc29\uc2dd<\/strong><\/td>\uc2e4\ud589 \uc911\uc5d0 \ub9ac\ud50c\ub809\uc158\uc73c\ub85c SQL\uc744 \ub9cc\ub4e4\uace0 \uacb0\uacfc\ub97c \uac1d\uccb4\uc5d0 \ub9e4\ud551<\/td>\uc2e4\ud589 \uc804\uc5d0 SQL \ud30c\uc77c\uc744 \uc77d\uc5b4 \uc21c\uc218 Go \ucf54\ub4dc\ub97c \ubbf8\ub9ac \uc0dd\uc131<\/td><\/tr>
\uc131\ub2a5<\/strong><\/td>\ub9e4\ud551 \uacfc\uc815\uc758 \uc624\ubc84\ud5e4\ub4dc\uac00 \uc788\uc74c (\uac00\uc871 \uaddc\ubaa8\uc5d0\uc11c\ub294 \uccb4\uac10 \ucc28\uc774\uac00 \uc791\uc744 \uc218 \uc788\uc74c)<\/td>\uc0dd\uc131\ub41c \ucf54\ub4dc\uac00 \ub4dc\ub77c\uc774\ubc84(pgx)\ub97c \uc9c1\uc811 \ud638\ucd9c\ud574 \uc624\ubc84\ud5e4\ub4dc\uac00 \uc801\uc74c<\/td><\/tr>
\ud0c0\uc785 \uc548\uc815\uc131<\/strong><\/td>\uceec\ub7fc\uba85 \uc624\ud0c0 \uac19\uc740 \uc2e4\uc218\ub97c \uc2e4\ud589 \uc911\uc5d0\uc57c \ubc1c\uacac\ud558\uae30 \uc26c\uc6c0<\/td>\uc0dd\uc131 \uc2dc\uc810\uc5d0 SQL\uc744 \uac80\uc0ac\ud558\uace0, \uacb0\uacfc\ub97c \ud0c0\uc785\uc774 \uc788\ub294 Go \uad6c\uc870\uccb4\ub85c \ub9cc\ub4e4\uc5b4 \uc90c<\/td><\/tr>
\ud559\uc2b5 \ube44\uc6a9<\/strong><\/td>\ud504\ub808\uc784\uc6cc\ud06c\ub9c8\ub2e4 \ub530\ub85c \ubb38\ubc95\uc744 \ubc30\uc6cc\uc57c \ud568<\/td>\ud3c9\uc18c \uc54c\ub358 PostgreSQL SQL\uc744 \uadf8\ub300\ub85c \uc791\uc131<\/td><\/tr>
\ub2e8\uc810<\/strong><\/td>\ubcf5\uc7a1\ud55c \ucffc\ub9ac\ub294 \uacb0\uad6d SQL\uc744 \uc9c1\uc811 \uc368\uc57c \ud568<\/td>\uac80\uc0c9 \uc870\uac74\uc774 \uacc4\uc18d \ubc14\ub00c\ub294 \ub3d9\uc801 \ucffc\ub9ac\ub294 \uc791\uc131\uc774 \ubc88\uac70\ub85c\uc6c0<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
\n
\ud83d\udca1 sqlc\uc758 \ub3d9\uc791:<\/strong> \uac1c\ubc1c\uc790\uac00 .sql<\/code> \ud30c\uc77c\uc5d0 \uc21c\uc218\ud55c PostgreSQL \ucffc\ub9ac\ub97c \uc791\uc131\ud558\uace0 \ud130\ubbf8\ub110\uc5d0\uc11c sqlc generate<\/code>\ub97c \uc2e4\ud589\ud558\uba74, sqlc\uac00 \ucffc\ub9ac\ub97c \ubd84\uc11d\ud574\uc11c Go \uad6c\uc870\uccb4\uc640 \ud568\uc218 \ucf54\ub4dc\ub97c \uc790\ub3d9\uc73c\ub85c \ub9cc\ub4e4\uc5b4 \uc90d\ub2c8\ub2e4.<\/p>\n<\/blockquote>\n\n\n\n
\n\n\n\n
3. \uc804\uccb4 \ub370\uc774\ud130\ubca0\uc774\uc2a4 \uc2a4\ud0a4\ub9c8 (DDL) \ubc0f \ud30c\uc77c \uc0dd\uc131<\/h2>\n\n\n\n
3.1 \ud30c\uc77c \uacbd\ub85c \ubc0f \ud3f4\ub354 \uad6c\uc870 \uc548\ub0b4<\/h3>\n\n\n\n
\ub9c8\uc774\uadf8\ub808\uc774\uc158 \ud30c\uc77c\uc740 server\/<\/code> \uc544\ub798\uc5d0 migrations\/<\/code> \ud3f4\ub354\ub97c \ub9cc\ub4e4\uc5b4 \uad00\ub9ac\ud569\ub2c8\ub2e4. golang-migrate\uc640 sqlc\uac00 \uc778\uc2dd\ud560 \uc218 \uc788\ub3c4\ub85d, \ud30c\uc77c \uc774\ub984 \ub4a4\uc5d0 .up.sql<\/code>(\uc801\uc6a9\uc6a9)\uacfc .down.sql<\/code>(\ub418\ub3cc\ub9ac\uae30\uc6a9)\uc744 \ubd99\uc774\ub294 \uac83\uc774 \ud45c\uc900 \uaddc\uce59\uc785\ub2c8\ub2e4.<\/p>\n\n\n\n
server\/\n \u251c\u2500 migrations\/\n \u2502   \u251c\u2500 000001_init_schema.up.sql    # \ud83d\udc48 [\uc9c0\uae08 \ub9cc\ub4e4 \ud30c\uc77c: \ud14c\uc774\ube14 \uc0dd\uc131 DDL]\n \u2502   \u2514\u2500 000001_init_schema.down.sql  # [\ub418\ub3cc\ub9ac\uae30\uc6a9 \ud30c\uc77c: \ud14c\uc774\ube14 \uc0ad\uc81c DROP]\n \u251c\u2500 queries\/                         # 5\uc7a5\uc5d0\uc11c \ub9cc\ub4e4 SQL \ucffc\ub9ac\ub4e4\n \u251c\u2500 sqlc.yaml                        # 4\uc7a5\uc5d0\uc11c \ub9cc\ub4e4 sqlc \uc124\uc815\n \u251c\u2500 cmd\/api\/main.go\n \u251c\u2500 Dockerfile\n \u251c\u2500 go.mod\n \u2514\u2500 go.sum<\/pre>\n\n\n\n
3.2 \ud14c\uc774\ube14 \uc0dd\uc131 \uc2a4\ud0a4\ub9c8 (server\/migrations\/000001_init_schema.up.sql<\/code>)<\/h3>\n\n\n\n
VS Code\uc5d0\uc11c server\/migrations\/<\/code> \ud3f4\ub354\ub97c \ub9cc\ub4e4\uace0, 000001_init_schema.up.sql<\/code> \ud30c\uc77c\uc744 \ub9cc\ub4e4\uc5b4 \uc544\ub798 DDL\uc744 \ubd99\uc5ec\ub123\uc2b5\ub2c8\ub2e4.<\/p>\n\n\n\n
\n
\ud83d\udca1 \ud0c8\ud1f4\ud55c \uc0ac\uc6a9\uc790\ub294 \ud589\uc744 \uc9c0\uc6b0\uc9c0 \uc54a\uc2b5\ub2c8\ub2e4.<\/strong> \uc77c\ubc18\uc801\uc778 \ud0c8\ud1f4\ub294 users.status<\/code>\ub97c 'deleted'<\/code>\ub85c \ubc14\uafb8\ub294 \ubc29\uc2dd(\uc18c\ud504\ud2b8 \uc0ad\uc81c)\uc73c\ub85c \ucc98\ub9ac\ud569\ub2c8\ub2e4. \uc544\ub798\uc758 ON DELETE SET NULL<\/code> \uac19\uc740 \uaddc\uce59\uc740 \uad00\ub9ac\uc790\uac00 \uc0ac\uc6a9\uc790 \ud589\uc744 \ubb3c\ub9ac\uc801\uc73c\ub85c \uc0ad\uc81c<\/strong>\ud558\ub294 \uacbd\uc6b0\uc5d0\ub3c4 \uc678\ub798\ud0a4 \uc624\ub958 \uc5c6\uc774 \ub3d9\uc791\ud558\uac8c \ud558\ub824\ub294 \uc548\uc804\uc7a5\uce58\uc785\ub2c8\ub2e4.<\/p>\n<\/blockquote>\n\n\n\n
-- 1. \uc0ac\uc6a9\uc790 (Users)\nCREATE TABLE users (\n    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),\n    display_name    VARCHAR(50)  NOT NULL,\n    status_message  VARCHAR(100),\n    avatar_file_id  UUID,                                   -- files\uc640 \uc11c\ub85c \ucc38\uc870\ud558\ubbc0\ub85c \uc678\ub798\ud0a4\ub294 \uac78\uc9c0 \uc54a\uc74c\n    phone_hash      VARCHAR(64)  UNIQUE,                    -- E.164 \ubc88\ud638\uc758 HMAC-SHA256 \ud574\uc2dc(16\uc9c4\uc218 64\uc790). UNIQUE\uac00 \uc778\ub371\uc2a4\ub97c \uc790\ub3d9 \uc0dd\uc131\n    role            VARCHAR(10)  NOT NULL DEFAULT 'user',   -- 'admin' | 'user'\n    status          VARCHAR(10)  NOT NULL DEFAULT 'active', -- 'active' | 'suspended' | 'deleted'\n    invited_by      UUID REFERENCES users(id) ON DELETE SET NULL,\n    created_at      TIMESTAMPTZ  NOT NULL DEFAULT now(),\n    updated_at      TIMESTAMPTZ  NOT NULL DEFAULT now()\n);\n\n-- 2. \ub85c\uadf8\uc778 \uacc4\uc815 \uc218\ub2e8 (Auth Identities)\nCREATE TABLE auth_identities (\n    id             UUID PRIMARY KEY DEFAULT gen_random_uuid(),\n    user_id        UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,\n    provider       VARCHAR(10) NOT NULL,          -- 'password' | 'google' | 'apple'\n    provider_uid   VARCHAR(255) NOT NULL,         -- \ub85c\uadf8\uc778 \uc544\uc774\ub514 \ub610\ub294 \uc18c\uc15c sub\n    password_hash  TEXT,                          -- Argon2id \ud574\uc2dc (\ube44\ubc00\ubc88\ud638 \ub85c\uadf8\uc778 \uc2dc)\n    created_at     TIMESTAMPTZ NOT NULL DEFAULT now(),\n    UNIQUE (provider, provider_uid)\n);\n\n-- 3. \uae30\uae30 \uc138\uc158 \ubc0f \ud478\uc2dc \ud1a0\ud070 (Devices)\nCREATE TABLE devices (\n    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),\n    user_id             UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,\n    platform            VARCHAR(10) NOT NULL,     -- 'android' | 'ios' | 'macos' | 'windows'\n    name                VARCHAR(100),             -- \uc608: \"\uc544\ube60 Galaxy S24 Ultra\"\n    refresh_token_hash  TEXT NOT NULL,            -- Refresh Token \uc6d0\ubcf8\uc774 \uc544\ub2cc SHA-256 \ud574\uc2dc\n    refresh_expires_at  TIMESTAMPTZ NOT NULL,\n    fcm_token           TEXT,\n    voip_token          TEXT,                     -- iOS PushKit \ud1b5\ud654\uc6a9\n    app_version         VARCHAR(20),\n    last_active_at      TIMESTAMPTZ,\n    created_at          TIMESTAMPTZ NOT NULL DEFAULT now()\n);\nCREATE INDEX idx_devices_user_id ON devices(user_id);\n-- \ud1a0\ud070 \uac31\uc2e0(refresh) \ub54c\ub9c8\ub2e4 \uc774 \ud574\uc2dc\ub85c \uae30\uae30\ub97c \ucc3e\uc73c\ubbc0\ub85c \uace0\uc720 \uc778\ub371\uc2a4\ub97c \ub461\ub2c8\ub2e4. (6\ud3b8\uc758 GetDeviceByRefreshToken)\nCREATE UNIQUE INDEX idx_devices_refresh_token_hash ON devices(refresh_token_hash);\n\n-- 4. \ucd08\ub300 \ucf54\ub4dc (Invite Codes)\nCREATE TABLE invite_codes (\n    code        VARCHAR(32) PRIMARY KEY,          -- \ucd94\uce21\ud558\uae30 \uc5b4\ub824\uc6b4 \ubb34\uc791\uc704 \ubb38\uc790\uc5f4\uc744 \uad8c\uc7a5\n    owner_id    UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,\n    max_uses    INT NOT NULL DEFAULT 10 CHECK (max_uses > 0),\n    used_count  INT NOT NULL DEFAULT 0,\n    expires_at  TIMESTAMPTZ NOT NULL,\n    created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),\n    CHECK (used_count >= 0 AND used_count <= max_uses)\n);\n\n-- 5. \uce5c\uad6c \uad00\uacc4 (Friendships)\nCREATE TABLE friendships (\n    user_id     UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,\n    friend_id   UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,\n    tag         VARCHAR(10) NOT NULL DEFAULT 'friend',  -- 'family' | 'friend'\n    nickname    VARCHAR(50),\n    blocked     BOOLEAN NOT NULL DEFAULT false,\n    created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),\n    PRIMARY KEY (user_id, friend_id)\n);\n\n-- 6. \ucc44\ud305\ubc29 (Rooms)\nCREATE TABLE rooms (\n    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),\n    type            VARCHAR(10) NOT NULL,             -- 'direct' | 'group'\n    direct_key      VARCHAR(80) UNIQUE,               -- 1:1 \ubc29 \uc911\ubcf5 \ubc29\uc9c0 \ud0a4 (\"\uc791\uc740UUID:\ud070UUID\")\n    name            VARCHAR(50),\n    avatar_file_id  UUID,\n    last_seq        BIGINT NOT NULL DEFAULT 0,        -- \ubc29 \uba54\uc2dc\uc9c0 \ucd5c\uc2e0 \uc21c\ubc88\n    created_by      UUID REFERENCES users(id) ON DELETE SET NULL,\n    created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),\n    updated_at      TIMESTAMPTZ NOT NULL DEFAULT now()\n);\n\n-- 7. \ucc44\ud305\ubc29 \ucc38\uc5ec \uba64\ubc84 (Room Members)\nCREATE TABLE room_members (\n    room_id        UUID NOT NULL REFERENCES rooms(id) ON DELETE CASCADE,\n    user_id        UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,\n    role           VARCHAR(10) NOT NULL DEFAULT 'member',  -- 'owner' | 'member'\n    last_read_seq  BIGINT NOT NULL DEFAULT 0,              -- \ub9c8\uc9c0\ub9c9\uc73c\ub85c \uc77d\uc740 \uba54\uc2dc\uc9c0 \uc21c\ubc88\n    joined_seq     BIGINT NOT NULL DEFAULT 0,              -- \uc785\uc7a5 \uc2dc\uc810 \uc21c\ubc88 (\uc774\uc804 \ub300\ud654 \uc228\uae40\uc6a9)\n    muted          BOOLEAN NOT NULL DEFAULT false,\n    pinned         BOOLEAN NOT NULL DEFAULT false,\n    joined_at      TIMESTAMPTZ NOT NULL DEFAULT now(),\n    PRIMARY KEY (room_id, user_id)\n);\nCREATE INDEX idx_room_members_user ON room_members(user_id);\n\n-- 8. \uba54\uc2dc\uc9c0 (Messages)\nCREATE TABLE messages (\n    id           UUID PRIMARY KEY,                  -- \ud074\ub77c\uc774\uc5b8\ud2b8\uac00 \uc0dd\uc131\ud55c \uc911\ubcf5 \ubc29\uc9c0\uc6a9 UUID v7\n    room_id      UUID NOT NULL REFERENCES rooms(id) ON DELETE CASCADE,\n    seq          BIGINT NOT NULL,                   -- \ubc29 \ub0b4 1\uc529 \uc99d\uac00\ud558\ub294 \ub2e8\uc870 \uc21c\ubc88\n    sender_id    UUID REFERENCES users(id) ON DELETE SET NULL,  -- \uc2dc\uc2a4\ud15c \uba54\uc2dc\uc9c0\uc774\uac70\ub098 \uc0ac\uc6a9\uc790 \ud589\uc774 \uc0ad\uc81c\ub418\uba74 NULL\n    type         VARCHAR(10) NOT NULL,              -- 'text' | 'image' | 'file' | 'call_log' | 'system'\n    text         TEXT CHECK (char_length(text) <= 10000),\n    attachments  JSONB,                             -- [{fileId, name, size, mime, width, height, thumbFileId}]\n    call_log     JSONB,                             -- {callId, durationSec, result}\n    deleted_at   TIMESTAMPTZ,\n    created_at   TIMESTAMPTZ NOT NULL DEFAULT now(),\n    UNIQUE (room_id, seq)                           -- \uc99d\ubd84 \ub3d9\uae30\ud654 \ucffc\ub9ac\uc6a9 \uc778\ub371\uc2a4\ub97c \uacb8\ud568\n);\nCREATE INDEX idx_messages_room_created ON messages(room_id, created_at DESC);\n\n-- 9. \ud30c\uc77c \uc800\uc7a5\uc18c \uba54\ud0c0\ub370\uc774\ud130 (Files)\nCREATE TABLE files (\n    id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),\n    uploader_id   UUID NOT NULL REFERENCES users(id),   -- \ud30c\uc77c\uc774 \ub0a8\uc544 \uc788\ub294 \uc0ac\uc6a9\uc790\ub294 \ubb3c\ub9ac \uc0ad\uc81c\ud560 \uc218 \uc5c6\uc74c\n    message_id    UUID REFERENCES messages(id) ON DELETE SET NULL,\n    purpose       VARCHAR(10) NOT NULL,             -- 'message' | 'avatar'\n    storage_path  TEXT NOT NULL,                    -- NAS \uc800\uc7a5 \uc0c1\ub300\uacbd\ub85c (\uc608: 2026\/10\/uuid.jpg)\n    name          VARCHAR(255) NOT NULL,\n    mime_type     VARCHAR(100),\n    size          BIGINT NOT NULL,\n    sha256        CHAR(64),\n    status        VARCHAR(10) NOT NULL DEFAULT 'uploading',  -- 'uploading' | 'ready'\n    created_at    TIMESTAMPTZ NOT NULL DEFAULT now()\n);\n\n-- 10. \ubcf4\uc774\uc2a4\ud1a1 \ud1b5\ud654 \uae30\ub85d (Calls & Call Participants)\nCREATE TABLE calls (\n    id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),\n    room_id       UUID NOT NULL REFERENCES rooms(id) ON DELETE CASCADE,\n    caller_id     UUID REFERENCES users(id) ON DELETE SET NULL,\n    status        VARCHAR(10) NOT NULL,             -- 'ringing'|'accepted'|'declined'|'missed'|'ended'\n    created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),\n    accepted_at   TIMESTAMPTZ,\n    ended_at      TIMESTAMPTZ\n);\n\nCREATE TABLE call_participants (\n    call_id    UUID NOT NULL REFERENCES calls(id) ON DELETE CASCADE,\n    user_id    UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,\n    joined_at  TIMESTAMPTZ,\n    left_at    TIMESTAMPTZ,\n    PRIMARY KEY (call_id, user_id)\n);<\/pre>\n\n\n\n
\n
\ud83d\udccc gen_random_uuid()<\/code>\ub294 PostgreSQL 13\ubd80\ud130 \uae30\ubcf8\uc73c\ub85c \ub4e4\uc5b4 \uc788\uc5b4\uc11c, \uc608\uc804 \uae00\uc5d0 \uc788\ub358 CREATE EXTENSION pgcrypto<\/code>\ub294 \ud544\uc694\ud558\uc9c0 \uc54a\uc2b5\ub2c8\ub2e4. \ub610 phone_hash<\/code>\uc5d0\ub294 UNIQUE<\/code>\uac00 \uc774\ubbf8 \uc778\ub371\uc2a4\ub97c \ub9cc\ub4e4\uc5b4 \uc8fc\ubbc0\ub85c \ubcc4\ub3c4 \uc778\ub371\uc2a4\ub97c \ub530\ub85c \ub9cc\ub4e4\uc9c0 \uc54a\uc558\uc2b5\ub2c8\ub2e4.<\/p>\n<\/blockquote>\n\n\n\n
3.3 \ub418\ub3cc\ub9ac\uae30\uc6a9 \uc2a4\ud0a4\ub9c8 (server\/migrations\/000001_init_schema.down.sql<\/code>)<\/h3>\n\n\n\n
\ub9c8\uc774\uadf8\ub808\uc774\uc158 \ub3c4\uad6c\ub294 \uc2e4\uc218\ub97c \ub418\ub3cc\ub9b4 \ub54c\ub97c \ub300\ube44\ud574 \ud14c\uc774\ube14\uc744 \uc9c0\uc6b0\ub294 .down.sql<\/code>\ub3c4 \ud544\uc694\ub85c \ud569\ub2c8\ub2e4. \ud14c\uc774\ube14\uc744 \ub9cc\ub4e0 \uc21c\uc11c\uc758 \ubc18\ub300\ub85c \uc9c0\uc6b0\ub294 \ucf54\ub4dc\uc785\ub2c8\ub2e4.<\/p>\n\n\n\n
DROP TABLE IF EXISTS call_participants CASCADE;\nDROP TABLE IF EXISTS calls CASCADE;\nDROP TABLE IF EXISTS files CASCADE;\nDROP TABLE IF EXISTS messages CASCADE;\nDROP TABLE IF EXISTS room_members CASCADE;\nDROP TABLE IF EXISTS rooms CASCADE;\nDROP TABLE IF EXISTS friendships CASCADE;\nDROP TABLE IF EXISTS invite_codes CASCADE;\nDROP TABLE IF EXISTS devices CASCADE;\nDROP TABLE IF EXISTS auth_identities CASCADE;\nDROP TABLE IF EXISTS users CASCADE;<\/pre>\n\n\n\n
\n
\u26a0\ufe0f down<\/code>\uc740 \ubaa8\ub4e0 \ub370\uc774\ud130\ub97c \uc9c0\uc6c1\ub2c8\ub2e4.<\/strong> \uac1c\ubc1c \uc911\uc5d0 \uc2a4\ud0a4\ub9c8\ub97c \ucc98\uc74c\ubd80\ud130 \ub2e4\uc2dc \ub9cc\ub4e4 \ub54c\ub9cc \uc0ac\uc6a9\ud558\uace0, \uc2e4\uc81c \uac00\uc871 \ub370\uc774\ud130\uac00 \ub4e4\uc5b4 \uc788\ub294 DB\uc5d0\uc11c\ub294 \uc808\ub300 \uc2e4\ud589\ud558\uc9c0 \ub9c8\uc138\uc694.<\/p>\n<\/blockquote>\n\n\n\n
3.4 PostgreSQL DB\uc5d0 \uc2e4\uc81c \ud14c\uc774\ube14 \uc0dd\uc131\ud558\uae30 (\ub9c8\uc774\uadf8\ub808\uc774\uc158 \uc2e4\ud589)<\/h3>\n\n\n\n
\uc791\uc131\ud55c \uc2a4\ud0a4\ub9c8 \ud30c\uc77c\uc744 3\ud3b8\uc5d0\uc11c \ub744\uc6cc \ub454 NAS\uc758 PostgreSQL\uc5d0 \uc801\uc6a9\ud569\ub2c8\ub2e4. 3\ud3b8\uc5d0\uc11c DB \ud3ec\ud2b8\ub97c NAS \uc790\uae30 \uc790\uc2e0(127.0.0.1<\/code>)\uc5d0\ub9cc \uc5f4\uc5b4 \ub450\uc5c8\uc73c\ubbc0\ub85c, \uba3c\uc800 Mac \ud130\ubbf8\ub110 \ud558\ub098\uc5d0\uc11c SSH \ud130\ub110\uc744 \uc5f4\uc5b4 \ub461\ub2c8\ub2e4.<\/strong> (3\ud3b8 4.4\uc808)<\/p>\n\n\n\n
ssh -N -L 5432:127.0.0.1:5432 <NAS\uacc4\uc815>@<NAS\ub0b4\ubd80IP><\/pre>\n\n\n\n
\uadf8\ub2e4\uc74c \ub2e4\ub978 \ud130\ubbf8\ub110 \ucc3d<\/strong>\uc5d0\uc11c server\/<\/code> \ud3f4\ub354\ub85c \uc774\ub3d9\ud574 \uc811\uc18d \uc8fc\uc18c\ub97c \ud658\uacbd\ubcc0\uc218\ub85c \uc9c0\uc815\ud558\uace0 \ub9c8\uc774\uadf8\ub808\uc774\uc158\uc744 \uc2e4\ud589\ud569\ub2c8\ub2e4. (\ube44\ubc00\ubc88\ud638\ub294 3\ud3b8\uc5d0\uc11c \ub9cc\ub4e0 \uac12\uc785\ub2c8\ub2e4. \uc601\ubb38\uacfc \uc22b\uc790\ub85c\ub9cc \ub9cc\ub4e4\uc5c8\ub2e4\uba74 \uc8fc\uc18c\uc5d0 \uadf8\ub300\ub85c \ub123\uc5b4\ub3c4 \ub429\ub2c8\ub2e4.)<\/p>\n\n\n\n
cd server\n\n# \ube44\ubc00\ubc88\ud638\ub97c \ud654\uba74\uacfc \uba85\ub839\uc5b4 \uae30\ub85d\uc5d0 \ub0a8\uae30\uc9c0 \uc54a\uace0 \uc785\ub825\ubc1b\uc544 \ud658\uacbd\ubcc0\uc218\ub85c \uc9c0\uc815 (macOS \uae30\ubcf8 \uc178 zsh \uae30\uc900)\nread -s \"DBPW?DB \ube44\ubc00\ubc88\ud638: \"; echo\nexport DATABASE_URL=\"postgres:\/\/dingdong:${DBPW}@localhost:5432\/dingdong?sslmode=disable\"\n\n# golang-migrate\ub85c DB\uc5d0 \ud14c\uc774\ube14 \uc77c\uad04 \uc0dd\uc131 (\uc124\uce58\uac00 \uc548 \ub418\uc5b4 \uc788\ub2e4\uba74 brew install golang-migrate)\nmigrate -path .\/migrations -database \"$DATABASE_URL\" up<\/pre>\n\n\n\n
\ud14c\uc774\ube14\uc774 \ub9cc\ub4e4\uc5b4\uc84c\ub294\uc9c0 NAS \ud130\ubbf8\ub110(SSH)\uc5d0\uc11c \ud655\uc778\ud574 \ubd05\ub2c8\ub2e4.<\/p>\n\n\n\n
docker exec -it dingdong-postgres psql -U dingdong -d dingdong -c \"\\dt\"<\/pre>\n\n\n\n
users<\/code>, rooms<\/code>, messages<\/code> \ub4f1 10\uc5ec \uac1c\uc758 \ud14c\uc774\ube14\uacfc schema_migrations<\/code>\uac00 \ubcf4\uc774\uba74 \uc131\uacf5\uc785\ub2c8\ub2e4.<\/p>\n\n\n\n
\n
\u26a0\ufe0f migrate<\/code>\uc640 psql<\/code>\ub85c \uc9c1\uc811 SQL\uc744 \uc801\uc6a9\ud558\ub294 \ubc29\uc2dd\uc744 \uc11e\uc5b4 \uc4f0\uc9c0 \ub9c8\uc138\uc694.<\/strong> migrate<\/code>\ub294 \uc5b4\ub514\uae4c\uc9c0 \uc801\uc6a9\ud588\ub294\uc9c0\ub97c schema_migrations<\/code> \ud14c\uc774\ube14\uc5d0 \uae30\ub85d\ud569\ub2c8\ub2e4. psql<\/code>\ub85c \uc9c1\uc811 .up.sql<\/code>\uc744 \uc2e4\ud589\ud558\uba74 \uc774 \uae30\ub85d\uc774 \ub0a8\uc9c0 \uc54a\uc544, \ub098\uc911\uc5d0 migrate up<\/code>\uc774 “\uc774\ubbf8 \uc874\uc7ac\ud558\ub294 \ud14c\uc774\ube14” \uc5d0\ub7ec\ub97c \ub0c5\ub2c8\ub2e4. \uc774\ubbf8 psql<\/code>\ub85c \uc801\uc6a9\ud574 \ubc84\ub838\ub2e4\uba74 \uc544\ub798 \uba85\ub839\uc73c\ub85c “1\ubc88\uae4c\uc9c0 \uc801\uc6a9\ub428”\uc774\ub77c\uace0 \uae30\ub85d\ud574 \uc8fc\uc138\uc694.<\/p>\n\n\n\n
migrate -path .\/migrations -database \"$DATABASE_URL\" force 1<\/pre>\n<\/blockquote>\n\n\n\n
\n\n\n\n
4. sqlc \uc124\uce58 \ubc0f \uc124\uc815 (sqlc.yaml<\/code>)<\/h2>\n\n\n\n
4.1 sqlc CLI \uc124\uce58 (Mac \uae30\uc900)<\/h3>\n\n\n\n
4\ud3b8\uc5d0\uc11c \uc774\ubbf8 \uc124\uce58\ud588\ub2e4\uba74 \uac74\ub108\ub6f0\uc5b4\ub3c4 \ub429\ub2c8\ub2e4. \ub9e5 \ud130\ubbf8\ub110\uc5d0\uc11c Homebrew\ub85c \uc124\uce58\ud569\ub2c8\ub2e4.<\/p>\n\n\n\n
brew install sqlc<\/pre>\n\n\n\n
4.2 sqlc.yaml \uc124\uc815 \ud30c\uc77c \uc791\uc131<\/h3>\n\n\n\n
server\/sqlc.yaml<\/code> \uacbd\ub85c\uc5d0 \uc124\uc815 \ud30c\uc77c\uc744 \ub9cc\ub4ed\ub2c8\ub2e4.<\/p>\n\n\n\n
\n
\ud83d\udca1 \uc54c\uc544 \ub450\uba74 \uc88b\uc740 \uc124\uc815 3\uac00\uc9c0 (sqlc v2 + pgx\/v5)<\/strong><\/p>\n\n\n\n
    \n
  • engine: \"postgresql\"<\/code>:<\/strong> sqlc v2 \uc124\uc815\uc5d0\uc11c\ub294 \uc0ac\uc6a9\ud560 DB \uc5d4\uc9c4\uc744 \ubc18\ub4dc\uc2dc \uc801\uc5b4\uc57c \ud569\ub2c8\ub2e4. \ube60\uc9c0\uba74 engine is required<\/code> \uac19\uc740 \uc5d0\ub7ec\uac00 \ub0a9\ub2c8\ub2e4.<\/li>\n\n\n\n
  • emit_pointers_for_null_types: true<\/code>:<\/strong> sqlc\uc640 pgx\/v5\ub97c \ud568\uaed8 \uc4f0\uba74 NULL\uc774 \uac00\ub2a5\ud55c \uceec\ub7fc\uc774 pgtype.Text<\/code> \uac19\uc740 \ub2e4\ub8e8\uae30 \ubc88\uac70\ub85c\uc6b4 \ud0c0\uc785\uc73c\ub85c \uc0dd\uc131\ub429\ub2c8\ub2e4. \uc774 \uc635\uc158\uc744 \ucf1c\uba74 Go\uc758 \ud3ec\uc778\ud130(*string<\/code>, *int<\/code> \ub4f1)\ub85c \uc0dd\uc131\ub418\uc5b4 \ucf54\ub4dc\uac00 \ud6e8\uc52c \uac04\ub2e8\ud574\uc9d1\ub2c8\ub2e4.<\/li>\n\n\n\n
  • NULL \uac00\ub2a5 UUID\uc758 pointer: true<\/code>:<\/strong> \ud0c0\uc785 \ubb38\uc790\uc5f4 \uc55e\uc5d0 *<\/code>\ub97c \ubd99\uc5ec \uc4f0\uba74 sqlc\uac00 import \ucf54\ub4dc\ub97c \uc798\ubabb \ub9cc\ub4ed\ub2c8\ub2e4. \ud3ec\uc778\ud130 \ud0c0\uc785\uc740 \ubc18\ub4dc\uc2dc \uc544\ub798\ucc98\ub7fc import<\/code>, type<\/code>, pointer: true<\/code> \uac1d\uccb4 \ud615\ud0dc\ub85c \uc120\uc5b8\ud574\uc57c *uuid.UUID<\/code>\uac00 \uc62c\ubc14\ub974\uac8c \uc0dd\uc131\ub429\ub2c8\ub2e4.<\/li>\n<\/ul>\n<\/blockquote>\n\n\n\n
    version: \"2\"\nsql:\n  - engine: \"postgresql\"\n    schema: \"migrations\"\n    queries: \"queries\"\n    gen:\n      go:\n        package: \"db\"\n        out: \"internal\/database\/db\"\n        sql_package: \"pgx\/v5\"\n        emit_json_tags: true\n        emit_prepared_queries: false\n        emit_interface: true\n        emit_empty_slices: true\n        emit_pointers_for_null_types: true\n        overrides:\n          - db_type: \"uuid\"\n            go_type: \"github.com\/google\/uuid.UUID\"\n          - db_type: \"uuid\"\n            nullable: true\n            go_type:\n              import: \"github.com\/google\/uuid\"\n              type: \"UUID\"\n              pointer: true<\/pre>\n\n\n\n
    \n
    \ud83d\udca1 schema: \"migrations\"<\/code>\ub294 golang-migrate \ud615\uc2dd\uc758 \ud3f4\ub354\ub97c \uc77d\uc5b4 \ud14c\uc774\ube14 \uad6c\uc870\ub97c \ud30c\uc545\ud569\ub2c8\ub2e4. sqlc\ub294 \uc774 \ud615\uc2dd\uc5d0\uc11c \ub418\ub3cc\ub9ac\uae30\uc6a9 .down.sql<\/code> \ud30c\uc77c\uc744 \uac74\ub108\ub6f0\uace0 .up.sql<\/code>\ub9cc \uc77d\uc2b5\ub2c8\ub2e4. \ub9cc\uc57d sqlc generate<\/code>\uc5d0\uc11c \uc2a4\ud0a4\ub9c8 \uad00\ub828 \uc5d0\ub7ec\uac00 \ub09c\ub2e4\uba74, schema<\/code>\ub97c \"migrations\/000001_init_schema.up.sql\"<\/code>\ucc98\ub7fc \ud30c\uc77c \ud558\ub098\ub85c \uc9c0\uc815\ud574 \ubcf4\uc138\uc694.<\/p>\n<\/blockquote>\n\n\n\n
    \n\n\n\n
    5. SQL \ucffc\ub9ac \uc791\uc131 \ubc0f \uc790\ub3d9 \ucf54\ub4dc \uc0dd\uc131 \uc2e4\uc2b5<\/h2>\n\n\n\n
    \uc774\uc81c server\/queries\/<\/code> \ud3f4\ub354\uc5d0 \ud544\uc694\ud55c \ucffc\ub9ac\ub97c \uc791\uc131\ud569\ub2c8\ub2e4.<\/p>\n\n\n\n
    5.1 \ud68c\uc6d0 \uad00\ub828 \ucffc\ub9ac (server\/queries\/users.sql<\/code>)<\/h3>\n\n\n\n
    \n
    \ud83d\udca1 sqlc.narg<\/code>\ub85c \uc120\ud0dd\uc801 \uc5c5\ub370\uc774\ud2b8 \ub9cc\ub4e4\uae30<\/strong>
    display_name<\/code> \uceec\ub7fc\uc774 NOT NULL<\/code>\uc77c \ub54c COALESCE($2, display_name)<\/code>\ucc98\ub7fc \uc4f0\uba74, sqlc\ub294 $2<\/code>\ub97c \uc77c\ubc18 string<\/code>\uc73c\ub85c \ub9cc\ub4ed\ub2c8\ub2e4. \uadf8\ub7f0\ub370 Go\uc758 \ube48 \ubb38\uc790\uc5f4(\"\"<\/code>)\uc740 SQL\uc758 NULL<\/code>\uc774 \uc544\ub2c8\uc5b4\uc11c, “\uc774\ub984\uc744 \ubc14\uafb8\uc9c0 \uc54a\uaca0\ub2e4”\ub294 \ub73b\uc73c\ub85c \ube48 \uac12\uc744 \ub118\uae30\uba74 \uc774\ub984\uc774 \ube48 \ubb38\uc790\uc5f4\ub85c \ub36e\uc5b4\uc368\uc9c0\ub294 \ubc84\uadf8\uac00 \uc0dd\uae41\ub2c8\ub2e4.
    sqlc.narg('display_name')<\/code>\uc744 \uc4f0\uba74 Go\uc5d0\uc11c \ud3ec\uc778\ud130(*string<\/code>)\ub85c \uc0dd\uc131\ub418\uc5b4, nil<\/code>\uc744 \ub118\uae30\uba74 \uae30\uc874 \uac12\uc774 \uc720\uc9c0\ub429\ub2c8\ub2e4. (\ub2e4\ub9cc \uc774 \ubc29\uc2dd\uc73c\ub85c\ub294 \uac12\uc744 NULL<\/code>\ub85c \ube44\uc6b0\ub294 \uac83\uc740 \ud45c\ud604\ud560 \uc218 \uc5c6\uc2b5\ub2c8\ub2e4. \uc0c1\ud0dc \uba54\uc2dc\uc9c0\ub97c \uc9c0\uc6b0\ub294 \uae30\ub2a5\uc774 \ud544\uc694\ud574\uc9c0\uba74 \ucffc\ub9ac\ub97c \ub530\ub85c \ub9cc\ub4dc\uc138\uc694.)<\/p>\n<\/blockquote>\n\n\n\n
    -- name: CreateUser :one\nINSERT INTO users (display_name, status_message, phone_hash, role)\nVALUES ($1, $2, $3, $4)\nRETURNING *;\n\n-- name: GetUserByID :one\n-- \ub85c\uadf8\uc778\/\ud1a0\ud070 \uac31\uc2e0(6\ud3b8)\uc5d0\uc11c \uc4f0\uc774\ubbc0\ub85c \uc815\uc0c1(active) \uacc4\uc815\ub9cc \uc870\ud68c\ud569\ub2c8\ub2e4.\n-- \uc815\uc9c0(suspended)\u00b7\uc0ad\uc81c(deleted)\ub41c \uacc4\uc815\uc740 \uc870\ud68c\ub418\uc9c0 \uc54a\uc544 \ub85c\uadf8\uc778\ud560 \uc218 \uc5c6\uc2b5\ub2c8\ub2e4.\nSELECT * FROM users\nWHERE id = $1 AND status = 'active';\n\n-- name: GetUserByPhoneHash :one\nSELECT * FROM users\nWHERE phone_hash = $1 AND status = 'active';\n\n-- name: UpdateUserProfile :one\nUPDATE users\nSET display_name = COALESCE(sqlc.narg('display_name'), display_name),\n    status_message = COALESCE(sqlc.narg('status_message'), status_message),\n    avatar_file_id = COALESCE(sqlc.narg('avatar_file_id'), avatar_file_id),\n    updated_at = now()\nWHERE id = sqlc.arg('id')\nRETURNING *;<\/pre>\n\n\n\n
    5.2 \ucc44\ud305 \uba54\uc2dc\uc9c0 \ucffc\ub9ac (server\/queries\/messages.sql<\/code>)<\/h3>\n\n\n\n
    \n
    \ud83d\udca1 CTE(WITH<\/code> \uad6c\ubb38)\ub85c \uc21c\ubc88 \uc99d\uac00\uc640 \uba54\uc2dc\uc9c0 \uc800\uc7a5\uc744 \ud55c \ubc88\uc5d0<\/strong>
    \uba54\uc2dc\uc9c0\ub97c \ubcf4\ub0bc \ub54c “\ubc29\uc758 \ucd5c\uc2e0 \uc21c\ubc88 1 \uc99d\uac00”\uc640 “\uba54\uc2dc\uc9c0 \uc0bd\uc785”\uc744 \ubcc4\ub3c4 \ucffc\ub9ac\ub85c \ub098\ub204\uba74 \ub124\ud2b8\uc6cc\ud06c \uc655\ubcf5\uc774 2\ubc88 \uc0dd\uae30\uace0, \uadf8 \uc0ac\uc774\uc5d0 \uc11c\ubc84\uac00 \uaebc\uc9c0\uba74 \uc21c\ubc88\ub9cc \uc62c\ub77c\uac00\uace0 \uba54\uc2dc\uc9c0\ub294 \uc0ac\ub77c\uc9c0\ub294 \ubd88\uc77c\uce58\uac00 \uc0dd\uae38 \uc218 \uc788\uc2b5\ub2c8\ub2e4.
    PostgreSQL\uc758 CTE(WITH ...<\/code>)\ub97c \uc4f0\uba74 \ud558\ub098\uc758 SQL \ubb38\uc7a5\uc73c\ub85c \uc21c\ubc88 \uc99d\uac00\uc640 \uba54\uc2dc\uc9c0 \uc800\uc7a5<\/strong>\uc744 \ucc98\ub9ac\ud560 \uc218 \uc788\uace0, SQL \ubb38\uc7a5 \ud558\ub098\ub294 PostgreSQL \uc548\uc5d0\uc11c \ud1b5\uc9f8\ub85c \uc131\uacf5\ud558\uac70\ub098 \ud1b5\uc9f8\ub85c \ucde8\uc18c\ub429\ub2c8\ub2e4. \uac19\uc740 \ubc29\uc5d0 \ub3d9\uc2dc\uc5d0 \uba54\uc2dc\uc9c0\uac00 \ub4e4\uc5b4\uc640\ub3c4 UPDATE<\/code>\uac00 \ubc29 \ud589\uc744 \uc7a0\uadf8\uae30 \ub54c\ubb38\uc5d0 \uc21c\ubc88\uc774 \uacb9\uce58\uc9c0 \uc54a\uc2b5\ub2c8\ub2e4.<\/p>\n<\/blockquote>\n\n\n\n
    -- name: SendMessageWithSeq :one\nWITH next_seq AS (\n    UPDATE rooms\n    SET last_seq = last_seq + 1,\n        updated_at = now()\n    WHERE id = sqlc.arg('room_id')::uuid\n    RETURNING last_seq\n)\nINSERT INTO messages (\n    id,\n    room_id,\n    seq,\n    sender_id,\n    type,\n    text,\n    attachments,\n    call_log\n)\nSELECT\n    sqlc.arg('id')::uuid,\n    sqlc.arg('room_id')::uuid,\n    next_seq.last_seq,\n    sqlc.narg('sender_id')::uuid,\n    sqlc.arg('type')::varchar,\n    sqlc.narg('text')::text,\n    sqlc.narg('attachments')::jsonb,\n    sqlc.narg('call_log')::jsonb\nFROM next_seq\nRETURNING *;\n\n-- name: GetMessagesAfterSeq :many\nSELECT * FROM messages\nWHERE room_id = sqlc.arg('room_id')::uuid\n  AND seq > sqlc.arg('after_seq')::bigint\nORDER BY seq ASC\nLIMIT sqlc.arg('max_count')::int;\n\n-- name: IsRoomMember :one\nSELECT EXISTS (\n    SELECT 1 FROM room_members\n    WHERE room_id = sqlc.arg('room_id')::uuid\n      AND user_id = sqlc.arg('user_id')::uuid\n) AS is_member;<\/pre>\n\n\n\n
    \n
    \ud83d\udca1 ::uuid<\/code>, ::bigint<\/code> \uac19\uc740 \ud0c0\uc785 \ud45c\uae30\ub97c \ubd99\uc778 \uc774\uc720:<\/strong> INSERT ... SELECT<\/code> \uc548\uc758 \ud30c\ub77c\ubbf8\ud130\ub294 sqlc\uac00 \ud0c0\uc785\uc744 \ucd94\uce21\ud558\uae30 \uc5b4\ub824\uc6cc interface{}<\/code>\ub85c \ub9cc\ub4e4 \uc218 \uc788\uc2b5\ub2c8\ub2e4. \ud0c0\uc785\uc744 \uc9c1\uc811 \uc801\uc5b4 \uc8fc\uba74 SendMessageWithSeqParams{ID, RoomID, SenderID, Type, Text, ...}<\/code>\ucc98\ub7fc \uc774\ub984\uacfc \ud0c0\uc785\uc774 \ubd84\uba85\ud55c \uad6c\uc870\uccb4\uac00 \uc0dd\uc131\ub429\ub2c8\ub2e4.<\/p>\n<\/blockquote>\n\n\n\n
    \n
    \u26a0\ufe0f \uc774 \ucffc\ub9ac\ub4e4\uc740 “\uc774 \uc0ac\ub78c\uc774 \uc774 \ubc29\uc758 \uba64\ubc84\uc778\uac00”\ub97c \ud655\uc778\ud558\uc9c0 \uc54a\uc2b5\ub2c8\ub2e4.<\/strong> \uc704\uc758 IsRoomMember<\/code>\ub97c \uc11c\ube44\uc2a4 \ucf54\ub4dc\uc5d0\uc11c \uba3c\uc800 \ud638\ucd9c\ud574\uc11c<\/strong> \uba64\ubc84\uac00 \uc544\ub2c8\uba74 \uc694\uccad\uc744 \uac70\ubd80\ud574\uc57c \ud569\ub2c8\ub2e4. \uc774 \ud655\uc778\uc744 \ube7c\uba39\uc73c\uba74 \ub85c\uadf8\uc778\ud55c \ub204\uad6c\ub098 \ub2e4\ub978 \ubc29\uc5d0 \uba54\uc2dc\uc9c0\ub97c \ubcf4\ub0b4\uac70\ub098 \ub300\ud654\ub97c \uc77d\uc744 \uc218 \uc788\uac8c \ub429\ub2c8\ub2e4. (\ucc44\ud305 \ud3b8\uc5d0\uc11c \uc2e4\uc81c\ub85c \uc801\uc6a9\ud569\ub2c8\ub2e4.)<\/p>\n\n\n\n
    \uadf8 \ubc16\uc5d0 \uc54c\uc544 \ub458 \uc810<\/p>\n\n\n\n