Drizzle ORM - PostgreSQL 전체 텍스트 검색

Drizzle | PostgreSQL 전체 텍스트 검색

This guide assumes familiarity with:

PostgreSQL 시작하기
SELECT 문
인덱스
SQL 연산자
drizzle-orm@0.31.0 및 drizzle-kit@0.22.0 이상 버전 필요

이 가이드는 Drizzle ORM을 사용하여 PostgreSQL에서 전체 텍스트 검색을 구현하는 방법을 보여줍니다. 전체 텍스트 검색은 문서나 문서 집합 내에서 텍스트를 검색하는 기술입니다. PostgreSQL은 to_tsvector 및 to_tsquery와 같은 전체 텍스트 검색을 위한 함수를 제공합니다.

to_tsvector 함수는 텍스트 문서를 토큰으로 파싱하고, 토큰을 어휘소(lexeme)로 줄인 후, 문서 내 위치와 함께 어휘소를 나열한 tsvector를 반환합니다.

import { sql } from 'drizzle-orm';

const db = drizzle(...);

await db.execute(
  sql`select to_tsvector('english', 'Guide to PostgreSQL full-text search with Drizzle ORM')`,
);

[
  {
    to_tsvector: "'drizzl':9 'full':5 'full-text':4
    'guid':1 'orm':10 'postgresql':3 'search':7 'text':6"
  }
]

to_tsquery 함수는 키워드를 정규화된 토큰으로 변환하고, tsvector의 어휘소와 일치하는 tsquery를 반환합니다. @@ 연산자는 직접 일치를 위해 사용됩니다.

await db.execute(
  sql`select to_tsvector('english', 'Guide to PostgreSQL full-text search with Drizzle ORM')
    @@ to_tsquery('english', 'Drizzle') as match`,
);

[ { match: true } ]

현재 Drizzle은 tsvector 타입을 기본적으로 지원하지 않으므로, text 컬럼의 데이터를 실시간으로 변환해야 합니다. 성능을 향상시키기 위해 다음과 같이 컬럼에 GIN 인덱스를 생성할 수 있습니다.

schema.ts

migration.sql

db_data

import { index, pgTable, serial, text } from 'drizzle-orm/pg-core';

export const posts = pgTable(
  'posts',
  {
    id: serial('id').primaryKey(),
    title: text('title').notNull(),
  },
  (table) => ({
    titleSearchIndex: index('title_search_index')
    .using('gin', sql`to_tsvector('english', ${table.title})`),
  }),
);

CREATE TABLE IF NOT EXISTS "posts" (
        "id" serial PRIMARY KEY NOT NULL,
        "title" text NOT NULL
);

CREATE INDEX IF NOT EXISTS "title_search_index" ON "posts"
  USING gin (to_tsvector('english', "title"));

[
  { id: 1, title: 'Planning Your First Trip to Europe' },
  { id: 2, title: "Cultural Insights: Exploring Asia's Heritage" },
  { id: 3, title: 'Top 5 Destinations for a Family Trip' },
  { id: 4, title: 'Essential Hiking Gear for Mountain Enthusiasts' },
  { id: 5, title: 'Trip Planning: Choosing Your Next Destination' },
  { id: 6, title: 'Discovering Hidden Culinary Gems in Italy' },
  { id: 7, title: 'The Ultimate Road Trip Guide for Explorers' },
];

Drizzle ORM을 사용하여 PostgreSQL에서 전체 텍스트 검색을 구현하려면 to_tsvector 및 to_tsquery 함수를 sql 연산자와 함께 사용할 수 있습니다.

import { sql } from 'drizzle-orm';
import { posts } from './schema';

const title = 'trip';

await db
  .select()
  .from(posts)
  .where(sql`to_tsvector('english', ${posts.title}) @@ to_tsquery('english', ${title})`);

[
  { id: 1, title: 'Planning Your First Trip to Europe' },
  { id: 3, title: 'Top 5 Destinations for a Family Trip' },
  { id: 5, title: 'Trip Planning: Choosing Your Next Destination' },
  { id: 7, title: 'The Ultimate Road Trip Guide for Explorers' }
]

