How to upsert records where columns are arrays

[wltrup]

Hello there,

I have the following struct and table,

@Table
struct SequenceCount: Identifiable {

   let id: UUID

   @Column(as: [Int].JSONRepresentation.self)
   let sourceSequence: SourceSequence

   @Column(as: [Int].JSONRepresentation.self)
   let targetSequence: TargetSequence

   let count: Int
}

which represents counts of 'transitions' from a source sequence (a short array of small integers) to a target sequence (another short array of small integers). (Actually, the [Int]s are Tagged types but I'm simplifying things a bit here.)

If all I needed to do were insertions, then this

for value in values {
   let seqCountDraft = SequenceCount.Draft(
      sourceSequence: value.sourceSequence,
      targetSequence: value.targetSequence,
      count: value.count
   )
   try SequenceCount.insert { seqCountDraft }
      .execute(db)
}

works, where values is of type [SequenceCount].

But I also need to do updates, something like

for value in values {
   try SequenceCount
      .update { $0.count += value.count }
      .where {
         $0.sourceSequence.eq(value.sourceSequence) &&
         $0.targetSequence.eq(value.targetSequence)
      }
      .execute(db)
}

but that doesn't compile. Also, the record to be updated isn't guaranteed to exist, in which case the update should become an insertion.

So, I have two questions:

• what is the correct way to write the where clause?
• how do I combine the insertion and the update into an upsert?

Many thanks in advance.

[mbrandonw]

Hi @wltrup, you should be able to do #bind(array) anywhere you need to use an array of values in the query.

And upserts are only for a conflict of the primary key. Seems like you are wanting to update on sourceSequence and targetSequence which are not a conflict target unless they have a unique index on them. So I think you just have to do this as separate steps.

[wltrup]

Hi @mbrandonw, thanks for the exceedingly fast reply. :)

This compiles fine now:

for value in values {
   try SequenceCount
      .update { $0.count += value.count }
      .where {
         $0.sourceSequence.eq(#bind(value.sourceSequence)) &&
         $0.targetSequence.eq(#bind(value.targetSequence))
      }
      .execute(db)
}

As for the upsert, what if I compute the id from the arrays, like this:

@Table
struct SequenceCount: Identifiable {

   let id: Int

   @Column(as: SourceSequence.JSONRepresentation.self)
   let sourceSequence: SourceSequence

   @Column(as: TargetSequence.JSONRepresentation.self)
   let targetSequence: TargetSequence

   let count: Int

   init(sourceSequence: SourceSequence, targetSequence: TargetSequence, count: Int) {
      self.id = sourceSequence.hashValue ^ targetSequence.hashValue
      self.sourceSequence = sourceSequence
      self.targetSequence = targetSequence
      self.count = count
   }

}

That should work, right? The update then becomes

for value in values {
   try SequenceCount
      .update { $0.count += value.count }
      .where { $0.id == value.id }
      .execute(db)
}

but I'm still not sure how to do the upsert. How can I detect that the update 'failed' (because the record to be updated didn't exist at the time of the update) so that I can insert a new record with the updated value?

I'm thinking of something like this:

for value in values {
   if value.id exists in the SequenceCount table {
      // update
      try SequenceCount
         .update { $0.count += value.count }
         .where { $0.id == value.id }
         .execute(db)
   } else {
      // insert
      let seqCountDraft = SequenceCount.Draft(
         sourceSequence: value.sourceSequence,
         targetSequence: value.targetSequence,
         count: value.count
      )
      try SequenceCount.insert { seqCountDraft }
         .execute(db)
   }
}

This is probably the most basic of questions but how do I implement the condition 'value.id exists in the SequenceCount table' ?

Thanks again.

[mbrandonw]

This won't work:

self.id = sourceSequence.hashValue ^ targetSequence.hashValue

Hash values are not stable across launches of the app (nor are they guaranteed to be unique).

This is probably the most basic of questions but how do I implement the condition 'value.id exists in the SequenceCount table' ?

Something like this:

if try SequenceCount.find(value.id).fetchOne(db) != nil {
  …
}

You could also just perform the update and check how many rows were changed, and if none were do an insert:

let didUpdate = try SequenceCount
  .find(value.id)
  .update { $0.count += value.count }
  .returning(\.id)
  .fetchCount(db) == 1
if !didUpdate {
  // insert
}

And there are some advanced tricks you could probably do with CTEs and "INSERT … SELECT …" to do all of this in a single query, but if extreme performance isn't a concern right now you could just do the multiple queries.

[wltrup]

Hash values are not stable across launches of the app (nor are they guaranteed to be unique).

Ah... indeed. I hadn't thought of that. Thank you for saving me from a huge potential headache!

I like your suggestion that uses didUpdate. Short and sweet.

Thank you once again. 👍