고급 패턴 - 🌲 Sonamu

Puri의 고급 기능들을 활용하여 복잡한 데이터베이스 작업을 수행할 수 있습니다.

고급 기능 개요

Transaction

트랜잭션으로 데이터 일관성 보장 ACID 속성

서브쿼리

중첩 쿼리로 복잡한 조건 표현 FROM, WHERE, SELECT

Upsert

INSERT OR UPDATE 한번에 ON CONFLICT

Vector Search

AI 임베딩 유사도 검색 pgvector

Transaction - 트랜잭션

트랜잭션은 여러 쿼리를 하나의 단위로 묶어 데이터 일관성을 보장합니다.

기본 트랜잭션

await db.transaction(async (trx) => {
  // 1. 사용자 생성
  const [userId] = await trx
    .table("users")
    .insert({
      username: "john",
      email: "john@test.com",
      password: "hashed",
      role: "normal",
    })
    .returning("id");

  // 2. 프로필 생성
  await trx.table("profiles").insert({
    user_id: userId.id,
    bio: "Hello world",
  });

  // 3. 초기 설정 생성
  await trx.table("user_settings").insert({
    user_id: userId.id,
    theme: "dark",
    language: "ko",
  });

  // 모든 작업이 성공하면 자동 커밋
  // 하나라도 실패하면 자동 롤백
});

트랜잭션의 ACID 속성: - Atomicity: 모두 성공 또는 모두 실패 - Consistency: 데이터 일관성 유지 - Isolation: 동시 실행 격리 - Durability: 영구 저장

명시적 롤백

await db.transaction(async (trx) => {
  // 데이터 삽입
  await trx.table("users").insert({
    username: "test",
    email: "test@test.com",
    password: "pass",
    role: "normal",
  });

  // 조건에 따라 롤백
  const existingUser = await trx.table("users").where("email", "test@test.com").first();

  if (existingUser) {
    // 수동 롤백
    await trx.rollback();
    return;
  }

  // 롤백하지 않으면 자동 커밋
});

트랜잭션 에러 처리

try {
  await db.transaction(async (trx) => {
    await trx.table("users").insert({
      username: "john",
      email: "john@test.com",
      password: "pass",
      role: "normal",
    });

    // 에러 발생 시 자동 롤백
    throw new Error("Something went wrong");
  });
} catch (error) {
  console.error("Transaction failed:", error);
  // 모든 변경사항이 롤백됨
}

서브쿼리

서브쿼리를 사용하여 복잡한 쿼리를 작성할 수 있습니다.

FROM 절 서브쿼리

// 서브쿼리 정의
const activeUsers = db.table("users").where("is_active", true).select({
  id: "id",
  username: "username",
  email: "email",
});

// 서브쿼리를 테이블처럼 사용
const results = await db
  .table({ active: activeUsers })
  .join("posts", "active.id", "posts.user_id")
  .select({
    userId: "active.id",
    username: "active.username",
    postCount: Puri.count("posts.id"),
  })
  .groupBy("active.id", "active.username");

WHERE 절 서브쿼리

// 평균 급여보다 높은 직원
const results = await db.table("employees").select({
  id: "id",
  name: "username",
  salary: "salary",
}).whereRaw(`
    salary > (SELECT AVG(salary) FROM employees)
  `);

SELECT 절 서브쿼리

const results = await db.table("users").select({
  id: "id",
  username: "username",
  postCount: Puri.rawNumber(`
      (SELECT COUNT(*) FROM posts WHERE posts.user_id = users.id)
    `),
  commentCount: Puri.rawNumber(`
      (SELECT COUNT(*) FROM comments WHERE comments.user_id = users.id)
    `),
});

WHERE 그룹 - 복잡한 조건

whereGroup - AND/OR 조합

const results = await db
  .table("users")
  .select({ id: "id", name: "username" })
  .whereGroup((g) => g.where("role", "admin").orWhere("role", "moderator"))
  .where("is_active", true);

// SQL: WHERE (role = 'admin' OR role = 'moderator') AND is_active = true

중첩 그룹

const results = await db
  .table("employees")
  .select({ id: "id", name: "username" })
  .whereGroup((g) =>
    g
      .whereGroup((g2) => g2.where("department_id", 1).where("salary", ">", 50000))
      .orWhereGroup((g2) => g2.where("department_id", 2).where("salary", ">", 60000)),
  );

