sqlest is a database library for Scala. It allows you to write SQL directly in Scala with type safety guarantees while also providing a simple mechanism of extracting domain specific case classes from the results.
sqlest-extractors is a second library used for extracting case classes from table data. It is used within sqlest. Check out the readme
To use sqlest from an existing project add the following resolvers
resolvers ++= Seq(
"Sonatype OSS Releases" at "http://oss.sonatype.org/content/repositories/releases/",
// Only needed if you are using a snapshot version of sqlest
"Sonatype OSS Snapshots" at "http://oss.sonatype.org/content/repositories/snapshots/"
)and the following library dependency
libraryDependencies ++= Seq(
"uk.co.jhc" %% "sqlest" % "0.8.6"
)sqlest is available for Scala 2.11
Before doing anything else a connection to a database must be available. This is encapsulated in the Database object which requires a DataSource and a StatementBuilder.
import sqlest._Configure a DataSource
val dataSource = {
val dataSource = new org.h2.jdbcx.JdbcDataSource
dataSource.setURL("jdbc:h2:~/test")
dataSource
}Choose the StatementBuilder that is compatible with the database you are using.
val statementBuilder = sqlest.sql.H2StatementBuilderUse the DataSource and the StatementBuilder to create an implicit database. This database is used in all execute calls.
implicit val database = Database.withDataSource(dataSource, statementBuilder)A table definition consists of a table name and any columns that you want to use. A table can be created with an alias but it is standard to create an object that has no alias.
class FruitTable(alias: Option[String]) extends Table("fruit", alias) {
val id = column[Int]("id")
val name = column[String]("name")
val juiciness = column[Int]("juiciness")
}
object FruitTable extends FruitTable(None)
class SmoothyTable(alias: Option[String]) extends Table("smoothy", alias) {
val id = column[Int]("id")
val description = column[String]("description")
}
object SmoothyTable extends SmoothyTable(None)
class IngredientsTable(alias: Option[String]) extends Table("ingredients", alias) {
val smoothyId = column[Int]("smoothy_id")
val fruitId = column[Int]("fruit_id")
}
object IngredientsTable extends IngredientsTable(None)Let's define some domain classes that we want to populate from our database
case class Fruit(name: String, juiciness: Int)
case class Smoothy(description: String, fruits: List[Fruit])Extractors are used to populate domain classes from ResultSets returned by running queries. They declaratively specify which parameter in a case class is populated by which column in a table
lazy val fruitExtractor = extract[Fruit](
name = FruitTable.name,
juiciness = FruitTable.juiciness
)Queries are written in sqlest the same way as they are written in SQL. The table and column definitions are used directly. Here is a query that will return the juiciest fruits
scala> select(FruitTable.name, FruitTable.juiciness).
| from(FruitTable).
| where(FruitTable.juiciness >= 8).
| orderBy(FruitTable.juiciness.desc).
| extractAll(fruitExtractor) // fruitExtractor is defined below
res11: List[fruitExtractor.SingleResult] = List(Fruit(Watermelon,10), Fruit(Tomato,9), Fruit(Grape,8))Extractors are designed for composition to allow nested case classes to be extracted, and multiple rows of the ResultSet to be combined into a single result
lazy val smoothyExtractor = extract[Smoothy](
description = SmoothyTable.description,
fruits = fruitExtractor.asList
).groupBy(SmoothyTable.id) // All results with the same value for this field are combined into a single resultThis extractor can then be used to find out which fruits are used in a smoothy
scala> select.
| from(SmoothyTable).
| innerJoin(IngredientsTable).on(SmoothyTable.id === IngredientsTable.smoothyId).
| innerJoin(FruitTable).on(IngredientsTable.fruitId === FruitTable.id).
| where(SmoothyTable.description === "Super banana smoothie").
| extractHead(smoothyExtractor)
res12: smoothyExtractor.SingleResult = Smoothy(Super banana smoothie,List(Fruit(Banana,4), Fruit(Grape,8)))- Dean Chapman - author of Sqler which inspired sqlest
- Frank Wallis - ideas, feedback and contributions
- Slick - a great project which contains many ideas used in sqlest
- jOOQ - a similar project written in Java which provided many ideas used in sqlest
