Skip unnecessary select queries on create/update/delete

[hrasekj]

Problem

Prisma is always trying return value from update/create/delete actions. But you don't need every time the returned value, therefore SELECT query is wasted.

Suggested solution

Would be nice to have option not to do select query, like so:

await prisma.create({
    data: {
        ...
    },
    select: false
});

[hrasekj]

For example, I'm working on identity server, where I store user login and tokens.

For example login endpoint returns accessToken a refreshToken. refreshToken is saved in database, but generated by app, so no SELECT query is needed.

const loginRequestHandler = async (request) => {
    const userId = ...
    const accessToken = ...
    const refreshToken = hash(randomString());

    await prisma.userToken.create({
        data: {
            id: refreshToken,
            userId,
            type: TokenType.AUTH,
            dateValidTo: add(new Date(), { months: 6 }),
        }
    });

    return { accessToken, refreshToken };
}

  prisma:client Prisma Client call: +141ms
  prisma:client prisma.userToken.create({
  prisma:client   data: {
  prisma:client     id: '8aec70970d150fe29fec4671b2e39447657753378524ad3befa6f32584f151214642b726b09dfb3dc94f27a6d28c50decbf319643582a5a5cd136ce8838b45e6',
  prisma:client     userId: '59357600-a486-4148-a709-230aa6ac697d',
  prisma:client     type: 'AUTH',
  prisma:client     dateValidTo: new Date('2022-01-05T12:19:15.161Z')
  prisma:client   }
  prisma:client }) +0ms
  prisma:client Generated request: +0ms
  prisma:client mutation {
  prisma:client   createOneUserToken(data: {
  prisma:client     id: "8aec70970d150fe29fec4671b2e39447657753378524ad3befa6f32584f151214642b726b09dfb3dc94f27a6d28c50decbf319643582a5a5cd136ce8838b45e6"
  prisma:client     userId: "59357600-a486-4148-a709-230aa6ac697d"
  prisma:client     type: AUTH
  prisma:client     dateValidTo: "2022-01-05T12:19:15.161Z"
  prisma:client   }) {
  prisma:client     id
  prisma:client     userId
  prisma:client     type
  prisma:client     dateCreated
  prisma:client     dateValidTo
  prisma:client   }
  prisma:client }
  prisma:client  +1ms
prisma:query BEGIN
prisma:query INSERT INTO `admin-local`.`UserToken` (`id`,`userId`,`type`,`dateCreated`,`dateValidTo`) VALUES (?,?,?,?,?)
prisma:query SELECT `admin-local`.`UserToken`.`id`, `admin-local`.`UserToken`.`userId`, `admin-local`.`UserToken`.`type`, `admin-local`.`UserToken`.`dateCreated`, `admin-local`.`UserToken`.`dateValidTo` FROM `admin-local`.`UserToken` WHERE `admin-local`.`UserToken`.`id` = ? LIMIT ? OFFSET ?
prisma:query COMMIT

[kannavue]

Is it any update on the performance for avoiding multiple query for just one query to update ?

[kannavue]

I was updating a row in a table but because of mapping it creates

3 Select query (completely I don't need it),
2 Update query (it depends on the data)

Any way to optimize it? Because of that response time was take 4seconds and more depending upon latency.

Prisma looks good to me. But as per performance(update, delete, updateMany) I can't say anything. Suggest me some solution.

[darthmaim]

How is the PR prisma/prisma-engines#4595 that closed this issue related? The PR just optimized delete queries by using returning instead of and additional select.

This issue is not about optimizing select statement for delete queries. It is about often not needing the result at all. It also didn't change create or update queries.

[paulintrognon]

Same issues (sill open) :

• Select none in update() #6252
• Add option to skip unnecessary SELECT after create() #4246

[laplab]

@darthmaim Thank you for pointing that out, we should have communicated that better.

There are two layers here:

1. Removing unnecessary SELECT query in favour of using RETURNING
2. Not returning any data at all

The reason prisma/prisma-engines#4595 closed that issue is that we support (1) since Prisma 5.1.0 for create and update queries. Delete queries were the only ones remaining.

We saw this ticket first of all as an optimisation opportunity. So once (1) was complete, we decided to close it.

The question of implementing (2) remains open, as well as the issues @paulintrognon referenced.

[renatocron]

Usually, for select at least, I add select: {id: true} just to reduce the payload, as I don't think prisma/javascript has any way to know if the caller will use or not the value (I don't think exists something like https://perldoc.perl.org/functions/wantarray in js)