Scala active record

Scala-ActiveRecord
Type-safe Active Record model for Scala
@teppei_tosa_en

Who am I
鉄平土佐
TEPPEI TOSA
iron peace place name

The ﬁrst ofﬁcial conference in Japan.
http://scalaconf.jp/en/

Typesafe members came to
Japan and gave speeches.

Talked about the case example of building
our original BRMS “BIWARD” in Scala.

Japanese engineers talked about
Scala tips or their libraries.
• “Stackable-controller” by @gakuzzzz
https://github.com/t2v/stackable-controller
• “How we write and use Scala libraries not
to cry” by @tototoshi
http://tototoshi.github.io/slides/how-we-write-and-use-scala-libraries-
scalaconfjp2013/#1
For example,
http://scalaconf.jp/en/

Scala-ActiveRecord
Type-safe Active Record model for Scala
• https://github.com/aselab/scala-activerecord
• Latest version : 0.2.2
• Licence : MIT

Features
• Squeryl wrapper
• type-safe (most part)
• Rails ActiveRecord-like operability
• Auto transaction control
• validations
• Associations
• Testing support

Most of the other
ORM Libraries
Wrap SQL
val selectCountries = SQL(“Select * from Countries”)
Have to deﬁne mappings from the results of SQL to
the models.
val countries = selectCountries().map(row =>
row[String](“code”) -> row[String](“name”)
).toList

The motivation of
Scala-ActiveRecord
• Want to define the model mapping more
easily.
• Want not to define the find methods in
each model classes.
• Want not to write SQLs.

Other libraries example
1. Anorm
2. Slick ( ScalaQuery )
3. Squeryl

