Architectural Sync: Mastering SQL-to-Zod Schema Generation

SQL to Zod: 繝繧ｿ繝吶繧ｹ繧ｹ繧ｭ繝ｼ繝医°繧牙梛迚ｹ蝙句ｮ牙縺ｸ

繝繧ｿ繝吶繧ｹ縺ｨ繧｢繝励Μ繧ｱ繝ｼ繧ｷ繝ｧ繝ｳ螻､縺ｮ髢薙蝙句ｸｮ 髮 縺ｯ縲√ヰ繧ｰ縺ｮ譛€螟ｧ縺ｮ蜴滓縺ｮ荳€縺､縺ｧ縺吶€、QL縺ｮDDL(繝繧ｿ螳夂ｾｩ險€隱)繧辿od繧ｹ繧ｭ繝ｼ繝医↓螟画鋤縺吶ｋ縺薙→縺ｧ縲√ョ繝ｼ繧ｿ繝吶繧ｹ縺ｮ縲梧ｭ｣隗｣縲阪ｒ繝ｩ繝ｳ繧ｿ繧､繝ヰ繝ｪ繝繧ｷ繝ｧ繝ｳ縺ｨ縺励※逶ｴ謗･豢ｻ逕ｨ縺ｧ縺阪∪縺吶€ゅ％繧後↓繧医ｊ縲√ョ繝ｼ繧ｿ繝吶繧ｹ縺九ｉ蜿門ｾ励＠縺ョ繝ｼ繧ｿ縺檎炊諠ｳ逧↑讒矩€縺ｧ縺ゅｋ縺薙→繧堤ｶｭ謖√＠縲√す繧ｹ繝Β蜈ｨ菴薙鬆ｧ育ｧｻ 驛 繧貞ｼｷ蛹悶＠縺ｾ縺吶€

Live Example: SQL DDL縺九ｉZod繧ｹ繧ｭ繝ｼ繝医∈縺ｮ螟画鋤

-- 蜈･蜉 SQL DDL
CREATE TABLE products (
    id UUID PRIMARY KEY,
    name TEXT NOT NULL,
    price DECIMAL(10, 2),
    is_available BOOLEAN DEFAULT TRUE
);

// 逕滓縺輔ｌ縺 Zod Schema
import { z } from 'zod';

const ProductSchema = z.object({
  id: z.string().uuid(),
  name: z.string().min(1),
  price: z.number().nullable(),
  is_available: z.boolean()
});

繧ｹ繝ャ繝励繧､繧ｹ繝ャ繝玲ｱ守畑繧ｬ繧､繝

1. DDL縺ｮ繧ｨ繧ｯ繧ｹ繝昴繝: 繝繧ｿ繝吶繧ｹ縺ｮ CREATE TABLE 譁ｒ諠 驛 縺ｨ縺励※蜿門ｾ励＠縺ｾ縺吶€
2. 蝙矩ぅ｣隕壹繝槭ャ繝斐Φ繧ｰ: INT繧 VARCHAR 繧辿od縺ｮ number 繧 string 縺ｫ螟画鋤縺励∪縺吶€
3. 蛻ｶ邏縺ｮ蜿榊ｰ: NOT NULL 繧 CHECK 蛻ｶ邏繧辿od縺ｮ繝舌Μ繝繧ｿ縺ｨ縺励※霑ｽ蜉縺励∪縺吶€
4. ORM縺ｨ縺ｮ騾｣謳ｺ: 蜿門ｾ励＠縺ョ繝ｼ繧ｿ繧 ProductSchema.parse(data) 縺ｧ讀懆ｨｼ縺励€∝梛迚ｹ蝙九ｒ遒ｺ菫昴＠縺ｾ縺吶€

Technical Deep Dive: 繝繧ｿ繝吶繧ｹ蝙九→JS蝙九驕輔＞