// SQL: WHERE (
//   (department_id = 1 AND salary > 50000)
//   OR
//   (department_id = 2 AND salary > 60000)
// )

실전 예제 - 검색 필터

async searchUsers(params: {
  search?: string;
  role?: string[];
  isActive?: boolean;
  minAge?: number;
}) {
  let query = this.getPuri("r")
    .table("users")
    .select({
      id: "id",
      username: "username",
      email: "email",
    });

  // 검색어 (여러 필드)
  if (params.search) {
    query = query.whereGroup((g) =>
      g
        .where("username", "like", `%${params.search}%`)
        .orWhere("email", "like", `%${params.search}%`)
        .orWhere("bio", "like", `%${params.search}%`)
    );
  }

  // 역할 필터
  if (params.role && params.role.length > 0) {
    query = query.whereIn("role", params.role);
  }

  // 활성 상태
  if (params.isActive !== undefined) {
    query = query.where("is_active", params.isActive);
  }

  // 나이 필터
  if (params.minAge !== undefined) {
    query = query.whereRaw(
      "EXTRACT(YEAR FROM AGE(birth_date)) >= ?",
      [params.minAge]
    );
  }

  return await query.orderBy("created_at", "desc");
}

UPSERT - ON CONFLICT

INSERT와 UPDATE를 한 번에 수행합니다.

기본 UPSERT

await db
  .table("users")
  .insert({
    email: "john@test.com",
    username: "john",
    password: "hashed",
    role: "normal",
  })
  .onConflict("email", {
    update: ["username", "password"],
  });

// email이 이미 존재하면 username과 password만 업데이트
// 존재하지 않으면 새로 삽입

선택적 업데이트

await db
  .table("user_stats")
  .insert({
    user_id: 1,
    login_count: 1,
    last_login: new Date(),
  })
  .onConflict("user_id", {
    update: {
      login_count: Puri.rawNumber("user_stats.login_count + 1"),
      last_login: new Date(),
    },
  });

// login_count는 기존 값 + 1
// last_login은 새 값으로 업데이트

DO NOTHING

await db
  .table("users")
  .insert({
    email: "john@test.com",
    username: "john",
    password: "pass",
    role: "normal",
  })
  .onConflict("email", "nothing");

// email이 이미 존재하면 아무것도 하지 않음

복수 컬럼 UNIQUE

await db
  .table("user_preferences")
  .insert({
    user_id: 1,
    key: "theme",
    value: "dark",
  })
  .onConflict(["user_id", "key"], {
    update: ["value"],
  });

// (user_id, key) 조합이 이미 존재하면 value만 업데이트

UpsertBuilder - 복잡한 관계 저장

여러 테이블의 관계를 한 번에 저장할 때 사용합니다.

기본 사용법

await db.transaction(async (trx) => {
  // 1. Company 등록
  const companyRef = trx.ubRegister("companies", {
    name: "Tech Corp",
  });

  // 2. Department 등록 (Company 참조)
  const deptRef = trx.ubRegister("departments", {
    name: "Engineering",
    company_id: companyRef, // UBRef 사용
  });

  // 3. User 등록
  const userRef = trx.ubRegister("users", {
    email: "dev@tech.com",
    username: "developer",
    password: "hashed",
    role: "normal",
  });

  // 4. Employee 등록 (User, Department 참조)
  trx.ubRegister("employees", {
    user_id: userRef,
    department_id: deptRef,
    employee_number: "E001",
    salary: "70000",
  });

  // 5. 순서대로 저장
  await trx.ubUpsert("companies");
  await trx.ubUpsert("departments");
  await trx.ubUpsert("users");
  await trx.ubUpsert("employees");
});

UBRef의 장점: - 외래 키를 신경 쓰지 않고 관계 정의 - 자동으로 의존 순서 해결 - 트랜잭션 내에서 ID 자동 할당

Many-to-Many 관계

await db.transaction(async (trx) => {
  // Project 생성
  const projectRef = trx.ubRegister("projects", {
    name: "New Feature",
    status: "in_progress",
  });

  // 여러 Employee 생성
  const emp1Ref = trx.ubRegister("employees", {
    employee_number: "E100",
    salary: "60000",
  });

  const emp2Ref = trx.ubRegister("employees", {
    employee_number: "E101",
    salary: "65000",
  });

  // M:N 관계 설정
  trx.ubRegister("projects__employees", {
    project_id: projectRef,
    employee_id: emp1Ref,
  });

  trx.ubRegister("projects__employees", {
    project_id: projectRef,
    employee_id: emp2Ref,
  });

  // 저장
  await trx.ubUpsert("projects");
  await trx.ubUpsert("employees");
  await trx.ubUpsert("projects__employees");
});

