sqlτyped - a macro which infers Scala types by analysing SQL statements

Towards a perfect impedance match...

• The types and column names are already defined in database schema and SQL query. Why not use those and infer types and accessor functions?

• SQL is a fine DSL for many queries. It is the native DSL of relational databases and wrapping it with another DSL is often unnecessary (SQL sucks when one has to compose queries, or if you have to be database agnostic).

sqlτyped converts SQL string literals into typed functions at compile time.

select age, name from person where age > ?

==>

Int => List[{ age: Int, name: String }]

Examples

The following examples use schema and data from test.sql

First some boring initialization...

Start console: sbt, then project sqltyped and test:console.

import java.sql._
import sqltyped._
Class.forName("com.mysql.jdbc.Driver")
implicit def conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/sqltyped", 
                                                "root", "")

Now we are ready to query the data.

scala> val q = sql("select name, age from person")
scala> q() map (_ get "age")
res0: List[Int] = List(36, 14)

Notice how the type of 'age' was infered to be Int.

scala> q() map (_ get "salary")
<console>:24: error: No field String("salary") in record ...
               q() map (_ get "salary")

Oops, a compilation failure. Can't access 'salary', it was not selected in the query.

Query results are returned as List of type safe records (think List[{name:String, age:Int}]). As the above examples showed a field of a record can be accessed with get function: row.get(name). Functions values and tuples can be used to drop record names and get just the query values.

scala> q().values
res1: List[shapeless.::[String,shapeless.::[Int,shapeless.HNil]]] = 
  List(joe :: 36 :: HNil, moe :: 14 :: HNil)

scala> q().tuples
res2: List[(String, Int)] = List((joe,36), (moe,14))

Input parameters are parsed and typed.

scala> val q = sql("select name, age from person where age > ?")

scala> q("30") map (_ get "name")
<console>:24: error: type mismatch;
 found   : String("30")
 required: Int
              q("30") map (_ get name)

scala> q(30) map (_ get "name")
res4: List[String] = List(joe)

Nullable columns are inferred to be Scala Options.

scala> val q = sql("""select p.name, j.name as employer, j.started, j.resigned 
                      from person p join job_history j on p.id=j.person order by employer""")
scala> q().tuples
res5: List[(String, String, java.sql.Timestamp, Option[java.sql.Timestamp])] = 
  List((joe,Enron,2002-08-02 12:00:00.0,Some(2004-06-22 18:00:00.0)), 
       (joe,IBM,2004-07-13 11:00:00.0,None))

Functions are supported too. Note how function 'max' is polymorphic on its argument. For String column it is typed as String => String etc.

scala> val q = sql("select max(name) as name, max(age) as age from person where age > ?")
scala> q(10).tupled
res6: (Option[String], Option[Int]) = (Some(moe),Some(36))

Analysis

So far all the examples have returned results as Lists of records. But with a little bit of query analysis we can do better. Like, it is quite unnecessary to box the values as records if just one column is selected.

scala> sql("select name from person").apply
res7: List[String] = List(joe, moe)

scala> sql("select age from person").apply
res8: List[Int] = List(36, 14)

Then, some queries are known to return just 0 or 1 values, a perfect match for Option type. The following queries return possible result as an Option instead of List. The first query uses a uniquely constraint column in its where clause. The second one explicitly wants at most one row.

scala> sql("select name from person where id=?").apply(1)
res9: Some[String] = Some(joe)

scala> sql("select age from person order by age desc limit 1").apply
res10: Some[Int] = Some(36)

Inserting data

scala> sql("insert into person(name, age, salary) values (?, ?, ?)").apply("bill", 45, 30000)
res1: Int = 1

Return value was 1, which means that one row was added. However, often a more useful return value is the generated primary key. Table 'person' has an autogenerated primary key column named 'id'. To get the generated value use a function sqlk (will be changed to sql(..., keys = true) once Scala macros support default and named arguments).

scala> sqlk("insert into person(name, age, salary) values (?, ?, ?)").apply("jill", 45, 30000)
res2: Long = 3

Inserting multiple values is supported too.

scala> sqlk("insert into person(name, age, salary) select name, age, salary from person").apply
res3: List[Long] = List(4, 5, 6)

Updates work as expected.

scala> sql("update person set name=? where age >= ?").apply("joe2", 30)
res4: Int = 1

Documentation

See wiki.

Demo app

How to try it?

Install

Requires at least Scala 2.10.2 and SBT 0.13.

sqlτyped is published to Sonatype repositories.

"fi.reaktor" %% "sqltyped" % "0.3.0"

Build

git clone https://github.com/jonifreeman/sqltyped.git
cd sqltyped

Then either:

mysql -u root -e 'create database sqltyped'
mysql -u root sqltyped < core/src/test/resources/test.sql

or:

sudo -u postgres createuser -P sqltypedtest  // Note, change the password from project/build.scala
sudo -u postgres createdb -O sqltypedtest sqltyped
sudo -u postgres psql sqltyped < core/src/test/resources/test-postgresql.sql

To run the tests you need to setup both databases.

Credits

(in order of appearance)

• Joni Freeman
• Dylan Alex Simon