Generated Columns

이 기능을 사용하려면 drizzle-orm@0.32.0 이상 및 drizzle-kit@0.23.0 이상이 필요합니다

SQL의 Generated columns는 같은 테이블 내의 다른 컬럼들을 포함하는 표현식을 기반으로 값이 자동으로 계산되는 컬럼을 생성할 수 있는 기능입니다. 이를 통해 데이터 일관성을 보장하고, 데이터베이스 설계를 단순화하며, 쿼리 성능을 향상시킬 수 있습니다.

Generated columns에는 두 가지 유형이 있습니다:

Virtual (또는 non-persistent) Generated Columns: 이러한 컬럼은 쿼리할 때마다 동적으로 계산됩니다. 데이터베이스에 저장 공간을 차지하지 않습니다.
Stored (또는 persistent) Generated Columns: 이러한 컬럼은 행이 삽입되거나 업데이트될 때 계산되며 그 값이 데이터베이스에 저장됩니다. 이를 통해 인덱싱이 가능하고 각 쿼리마다 값을 재계산할 필요가 없기 때문에 쿼리 성능을 향상시킬 수 있습니다.

Generated columns는 다음과 같은 경우에 특히 유용합니다:

기존 컬럼에서 새로운 데이터 도출
수동 업데이트를 피하기 위한 계산 자동화
데이터 무결성 및 일관성 강화
복잡한 계산을 데이터베이스 스키마 내에 유지하여 애플리케이션 로직 단순화

Generated columns의 구현 및 사용법은 서로 다른 SQL 데이터베이스에서 크게 달라질 수 있습니다. PostgreSQL, MySQL, SQLite는 각각 generated columns와 관련하여 고유한 기능, 역량 및 제한 사항을 가지고 있습니다. 이 섹션에서는 각 데이터베이스 시스템에서 generated columns를 가장 잘 활용하는 방법을 이해하는 데 도움이 되도록 이러한 차이점을 자세히 살펴봅니다.

PostgreSQL

MySQL

SQLite

SingleStore(WIP)

데이터베이스 측

타입: STORED만 지원

작동 방식

삽입 또는 업데이트 시 다른 컬럼을 기반으로 값을 자동으로 계산합니다.

기능

복잡한 표현식을 미리 계산하여 데이터 액세스를 단순화합니다.
Generated columns에 대한 인덱스 지원으로 쿼리 성능을 향상시킵니다.

제한 사항

기본값을 지정할 수 없습니다.
표현식은 다른 generated columns를 참조하거나 서브쿼리를 포함할 수 없습니다.
Generated column 표현식을 수정하려면 스키마 변경이 필요합니다.
Primary keys, foreign keys 또는 unique constraints에서 직접 사용할 수 없습니다.

자세한 정보는 PostgreSQL 문서를 확인하세요.

Drizzle 측

Drizzle에서는 모든 컬럼 타입에 .generatedAlwaysAs() 함수를 지정하고 지원되는 sql 쿼리를 추가하여 이 컬럼 데이터를 생성할 수 있습니다.

기능

이 함수는 3가지 방법으로 generated 표현식을 받을 수 있습니다:

string

export const test = pgTable("test", {
    generatedName: text("gen_name").generatedAlwaysAs(`hello world!`),
});

CREATE TABLE IF NOT EXISTS "test" (
    "gen_name" text GENERATED ALWAYS AS (hello world!) STORED
);

sql 태그 - Drizzle이 일부 값을 이스케이프하도록 하려는 경우

export const test = pgTable("test", {
    generatedName: text("gen_name").generatedAlwaysAs(sql`hello "world"!`),
});

CREATE TABLE IF NOT EXISTS "test" (
    "gen_name" text GENERATED ALWAYS AS (hello "world"!) STORED
);

callback - 테이블의 컬럼을 참조해야 하는 경우

export const test = pgTable("test", {
    name: text("first_name"),
    generatedName: text("gen_name").generatedAlwaysAs(
      (): SQL => sql`hi, ${test.name}!`
    ),
});

CREATE TABLE IF NOT EXISTS "test" (
    "first_name" text,
    "gen_name" text GENERATED ALWAYS AS (hi, "test"."first_name"!) STORED
);