대량 등록

await db.transaction(async (trx) => {
  const companyRef = trx.ubRegister("companies", {
    name: "Startup Inc",
  });

  // 10명의 직원 등록
  for (let i = 0; i < 10; i++) {
    const userRef = trx.ubRegister("users", {
      email: `user${i}@startup.com`,
      username: `user${i}`,
      password: "pass",
      role: "normal",
    });

    trx.ubRegister("employees", {
      user_id: userRef,
      employee_number: `E${1000 + i}`,
      salary: String(50000 + i * 5000),
    });
  }

  // 일괄 저장
  await trx.ubUpsert("companies");
  await trx.ubUpsert("users");
  await trx.ubUpsert("employees");
});

Vector Search - 벡터 유사도 검색

AI 임베딩을 사용한 의미론적 검색입니다.

Cosine Similarity (기본)

const queryEmbedding = [0.1, 0.2, 0.3, ...]; // 1536차원 벡터

const results = await db
  .table("documents")
  .vectorSimilarity("embedding", queryEmbedding, {
    method: "cosine",
    threshold: 0.7, // 70% 이상 유사도
  })
  .select({
    id: "id",
    title: "title",
    similarity: "similarity", // 자동 추가됨
  })
  .limit(10);

// 결과는 similarity 내림차순 정렬
results[0].similarity; // 0.95 (높을수록 유사)

L2 Distance

const results = await db
  .table("documents")
  .vectorSimilarity("embedding", queryEmbedding, {
    method: "l2",
    threshold: 1.0, // 거리 1.0 이하
  })
  .select({
    id: "id",
    title: "title",
    similarity: "similarity", // 거리값 (낮을수록 유사)
  })
  .limit(10);

Inner Product

const results = await db
  .table("documents")
  .vectorSimilarity("embedding", queryEmbedding, {
    method: "inner_product",
    threshold: 0.5,
  })
  .select({
    id: "id",
    content: "content",
    similarity: "similarity",
  })
  .limit(20);

Vector Search 메서드 선택: - cosine: 일반적인 임베딩 검색 (OpenAI, Cohere 등) - l2: 거리 기반 검색 - inner_product: 정규화된 벡터에 적합

Full-text Search

PGroonga (한글 지원 우수)

// 단일 컬럼 검색
const results = await db
  .table("posts")
  .whereSearch("title", "검색어")
  .select({
    id: "id",
    title: "title",
    score: Puri.score(), // 검색 점수
  })
  .orderBy("score", "desc");

// 복합 컬럼 검색 (가중치)
const results = await db
  .table("posts")
  .whereSearch(["title", "content"], "검색어", {
    weights: [10, 1], // title이 10배 중요
  })
  .select({
    id: "id",
    title: "title",
    highlightedTitle: Puri.highlight("title", "검색어"),
    score: Puri.score(),
  })
  .orderBy("score", "desc");

PostgreSQL tsvector

// 기본 검색
const results = await db
  .table("documents")
  .whereTsSearch("title", "search query", "simple")
  .select({
    id: "id",
    title: "title",
    highlighted: Puri.tsHighlight("title", "search query"),
    rank: Puri.tsRank(Puri.toTsVector("documents.title", "simple"), "search query"),
  })
  .orderBy("rank", "desc");

// 고급 검색 옵션
const results = await db
  .table("documents")
  .whereTsSearch("content", "important keywords", {
    config: "english",
    parser: "plainto_tsquery",
  })
  .select({
    id: "id",
    content: Puri.tsHighlight("content", "important keywords", {
      startSel: "<mark>",
      stopSel: "</mark>",
      maxFragments: 3,
    }),
    rank: Puri.tsRankCd(Puri.toTsVector("documents.content", "english"), "important keywords"),
  })
  .orderBy("rank", "desc");

배치 작업

Batch Insert

const users = [
  { username: "user1", email: "user1@test.com", password: "pass", role: "normal" },
  { username: "user2", email: "user2@test.com", password: "pass", role: "normal" },
  { username: "user3", email: "user3@test.com", password: "pass", role: "normal" },
];

