kysely-org / kysely

A type-safe typescript SQL query builder
https://kysely.dev
MIT License
10.8k stars 275 forks source link

add mssql's `output` clause support. #687

Closed igalklebanov closed 5 months ago

igalklebanov commented 1 year ago

Hey 👋

Now that mssql dialect is in the core, it's time to pick and choose which clauses and keywords should get support in Kysely's query API.

The OUTPUT clause, is pretty much returning clause, but with deleted and inserted (both are available in update queries so you could return new and old values) "tables" in context.

This one seems like an easy win. Without it, consumers will have to perform another query in inserts to get IDs.

movahhedi commented 5 months ago

I currently do it like this:

export async function GetInsertedIdIfUndefined(
    trx: IKyselyTrx,
    insertResult: InsertResult,
) {
    if (insertResult.insertId === undefined) {
        // return db.fn.scopeIdentity();
        const queryResult: any = await sql`SELECT @@IDENTITY as id`.execute(trx);
        // const queryResult: any = await sql`SELECT SCOPE_IDENTITY() as id`.execute(db);
        return queryResult.rows?.[0]?.id;
    }

    return insertResult.insertId;
}

P.S.: Should db.fn.scopeIdentity() be implemented too?