SQLInterpolation

What’s SQLInterpolation

SQLInterpolation is an SQL builder that utilizes string interpolation, available since Scala 2.10. Its usage is straightforward: simply embed values into the sql"" template without using #bind or #bindByName. This approach is quite efficient.

import scalikejdbc._

val id = 123
val member = sql"select id, name from members where id = ${id}"
    .map(rs => Member(rs)).single.apply()

// above code is same as follows:
// SQL("select id, name from members where id = {id}").bindByName("id" -> id)
//   .map(rs => Member(rs)).single.apply()

Don’t worry, this code is secure against SQL injection attacks. ${id} serves as a placeholder.

select id, name from members where id = ?

Natively-handled types

The following types are natively supported through SQL parameter binding, applicable to both SQL interpolation and explicit parameter binding:

• Numeric types: Int, Short, Long, Float, Double, java.math.{BigInt, BigDecimal}
• Time and date types: java.sql.{Date, Time, Timestamp}, java.util.Date, org.joda.time.{DateTime, LocalDateTime, LocalDate, LocalTime}, java.time.{ZonedDateTime, Instant, LocalDateTime, LocalDate, LocalTime}
• Other types with a straightforward SQL correspondence: java.sql.{Array, SQLXML}, java.io.InputStream (as a binary stream)
• Option – converted to the corresponding value for Some instances, and null for None.

Additionally, SQL interpolation unwraps instances of scala.collection.Traversable and java.lang.Iterable, converting each element into a bound parameter separated by either backticks or commas, ensuring behavior as expected:

val ids = Seq(123, 124, 125)
sql"SELECT * FROM members WHERE id IN (${ids})"

Other values are directly mapped as Objects.

SQLSyntax

SQLSyntax is not a binding parameter but a part of the SQL. You can create a SQLSyntax object with sqls"“ String interpolation.

val ordering = if (isDesc) sqls"desc" else sqls"asc"
val members = sql"select id, name from members order by id ${ordering} limit 10"
  .map(rs => Member(rs)).list.apply()

${ordering} will be directly converted to a part of SQL.

select id , name from members order by id desc limit 10

Don’t worry again, this code is secure. sqls"" always treats external input values as binding parameters.

SQLSyntaxSupport

SQLSyntaxSupport is just shy of being an ORM. It provides a DRY method to write SQL effectively.

Firstly, integrate the SQLSyntaxSupport[A] trait into the companion object of A, and specify tableName along with the #apply method to map values from the ResultSet. The ResultName[A], which may be unfamiliar, will be explained later.

NOTE: Utilizing case classes for entities (like Group and GroupMember) generally works well. However, if you use regular classes for entities, be mindful of entity equality. For more details, refer to: documentation/one-to-x.html

import scalikejdbc._

case class Group(id: Long, name: String)
case class GroupMember(id: Long, name: String,
  groupId: Option[Long] = None, group: Option[Group] = None)

object Group extends SQLSyntaxSupport[Group] {

  // If you need to specify schema name, override this
  // def table will return sqls"public.groups" in this case
  // Of course, schemaName doesn't work with MySQL
  override val schemaName = Some("public")

  // If the table name is same as snake_case'd name of this companion object,
  // you don't need to specify tableName explicitly.
  override val tableName = "groups"

  // If you use NamedDB for this entity, override connectionPoolName
  //override val connectionPoolName = 'anotherdb

  def apply(g: ResultName[Group])(rs: WrappedResultSet) =
    new Group(rs.long(g.id), rs.string(g.name))
}

object GroupMember extends SQLSyntaxSupport[GroupMember] {
  def apply(m: ResultName[GroupMember])(rs: WrappedResultSet) =
    new GroupMember(rs.long(m.id), rs.string(m.name), rs.longOpt(m.groupId))

  def apply(m: ResultName[GroupMember], g: ResultName[Group])(rs: WrappedResultSet) =  {
    apply(m)(rs).copy(group = rs.longOpt(g.id).map(_ => Group(g)(rs)))
  }
}

