Raw SQL shortcuts similar to get, take, takeOptional

[IlyaSemenov]

As a developer, sometimes I need to fetch a single value with raw SQL, such as the next value in sequence. Currently, the proposed way is:

const walletId = (await db.$query<{ wallet_id: number }>`select nextval('wallet_id_seq') as wallet_id`).rows[0].wallet_id

This works, but is somewhat ugly.

Another approach is abusing db.table.get:

const walletId = await db.user.get(sql<number>`nextval('wallet_id_seq')`)

This also works, and much simpler to read, however it's coupled to db.user (or some other table) for no reason at all. (Also, it needs at least one record in the respective table).

I was thinking, what if there was not only db.$query but also db.$get and possibly db.$take / db.$takeOptional? Or perhaps some common raw SQL wrapper with "usual" ORM methods like db.$raw.select/get/take/...(something else?)...?

[romeerez]

This needs some thinking about what to add, how to add, what to depreciate and remove, I'd like to postpone this for later, since it's already doable just somewhat messy.

Ideally:

• get for a single value, throws when not found, and I'm still considering renaming it to getOrThrow for explicitness
• pluck for a flat array of values
• exec for no result
• etc.
• transform if you wish to transform the value in a callback. No problem to map the result, but the chained transform may be a bit more declarative.
• type to use a column type for both typing and parsing, so decimal is parsed in a way how it's configured (Decimal lib in your case).

This all should be taken into account, ideally.