여러 키워드 중 하나와 일치시키려면 | 연산자를 사용할 수 있습니다.

const title = 'Europe | Asia';

await db
  .select()
  .from(posts)
  .where(sql`to_tsvector('english', ${posts.title}) @@ to_tsquery('english', ${title})`);

[
  { id: 1, title: 'Planning Your First Trip to Europe' },
  { id: 2, title: "Cultural Insights: Exploring Asia's Heritage" }
]

여러 키워드를 일치시키려면 plainto_tsquery 함수를 사용할 수 있습니다.

// 'discover & Italy'
const title = 'discover Italy';

await db
  .select()
  .from(posts)
  .where(sql`to_tsvector('english', ${posts.title}) @@ plainto_tsquery('english', ${title})`);

select * from posts
  where to_tsvector('english', title) @@ plainto_tsquery('english', 'discover Italy');

[ { id: 6, title: 'Discovering Hidden Culinary Gems in Italy' } ]

구문을 일치시키려면 phraseto_tsquery 함수를 사용할 수 있습니다.

// "trip family"로 검색하면 결과가 반환되지 않음
// 'family  trip'
const title = 'family trip';

await db
  .select()
  .from(posts)
  .where(sql`to_tsvector('english', ${posts.title}) @@ phraseto_tsquery('english', ${title})`);

select * from posts
  where to_tsvector('english', title) @@ phraseto_tsquery('english', 'family trip');

[ { id: 3, title: 'Top 5 Destinations for a Family Trip' } ]

웹 검색 엔진과 유사한 구문을 사용하는 websearch_to_tsquery 함수도 사용할 수 있습니다.

// 'family | first & trip & europ | asia'
const title = 'family or first trip Europe or Asia';

await db
  .select()
  .from(posts)
  .where(sql`to_tsvector('english', ${posts.title}) @@ websearch_to_tsquery('english', ${title})`);

select * from posts
  where to_tsvector('english', title)
  @@ websearch_to_tsquery('english', 'family or first trip Europe or Asia');

[
  { id: 1, title: 'Planning Your First Trip to Europe' },
  { id: 2, title: "Cultural Insights: Exploring Asia's Heritage" },
  { id: 3, title: 'Top 5 Destinations for a Family Trip' }
]

여러 컬럼에 전체 텍스트 검색을 구현하려면, 여러 컬럼에 인덱스를 생성하고 to_tsvector 함수로 컬럼을 연결할 수 있습니다.

schema.ts

migration.sql

db_data

import { sql } from 'drizzle-orm';
import { index, pgTable, serial, text } from 'drizzle-orm/pg-core';

export const posts = pgTable(
  'posts',
  {
    id: serial('id').primaryKey(),
    title: text('title').notNull(),
    description: text('description').notNull(),
  },
  (table) => ({
    searchIndex: index('search_index').using(
      'gin',
      sql`(
          setweight(to_tsvector('english', ${table.title}), 'A') ||
          setweight(to_tsvector('english', ${table.description}), 'B')
      )`,
    ),
  }),
);

CREATE TABLE IF NOT EXISTS "posts" (
      "id" serial PRIMARY KEY NOT NULL,
      "title" text NOT NULL,
      "description" text NOT NULL
);

CREATE INDEX IF NOT EXISTS "search_index" ON "posts"
  USING gin ((setweight(to_tsvector('english', "title"), 'A') ||
  setweight(to_tsvector('english', "description"), 'B')));

[
  {
    id: 1,
    title: 'Planning Your First Trip to Europe',
    description:
      'Get essential tips on budgeting, sightseeing, and cultural etiquette for your inaugural European adventure.',
  },
  {
    id: 2,
    title: "Cultural Insights: Exploring Asia's Heritage",
    description:
      'Dive deep into the rich history and traditions of Asia through immersive experiences and local interactions.',
  },
  {
    id: 3,
    title: 'Top 5 Destinations for a Family Trip',
    description:
      'Discover family-friendly destinations that offer fun, education, and relaxation for all ages.',
  },
  {
    id: 4,
    title: 'Essential Hiking Gear for Mountain Enthusiasts',
    description:
      'Equip yourself with the latest and most reliable gear for your next mountain hiking expedition.',
  },
  {
    id: 5,
    title: 'Trip Planning: Choosing Your Next Destination',
    description:
      'Learn how to select destinations that align with your travel goals, whether for leisure, adventure, or cultural exploration.',
  },
  {
    id: 6,
    title: 'Discovering Hidden Culinary Gems in Italy',
    description:
      "Unearth Italy's lesser-known eateries and food markets that offer authentic and traditional flavors.",
  },
  {
    id: 7,
    title: 'The Ultimate Road Trip Guide for Explorers',
    description:
      'Plan your next great road trip with tips on route planning, packing, and discovering off-the-beaten-path attractions.',
  },
];

