Home

Awesome

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

Towards a perfect impedance match...

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.4.3"

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)