생성된 컬럼(Generated Columns)

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

SQL에서 생성된 컬럼은 동일한 테이블 내의 다른 컬럼을 기반으로 자동으로 계산된 값을 가지는 컬럼을 생성할 수 있는 기능입니다. 이를 통해 데이터 일관성을 보장하고, 데이터베이스 설계를 단순화하며, 쿼리 성능을 향상시킬 수 있습니다.

생성된 컬럼에는 두 가지 유형이 있습니다:

가상(Virtual) 생성된 컬럼: 쿼리할 때마다 동적으로 계산됩니다. 데이터베이스 저장 공간을 차지하지 않습니다.
저장(Stored) 생성된 컬럼: 행이 삽입되거나 업데이트될 때 계산되며, 그 값은 데이터베이스에 저장됩니다. 이로 인해 인덱싱이 가능하며, 쿼리 성능이 향상됩니다.

생성된 컬럼은 다음과 같은 경우에 특히 유용합니다:

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

생성된 컬럼의 구현과 사용법은 SQL 데이터베이스마다 크게 다를 수 있습니다. PostgreSQL, MySQL, SQLite는 각각 고유한 기능, 능력, 제한 사항을 가지고 있습니다. 이 섹션에서는 각 데이터베이스 시스템에서 생성된 컬럼을 최적으로 활용하는 방법을 자세히 살펴보겠습니다.

PostgreSQL

MySQL

SQLite

SingleStore(WIP)

데이터베이스 측면

유형: STORED만 지원

동작 방식

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

기능

복잡한 표현식을 미리 계산하여 데이터 접근을 단순화합니다.
생성된 컬럼에 인덱스를 지원하여 쿼리 성능을 향상시킵니다.

제한 사항

기본값을 지정할 수 없습니다.
표현식이 다른 생성된 컬럼을 참조하거나 서브쿼리를 포함할 수 없습니다.
생성된 컬럼 표현식을 수정하려면 스키마 변경이 필요합니다.
기본 키, 외래 키, 고유 제약 조건에 직접 사용할 수 없습니다.

자세한 내용은 PostgreSQL 문서를 참조하세요.

Drizzle 측면

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

기능

이 함수는 세 가지 방식으로 생성된 표현식을 받을 수 있습니다:

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,
);

콜백 - 테이블의 컬럼을 참조해야 할 때

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,
);

예제 전체 텍스트 검색과 함께 생성된 컬럼

schema.ts

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

const tsVector = customType({
  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) => ({
    idx: 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

동작 방식

테이블 스키마에 표현식으로 정의됩니다.
가상 컬럼은 읽기 작업 중에 계산됩니다.
저장 컬럼은 쓰기 작업 중에 계산되고 저장됩니다.

기능

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

제한 사항

생성된 컬럼에 직접 값을 삽입하거나 업데이트할 수 없습니다.

자세한 내용은 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 태그 - Drizzle가 일부 값을 이스케이프하도록 할 때

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,
);

콜백 - 테이블의 컬럼을 참조해야 할 때

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를 사용하여 생성된 제약 조건 표현식과 타입을 변경할 수 없습니다. Drizzle-kit은 이 변경을 무시합니다. 이를 작동시키려면 컬럼을 삭제, push, 그리고 새로운 표현식으로 컬럼을 추가해야 합니다. 이는 데이터베이스 측면에서 복잡한 매핑 때문에 이루어졌습니다. 데이터베이스 측에서 스키마 표현식이 수정되고, 인트로스펙션 시 다른 문자열을 얻을 수 있기 때문입니다. 이 표현식이 변경되었는지 또는 데이터베이스에 의해 변경되고 포맷되었는지 확신할 수 없습니다. 생성된 컬럼이므로 push는 주로 로컬 데이터베이스에서 프로토타이핑에 사용되며, 생성된 컬럼을 삭제하고 생성하는 것이 빠를 것입니다. 이 컬럼은 생성된 컬럼이므로 모든 데이터가 복원됩니다.
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

동작 방식

테이블 스키마에 표현식으로 정의됩니다.
가상 컬럼은 읽기 작업 중에 계산됩니다.
저장 컬럼은 쓰기 작업 중에 계산되고 저장됩니다.

기능

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

제한 사항

생성된 컬럼에 직접 값을 삽입하거나 업데이트할 수 없습니다.

자세한 내용은 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,
);

콜백 - 테이블의 컬럼을 참조해야 할 때

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 명령에도 다음과 같은 제한 사항이 있습니다:

기존 테이블에서 저장 타입의 생성된 제약 조건 표현식을 변경할 수 없습니다. 이 테이블을 삭제하고 다시 생성해야 합니다. 이는 SQLite의 제한 사항 때문입니다. 향후 릴리스에서 이 경우를 처리할 예정입니다(새 테이블 생성 및 데이터 마이그레이션 포함).
기존 컬럼에 저장 생성된 표현식을 추가할 수 없습니다. 그러나 가상 표현식은 추가할 수 있습니다.
기존 컬럼에서 저장 생성된 표현식을 변경할 수 없습니다. 그러나 가상 표현식은 변경할 수 있습니다.
생성된 제약 조건 타입을 가상에서 저장으로 변경할 수 없습니다. 그러나 저장에서 가상으로는 변경할 수 있습니다.

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
);