await db.table("users").insert(users);

Batch Update

await db.transaction(async (trx) => {
  for (const userId of userIds) {
    await trx.table("users").where("id", userId).update({ is_active: true });
  }
});

Batch Delete

// IN 사용
await db.table("users").whereIn("id", [1, 2, 3, 4, 5]).delete();

// 조건
await db.table("users").where("last_login", "<", "2023-01-01").delete();

성능 최적화

인덱스 활용

-- 자주 조회하는 컬럼에 인덱스
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_posts_user_id ON posts(user_id);

-- 복합 인덱스
CREATE INDEX idx_users_role_active ON users(role, is_active);

-- 벡터 인덱스 (HNSW)
CREATE INDEX idx_documents_embedding ON documents
USING hnsw (embedding vector_cosine_ops);

-- Full-text 인덱스
CREATE INDEX idx_posts_content_pgroonga ON posts
USING pgroonga (content);

SELECT 최적화

// ❌ 나쁨: 불필요한 컬럼 조회
await db.table("users").selectAll();

// ✅ 좋음: 필요한 컬럼만
await db.table("users").select({
  id: "id",
  name: "username",
});

JOIN 최적화

// ✅ 작은 테이블 → 큰 테이블 순서
await db
  .table("departments")      // 10개
  .join("employees", ...)    // 100개
  .join("user_logs", ...);   // 10000개

페이지네이션 최적화

// ✅ Cursor 기반 페이지네이션 (대량 데이터)
async function getCursorPage(lastId: number, limit: number) {
  return await db
    .table("posts")
    .select({ id: "id", title: "title" })
    .where("id", ">", lastId)
    .orderBy("id", "asc")
    .limit(limit);
}

// 사용
const page1 = await getCursorPage(0, 20);
const page2 = await getCursorPage(page1[page1.length - 1].id, 20);

다음 단계

Model

Model에서 Puri 활용하기

API Development

API에서 Puri 사용하기

Testing

Puri 쿼리 테스트하기

Basic Queries

기본 쿼리로 돌아가기

​고급 기능 개요

Transaction

서브쿼리

Upsert

Vector Search

​Transaction - 트랜잭션

​기본 트랜잭션

​명시적 롤백

​트랜잭션 에러 처리

​서브쿼리

​FROM 절 서브쿼리

​WHERE 절 서브쿼리

​SELECT 절 서브쿼리

​WHERE 그룹 - 복잡한 조건

​whereGroup - AND/OR 조합

​중첩 그룹

​실전 예제 - 검색 필터

​UPSERT - ON CONFLICT

​기본 UPSERT

​선택적 업데이트

​DO NOTHING

​복수 컬럼 UNIQUE

​UpsertBuilder - 복잡한 관계 저장

​기본 사용법

​Many-to-Many 관계

​대량 등록

​Vector Search - 벡터 유사도 검색

​Cosine Similarity (기본)

​L2 Distance

​Inner Product

​Full-text Search

​PGroonga (한글 지원 우수)

​PostgreSQL tsvector

​배치 작업

​Batch Insert

​Batch Update

​Batch Delete

​성능 최적화

​인덱스 활용

​SELECT 최적화

​JOIN 최적화

​페이지네이션 최적화

​다음 단계

Model

API Development

Testing

Basic Queries

고급 기능 개요

Transaction - 트랜잭션

기본 트랜잭션

명시적 롤백

트랜잭션 에러 처리

서브쿼리

FROM 절 서브쿼리

WHERE 절 서브쿼리

SELECT 절 서브쿼리

WHERE 그룹 - 복잡한 조건

whereGroup - AND/OR 조합

중첩 그룹

실전 예제 - 검색 필터

UPSERT - ON CONFLICT

기본 UPSERT

선택적 업데이트

DO NOTHING

복수 컬럼 UNIQUE

UpsertBuilder - 복잡한 관계 저장

기본 사용법

Many-to-Many 관계

대량 등록

Vector Search - 벡터 유사도 검색

Cosine Similarity (기본)

L2 Distance

Inner Product

Full-text Search

PGroonga (한글 지원 우수)

PostgreSQL tsvector

배치 작업

Batch Insert

Batch Update

Batch Delete

성능 최적화

인덱스 활용

SELECT 최적화

JOIN 최적화

페이지네이션 최적화

다음 단계