Use them as follows:

val id = 123

val (m, g) = (GroupMember.syntax("m"), Group.syntax("g"))
val groupMember: Option[GroupMember] = sql"""
  select
    ${m.result.*}, ${g.result.*}
  from
    ${GroupMember.as(m)} left join ${Group.as(g)} on ${m.groupId} = ${g.id}
  where
    ${m.id} = ${id}
  """
  .map(GroupMember(m.resultName, g.resultName)).single.apply()

While the code includes several ${...} placeholders, I trust you can grasp their meaning. Essentially, this code executes the following SQL statement.

select
   m.id as i_on_m, m.name as n_on_m, m.group_id as gi_on_m, g.id as i_on_g, g.name as n_on_g
from
  group_member m left join group g on m.group_id = g.id
where
  m.id = ?

It’s logical that ${m.result.*} translates to listing all the columns, and ${m.camelCase} converts to the corresponding snake_case column name. However, should you have any questions, I’m here to clarify specific points in the next section.

Why is ${m.result.*} transformed to listing all the columns?

Although column names are not explicitly defined, ${m.result.*} is converted to list all the column names. This is achieved by loading from metadata, which is cached upon the first access to the table. It’s possible to retrieve all column names as a Seq[String] value via columns.

If you need to manually define column names or require access to multiple databases, please define column names as follows:

object GroupMember extends SQLSyntaxSupport[GroupMember] {
  override val tableName = "groups_members"
  override val columns = Seq("id", "name", "group_id")
}

Why can we use undefined methods such as m.groupId?

With Type Dynamic (SIP-17) available since Scala 2.10.0, you can call undefined methods like m.groupId. Type Dynamic functions similarly to Ruby’s method_missing.

https://docs.google.com/document/d/1XaNgZ06AR7bXJA9-jHrAiBVUwqReqG4-av6beoLaf3U

When camel case fields are invoked, they are automatically transformed into underscore-separated column names. If a column name doesn’t exist, an InvalidColumnNameException will be thrown.

Furthermore, the case field name must match one of the primary constructor argument names of type A in SQLSyntaxSupport[A]. This rule is validated during the compilation phase with the help of Scala macros.

https://docs.scala-lang.org/overviews/macros/overview.html

If Type Dynamic does not suit your needs, you can alternatively use #field(String) or #column(String) to specify column names explicitly. Here are four examples demonstrating this approach, all of which achieve the same result:

m.groupId
m.field("groupId")
m.column("group_id")
m.c("group_id")

If you prefer not to convert field names to snake-cased column names, you can set useSnakeCaseColumnName to false as shown below:

object UserType extends SQLSyntaxSupport[UserType] {
  override val useSnakeCaseColumnName = false
  ...
}

In situations where you prefer not to expose column names to the application layer, you can override nameConverters. For example, if you wish to refer to the service_cd column as serviceCode, you can define a regular expression and the replacement name in nameConverters as follows. Since nameConverters operates on partial match retrieval, you can also simply specify mappings such as Map("Code" -> "cd").

case class Event(id: Long, name: String, serviceCode: Long)

object Event extends SQLSyntaxSupport[Event] {
  override val tableName = "events"
  override val columns = Seq("id" "name", "service_cd")

  // specify regular expression to match
  override val nameConverters = Map("^serviceCode$" -> "service_cd")
}

What is the difference between m.id, m.result.id and m.resultName.id?

Here’s an explanation of the differences between m.id, m.result.id, and m.resultName.id based on the Member.syntax("mm") API:

• m.groupId is generally used to reference the column directly in SQL, converting it to sqls"mm.group_id"
• m.result.groupId refers to the same column but with an alias for use in result processing, which is converted to sqls"mm.group_id as gi_on_mm"
• m.resultName provides a ResultName[Member] object
• m.resultName.groupId specifically accesses the aliased column name for result processing, resulting in sqls"gi_on_mm"

Additionally, using Member.as(m) translates to members m in SQL, contextualizing the Member table alias.

