How to do upsert?

[halvardssm]

Hi! I was wondering if you have an example of doing upsert (prisma style) using kvdex?

[oliver-oloughlin]

There really isn't an exact example of upsert. The closest you would get is the write() or update(), updateMany(), updateByPrimaryIndex() and updateBySecondaryIndex() methods. write() will set a new document entry if no document with the given id exists, or replace the value of an existing document, while the update... methods will only update the value of existing documents (with either full or partial updated values), by id or by an index. None of these truly do the same as upsert, as upsert (from my understanding) could set a new entry not just by id but by any value.

Example of write():

import { model, collection, kvdex } from "kvdex"
const kv = await Deno.openKv()
const db = kvdex(kv, {
  users: collection(model<User>()),
})

// Before any existing document by id is set, this creates a new entry
await db.users.write("user_id", {
  ...user values
})

// Next query updates the existing entry with a new value
await db.users.write("user_id", {
  ...other user values
})

The update... methods exclusively work on existing documents, so they don't really fit within the scope of upsert.

You could possibly accomplish an upsert-ish type of operation doing the following:

// Update documents with a "where" equivalent filter
const { result } = await db.users.updateMany(<new value>, { filter: (doc) => <some predicate> })

// If no documents were updated, insert a new entry
if (result.length === 0)) {
  await db.users.add(<new value>)
}

None of these really provide the exact same functionality or equal simplicity to upsert. It's an interesting question though, I might work on supporting it. I've never had a use case for using it myself, but it obviously exists for a reason.

[halvardssm]

@oliver-oloughlin Thanks for getting back to me! My observations were also the same in this regard.

as upsert (from my understanding) could set a new entry not just by id but by any value.

I am not sure about that, an upsert should only be possible to do on unique indexes. Otherwise, it would just be an "add" no? Upsert in its base form (taking Postgres as an example) is a CREATE with a ON CONFLICT ... DO ....

I think a simple wrapper could be easy to implement by using the KV built-ins (2 operations at max). Of course my example underneath doesn't take into account every case, but it is just as a thought as to how it can be achieved.

simplified example:

function upsert({
  create,
  update,
  key,
}){
  const existingEntry = await kv.get(key)
  if(existing.versionstamp === null){
    return await kv.set(key, create)
  } else {
    return await kv.set(key, { ...existingEntry, ...update })
  }
}

It's an interesting question though, I might work on supporting it. I've never had a use case for using it myself, but it obviously exists for a reason.

Would be great to get support for it! I would create a PR if I would have some more time on my hands, but maybe I'll find it one of the days!

[oliver-oloughlin]

@halvardssm I see. Then I believe this shouldn't bee too hard to implement. I'm currently working on supporting the newly introduced kv.watch functionality. After that I can work on supporting upsert.

[oliver-oloughlin]

I have added support for upsert in the latest release (v0.27.0).

I hope this helps or fits what you had in mind. Note that the signature of the method is a little different to what you would expect from Prisma, as I wanted to keep the semantics in line with the rest of the API (e.g. "set" instead of "create", and "id" or "index"). You can find an example in the README.