Monday, April 22, 2013

Storing UUIDs with Anorm

I am not a big fan of Object Relational Mappers (ORMs). It is too easy to turn a simple query into a massive CPU-eating hydra - complete with 7 outer joins and no index usage. When I write code, I try to write it in such a way that the worst things to do are the hardest. That way, when I'm in a rush, I still write good code.

Enough about my rant: enter Anorm. I've been developing software on the Scala / Play stack for over a year now. Anorm is currently the supported database connector. It is a thin layer on top of JDBC that takes out all of the ugliness, but leaves the manual SQL writing goodness.

One of the things that Anorm does not do is read and write UUIDs. In 200 lines of copy-paste code, you'll have Anorm reading and writing UUIDs like a pro. A side effect of this code is that Anorm will also be able to handle byte arrays.

Wednesday, April 17, 2013

"In" Clause with Anorm

At Lucidchart, two coding styles have been adopted because Anorm doesn't support the 'in' clause.

  1. Code that allows for, or is conducive to, SQL injection.

    SQL(""" SELECT * FROM users WHERE id IN (%s) """.format(userIds.mkString(",")))

  2. Code that is hard to follow and duplicate.

    val params = List(1, 2, 3) 
    val paramsList = for ( i <- 0 until params.size ) yield ("userId" + 
    i) // results in List("userId0", "userId1", "userId2") 
    SQL(""" SELECT * FROM users WHERE id IN ({%s}) """.format(paramsList.mkString("},{")) // 
    produces "id in ({userId0},{userId1},{userId2})" 
    .on( // produces Map("userId0" -> 1, 
    "userId1" -> 2, ...)
Ugly much? The madness ends here with code samples.