How bad an idea is it to use structural types to represent DB query results?

Question
1

I’ve been looking for a simple way to do queries (I’m using com.twitter.finagle.mysql.Client, but the specifics aren’t important for this question) and potentially one method I like is using structural types to represent the results. This is because I want to actually craft proper queries with joins and fetching exactly what I need, instead of grabbing all columns from the tables and calling it a day. Structural types seem like a good fit for this flexibility of each query returning exactly what is needed, without having to define a hundred and one types beforehand.

For example,

trait DbService {
  import com.twitter.finagle.mysql.Client
  import com.twitter.finagle.mysql.Parameter
  import com.twitter.finagle.mysql.Parameter._

  def getAccount(id: Long) = client
    .prepare("""
select a.id, a.name, c.name as contact_name, c.phone_no
from account as a
inner join contact as c on (a.billing_contact_id = c.id)
where a.id = ?""")
    .select(id) { row =>
      new {
        val id = row.getLong("id").get
        val name = row.getString("name").get
        val contactName = row.getString("contact_name").get
        val phoneNo = row.getString("phone_no")
      }
    }

  protected def client: Client
}

getAccount should return a Future[Seq[AnyRef{val id: Long; val name: String; ...}]]. (I would annotate the return type of course.)

I can wrangle this Seq of query result objects into a more ‘object-y’ shape i.e. creating the actual sub-objects and wiring them up properly. The only thing that’s giving me pause is the warning about reflective calls:

Even where reflection is available, reflective dispatch can lead to surprising performance degradations.

So, any ideas how bad this could get in my proposed use case? These are database queries so I properly won’t be doing massive numbers of them, but it would be a ‘respectable’ amount.

2

Well, the important thing to keep in mind is that the performance hit here is going to be when your application code uses the resulting object. Every field access is a reflective call – I’m not an expert on this, but I suspect that’s probably orders of magnitude slower than a normal field access.

So I suspect the performance hit during the DB accesses themselves, when you are constructing this object, is minor compared to the DB fetch itself. But if you use the resulting objects a lot, it could be a significant issue. How much of an issue depends on how your code works. (It’s quite plausible that all of the usage of a resulting object is still dwarfed by the time taken by the DB fetch, but it depends on the application.)

Mind, the idea makes sense – indeed, it’s exactly the motivating example for the rewrite of structural types in Scala 3, which allows you to implement the same concept much more efficiently and precisely. But in Scala 2, I’d recommend doing this with a bit of caution.

2 Likes
3

Interesting, thank you! After fleshing out the idea a bit more I think it wouldn’t be testable in the way I would want to be able to test it. E.g., imagining that I implement the transformation in the GetAccount object:

trait DbService {
  import com.twitter.finagle.mysql.Client
  import com.twitter.finagle.mysql.Parameter
  import com.twitter.finagle.mysql.Parameter._

  def getAccount(id: Long) = client
    .prepare(GetAccount.Sql)
    .read(id)
    .map(resultSet => GetAccount.decode(resultSet.rows))

  protected def client: Client
}

Now the DbService#getAccount method would be returning a Future[AnyRef{...}] and I wouldn’t be able to set up a mock DbService implementation that would return this same type for testing. So I guess right now my only option is to define all the nominal types.

4

If you don’t mind another dependency, Shapeless’s record types are similar to the Scala 3 structural types rework. They don’t provide the field-like access, instead using string keys, but the keys are compile-time checked.

1 Like
5

Thanks, I’m open to using Shapeless if I already have it as a transitive dep. I’ll check.