TypeORM - duplicate key value violation

Question

Let's say I have a simple TypeORM entity like this:

import { Entity, PrimaryGeneratorColumn, Column } from 'typeorm';

@Entity()
export class Employee {
    @PrimaryGeneratedColumn()
    id: number;

    @Column()
    Name: string;
}

It all works fine as long as I do not touch database manually. If I enter a record manually, the Entity will not be able to check the last current id from the table and throws error duplicate key value violates unique constraint.

For example:

1. Employee Entity creates two records by itself.
2. I have two records in my database, last record has id 2
3. I create third record in the database manually, it has id 3
4. Afterwards, when Employee Entity runs, it does not = create another record, since it wants to use id 3, but I added that manually already.
5. I am getting error duplicate key value violates unique constraint
6. When Employee Entity runs again, it works, because it tries to save another record under id 4 already which is available.

So how can I ensure, that Employee Entity checks the last id from the database and then saves the next record with incremental id?

Thank you

Answer 1

In TypeORM the decorator @PrimaryGeneratedColumn() creates a primary column which the value will be generated using a sequence. Every time you add a new record on the database it uses the nextval of the sequence set for that column. Assuming you are using PostgreSQL (I believe for other databases it would be similar) you have an Employee table

CREATE TABLE public.Employee (
    number serial NOT NULL,
    nane varchar NULL
);

And because the decorator @PrimaryGeneratedColumn() a sequence employee_id_seq will also be created. You can check its latest value by running

select last_value from employee_id_seq; 

When your application creates two records it uses the sequence to generate the new ids, changing the employee_id_seq last_value value. With two records in the database the new last_value will be updated to 2.

If you create a third record manually passing id = 3

INSERT INTO public.Employee (id, name) VALUES(3, 'new_name')

the sequence will not be used, and its last_value will be 2. Next time your application tries to insert a new record it executes an insert without passing the id, because it relies on the sequence to generate the value, like this

INSERT INTO public.Employee (name) VALUES('another_name');

or (not sure how TypeORM handles it exactly)

INSERT INTO public.Employee (id, name) VALUES(nextval('employee_id_seq', 'another_name')

The SQL above will use the last_value from the sequence and add 1, resulting in 3 and a duplicate key violation. So if you add a record manually you don't need set the id manually, you can run

INSERT INTO public.Employee (name) VALUES('name') 

and the id will be auto-generated using the sequence.