Typeorm: Inserting id (primary column) from code.

[PavanBahuguni]

Issue type:

[ ] question
[ ] bug report
[ x ] feature request
[ ] documentation issue

Database system/driver:

[ ] cordova
[ ] mongodb
[ ] mssql
[ ] mysql / mariadb
[ ] oracle
[ x ] postgres
[ ] sqlite
[ ] sqljs
[ ] react-native

TypeORM version:

[ x ] latest
[ ] @next
[ ] 0.x.x (or put your version here)

Steps to reproduce or a small repository showing the problem:

I have a table with id as PrimaryGeneratedColumn. In some scenario i need to give the id in code and dont want to use the autogenerated id. Even if i give id, it is being ignored.

[krislefeber]

What is your use case for this? If you don't want it to auto-generate, then don't use the PrimaryGeneratedColumn. If you manually set this id, the auto-increment could break on future entities that try to auto-generate an id with that value. I think it would be easier to have a separate field for the cases where you want to override the value.

[PavanBahuguni]

I have a set of masterdata which have fixed ids, i have set the autogenerate sequence to start from after the ids which are allocated to masterdata. I want the masterdata to be inserted with the specific ids. Other data can get the db generated ids. Is it possible to do this?

[pleerock]

I think this is duplicate of #2199 ?

[teyou]

