Returning values with useKnexQueryBuilder

[jellelicht]

Issue type:

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

Database system/driver:

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

typed-knex version:

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

Knex.js version: 1.0.4

Steps to reproduce or a small repository showing the problem: Given that I have a table mytable, with a unique string name and a Numeric(12, 3) counter. How can I have an incrementing update that returns all records that were changed?

I tried using useKnexQueryBuilder, which gives a correct query, but cannot be executed without

@Table('mytable')
export class MyTable {
    @Column({ primary: true }) public id!: number;
    @Column() public name!: string;
    @Column() public counter!: string; // numeric(12, 3)
}

const handle = new TypedKnex(knex({ client: 'pg', connection: <my-db>}));
const q = handle
    .query(MyTable)
    .where('name', 'hello')
    .useKnexQueryBuilder((query) => query.increment('counter', 10))
    .toQuery();
console.log(q);

Gives:

update "mytable" set "counter" = "counter" + 10 where "mytable"."name" = 'hello'

Which is perfect, except for the fact that I still need to run this query. Using execute works, but returns undefined, which is not what I want. How can I combine useKnexQueryBuilder with something that does not return undefined, but just unknown or any?

[jellelicht]

Got something that works, sorry for the noise. Sharing it here for the impatient user wondering the same :smile:

const q = handle
    .query(MyTable)
    .where('name', 'hello')
    .useKnexQueryBuilder((query) => query.increment('counter', 10).returning('*'))
    .getMany();

[wwwouter]

Thanks for the working code!

BTW, right now there is typed support for updating one item:

const q = handle
            .query(MyTable)
            .where("name", "unique name")
            .useKnexQueryBuilder((query) => query.increment("counter", 10))
            .updateItemWithReturning({}, ["id"]);