lucidsoftware/relate — pull request #11 by pauldraper (2014-09-22 09:19:09+00:00)
Title: String interpolation for queries

The API for relate, while still more polished than JDBC, is cumbersome. The change allows for greater readability, safety, and performance.

The new query-writing implementation can be used by

```scala
import com.lucidchart.open.relate.interp._
```

The existing implementation has not be affected, except for limited amount of necessary refactoring.

#### Readable

Consider this example from the [documentation](https://github.com/lucidsoftware/relate/wiki/Query-Expansion#commaSeparated):

```scala
val ids = 0 until 150
SQL("SELECT * FROM pokedex WHERE id IN ({ids})").expand { implicit query =>
commaSeparated("ids", ids.size)
}.on { implicit query =>
ints("ids", ids)
}
```

It's a bit messy. After this change, you can do

```scala
val ids = 0 until 150
sql"SELECT * FROM pokedex WHERE id IN ($ids)"
```

or even

```scala
sql"SELECT * FROM pokedex WHERE id IN (${0 until 150})"
```

The parameter type is gather from the type information available at compile-time. The previous example was a comma-separated list of integers, the next is a single (properly escaped) string.

```scala
val email = "foo@example.com"
sql"SELECT * FROM users WHERE email = $email"
```

Queries can also be composed.

```scala
val sql1 = sql"SELECT * FROM users WHERE deleted = 0"
val sq2 = sql"$sql1 LIMIT 5"
```

#### Safe

In addition to being much more readable, this is less avoids some kinds of (runtime) errors, like

```scala
SQL("SELECT * FROM users WHERE email = {enail}").on { implicit query =>
string("email", email)
}
```

#### Performant

For reading results, performance stays the same. For writing queries, the performance improves a bit, partly because the tokenzing/parsing happens at compile time, and partly because the new implementation is just simpler.

Generally, the more parameters, the better the interpolation does. [relate-benchmarks](https://github.com/lucidsoftware/relate-benchmarks) results.

```
| old | new
Select 25 columns from 10000 records | 22.5 | 12.8
Update 20 columns , 10000 records | 19 | 13.5
Insert 100 columns , 10000 records | 5630 | 4780
```

Granted, these are some extreme cases. The more compelling real-world advantage is the clarity of the API.