1.Anorm
• Anorm doesn’t have the Model layer.
• Have to deﬁne similar methods in each
Class.
case class Person(id:Pk[Long], name:String)
object Person {
! def create(person:Person):Unit = {
! ! DB.withConnection { implicit connection =>
! ! ! SQL("insert int person(name) values ({name}")
! ! ! ! .on('name -> person.name)
! ! ! ! .executeUpdate()
! ! }
! }
! ...
}

2. Slick ( ScalaQuery )
• Query Interface is good.
• Deﬁnding tables syntax is redundant.
• Have to deﬁne the mapping between table
and model in each table.
case class Member(id:Int, name:String, email:Option[String])
object Members extends Table[Member]("MEMBERS") {
! def id = column[Int]("ID", O.PrimaryKey, O.AutoInc)
! def name = column[String]("Name")
! def email = column[Option[String]]("EMAIL")
! def * = id.? ~ name ~ email <> (Member, Member.unapply _)
}

3. Squeryl
• The best one in these libraries.
• Scala-ActiveRecord wraps this with some
improvement.
1. Optimize the generated SQLs
2. Automate the transaction control
3. Use CoC approach to build relationship

When queries are combined
Squeryl generates sub-query SQL.
Squeryl
val query = from(table)(t => where(t.id.~ > 20) select(t))
from(query)(t => where(t.name like "%test%) select(t))
Scala
select * from
! (Select * from table where table.id > 20) q1
where q1.name like "test"
SQL
It negatively affect performance.

When queries are combined
Squeryl generates sub-query SQL.
Scala-ActiveRecord
val query = Table.where(_.id.~ > 20)
query.where(_.name like "%test%").toList
Scala
select * from table
where table.id > 20 and table.name like "test"
SQL
It can generate more simple SQL statement.

Automate
the transaction control
Squeryl
Scala-ActiveRecord
• Call “inTransaction” automatically at accessing
Iterable#iterator.
• When the save or delete method is called,
“inTransaction” is executed by default.
• Off course, you can call “inTransaction” expressly.
inTransaction {
books.insert(new Author(1, "Michel","Folco"))! !
val a = from(authors)
(a=> where(a.lastName === "Folco") select(a))
}

Use CoC approach to
build relationship.
Squeryl
object Schema extends Schema{
! val foo = table[Foo]
! val bar = table[Bar]
! val fooToBar = oneToManyRelation(Foo, Bar).via(
! ! (f,b) => f.barId === b.id
! )
}
class Foo(var barId:Long) extends SomeEntity {
! lazy val bar:ManyToOne[Bar] = schema.fooToBar.right(this)
}
class Bar(var bar:String) extends SomeEntity {
! lazy val foos:OneToMany[Foo] = schema.fooToBar.left(this)
}

Use CoC approach to
build relationship.
Scala-ActiveRecord
object Table extends ActiveRecordTabels {
! val foo = table[Foo]
! val bar = table[Bar]
}
class Foo(var barId:Long) extends ActiveRecord {
! lazy val bar = belongsTo[Bar]
}
class Bar(var bar:String) extends ActiveRecord {
! lazy val foos = hasMany[Foo]
}

Deﬁne the dependency
in SBT project deﬁnition
Add the following settings in build.sbt or project/Build.scala.
libraryDependencies ++= Seq(
"com.github.aselab" %% "scala-activerecord" % "0.2.2",
"org.slf4j" % "slf4j-nop" % "1.7.2", // optional
"com.h2database" % "h2" % "1.3.170" // optional
)
resolvers += Resolver.sonatypeRepo("releases")

Using Scala ActiveRecord
Play2.1 Plugin
Add the following settings in project/Build.scala
val appDependencies = Seq(
"com.github.aselab" %% "scala-activerecord-play2" % "0.2.2",
jdbc,
"com.h2database" % "h2" % "1.3.170"
)
val main = play.Project(appName, appVersion, appDependencies)
.settings(
resolvers ++= Seq(
Resolver.sonatypeRepo("releases")
)
)
Add the following settings in conf/play.plugins
9999:com.github.aselab.activerecord.ActiveRecordPlugin

Database Support
H2 database
MySQL
PostgrSQL
Derby
Oracle

Model implementation
case class Person(var name:String, var age:Int)
! extends ActiveRecord
object Person
! extends ActiveRecordCompanion[Person]
Schema deﬁnition
object Tables extends ActiveRecordTable {
! val people = table[Person]
}

Create
val person = Person("person1", 25)
person.save // return true
val person = Preson("person1", 25).create
// return Person("person1", 25)

Read
Person.find(1)
// Some(Person("person1"))
Person.toList
// List(person("person1”), ...)
Person.findBy("name", "john")
// Some(Person("John"))
Person.where(_.name === "john").headOption
// Some(Person("john"))

Update
Person.find(1).foreach { p =>
! p.name = "Ichiro"
! p.age = 37
! p.save
}
Person.forceUpdate( _.id === 1)(
! _.name := "ichiro", _.age := 37
)

Delete
Person.where(_.name === "john").foreach(_.delete)
Person.find(1) match {
! case Some(person) => person.delete
! case _ =>
}
Person.delete(1)

Find single object
val client = Client.find(10)
// Some(Client) or None
val John = Client.findBy("name", "john")
// Some(Client("john")) or None
val john = Client.findBy(("name", "john"), ("age",25))
// Some(Client("john",25)) or None

Get the search result as List
Scala
Clients.where(c =>
! c.name === "john" and c.age.~ > 25
).toList
Clients
! .where(_.name == "john")
! .where(_.age.~ > 25)
! .toList
generated SQL
select clients.name, clients.age, clients.id
from clients
where clients.name = "john" and clients.age > 25

Using iterable methods
val client = Client.head
// First Client or RecordNotFoundException
val client = Client.lastOption
// Some(Last Client) or None
val (adults, children) = Client.partition(_.age >= 20)
// Parts of clients

Ordering
Client.orderBy(_.name)
Client.orderBy(_.name asc)
Client.orderBy(_.name asc, _.age desc)

Limit
Client.limit(10)
Offset
Client.page(2, 5)
Existence
Client.exists(_.name like “john%”)
// true or false

Specify selected ﬁelds
Client.select(_.name).toList
// List[String]
Client.select(c => (c.name, c.age)).toList
// List[(String, Int)]

Combine Queries
Scala
Clients.where(_.name like "john%")
! .orderBy(_.age desc)
! .where(_.age.~ < 25)
! .page(2, 5)
! .toList
generated SQL
select clients.name, clients.age, clients.id
from clients
where ((clients.name like "john%") and (clients.age < 25))
order by clients.age desc
limit 5 offset 2

Cache Control
val orders = Order.where(_.age.~ > 20)
//execute this SQL query and cheche the query
orders.toList
//don't execute the SQL query
orders.toList
When the query is implicitly converted, the query is cached.

Annotation-based
Validation
case class User(
! @Required name:String,
! @Length(max=20) profile:String,
! @Range(min=0, max=150) age:Int
) extends ActiveRecord
Object User extends ActiveRecordCompanion[User]

Example
val user = user("", "Profile", 25).create
user.isValid // false
user.hasErrors // true
user.errors.messages // Seq("Name is required")
user.hasError("name") // true
User("", "profile", 15).saveEither match {
case Right(user) => println(user.name)
case Left(errors) => println(errors.message)
}
// "Name is required"

Available hooks
•beforeValidation
•beforeCreate
•afterCreate
•beforeUpdate
•afterUpdate
•beforeSave
•afterSave
•beforeDelete
•afterDelete

Example
case class User(login:String) extends ActiveRecord {
! @Transient
! var password:String = _
! var hashedPassword:String = _
! override def beforeSave() {
! ! hashedPassword = SomeLibrary.encrypt(password)
! }
}
val user = User("john")
user.password = "raw_password"
user.save
// stored encrypted password

One-to-Many
case class User(name:String) extends ActiveRecord {
! val groupId:Option[Long] = None
! lazy val group = belongsTo[Group]
}
case class Group(name:String) extends ActiveRecord {
! lazy val users = hasMany[User]
}
groups
id
name
users
id
group_id
name

One-to-Many
val user1 = User("user1").create
val group1 = Group("group1").create
group1.users << user1
group1.users.toList
// List(User("user1"))
user1.group.getOrElse(Group("group2"))
// Group("group1")

Generated SQL sample
Scala
group1.users.where(_.name like "user%")
! .orderBy(_.id desc)
! .limit(5)
! .toList
generated SQL
Select users.name, users.id
From users
Where ((users.group_id = 1) And (users.name like "user%"))
Order by users.id Desc
limit 5 offset 0

Many-to-Many (HABTM)
! lazy val groups = hasAndBelongsToMany[Group]
}
! lazy val users = hasAndBelongsToMany[user]
}
groups
id
name
groups_users
left_id
right_id
users
id
name

val user1 = User("user1").create
user1.groups := List(group1, group2)
user1.groups.toList
// List(Group("group1"), Group("group2"))
group1.users.toList
// List(User("user1"))
Many-to-Many (HABTM)

Many-to-Many
(hasManyThrough)
groups
id
name
memberships
id
user_id
group_id
isAdmin
users
id
name

Many-to-Many
(hasManyThrough)
case class Membership(
! userId:Long, projectid:Long, isAdmin:Boolean = false
) extends ActiveRecord {
! lazy val user = belongsTo[User]
! lazy val group = belongsTo[Group]
}
! lazy val memberships = hasMany[Membership]
! lazy val groups = hasManyThrough[Group, Membership](memberships)
}
! lazy val memberships = hasmany[Membership]
! lazy val users = hasManyThrough[User, Membership](memberships)
}