SQL縺ｨJavaScript縺ｮ髢薙↓縺ｯ蝙九驍ｵ螟壹′蟄 Pard 縺励∪縺吶€ゅ◆縺ｨ縺え、SQL縺ｮ BIGINT 縺ｯJavaScript縺ｮ number (64bit豬ｮ蜍募ｰ乗焚蠑)縺ｧ縺ｯ邊ｾ蠎ｦ縺瑚ｶｳ繧翫↑縺繧√€√€BigInt 繧 string 縺ｨ縺励※謇ｱ縺ｿｦ√′縺≠繧翫∪縺吶€ゅ％縺ｮ螟画鋤繝ｼ繝ｫ縺ｯ縲√◎縺＠縺溘お繝ず繧ｱ繝ｼ繧ｹ繧定ｪ崎ｭ倥＠縲∬ｪ､縺｣縺ョ繝ｼ繧ｿ荳ｸ繧√ｄ邊ｾ蠎ｦ荳ｸ繧√ｒ 髯 縺代ｋ縺溘ａ縺ｮ譛€驕ｩ縺ｪZod蝙九ｒ謗ｨ隲悶＠縺ｾ縺吶€

豈碑ｼ→莉｣譛ｿ譯

Prisma 繧 Drizzle 縺ｮ繧医≧縺ｪ迴ｾ莉｣逧ORM縺ｯ蝙句ｮ牙繧貞陞ｷ縺励※縺∪縺吶′縲√Ξ繧ｬ繧ｷ繝ｼ縺ｪ繝繧ｿ繝吶繧ｹ繧 pg 縺ｮ繧医≧縺ｪ逕溘繝峨Λ繧､繝舌ｒ菴ｿ逕ｨ縺吶ｋ蝣ｴ蜷医€、QL-to-Zod縺ｯ譛€繧りｻｽ驥上〒蠑ｷ蜉帷噪縺ｪ蝙句ｮ牙縺ｮ遒ｺ菫晉ｭ門ｱ輔〒縺吶€

譛ｬ逡ｪ迺ｰ蠅〒縺ｮ繝吶せ繝医繝ｩ繧ｯ繝ぅ繧ｹ

• Single Source of Truth: 蟶ｸ縺ｫSQL縺九ｉZod繧ｹ繧ｭ繝ｼ繝医ｒ逕滓縺励€∝曙螟ｧ縺ｪ蝙九譽悟､ｱ繧定ｪ 髯 縺代∪縺吶€
• Nullability縺ｮ隴ｦ謌: SQL縺ｮ DEFAULT 蛟､縺ｨ NULL 蜿ｯ閭ｽ諤ｧ繧鱈od縺ｮ .optional() 繧 .nullable() 縺ｫ蟄 Pard 縺ｫ蜿榊ｰ縺励∪縺吶€
• Enum縺ｮ豢ｻ逕ｨ: SQL ENUM 繧辿od z.enum() 縺ｫ螟画鋤縺励€√ヵ繧｣繝ｼ繝ｫ繝牙€､縺ｮ螟ｧ蟷遒ｺ菫｡繧貞ｾ励∪縺吶€

FAQ

Q: 隍｡代↑Join縺ｮ繝繧ｿ縺ｯ?
A: Join蠕後JSON蠖｢蠑上↓蜷医ｏ縺帙※Zod繧ｹ繧ｭ繝ｼ繝医ｒ邨∩蜷医ｏ縺帙ｋ 豎ｺ 縺梧怏蜉ｹ縺ｧ縺吶€

Q: PostgreSQL莉･螟悶ｂ蟇ｾ蠢懊＠縺ｦ繧?
A: MySQL繧SQLite縺ｪ縺ｩ縲√せ繧ｿ繝ｳ繝€繝ｼ繝峨↑SQL螟画鋤縺ｫ蟇ｾ蠢懊＠縺ｦ縺∪縺吶€

Q: 繝舌ヵ繧｡縺ｯ縺ｩ縺扱縺?
A: SQL BYTEA 縺ｯ z.instanceof(Buffer) 縺ｫ繝槭ャ繝斐Φ繧ｰ縺輔ｌ縺ｾ縺吶€