Feat: Drizzle-ORM support for Generated columns

[Angelelz]

This PR will close #261, will close #579 and will close #1464.

The API for generated columns will look like this:

const users = sqliteTable(
  'users',
  {
    id: int('id').primaryKey(),
    firstName: text('first_name', { length: 255 }),
    lastName: text('last_name', { length: 255 }),
    email: text('email').notNull(),
    fullName: text('full_name').generatedAlwaysAs(sql`concat_ws(first_name, ' ', last_name)`, { mode: 'stored' }),
    upperName: text('upper_name').generatedAlwaysAs(
      sql` case when first_name is null then null else upper(first_name) end `,
      { mode: 'virtual' }
    ).$type<string | null>(), // There is no way for drizzle to detect nullability in these cases. This is how the user can work around it
  },
);

In Postgres there is also a new method on integer bigint and smallint columns called .generatedAsIdentity()

You can pass a configuration object that will be different depending on your dialect:

// MySql & SQLite:
interface config {
  mode?: 'virtual' | 'stored';  // virtual is default
}

// Pg only for .generatedAsIdentity() method
interface config {
  type?: 'always' | 'byDefault';  // always is the default
}

For this table, the inferred types will be as follows:

type User = typeof users.$inferSelect;
    // ^?  {
    //   id: number;
    //   firstName: string | null;
    //   lastName: string | null;
    //   email: string;
    //   fullName: string;
    //   upperName: string | null;
    // },

type NewUser = typeof users.$inferInsert;
    // ^?  {
    //   email: string;
    //   id?: number | undefined;
    //   firstName?: string | null | undefined;
    //   lastName?: string | null | undefined;
    // },

Please note that drizzle kit support for this is still pending.

[Angelelz]

This will also close #1239

[adicco]

Any updates on this? Thank you!

[nikelborm]

@Angelelz could you please show how this feature infers select or insert types for "generated always as identity" columns? It should forbid the user to insert values to a column of this type because postgres will always throw an error. It should either be

type NewUser = typeof users.$inferInsert;
    // ^?  {
    //   email: string;
    //   firstName?: string | null | undefined;
    //   lastName?: string | null | undefined;
    // },

or be

type NewUser = typeof users.$inferInsert;
    // ^?  {
    //   email: string;
    //   id: never;
    //   firstName?: string | null | undefined;
    //   lastName?: string | null | undefined;
    // },

[davepar]

This looks awesome! What's holding up merging this PR? Generated columns are currently very difficult, because of the custom type bug in drizzle-kit https://github.com/drizzle-team/drizzle-kit-mirror/issues/167.

[emmnull]

Is there anything the community can do to help this move forward? It's currently kind of cumbersome to create a custom type every time we need a generated column and this feature would make schema declarations much cleaner imho.

[kedarv]

Echoing the comments above on how we can help move this PR forward -- I think this PR adds some fairly necessary functionality!

[gfmio]

Hi!

Thanks for this PR! Exactly what I was looking for :)

I just made a fork of this change to try it out and installed it as an override for drizzle-orm in my test project.

I tried it using it with sqlite, mysql and postgres, but unfortunately I was not able to get it to work.

Here's the small test model I used for sqlite.

const urnPrefix = "urn:example:users:";
export const usersTable = sqliteTable("users", {
  id: integer("id").unique().notNull().primaryKey({ autoIncrement: true }),
  urn: text("urn").generatedAlwaysAs(sql`${urnPrefix} || id`, { mode: "virtual" }).notNull().unique(),
});

I ran drizzle-kit to generate the migrations but the urn field is generated as a regular text column and not as a generated column.

Is this meant to work with drizzle-kit or will this require a separate change?

I played around with it a bit more and I came up with the following solution that does work for me:

export interface GenerateCustomTypeOptions<T> {
  data: T;
  config: { sql: SQL | string; mode?: "stored" | "virtual" | undefined };
  notNull: true;
  default: true;
  configRequired: true;
}

export type GeneratedColumnType = <T>(
  ...args: Parameters<ReturnType<typeof customType<GenerateCustomTypeOptions<T>>>>
) => ReturnType<ReturnType<typeof customType<GenerateCustomTypeOptions<T>>>>;

const generated: GeneratedColumnType = <T>(
  ...args: Parameters<ReturnType<typeof customType<GenerateCustomTypeOptions<T>>>>
) => {
  const [_, config] = args;
  const asSql = sql`as (${config.sql})`;
  const asSqlString = asSql.toQuery({
    escapeName: (name) => `'${name}`,
    escapeParam: (_, value) => `'${value}'`,
    escapeString: (str) => `'${str}'`,
  }).sql;
  const joinedSqlString = `${asSqlString} ${config?.mode ?? "virtual"}`;

  return customType<GenerateCustomTypeOptions<T>>({
    dataType: () => joinedSqlString,
  })(...args).generatedAlwaysAs(asSql, { mode: config.mode });
};