Conditions Options
! lazy val adminUsers =
! ! hasMany[User](conditions = Map("isAdmin" -> true))
}
group.adminUsers << user
// user.isAdmin == true

ForeignKey option
case class Comment(name:String) extends ActiveRecord {
! val authorId:Long
! lazy val author
= belongsTo[User](foreignKey = "authorId")
}

Join Tables
Scala
Client.joins[Order](
! (client, order) => client.id === order.clientId
).where(
! (client, order) => client.age.~ < 20 and order.price.~ > 1000
).select(
! (client, order) => (client.name, client.age, order.price)
).toList
generated SQL
Select clients.name, clients.age, order.price
From clients inner join orders on (clients.id = orders.client_id)
Where ((clients.age < 20) and (groups.price > 1000))

Eager loading associations
The solution for N+1 problem.
Scala
Order.includes(_.client).limit(10).map {
! order => order.client.name
}.mkString("n")
generated SQL
Select orders.price, orders.id
From orders limit 10 offset 0
Select clients.name, clients.age, clients.id
From clients inner join orders on (clients.id = orders.client_id)
Where (orders.id in (1,2,3,4,5,6,7,8,9,10))

See the generated SQLs
Use the toSql method
println(User.where(_.name like "john%").orderBy(_.age desc).toSql)
Set logging level with “debug” in logback.xml
<configuration>
<appender name="STDOUT" class="ch.qos.logback.core.ConsoleAppender">
<encoder>
<pattern>%highlight(%-5level) %cyan(%logger{15}) - %msg %n</pattern>
</encoder>
</appender>
<root level="DEBUG">
<appender-ref ref="STDOUT" />
</root>
</configuration>

