Prisma

Hi everyone, Is there a way to use aggregate and create in single query.
Here I want to achieve below with prisma
*INSERT INTO user (name, custom_id) VALUES ("someName",  (SELECT IFNULL(MAX(custom_id) + 1,1) from user where company_id = someId))*

<@U027KNS5J2E> :wave:
This isn’t possible in a single query natively in Prisma, You would need to use the above raw query via `prisma.$executeRaw`.

As for this implementation, I would recommend either using a transaction for this or using a random `uuid` instead of incrementing numbers as this would cause conflicts if multiple parallel inserts are made.

This is because it relies on the max value of the last record and that could be same for two parallel requests.

<@UESADRKMY>  Record inserted in db using below query but not getting inserted record in assigned variable.
```const user = await ctx.prisma.$queryRaw(
  `INSERT INTO user (name, custom_id) VALUES ("someName",  (SELECT IFNULL(MAX(custom_id) + 1,1) from user where company_id = someId))`
)```
Here getting user as []. Also I tried without custom_id still getting empty array in variable.

`prisma.$executeRaw` gives number of affected raws but I need inserted record id for further query in resolver

You can use:
```const user = await ctx.prisma.$queryRaw(
  `INSERT INTO user (name, custom_id) VALUES ("someName",  (SELECT IFNULL(MAX(custom_id) + 1,1) from user where company_id = someId)) returning *`
)```
This will return the values created.

<@U027KNS5J2E> :wave:
We have introduced long running transactions that could be useful for your use case and dependent writes :slightly_smiling_face:
<https://github.com/prisma/prisma/releases/tag/2.29.0>