setweight 함수는 tsvector의 항목에 주어진 가중치(A, B, C, D 중 하나)를 부여하는 데 사용됩니다. 이는 일반적으로 제목과 본문과 같은 문서의 다른 부분에서 오는 항목을 표시하는 데 사용됩니다.

여러 컬럼에 대해 쿼리하는 방법은 다음과 같습니다.

const title = 'plan';

await db.select().from(posts)
  .where(sql`(
      setweight(to_tsvector('english', ${posts.title}), 'A') ||
      setweight(to_tsvector('english', ${posts.description}), 'B'))
      @@ to_tsquery('english', ${title}
    )`
  );

[
  {
    id: 1,
    title: 'Planning Your First Trip to Europe',
    description: 'Get essential tips on budgeting, sightseeing, and cultural etiquette for your inaugural European adventure.'
  },
  {
    id: 5,
    title: 'Trip Planning: Choosing Your Next Destination',
    description: 'Learn how to select destinations that align with your travel goals, whether for leisure, adventure, or cultural exploration.'
  },
  {
    id: 7,
    title: 'The Ultimate Road Trip Guide for Explorers',
    description: 'Plan your next great road trip with tips on route planning, packing, and discovering off-the-beaten-path attractions.'
  }
]

검색 결과의 순위를 매기려면 ts_rank 또는 ts_rank_cd 함수와 orderBy 메서드를 사용할 수 있습니다.

import { desc, getTableColumns, sql } from 'drizzle-orm';

const search = 'culture | Europe | Italy | adventure';

const matchQuery = sql`(
  setweight(to_tsvector('english', ${posts.title}), 'A') ||
  setweight(to_tsvector('english', ${posts.description}), 'B')), to_tsquery('english', ${search})`;

await db
  .select({
    ...getTableColumns(posts),
    rank: sql`ts_rank(${matchQuery})`,
    rankCd: sql`ts_rank_cd(${matchQuery})`,
  })
  .from(posts)
  .where(
    sql`(
      setweight(to_tsvector('english', ${posts.title}), 'A') ||
      setweight(to_tsvector('english', ${posts.description}), 'B')
      ) @@ to_tsquery('english', ${search})`,
  )
  .orderBy((t) => desc(t.rank));

[
  {
    id: 1,
    title: 'Planning Your First Trip to Europe',
    description: 'Get essential tips on budgeting, sightseeing, and cultural etiquette for your inaugural European adventure.',
    rank: 0.2735672,
    rankCd: 1.8
  },
  {
    id: 6,
    title: 'Discovering Hidden Culinary Gems in Italy',
    description: "Unearth Italy's lesser-known eateries and food markets that offer authentic and traditional flavors.",
    rank: 0.16717994,
    rankCd: 1.4
  },
  {
    id: 2,
    title: "Cultural Insights: Exploring Asia's Heritage",
    description: 'Dive deep into the rich history and traditions of Asia through immersive experiences and local interactions.',
    rank: 0.15198177,
    rankCd: 1
  },
  {
    id: 5,
    title: 'Trip Planning: Choosing Your Next Destination',
    description: 'Learn how to select destinations that align with your travel goals, whether for leisure, adventure, or cultural exploration.',
    rank: 0.12158542,
    rankCd: 0.8
  }
]

ts_rank 함수는 문서 전체에서 쿼리 용어의 빈도에 초점을 맞춥니다. ts_rank_cd 함수는 문서 내에서 쿼리 용어의 근접성에 초점을 맞춥니다.