CODEWITHSUNDEEP
postgresqltypescriptuuidtypeorm
Neil Stevens
Neil Stevens

Reputation: 3914

TypeORM: auto generated UUID in PostgreSQL

I am writing a REST API and for data access I am using TypeORM. I have used this successfully but I would like to have a UUID auto-generated primary key on one of my tables.

Does anyone know how to set up a column in TypeORM that is a UUID type and auto-generated? I have tried the following:

Using @PrimaryGeneratedColumn()

@PrimaryGeneratedColumn() id: string;

This gives me an exception when synchronising with the database:

TypeORM connection error: Error: column "id" cannot be cast automatically to type integer
app.ts:65
at new QueryFailedError (/Users/neilstevens/repositories/Capp.co/capp/ms/ms-token-server/node_modules/typeorm/error/QueryFailedError.js:27:28)
at Query.callback (/Users/neilstevens/repositories/Capp.co/capp/ms/ms-token-server/node_modules/typeorm/driver/postgres/PostgresQueryRunner.js:216:38)
at Query.handleError (/Users/neilstevens/repositories/Capp.co/capp/ms/ms-token-server/node_modules/pg/lib/query.js:143:17)
at Connection.connectedErrorHandler (/Users/neilstevens/repositories/Capp.co/capp/ms/ms-token-server/node_modules/pg/lib/client.js:132:26)
at emitOne (events.js:115:13)
at Connection.emit (events.js:210:7)
at Socket.<anonymous> (/Users/neilstevens/repositories/Capp.co/capp/ms/ms-token-server/node_modules/pg/lib/connection.js:118:12)
at emitOne (events.js:115:13)
at Socket.emit (events.js:210:7)
at addChunk (_stream_readable.js:266:12)

Using @PrimaryColumn and @Generated

@PrimaryColumn({type:"uuid"})
@Generated("uuid") id: string;

I get the following error when attempting this:

TypeORM connection error: Error: sequence "SystemUser_id_seq" does not exist
app.ts:65
at new QueryFailedError (/Users/neilstevens/repositories/Capp.co/capp/ms/ms-token-server/node_modules/typeorm/error/QueryFailedError.js:27:28)
at Query.callback (/Users/neilstevens/repositories/Capp.co/capp/ms/ms-token-server/node_modules/typeorm/driver/postgres/PostgresQueryRunner.js:216:38)
at Query.handleError (/Users/neilstevens/repositories/Capp.co/capp/ms/ms-token-server/node_modules/pg/lib/query.js:143:17)
at Connection.connectedErrorHandler (/Users/neilstevens/repositories/Capp.co/capp/ms/ms-token-server/node_modules/pg/lib/client.js:132:26)
at emitOne (events.js:115:13)
at Connection.emit (events.js:210:7)
at Socket.<anonymous> (/Users/neilstevens/repositories/Capp.co/capp/ms/ms-token-server/node_modules/pg/lib/connection.js:118:12)
at emitOne (events.js:115:13)
at Socket.emit (events.js:210:7)
at addChunk (_stream_readable.js:266:12)

So it looks like this way I can get a primary column but TypeORM is not creating the sequence required for this to be an auto-generated column.

If I use @PrimaryColumn({type: "uuid"}) then I do get a UUID column in the table but NOT an auto-generated column.

I cannot see any other way to achieve this so could someone please advise if this is a) even possible and b) how one would go about creating a auto-generated UUID column...please?

Upvotes: 53

Views: 122411

Answers (6)

Samuel Mutemi
Samuel Mutemi

Reputation: 398

After effecting the changes others have talked about. Specifically in my case:

 @Column({ primary: true, generated: 'uuid' })
  id: string;

I also had to let TypeOrm Synchronize the changes to my database table. I had set synchronize to false in OrmConfig.json and that's why the changes were not taking effect. So also enable TypeOrm to effect the changes by setting :

  "synchronize": true

in your orm configuration.

Upvotes: 4

Mohammed Al-Reai
Mohammed Al-Reai

Reputation: 2806

for me, I use this shape

@PrimaryGeneratedColumn('uuid')
  id: string

we can use another shape you should install the UUID package

@PrimaryColumn() id:string and used the @BeforeInsert() genarate(){ this.id=uuid()

Upvotes: 14

brunofl
brunofl

Reputation: 155

In the migration you could try setting the default value with uuid_generate_v4() this:

enter columns: [
        {
          name: 'id',
          type: 'varchar',
          isPrimary: true,
          generationStrategy: 'uuid',
          default: 'uuid_generate_v4()',
        },

In the Model component I set the id as below:

@Entity('<table_name>') 
class SvcRequest {
  @PrimaryGeneratedColumn('uuid')
  id: string;

hope it helps

Upvotes: 12

Joshua Manns
Joshua Manns

Reputation: 585

As of Typeorm version 0.1.16, the decorator @PrimaryGeneratedColumn supports uuid for all databases.

Usage:

@Entity()
class MyClass {

  @PrimaryGeneratedColumn('uuid')
  id: string;

}

If your version of Postgres doesn't already include uuid-ossp (used to generate the UUID), you can install it using create extension "uuid-ossp";.

Upvotes: 31

Dan
Dan

Reputation: 2715

This is what I am using for Postgres 9.6 and 10. UUID is generated by default.

CREATE EXTENSION pgcrypto;

CREATE TABLE my_table
(
  uuid UUID NOT NULL UNIQUE DEFAULT gen_random_uuid()
);

However, I haven't been using the UUID as my primary key. This is here mainly for migration purposes. What I do know is that its unique, and haven't had a collision yet in the development environment. You can try replacing NOT NULL and UNIQUE with PRIMARY KEY instead.

Upvotes: 4

D.Zotov
D.Zotov

Reputation: 2324

Try:

@PrimaryGeneratedColumn("uuid")
id: string;

Also, if you don't need a primary column, but need to generate uuid sequence, you can try this:

@Column()
@Generated("uuid")
uuid: string;

Upvotes: 128

Related Questions