Miles Posts - Answer Overflow

Miles

Explore posts from servers

•Created by Miles on 8/11/2024 in #help

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?

5 replies

DTDrizzle Team

•Created by Miles on 8/9/2024 in #help

Unifying a JSON column's return type with `drizzle-zod`'s `Json`

By default, drizzle-zod gives json columns in a table the Json type and schema, a utility type it defines (unexported) in the library. Drizzle itself, however, returns a never value when querying for the value of a json column unless you use the .$type function to give it a type explicitly. What's the best way to bridge this gap such that drizzle-zod generated schema matches the query's returned type while losing as little type safety as possible?

2 replies

Gaming

Programming