FYI Postgres has different way of remembering AUTO_INCREMENT value.
My PR (https://github.com/typeorm/typeorm/pull/2218) doesn't cater for Postgres database.

[Tiketti]

I would also like to see this behavior reverted. Our use case is related to tests and inserting entities into the database with preset ID's and asserting API returns the right values. An example in pseudo code:

const person = new Person();
person.id = 1;
personRepo.save(person);
...

describe('GET /api/person/{id}', async () => {
  test(('should return person with correct id'), () => {
    const person = await fetch('/api/person/1');
    expect(person).not.toBeUndefined();
  });
}

Above test fails, because person is inserted with an autoincremented id instead of 1 and can't be found.

[hauau]

As a temporary workaround for MySQL/MariaDB/InnoDB setting autoincrement value for table and inserting in the desirable order is possible. This query rebuilds table and requires that no existing id is bigger than a new autoincrement value, you should probably truncate it beforehand.

ALTER TABLE table AUTO_INCREMENT = 1

[pleerock]

@teyou don't you think that adding similar check for postgres is going to solve this issue?

[teyou]

@pleerock I believe not for Postgres.

let's assume we have this entity

export class bar {
    @PrimaryGeneratedColumn()
    id: number;

    @Column()
    description: string;
}

and we explicitly run below insert query in sequence
INSERT INTO bar(id, description) VALUES(2,'aaa'); <-- id = 2, passed
INSERT INTO bar(description) VALUES('bbb'); < -- id = 1, passed
INSERT INTO bar(description) VALUES('ccc'); <-- id = 2, failed... ERROR: duplicate key value violates unique constraint "INDEX_NAME"

I don't have fix for postgres now, perhaps someone else can come up with a better fix for postgres.

[PavanBahuguni]

@teyou Is't that behaviour a choice of postgres?

[teyou]

@PavanBahuguni hmm sorry i'm not that familiar with postgres. can't comment much on it

[RIKIL]

@teyou
This is the type of insert you might need to use:
INSERT INTO bar values(NEXTVAL('bar_id_seq'),'bbb')

Each time you run this it inserts a new ID +1 greater than what was in the table before the insert.

reference: https://stackoverflow.com/questions/12336789/in-postgresql-how-do-you-insert-into-a-table-with-only-one-identity-column

[andreasrueedlinger]

I have a similar scenario as @Tiketti: I run unit tests on sqlite with fixed id values for better test fixture data creation and assertions. For end-to-end tests I use mysql and thanks to the #2218 this works in mysql (what is the motivation tough to just revert the introduced behavior for mysql?)
Instead of just reverting the logic for certain database types I would rather like an option to enable/allow fixed values for generated columns in the environment (eg. just for the tests). Like this it is more transparent and controllable and you can delegate the responsibility for any side-effects to the end-user of the framework!

[andreasrueedlinger]

@pleerock What do you think about my proposition to make it configurable in connection options to allow fixed ids set by code instead of the current intransparent expections?

[anodynos]

Any progress on Postgres on the silly Identity Insert problem?
I'm switching to MySQL only for this reason :-(

We need to be able to do ID inserts not just for tests, but also for dealing with legacy data, migrations etc.

So, is there any PR for this, and any intention on merging (or remaking it)?

[andreasrueedlinger]

@anodynos see my PR here: https://github.com/typeorm/typeorm/pull/2722
But I did not get any feedback so far for my proposition.
For the time being I am using the following workaround in my test helper (note that this is pure js):

const typeorm_1 = require("typeorm");

// patch typeorm to allow fixed ids for generated columns
typeorm_1.InsertQueryBuilder.prototype.getInsertedColumns = function () {
  var _this = this;
  if (!this.expressionMap.mainAlias.hasMetadata)
      return [];
  return this.expressionMap.mainAlias.metadata.columns.filter(function (column) {
      // if user specified list of columns he wants to insert to, then we filter only them
      if (_this.expressionMap.insertColumns.length)
          return _this.expressionMap.insertColumns.indexOf(column.propertyPath) !== -1;
      return true;
  });
};

[anodynos]

Thank you Andreas, but I don't think this solves the postgres-specific problem.
Postrgres doesn't support Identity Insert out of the Box like MySQL, or other databases do (see for example, how MSSQL does it for 18 years now, and how Postgress doesn't unless you use a bit of hackery https://stackoverflow.com/questions/21375651/set-identity-insert-postgresql)

I think we should have in TypeORM a translation of the setting to various DBs, even if they don't support it by default, TypeORM could just issue the right command before/after (like SET IDENTITY_INSERT my_table ON in MSQL or calling the next index function in Postgres) and do it behind the scenes.

The setting could be per @Column per @Entity or even Connection and perhaps per transaction with some option/method override.

I hope we'll see this soon so I can get back to Postgres :-)

[pleerock]

@anodynos are you sure its not supported by postgres, because looks like it does. And probably @andreasrueedlinger 's PR implement it, I just don't understand why we need a configuration option for it.

[anodynos]

Umed @pleerock thanks for your answer & amazing project!

Well, you're both right and wrong!

• [email protected] doesn't support it natively, only with some hackery - eg https://stackoverflow.com/questions/21375651/set-identity-insert-postgresql. (I was using P9.6 and I had to revert to mysql because of this)

• [email protected] supports it http://www.postgresqltutorial.com/postgresql-identity-column/

The problem is that every DB (& version) out there has a different way to enable it & use it (eg see the MS SQL Server way above). So the question is if TypeORM supports a declarative & DB agnostic way, to be able for user to enable (or disable) Identity Insert throughout a connection or a repo or a transaction/operation etc.

I think one of the points of a great ORM is to expose functionality transparently of DB differences...
Thanks for your considerations.
Best Regards, Angelos

[fwchen]

Any update for postgres?
insert identity for migrations..is every important.

[vlapo]

@pleerock I believe not for Postgres.

let's assume we have this entity

export class bar {
    @PrimaryGeneratedColumn()
    id: number;

    @Column()
    description: string;
}

and we explicitly run below insert query in sequence
INSERT INTO bar(id, description) VALUES(2,'aaa'); <-- id = 2, passed
INSERT INTO bar(description) VALUES('bbb'); < -- id = 1, passed
INSERT INTO bar(description) VALUES('ccc'); <-- id = 2, failed... ERROR: duplicate key value violates unique constraint "INDEX_NAME"

I don't have fix for postgres now, perhaps someone else can come up with a better fix for postgres.

@teyou why is this wrong behaviour? I mean if someone want to define id programatically he have to keep in mind it will break postgres "autoincrement" functionality.

[anodynos]

Any updates on this? Can TypeORM perform identity insert in Postgres, like it's done in MySQL etc?

I guess it would be as simple as having an option in@PrimaryGeneratedColumn() eg autoGenerate: 'default' | 'always' and pass this to Postgres 10.x like so:

column_name type GENERATED BY DEFAULT 

as described in http://www.postgresqltutorial.com/postgresql-identity-column/

I would really prefer to revert to Postgres instead of MySQL now that Postgres 10 supports it natively :-)

[JClackett]

Also would really like this feature to use postgres!

[RizkyArifNur]

any update for this issue? I would really like this feature to be implemented

[RizkyArifNur]

maybe this can help, you can disable auto-increment field before you inserting some data

async function disableAutoIncrementId(repo: Repository<any>) {
  repo.metadata.columns = repo.metadata.columns.map<ColumnMetadata>(c => {
    if (c.propertyName === 'id') {
      c.isGenerated = false
      c.generationStrategy = undefined
    }
    return c
  })
  await repo.query(
    `ALTER TABLE ${repo.metadata.tableName} ALTER COLUMN id DROP DEFAULT;`
  )
}