Drizzle Team•4mo ago

Figuring out where a query short-circuts for a PUT endpoint

I'm building a PUT endpoint in my API, which allows you to add properties to an entity. I do this by having a table of properties, where the property's primary key is a composite of the parent entity's ID (which references the entity table) and the property key. The endpoints desired behavior is the following insert

    await db
      .insert(properties)
      .values({ // Insert into the properties table the property sent to the API
        entityId, 
        key,
        value,
        static: isStatic,
      })
      .onConflictDoUpdate({ 
        target: [ // If there is already a property on that entity with that key
          properties.entityId,
          properties.key
        ],
        targetWhere: eq(properties.static, false), // As long as the property isn't marked as static
        set: { value }, // Update the value as follows
      });

    await db
      .insert(properties)
      .values({ // Insert into the properties table the property sent to the API
        entityId, 
        key,
        value,
        static: isStatic,
      })
      .onConflictDoUpdate({ 
        target: [ // If there is already a property on that entity with that key
          properties.entityId,
          properties.key
        ],
        targetWhere: eq(properties.static, false), // As long as the property isn't marked as static
        set: { value }, // Update the value as follows
      });

This means the API can respond 4 possible ways (as for the specification of PUT): * 201 Created - The property didn't exist and it was created * 204 No Content - The property already existed and it was updated * 404 Not Found - The parent entity wasn't found, so the backend couldn't give it a property * 409 Conflict - The property already exists an is marked static therefore it cannot be updated. That means that if the query

    await db
      .insert(properties)
      .values({
        entityId, // Fails here, because entityId references a nonexistent entity, return 404
        key,
        value,
        static: isStatic,
      }) // Succeeds here before reaching the conflict, because the property didn't exist, return 201
      .onConflictDoUpdate({ 
        target: [
          properties.entityId,
          properties.key
        ],
        targetWhere: eq(properties.static, false), // Fails here because the property exists and is static, return 409
        set: { value },
      }); // Succeeds here because the property existed and was updated, return 204

    await db
      .insert(properties)
      .values({
        entityId, // Fails here, because entityId references a nonexistent entity, return 404
        key,
        value,
        static: isStatic,
      }) // Succeeds here before reaching the conflict, because the property didn't exist, return 201
      .onConflictDoUpdate({ 
        target: [
          properties.entityId,
          properties.key
        ],
        targetWhere: eq(properties.static, false), // Fails here because the property exists and is static, return 409
        set: { value },
      }); // Succeeds here because the property existed and was updated, return 204

Obviously, this behavior is trivial if I were to split this into multiple queries, but for efficiency's sake I'd like to keep this as tight as possible. Is it possible to have drizzle tell me where the query stopped without splitting it and using application logic? And if not, whats the most efficient way to replicate this behavior?

3 Replies

rphlmr ⚡•4mo ago

The 404 could be deducted by a db reference error 201 and 204, maybe comparing value you try to set and value you get with “returning” you can add to the query. 409 maybe a db error about no matching target conditions. You could try each cases and analyze what the db drivers throws / the query returns

MilesOP•4mo ago

201 and 204, maybe comparing value you try to set and value you get with “returning” you can add to the query.

how would i do this? after updating they'd both end up equaling each other (this is put so in theory I'm dropping and replacing; this approach is just an optimization essentially)

409 maybe a db error about no matching target conditions.

hmm ok

You could try each cases and analyze what the db drivers throws / the query returns

this might be the best approach yesh

rphlmr ⚡•4mo ago

https://drizzle.run/dsjwup8crfnx11ydp5k0ut4i Something like that

Drizzle Run

Upsert with result control - Drizzle Run

Gaming

Programming

Figuring out where a query short-circuts for a PUT endpoint