Get ID after inserting row

[Meldiron]

Issue type:

[X] Question
[ ] Bug report
[X] Feature request
[ ] Documentation issue

Database system/driver:

[ ] Postgres
[ ] MSSQL
[X] MySQL
[ ] MariaDB
[ ] SQLite3
[ ] Oracle
[ ] Amazon Redshift

typed-knex version:

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

Is it possible to get ID of the last inserted row? Knex itself has .returning('id') but typedKnex always returns void with insertItem so I could not find a way to achieve this.

[Meldiron]

#36

Looks awesome :O I will try to implement it tomorrow.

[wwwouter]

Thanks!
Available in 4.3.0 https://github.com/wwwouter/typed-knex#insertItemWithReturning

[Meldiron]

I have tested it out and got 2 problems. My console log is:

(in warning colors) .returning() is not supported by mysql and will not have any effect.
TEST 4

My code is:

const userData = await SQLManager.typedKnex
  .query(TableCustomers)
  .insertItemWithReturning(
    new TableCustomers({
      email: body.email,
      password: encryptedPass,
      agreeNewsletter: body.agreeNewsletter,
    }),
    ['id']
  );

console.log('TEST', userData);

Meanwhile, the autocomplete is:

• Why does it say .returning() is not supported? Is this function deprecated, should we be using something else?
• Why does autocomplete suggest .id as a number if the object itself seems to be the number?

[wwwouter]

• It seems that returning is only supported by PostgreSQL, MSSQL, and Oracle databases: https://knexjs.org/#Builder-returning

• The function returns Pick<TableCustomers, 'id'> and not number.

[bgilman-nyk]

@wwwouter You should also implement this for update in addition to insert. It's been very useful for us so far, but we've been having to make 2 queries on update. updateItemWithReturning would be huge!

[wwwouter]

@bgilman-nyk I added this to v4.5.0. Can you check if this is what you actually need?

[bgilman-nyk]

@wwwouter I see the change on your repo. Exactly what we need! But when I grabbed latest from npm, it looks like while the src of the package has been updated, the changes are still not reflected in dist

[wwwouter]

Not sure what went wrong. Can you try v4.5.1?

[bgilman-nyk]

Fantastic, thank you!

[wwwouter]

@Meldiron Can this be closed?

[bam365]

@wwwouter I think the types of these functions don't match what they actually return. For example, if I have some table with an aliased ID field:

@Table("foo")
class Foo {
    @Column({ primary: true, name: "foo_id" })
    id: number
    
    // ...
}

If I do typedKnex.query(Foo).insertItemWithReturning({ ... }, ["id"]), the type on that is something close to Pick<Foo, "id"> but what I actually get is something of type { foo_id: number }.

Seems like either the types should be changed to reflect that, or the functions should be changed to alias the returned columns back to their property names

[wwwouter]

I see the alias mapping is omitted. Will fix.

[wwwouter]

@bam365 Could you check to see if it works in v4.7.0?