Note: The generatedAlwaysAs(asSql, { mode: config.mode }) portion is only needed to prevent errors during inserts / updates since drizzle would otherwise attempt to set urn to null.

Do you think it would make sense to adapt this PR to provide a generated column type builder function like this and add, instead of the generated field in the column type add a writable field to indicate that the field can and should be written to? This would (in my opinion anyway) nicely mirror the rest of the drizzle API and for example also be useful if we want to prevent schema users from ever directly writing to a field.

[SiNONiMiTY]

It is time to ditch the PostgreSQL specific way of doing auto incremented columns with SERIAL.

[AndriiSherman]

Changing an a bit API to this and adding it to drizzle-kit and releasing. Finally 💪

Thanks @Angelelz a lot! I'll take from here and finish everything we think should be changed

export const seq = pgSeq('seq_name', { min, by, ... })

// pg
.generatedAlwaysAs((): SQL => sql`${user.firstName}`, { mode?: 'stored' })
.generatedAlwaysAs(sql``, { mode?: 'stored' })

.generatedAlwaysAsIdentity(seq)
.generatedAlwaysAsIdentity({ name?, min, by, ... })

.generatedByDefaultAsIdentity(seq)

// mysql
.generatedAlwaysAs(sql``, { mode?: 'stored' | 'virtual' })
.generatedAlwaysAs((): SQL => sql`${user.firstName}`, { mode?: 'stored' | 'virtual' })

// sqlite
.generatedAlwaysAs(sql``, { mode?: 'stored' | 'virtual' })
.generatedAlwaysAs((): SQL => sql`${user.firstName}`, { mode?: 'stored' | 'virtual' })

[AndriiSherman]

Finally found a proper strategy and queries so that generated columns and identity columns can work with push, introspect, and generate. Adding all of that to drizzle-kit, and I guess we are going live.

[gera-cameron]

Any ETA on when this will get merged? Our team would greatly benefit from this feature. Thank you!

[anthonyhoegberg]

Thank you all for your hard work. Is this feature ready to be merged?

[psygo]

Maybe, in the meantime, leave an example in the docs with the necessary SQL code to make this happen?

[emmnull]

Maybe, in the meantime, leave an example in the docs with the necessary SQL code to make this happen?

The catch is that there's currently no way to implement this using a custom type in a manner that is compatible with drizzle-kit (https://github.com/drizzle-team/drizzle-orm/issues/579#issuecomment-1679621289). So adding the workaround in the docs might just end up confusing more users who are going to face failing migrations.

[arjunyel]

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

[gediminastub]

Heya, thanks a lot for amazing work! Are there any plans to finish this up soon? Thanks!

[dkmooers]

Adding a +1 for this - would love to use generated columns for post comment counts and such! It would get widespread use throughout the app I'm building. 🙏 😁

[modernmusician]

+1 for this!

[Julian-Hackenberg]

+1 for this, would be great for generated columns in fulltext search for types like arrays with tags for example!

[davepar]

Back in March it sounded like this PR was set to be merged, and then progress stalled. I can't find the discussion now, but I remember reading in Discord that the Drizzle team was going to make some major changes to schema definition implying that it would require a different approach for generated columns. (Maybe this? https://github.com/drizzle-team/drizzle-orm/discussions/2316) So I wouldn't expect this PR to get merged soon.

Personally, after waiting several months for generated columns I couldn't wait any longer. Text and location search are a vital part of my project. I ended up ripping Drizzle out of everything, and just using the Postgres.js driver directly. Which also solved a lot of other issues (e.g. lack of query aggregations, a bug in migration generation for custom types, etc.). It turns out that writing my own TS type definitions was easier than I expected.

Not trying to bash Drizzle. It's excellent. It's just missing some features that I need right now.

[AndriiSherman]

Back in March it sounded like this PR was set to be merged, and then progress stalled. I can't find the discussion now, but I remember reading in Discord that the Drizzle team was going to make some major changes to schema definition implying that it would require a different approach for generated columns. (Maybe this? #2316) So I wouldn't expect this PR to get merged soon.

Personally, after waiting several months for generated columns I couldn't wait any longer. Text and location search are a vital part of my project. I ended up ripping Drizzle out of everything, and just using the Postgres.js driver directly. Which also solved a lot of other issues (e.g. lack of query aggregations, a bug in migration generation for custom types, etc.). It turns out that writing my own TS type definitions was easier than I expected.

Not trying to bash Drizzle. It's excellent. It's just missing some features that I need right now.

sending this to beta release today, I'm finishing release notes, will update here

[AndriiSherman]

Release notes with all the detailed info: https://github.com/drizzle-team/drizzle-orm/releases/tag/v0.32.0-beta

GitHub discussions: https://github.com/drizzle-team/drizzle-orm/discussions/2564