drizzle-orm

drizzle-orm copied to clipboard

Hi! I have a column in a table which is a Generated Column. Is there a solution for drizzle orm pgTable schema columns that are Generated Columns? Do you have a solution for this in the meantime? Thanks! T

This is how I implemented Generated Columns in one of my projects:

const customGeneratedText = (name: string, ...columns: [string, ...string[]]) =>
  customType<{ data: undefined; driverData: undefined }>({
    dataType() {
      return `text as (concat_ws(" ", ${columns.join(", ")})) invisible`;
    }
  })(name);

To be used like this:

export const vehicles = mysqlTable(
  "vehicles",
  {
    id: char("id", { length: 26 }).primaryKey().notNull(),
    make: varchar("make", { length: 191 }).notNull(),
    model: varchar("model", { length: 191 }).notNull(),
    year: int("year").notNull(),
    name: varchar("name", { length: 191 }).notNull(),
    vin: varchar("vin", { length: 191 }).notNull(),
    tag: varchar("tag", { length: 191 }),
    createdAt: datetime("createdAt", { mode: "date", fsp: 3 })
      .default(sql`(CURRENT_TIMESTAMP(3))`)
      .notNull(),
    search: customGeneratedText(
      "search",
      "make",
      "model",
      "year",
      "name",
      "vin",
      "tag"
    )
  },
  (table) => {
    return {
      nameKey: uniqueIndex("vehicle_name_key").on(table.name),
      vinKey: uniqueIndex("vehicle_vin_key").on(table.vin)
    };
  }
);

And the SQL output is:

ALTER TABLE `vehicles` ADD `search` text as (concat_ws(" ", make, model, year, name, vin, tag)) invisible;

The issue to track is #261

I believe this can be closed as duplicate.

For some reason, using the @Angelelz approach does not work on the initial push, since it generates the following SQL:

CREATE TABLE IF NOT EXISTS "contacts" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"first_name" varchar(50),
	"last_name" varchar(50),
	"full_name" "text GENERATED ALWAYS AS (TRIM(concat_ws(" ", first_name, last_name))) STORED",
	"created_at" timestamp DEFAULT now() NOT NULL
);

Custom Type definition:

export const generatedConcatColumns = customType<{
  data: string;
  driverData: string;
  config: {
    columns: string[];
  };
}>({
  dataType(config) {
    return `text GENERATED ALWAYS AS (TRIM(concat_ws(" ", ${config?.columns.join(
      ", ",
    )}))) STORED`;
  },
});

Schema Definition:

export const contacts = pgTable("contacts", {
  id: uuid("id").defaultRandom().primaryKey(),
  first_name: varchar("first_name", { length: 50 }),
  last_name: varchar("last_name", { length: 50 }),
  full_name: generatedConcatColumns("full_name", {
    columns: ["first_name", "last_name"],
  }),
  createdAt: timestamp("created_at")
    .notNull()
    .default(sql`now()`),
});

Interesting, I've never tried it on initial push. It might be a bug, but it might be a Drizzle-kit bug.

The issue above mentioned by @ocruzv is most likely caused by a bug in drizzle-kit: https://github.com/drizzle-team/drizzle-kit-mirror/issues/167.

For Postgres users here is a patch-package to fix this with drizzle-kit

diff --git a/node_modules/drizzle-kit/bin.cjs b/node_modules/drizzle-kit/bin.cjs
index 9b92321..d347342 100755
--- a/node_modules/drizzle-kit/bin.cjs
+++ b/node_modules/drizzle-kit/bin.cjs
@@ -15496,6 +15496,7 @@ var init_sqlgenerator = __esm({
       "interval minute to second"
     ]);
     isPgNativeType = (it) => {
+      return true
       if (pgNativeTypes.has(it))
         return true;
       const toCheck = it.replace(/ /g, "");

I've only tested this type, test other custom types as I have no clue if this will break them

import { customType } from "drizzle-orm/pg-core";
export const identity = (name: string) =>
  customType<{
    data: string;
    notNull: true;
    default: true;
  }>({
    dataType() {
      return "BIGINT GENERATED ALWAYS AS IDENTITY";
    },
  })(name);

any update

We just released it yesterday!

release notes: https://github.com/drizzle-team/drizzle-orm/releases/tag/0.32.0 docs: https://orm.drizzle.team/docs/generated-columns