PostgreSQL: add `generated always as identity` as modern alternative to `serial`

[pigoz]

It's basically the newer (and recommended) way to make auto incrementing columns. Example:

create table old_way (id serial primary key);
create table new_way (id integer primary key generated always as identity);

You can read about the benefits of using the new implementation on this StackOverflow post

[dankochetov]

Also related to https://github.com/drizzle-team/drizzle-orm/issues/261

[JohnGemstone]

Yeah as the SO answer references, the postgres docs recommend not to use SERIAL for anything on postgres 10+ https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use_serial

[MatanYadaev]

@dankochetov There is a workaround we can implement meanwhile?

[ArnaudD]

Using a custom type seems to work :

import { customType } from "drizzle-orm/pg-core";

export const identity = (name: string) =>
  customType<{
    data: number;
    notNull: true;
    default: true;
  }>({
    dataType() {
      return "INTEGER GENERATED ALWAYS AS IDENTITY";
    },
  })(name);

pgTable("table_with_id", {
  id: identity("id").primaryKey(),
});

It generates the following migration :

CREATE TABLE IF NOT EXISTS "table_with_id" (
  "id" INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY NOT NULL
)

[emmbm]

@ArnaudD, what version of drizzle-kit are you using? Generating migrations with 0.19.13 and your workaround appears to results in an invalid quoted column type:

CREATE TABLE IF NOT EXISTS "project_types" (
  "id" "INTEGER GENERATED ALWAYS AS IDENTITY" PRIMARY KEY NOT NULL
);

[ArnaudD]

Hi @iolyd ,

I was indeed on drizzle-kit@0.18.1 & drizzle-orm@0.26.3. I can reproduce your issue after upgrading. Someone already reported it here : https://github.com/drizzle-team/drizzle-kit-mirror/issues/167 with a workaround (by removing the quotes in the generated sql).

[mislam]

It's been almost a year since the need for this modern alternative to "serial" type. The temporary solution from @ArnaudD in https://github.com/drizzle-team/drizzle-orm/issues/295#issuecomment-1723011522 still raise another bug related to generated quotes ""

Is there any update or official support on this?

[emmbm]

@mislam, A guess would be that, in relation to this discord post, some internal revisions are most-likely being made to better support addition of new features like generated columns (e.g. https://github.com/drizzle-team/drizzle-orm/pull/1471). We might have to wait a few more weeks.

[flysand7]

Using a custom type seems to work

Doesn't work on latest drizzle. Any update on this issue?

[arjunyel]

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

[michaelsogos4G]

Hi there Any news about this ?

[flysand7]

From what I see, this issue has been put in the backlog of things to do, but it's not a priority. I guess we'll have to wait a couple years for this feature to make it into drizzle...

[Julian-Hackenberg]

+1 this

[hanjae-jea]

+1 for issue

[oscar-b]

https://github.com/drizzle-team/drizzle-orm/releases/tag/v0.32.0-beta

[AndriiSherman]

We just released it yesterday together with sequences and more

please check release notes: https://github.com/drizzle-team/drizzle-orm/releases/tag/0.32.0 docs for sequences: https://orm.drizzle.team/docs/sequences docs for identity columns: https://orm.drizzle.team/docs/column-types/pg#identity-columns

[hanjae-jea]

Great work! Thanks a lot.