예제 전체 텍스트 검색을 사용한 generated columns

schema.ts

import { SQL, sql } from "drizzle-orm";
import { customType, index, integer, pgTable, text } from "drizzle-orm/pg-core";

const tsVector = customType<{ data: string }>({
  dataType() {
    return "tsvector";
  },
});

export const test = pgTable(
  "test",
  {
    id: integer("id").primaryKey().generatedAlwaysAsIdentity(),
    content: text("content"),
    contentSearch: tsVector("content_search", {
      dimensions: 3,
    }).generatedAlwaysAs(
      (): SQL => sql`to_tsvector('english', ${test.content})`
    ),
  },
  (t) => [
    index("idx_content_search").using("gin", t.contentSearch)
  ]
);

CREATE TABLE IF NOT EXISTS "test" (
	"id" integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY (sequence name "test_id_seq" INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START WITH 1 CACHE 1),
	"content" text,
	"content_search" "tsvector" GENERATED ALWAYS AS (to_tsvector('english', "test"."content")) STORED
);
--> statement-breakpoint
CREATE INDEX IF NOT EXISTS "idx_content_search" ON "test" USING gin ("content_search");

데이터베이스 측

타입: STORED, VIRTUAL

작동 방식

테이블 스키마에서 표현식으로 정의됩니다.
Virtual columns는 읽기 작업 중에 계산됩니다.
Stored columns는 쓰기 작업 중에 계산되고 저장됩니다.

기능

SELECT, INSERT, UPDATE, DELETE 문에서 사용됩니다.
Virtual 및 stored 모두 인덱싱이 가능합니다.
NOT NULL 및 기타 제약 조건을 지정할 수 있습니다.

제한 사항

Generated column에 값을 직접 삽입하거나 업데이트할 수 없습니다.

자세한 정보는 MySQL Alter Generated 문서 및 MySQL create generated 문서를 확인하세요.

Drizzle 측

기능

string

export const test = mysqlTable("test", {
    generatedName: text("gen_name").generatedAlwaysAs(`hello world!`),
});

CREATE TABLE `test` (
    `gen_name` text GENERATED ALWAYS AS (hello world!) VIRTUAL
);

sql tag - if you want drizzle to escape some values for you

export const test = mysqlTable("test", {
    generatedName: text("gen_name").generatedAlwaysAs(sql`hello "world"!`),
});

CREATE TABLE `test` (
    `gen_name` text GENERATED ALWAYS AS (hello "world"!) VIRTUAL
);

callback - 테이블의 컬럼을 참조해야 하는 경우

export const test = mysqlTable("test", {
    name: text("first_name"),
    generatedName: text("gen_name").generatedAlwaysAs(
      (): SQL => sql`hi, ${test.name}!`
    ),
});

CREATE TABLE `test` (
	`first_name` text,
	`gen_name` text GENERATED ALWAYS AS (hi, `test`.`first_name`!) VIRTUAL
);

제한 사항

Drizzle Kit도 push 명령에 대한 제한 사항이 있습니다:

push를 사용하여 generated constraint 표현식 및 타입을 변경할 수 없습니다. Drizzle-kit은 이 변경 사항을 무시합니다. 이를 작동시키려면 컬럼을 삭제하고, push한 다음, 새 표현식으로 컬럼을 추가해야 합니다. 이는 데이터베이스 측의 복잡한 매핑 때문인데, 스키마 표현식이 데이터베이스 측에서 수정되고 인트로스펙션 시 다른 문자열을 얻게 됩니다. 이 표현식을 변경했는지 또는 데이터베이스에서 변경하고 포맷했는지 확실하지 않습니다. 이것들이 generated columns이고 push가 주로 로컬 데이터베이스에서 프로토타이핑에 사용되는 한, generated columns를 삭제하고 생성하는 것이 빨라야 합니다. 이러한 컬럼들은 generated이므로 모든 데이터가 복원됩니다.
generate는 제한 사항이 없어야 합니다.

schema.ts

