Generated Columns

Stored (or persistent) Generated Columns: These columns are computed when a row is inserted or updated and their values are stored in the database. This allows them to be indexed and can improve query performance since the values do not need to be recomputed for each query.

Database side

Types: STORED only

How It Works

• Automatically computes values based on other columns during insert or update.

Capabilities

• Simplifies data access by precomputing complex expressions.
• Enhances query performance with index support on generated columns.

Limitations

• Cannot specify default values.
• Expressions cannot reference other generated columns or include subqueries.
• Schema changes required to modify generated column expressions.
• Cannot directly use in primary keys, foreign keys, or unique constraints

For more info, please check CockroachDB docs

Drizzle side

In Drizzle you can specify .generatedAlwaysAs() function on any column type and add a supported sql query, that will generate this column data for you.

Features

This function can accept generated expression in 2 ways:

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

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

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

callback - if you need to reference columns from a table

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

CREATE TABLE "test" (
        "first_name" string,
        "gen_name" string GENERATED ALWAYS AS ('hi, ' || "test"."first_name" || '!') STORED
);

Example generated columns with full-text search

import { SQL, sql } from "drizzle-orm";
import { customType, index, int4, cockroachTable, string } from "drizzle-orm/cockroach-core";

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

export const test = cockroachTable(
  "test",
  {
    id: int4("id").primaryKey().generatedAlwaysAsIdentity(),
    content: string("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 "test" (
        "id" int4 PRIMARY KEY GENERATED ALWAYS AS IDENTITY (INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START WITH 1 CACHE 1),
        "content" string,
        "content_search" tsvector GENERATED ALWAYS AS (to_tsvector('english', "test"."content")) STORED
);

CREATE INDEX "idx_content_search" ON "test" USING gin ("content_search");