If you use Member.syntax(), the table name is included in aliases. For example, m.result.groupId becomes "members.group_id as gi_on_members". If you set it as Member.syntax("m"), m.result.groupId translates to "m.group_id as gi_on_m".

That covers all the rules of SQLSyntaxSupport. I believe this explanation will make the following code easier for you to understand.

val ids: List[Long] = sql"select ${m.result.id} from ${Member.as(m)} where ${m.groupId} = 1"
  .map(rs => rs.long(m.resultName.id)).list.apply()

By defining the #apply(ResultName[Member]) method, your #map operations become straightforward and efficient.

object Member extends SQLSyntaxSupport[Member] {
  override val tableName = "members"
  def apply(m: ResultName[User])(rs: WrappedResultSet) = {
    new Member(id = rs.long(m.id), name = rs.string(m.name))
  }
}

You can use the above code this way:

val m = Member.syntax("m")
val members = sql"select ${m.result.*} from ${Member.as(m)}".map(Member(m.resultName)).list.apply()
// select m.id as i_on_m, m.name as n_on_m from members m

If you only require column names for insert, update, or delete queries, use #column.{name} as shown below:

val c = Member.column
sql"insert into ${Member.table} (${c.name}, ${c.birthday}) values (${name}, ${birthday})"
  .update.apply()

Since #column is a member of SQLSyntaxSupport[A], you can utilize #column within the Member object:

object Member extends SQLSyntaxSupport[Member] {
  def create(name: String, birthday: LocalDate)(implicit s: DBSession = AutoSession): Member = {
    val id = sql"insert into ${table} (${column.name}, ${column.birthday}) values (${name}, ${birthday})"
      .updateAndReturnGeneratedKey.apply()
    Member(id, name, birthday)
  }

Use Case: SQLSyntaxSupport with table sharding

If you have multiple tables for the same entity, for example, orders_2011, orders_2012, and orders_2013:

case class Order(id: Long, productId: Long, customerId: Option[Long], createdAt: ZonedDateTime)

class OrderTable(val year: Int) extends SQLSyntaxSupport[Order] {
  // Be careful if you build tableName with input values
  // ScalikeJDBC cannot protect your app from SQL injection vulnerability
  override val tableName = s"orders_$year"

  def apply(o: ResultName[Order])(rs: WrappedResultSet) = new Order(
    id         = rs.long(o.id),
    productId  = rs.long(o.productId),
    customerId = rs.longOpt(o.customerId),
    createdAt  = rs.zonedDateTime(o.createdAt)
  )
}
val (o2011, o2012, o2013) = (new OrderTable(2011), new OrderTable(2012), new OrderTable(2013))

val ordersIn2011 = DB readOnly { implicit s =>
  val o = o2011.syntax("o")
  sql"select ${o.result.*} from ${o2011 as o} where ${o.customerId} is not null"
    .map(o2011(o.resultName)).list.apply()
}
DB readOnly { implicit s =>
  val o2 = o2012.syntax("o")
  val ordersIn2012 =
    sql"select ${o2.result.*} from ${o2012 as o2} where ${o2.customerId} is not null"
      .map(o2012(o2.resultName)).list.apply()

  val o3 = o2013.syntax("o")
  val ordersIn2013 =
    sql"select ${o3.result.*} from ${o2013 as o3} where ${o3.customerId} is not null"
      .map(o2013(o3.resultName)).list.apply()
}

Use Case: Working with 22+ columns table

NOTICE: Since Scala 2.11, you can use a case class for 22+ columns table.

Just use a normal class and implement the EntityEquality trait for one-to-many relations.

import scalikekdbc._
import java.time.ZonedDateTime

class HugeTable(
  val column1: Long,
  val column2: Option[String],
  val column3: String,
  val column4: Int,
  val column5: ZonedDateTime,
  ...
  val column22: Int,
  val column23: ZonedDateTime) extends EntityEquality {

  override val entityIdentity = Seq(column1, column2, ..., column23).mkString("\t")

}