export const users = mysqlTable("users", {
    id: int("id"),
    id2: int("id2"),
    name: text("name"),
    storedGenerated: text("stored_gen").generatedAlwaysAs(
      (): SQL => sql`${users.name} || 'hello'`,
      { mode: "stored" }
    ),
    virtualGenerated: text("virtual_gen").generatedAlwaysAs(
      (): SQL => sql`${users.name} || 'hello'`,
      { mode: "virtual" }
    ),
})

CREATE TABLE `users` (
    `id` int,
    `id2` int,
    `name` text,
    `stored_gen` text GENERATED ALWAYS AS (`users`.`name` || 'hello') STORED,
    `virtual_gen` text GENERATED ALWAYS AS (`users`.`name` || 'hello') VIRTUAL
);

데이터베이스 측

타입: STORED, VIRTUAL

작동 방식

테이블 스키마에서 표현식으로 정의됩니다.
Virtual columns는 읽기 작업 중에 계산됩니다.
Stored columns는 쓰기 작업 중에 계산되고 저장됩니다.

기능

SELECT, INSERT, UPDATE, DELETE 문에서 사용됩니다.
Virtual 및 stored 모두 인덱싱이 가능합니다.
NOT NULL 및 기타 제약 조건을 지정할 수 있습니다.

제한 사항

Generated column에 값을 직접 삽입하거나 업데이트할 수 없습니다.

자세한 정보는 SQLite 문서를 확인하세요.

Drizzle 측

기능

string

export const test = sqliteTable("test", {
    generatedName: text("gen_name").generatedAlwaysAs(`hello world!`),
});

CREATE TABLE `test` (
    `gen_name` text GENERATED ALWAYS AS (hello world!) VIRTUAL
);

sql 태그 - Drizzle이 일부 값을 이스케이프하도록 하려는 경우

export const test = sqliteTable("test", {
    generatedName: text("gen_name").generatedAlwaysAs(sql`hello "world"!`),
});

CREATE TABLE `test` (
    `gen_name` text GENERATED ALWAYS AS (hello "world"!) VIRTUAL
);

callback - 테이블의 컬럼을 참조해야 하는 경우

export const test = sqliteTable("test", {
    name: text("first_name"),
    generatedName: text("gen_name").generatedAlwaysAs(
      (): SQL => sql`hi, ${test.name}!`
    ),
});

CREATE TABLE `test` (
    `first_name` text,
    `gen_name` text GENERATED ALWAYS AS (hi, "first_name"!) VIRTUAL
);

제한 사항

Drizzle Kit도 push 및 generate 명령에 대한 제한 사항이 있습니다:

기존 테이블에서 stored 타입의 generated constraint 표현식을 변경할 수 없습니다. 이 테이블을 삭제하고 다시 생성해야 합니다. 이는 이러한 작업에 대한 SQLite 제한 때문입니다. 향후 릴리스에서 이 경우를 처리할 예정입니다(데이터 마이그레이션과 함께 새 테이블 생성이 포함됩니다).
위와 같은 이유로 기존 컬럼에 stored generated 표현식을 추가할 수 없습니다. 그러나 기존 컬럼에 virtual 표현식을 추가할 수 있습니다.
위와 같은 이유로 기존 컬럼에서 stored generated 표현식을 변경할 수 없습니다. 그러나 virtual 표현식은 변경할 수 있습니다.
위와 같은 이유로 generated constraint 타입을 virtual에서 stored로 변경할 수 없습니다. 그러나 stored에서 virtual로는 변경할 수 있습니다.

index.ts

schema.ts

export const users = sqliteTable("users", {
  id: int("id"),
  name: text("name"),
  storedGenerated: text("stored_gen").generatedAlwaysAs(
    (): SQL => sql`${users.name} || 'hello'`,
    { mode: "stored" }
  ),
  virtualGenerated: text("virtual_gen").generatedAlwaysAs(
    (): SQL => sql`${users.name} || 'hello'`,
    { mode: "virtual" }
  ),
});

CREATE TABLE `users` (
    `id` integer,
    `name` text,
    `stored_gen` text GENERATED ALWAYS AS ("name" || 'hello') STORED,
    `virtual_gen` text GENERATED ALWAYS AS ("name" || 'hello') VIRTUAL
);