In SBT console
build.sbt or project/Build.scala
initialCommands in console := """
import com.github.aselab.activerecord._
import com.github.aselab.activerecord.dsl._
import models._
SomeTables.initialize(Map("schema" -> "models.SomeTables"))
"""
In the console
> console
scala> User.forceInsertAll{ (1 to 10000).map{i => User("name" + i)} }
scala> User.where(_.name === "name10").toList

Setting for test
build.sbt or project/Build.scala
libraryDependencies ++= Seq(
"com.github.aselab" %% "scala-activerecord-specs" % "0.2.2" % "test",
"org.specs2" %% "specs2" % "1.12.3" % "test"
)
resolvers += Resolver.sonatypeRepo("releases")
application.conf
test {
schema = "models.Tables"
driver = "org.h2.Driver"
jdbcurl = "jdbc:h2:mem:test"
}

Test Example
import com.github.aselab.activerecord._
object SomeModelSpecs extends ActiveRecordSpecification {
override val config
= Map("schema" -> "com.example.models.Tables")
override def beforeAll = {
super.beforeAll
SomeModel("test1").create
}
override def afterAll = {
super.afterAll
}
"sample" should {
// Some specifications code
}
}

ActiveRecord Overhead
• How Sql statements are generated.
• The time to create active-record object
from the results of query.

ORM Race
•Anorm
•Slick
•Squerl
•Scala-ActiveRecord

Race Condition
• Create the “User” table which has only 3 columns
• Insert 1000 records into the “User” table
• Select all from the table with same statements
• Time their trip to the end of creation objects
• Exclude the time to creating DB connection
• Use Play framework 2.1.1
• Use H2-database
• Run in Global.onStart
• Run 5 times
• Compare with the average times

The Racers
Anorm Squeryl
Slick Scala-ActiveRecord
SQL("SELECT * FROM USER")
.as(User.simple *)
Query(Users).list
from(AppDB.user)
(s => select(s))
.toList
User.all.toList

The Race Results
39.8ms
116.8ms
177.2ms
258.8ms
Squeryl
Anorm
Scala-ActiveRecord
Slick

Validation at compiling
(with “Macro”)
• The ﬁndBy method and conditions of
association will be type-safe.
• Validate whether foreign-key is speciﬁed
with existing key or not.

Support Serialization
Form Model XML
JSON
MessagePack
Validation
View
Bind
View
Helper

Support Web framework
• CRUD controller
• Form Helper for Play and Scalatra
• Code generator as SBT plugin

DEMO withYATTER
(Yet Another twiTTER )

YATTER’s tables
Follows
id
userid
follows_users
id
user_id
follow_id
Users
id
name
Tweets
id
userId
textManyToMany
OneToMany
OneToOne
(But not supported yet)

https://github.com/ironpeace/yatter

@teppei_tosa_en
https://github.com/aselab/scala-activerecord
https://github.com/ironpeace/yatter
Thank you

Scala active record

More Related Content

What's hot

Similar to Scala active record

More from 鉄平 土佐

Recently uploaded

Scala active record

More from 鉄平土佐