ballon launch

Over my career I’ve heard from colleagues that PostgreSQL was their favorite RDBMS and having recently launched a successful product on Postgres which made extensive use of its fuzzy search capabilities I was looking forward to learning more to qualify these endorsements.

A recent Hacker News post called Lesser-known Postgres features made the front page had a lot of great advice and community discussion.

One particular section described how to prevent setting the value of an auto generated key caught my eye: a few days earlier one of my projects had accidentally written to the primary key of a table and put it into a bad state, unable to write additional rows.

This project uses TypeORM - the research I did, included chatting with the project’s maintainer, informed me that identity columns were not well supported. Some efforts had been added recently but were not extensible enough to provide all possible identity column options that the community desired: notably it was still not possible create identity columns on non-primary keys nor to use the GENERATED ALWAYS style which is a powerful way to prevent users from writing to identity columns:

db=# INSERT INTO sale (id, sold_at, amount) VALUES (2, now(), 1000);
ERROR:  cannot insert into column "id"
DETAIL:  Column "id" is an identity column defined as GENERATED ALWAYS.

Postgres itself has had this feature for many years and recommends all developers use it instead of serial types.

I decided to work on having this new functionality accepted!

Contributing to open source projects, as with any other work, should be done with an emphatic attitude towards the maintainter’s time. To improve the chances of my efforts being quickly accepted, my goal was to put together a well researched Pull Request in-line with the project’s norms. I made a plan for how to implement the new API by chatting with another developer who had flagged the same concern and we created suitable API. This was done over Slack and Discord and was overall a really fun experience - IMO much more productive than message board chats.

Implementing code to a spec is the easy part of the job - as software engineers we must understand how to best serve customers. Getting our interfaces and design decisions should take longer than expected; we need to couple this investment by working quickly on executing the implementation.

I’m happy to report that my diff was accepted and will be available in the next release of TypeORM - https://github.com/typeorm/typeorm/pull/8371

The New API

If you are wanting to create identity columns in Postgres two enhanced decorators are available:

  • @PrimaryGeneratedColumn('identity'...)
  • @Column({generated: 'identity', ...

An example showing the various permutations and defaults:

@Entity()
export class User {
  @PrimaryGeneratedColumn('identity', { generatedIdentity: 'ALWAYS' })
  id: number;

  @Column({
    type: 'bigint',
    generated: 'identity',
    generatedIdentity: 'ALWAYS',
  })
  secondId: number;

  @Column({
    type: 'int',
    generated: 'identity',
    generatedIdentity: 'BY DEFAULT',
  })
  thirdId: number;

  @Column({ 
    type: 'int', 
    generated: 'identity',
  })
  fourthId: number;
}
  • You can chose any valid column type that Postgres allows for an identity column: smallint, int, bigint
  • Omitted a value for generatedIdentity defaults to BY DEFAULT to maintain the API contract of the previous implementation

The model in the example generates this migration:

CREATE TABLE "user" (
  "id" bigint GENERATED ALWAYS AS IDENTITY NOT NULL, 
  "secondId" integer GENERATED ALWAYS AS IDENTITY NOT NULL, 
  "thirdId" integer GENERATED BY DEFAULT AS IDENTITY NOT NULL, 
  "fourthId" integer GENERATED BY DEFAULT AS IDENTITY NOT NULL, 
CONSTRAINT "PK_cace4a159ff9f2512dd42373760" PRIMARY KEY ("id"))

How to migrate an existing model with serial columns

If you are wishing to migrate any existing serial column to identity plan carefully for this change and reference this excellent answer on StackOverflow:

https://stackoverflow.com/a/59233169

Next steps

I’m excited to see the use of IDENTITY columns become more broadly used in TypeORM projects. Please reach out with suggestions or questions on twitter.