ORM

Skinny-ORM

Skinny provides you with Skinny-ORM as the default O/R mapper, which is built with ScalikeJDBC. This is a portable library, so you can also use it with Play2, Scalatra, Lift and any other frameworks.

Logo

A key feature of Skinny-ORM is that it avoids N+1 queries because associations are resolved by join queries.

#belongsTo, #hasOne and #hasMany(Through) associations are converted into join queries, so you don’t need to worry about performance problems caused by N+1 queries.

Furthermore, the #byDefault option allows you to resolve associations anytime. If you don’t always need some association, miss the #byDefault and just use #joins method such as Team.joins(Team.members).findById(123) on demand.

On the other hand, it’s impossible to resolve all the nested attributes’ relationships by a single join query. If you need to resolve nested relationships, you can retrieve them with eager loading by using #includes method.

Minimum Setup

Even if you’re not familiar with Skinny apps, don’t worry.

Skinny ORM is an independent library from Skinny environment. So you can use only Skinny ORM with the following settings.

Let’s prepare right now!

build.sbt

libraryDependencies ++= Seq(
  "org.skinny-framework" %% "skinny-orm"      % "4.0.0",
  "com.h2database"       %  "h2"              % "1.4.+",
  "ch.qos.logback"       %  "logback-classic" % "1.1.+"
)

// will be executed when invoking sbt console
initialCommands := """
import scalikejdbc._
import skinny.orm._, feature._
import org.joda.time._
skinny.DBSettings.initialize()
implicit val session = AutoSession
"""

src/main/resources/application.conf

development {
  db {
    default {
      driver="org.h2.Driver"
      url="jdbc:h2:file:./db/development;MODE=PostgreSQL;AUTO_SERVER=TRUE"
      user="sa"
      password="sa"
      poolInitialSize=2
      poolMaxSize=10
      poolValidationQuery="select 1 as one"
      poolFactoryName="commons-dbcp"
    }
  }
}

Now you can try the following example code on sbt console (the Scala REPL).

Understanding Basic Mapper Traits

Skinny ORM users should choose one of the following traits to implement mapper for table.

You can find examples here:

If you have any questions or feedback, feel free to ask the Skinny team or users in the mailing list.

https://groups.google.com/forum/#!forum/skinny-framework

SkinnyMapper

This is the most basic trait to implement Skinny ORM mapper.

Primary key should be single and long numeric
Finder APIs
Querying APIs

Entity case class and mapper companion object should be like this:

// create member table
sql"create table member (id serial, name varchar(64), created_at timestamp)".execute.apply()

// When you're trying on the Scala REPL, use :paste mode
case class Member(id: Long, name: Option[String], createdAt: DateTime)
object Member extends SkinnyMapper[Member] {
  override lazy val defaultAlias = createAlias("m")
  override def extract(rs: WrappedResultSet, n: ResultName[Member]): Member = new Member(
    id        = rs.get(n.id),
    name      = rs.get(n.name),
    createdAt = rs.get(n.createdAt))
}

And you can use Finder and Querying as follows:

val member: Option[Member] = Member.findById(123)
val members: Seq[Member] = Member.where("name" -> "Alice").apply()

SkinnyCRUDMapper

Primary key should be single and long numeric
Finder APIs
Querying APIs
CRUD Operation APIs

The difference from SkinnyMapper is that insert/update/delete operations are available.

case class Member(id: Long, name: Option[String], createdAt: DateTime)
object Member extends SkinnyCRUDMapper[Member] {
  ...
}

The usage is like this:

// create
Member.createWithAttributes("name" -> "Alice", "createdAt" -> DateTime.now)
val column = Member.column
Member.createWithNamedValues(column.name -> "Alice", column.createdAt -> DateTime.now)
// update
Member.updateById(123).withAttributes("name" -> "Bob")
Member.updateBy(sqls.eq(Member.column.name, "Bob")).withAttributes("name" -> "Bob")
// delete
Member.deleteById(123)
Member.deleteBy(sqls.eq(Member.column.name, "Alice"))

SkinnyCRUDMapperWithId

SkinnyMapper expects a Long (bigint) value named id for primary key column by default (id can be changed by overriding primaryKeyFieldName in mapper).

When your table has a non-numeric primary key or you’d like to make numeric primary key typed such as case class MemberId(value: Long), use Skinny(CRUD)MapperWithId traits instead. In this case, you must implement idToRawValue and rawValueToId methods.

If you need to use a complex object (e.g. MemberId class) as a primary key, it’s also easy to implement.

case class MemberId(value: Long)
case class Member2(id: MemberId, name: Option[String], createdAt: DateTime)

object Member2 extends SkinnyCRUDMapperWithId[MemberId, Member2] {
  override lazy val tableName = "member"
  override lazy val defaultAlias = createAlias("m")
  override def idToRawValue(id: MemberId) = id.value
  override def rawValueToId(value: Any) = MemberId(value.toString.toLong)

  override def extract(rs: WrappedResultSet, n: ResultName[Member2]): Member2 = new Member2(
    id        = MemberId(rs.get(n.id)),
    name      = rs.get(n.name),
    createdAt = rs.get(n.createdAt))
}

The usage is like this:

// create
Member2.createWithAttributes("name" -> "Alice", "createdAt" -> DateTime.now)
val m = Member2.column
Member2.createWithNamedValues(m.name -> "Alice", m.createdAt -> DateTime.now)
// update
Member2.updateById(MemberId(123)).withAttributes("name" -> "Bob")
Member2.updateBy(sqls.eq(Member.column.name, "Bob")).withAttributes("name" -> "Bob")
// delete
Member2.deleteById(MemberId(123))
Member2.deleteBy(sqls.eq(Member.column.name, "Alice"))

And your SkinnyResource will be like this:

package controller
import skinny._
import skinny.validator._
import model._

object MembersController extends SkinnyResourceWithId[MemberId] with ApplicationController {
  protectFromForgery()

  implicit override val scalatraParamsIdTypeConverter = new TypeConverter[String, MemberId] {
    def apply(s: String): Option[MemberId] = Option(s).map(model.rawValueToId)
  }

  override def model = Member2
  override def resourcesName = "members"
  override def resourceName = "member"

  // ...
}

SkinnyNoIdMapper, SkinnyNoIdCRUDMapper

This trait doesn’t expect single and numeric primary key.

These traits will be useful when you deal with tables that have no primary key(!) or compound primary keys or any cases.

This trait doesn’t mind the table has no primary key or compound primary keys
Finder APIs
Querying APIs
CRUD Operation APIs

sql"create table useless_data(a varchar(16) not null, b bigint, created_timestamp timestamp not null)".execute.apply()

case class UselessData(a: String, b: Option[Long], createdTimestamp: DateTime)
object UselessData extends SkinnyNoIdCRUDMapper[UselessData] {
  override def defaultAlias = createAlias("ud")
  override def extract(rs: WrappedResultSet, n: ResultName[UselessData]) = new UselessData(
    a = rs.get(n.a), b = rs.get(n.b), createdTimestamp = rs.get(n.createdTimestamp))
}

The usage is like this:

UselessData.createWithAttributes("a" -> "foo", "b" -> Some(123), "createdTimestamp" -> DateTime.now)
UselessData.findAll()

SkinnyJoinTable

“join table” is used for representing relationship between tables. Here is a simple examples:

sql"create table account (id serial, name varchar(128) not null)".execute.apply()
sql"create table email (id serial, email varchar(256) not null)".execute.apply()
sql"create table account_email (account_id bigint not null, email_id bigint not null)".execute.apply()

// use :paste on the REPL

case class Email(id: Long, email: String)
object Email extends SkinnyCRUDMapper[Email] {
  override def defaultAlias = createAlias("e")
  override def extract(rs: WrappedResultSet, n: ResultName[Email]) = new Email(id = rs.get(n.id), email = rs.get(n.email))
}

case class Account(id: Long, name: String, emails: Seq[Email] = Nil)
object Account extends SkinnyCRUDMapper[Account] {
  override def defaultAlias = createAlias("a")
  override def extract(rs: WrappedResultSet, n: ResultName[Account]) = new Account(id = rs.get(n.id), name = rs.get(n.name))

  hasManyThrough[Email](
    through = AccountEmail, 
    many = Email, 
    merge = (a, emails) => a.copy(emails = emails)).byDefault
}

// def extract is not needed 
case class AccountEmail(accountId: Long, emailId: Long)
object AccountEmail extends SkinnyJoinTable[AccountEmail] {
  override def defaultAlias = createAlias("ae")
}

The usage is like this:

Email.createWithAttributes("email" -> "alice@example.com")
Account.createWithAttributes("name" -> "Alice")
AccountEmail.createWithAttributes("accountId" -> 1, "emailId" -> 1)
Account.findAll() // with emails

Transaction

Skinny ORM is built upon ScalikeJDBC. Transaction management is based on ScalikeJDBC. See the following documentation. Skinny mappers seamlessly work with the described APIs.

http://scalikejdbc.org/documentation/transaction.html

Useful APIs by Skinny ORM

Skinny-ORM is very powerful, so you don’t need to write much code. Your first model class and companion are here.

case class Member(id: Long, name: String, createdAt: DateTime)
object Member extends SkinnyCRUDMapper[Member] {
  override def defaultAlias = createAlias("m")
  override def extract(rs: WrappedResultSet, n: ResultName[Member]) = new Member(
    id        = rs.get(n.id),
    name      = rs.get(n.name),
    createdAt = rs.get(n.createdAt)
  )
}

That’s all! Now you can use the following APIs.

val m = Member.defaultAlias

// ------------
// find by primary key
val member: Option[Member] = Member.findById(123)

val member: Option[Member] = Member.where("id" -> 123).apply().headOption

// ------------
// find many
val members: List[Member] = Member.findAll()

// ------------
// in clause
val members: List[Member] = Member.findAllBy(sqls.in(m.id, Seq(123, 234, 345)))

val members: List[Member] = Member.where("id" -> Seq(123, 234, 345)).apply()

// will return 345, 234, 123 in order
val m = Member.defaultAlias
val members: List[Member] = 
  Member.where("id" -> Seq(123, 234, 345))
    .orderBy(m.id.desc).offset(0).limit(5)
    .apply()

// ------------
// find by condition

val members: List[Member] = Member.findAllBy(
  sqls.eq(m.groupName, "scalajp").and.eq(m.delete, false))

val members: List[Member] = Member.where(
  'groupName -> "scalajp", 'deleted -> false).apply()

// use Pagination instead. Easier to understand than limit/offset
val members = Member.where(sqls.eq(m.groupId, 123))
  .paginate(Pagination.page(1).per(20))
  .orderBy(m.id.desc).apply()

// ------------
// count

Order.count()
Order.countBy(sqls.isNull(m.deletedAt).and.eq(m.shopId, 123))

Order.where("deletedAt" -> None, "shopId" -> 123).count()
Order.where(sqls.eq(o.cancelled, false)).distinctCount("customerId")

// ------------
// calculations
// min, max, average and sum

Order.sum("amount")

Product.min("price")
Product.where(sqls.ge(p.createdAt, startDate)).max("price")
Product.average("price")

Product.calculate(sqls"original_func(${p.userId})")

// ------------
// create with strong parameters

val params = Map("name" -> "Bob")
val id = Member.createWithPermittedAttributes(params.permit("name" -> ParamType.String))

// ------------
// create with parameters

val m = Member.defaultAlias
val id = Member.createWithNamedValues(m.name -> "Alice")

Member.createWithAttributes(
  "id" -> 123,
  "name" -> "Chris",
  "createdAt" -> DateTime.now
)

// ------------
// update with strong parameters

Member.updateById(123).withPermittedAttributes(params.permit("name" -> ParamType.String))

// ------------
// update with parameters

Member.updateById(123).withAttributes("name" -> "Alice")

// update by condition

Member.updateBy(sqls.eq(m.groupId, 123)).withAttributes("groupId" -> 234)

// ------------
// delete

Member.deleteById(234)
Member.deleteBy(sqls.eq(m.groupId, 123))

Source code: orm/src/main/scala/skinny/orm/feature/CRUDFeature.scala

Associations

If you need to join other tables, just add belongsTo, hasOne or hasMany(Through) to the companion.

Examples: orm/src/test/scala/skinny/orm/models.scala

Be aware of Skinny ORM’s concept that basically joins tables to resolve associations to reduce N+1 queries. We recommend enabling query logging for development.

http://scalikejdbc.org/documentation/query-inspector.html

Typically defining associations are fundamentally not so simple, so you might be confused when specifying these definitions. Understanding how ScalikeJDBC’s join queries and One-to-X APIs work may be useful.

http://scalikejdbc.org/documentation/one-to-x.html

BelongsTo

Same as ActiveRecord’s belongs_to association:

http://guides.rubyonrails.org/association_basics.html#the-belongs-to-association

We need to specify some types, so definitions are not as simple as ActiveRecord, but it’s easy to understand and simple enough.

case class Company(id: Long, name: String)
class Member(id: Long, name: String,
  mentorId: Long, mentor: Option[Member] = None,
  // Naming convention: {className}+{primaryKeyFieldName}
  // If the name of ID is "no", fk should be "companyNo" instead.
  companyId: Long, company: Option[Company] = None)

object Member extends SkinnyCRUDMapper[Member] {
  // basic settings ...

  // If byDefault is called, this join condition is enabled by default
  belongsTo[Company](Company, (m, c) => m.copy(company = c)).byDefault

  // or more explanatory
  belongsTo[Company](
    // entity mapper on the right side
    // in this case, default alias will be used in join query
    right = Company, 
    // function to merge association to main entity
    merge = (member, company) => member.copy(company = company)
  ).byDefault

  // when you cannot use defaultAlias, use this instead
  lazy val mentorAlias = createAlias("mentor")
  lazy val mentor = belongsToWithAlias(
    // in this case, "mentor" alias will be used in join query
    // the fk's name will be {aliasName} + {primaryKeyFieldName}
    right = Member -> mentorAlias,
    merge = (member, mentor) => member.copy(mentor = mentor)
  )
  // mentor will be resolved only when calling #joins 
  /*.byDefault */ 
}

Member.findById(123) // without mentor

Member.joins(Member.mentor).findById(123) // with mentor

In this case, following table is expected:

create table members (
  id bigint auto_increment primary key not null,
  company_id bigint,
  mentor_id bigint
);
create table companies (
  id bigint auto_increment primary key not null,
  name varchar(64) not null
);

Find more here: orm/src/main/scala/skinny/orm/feature/AssociationsFeature.scala

HasOne

Same as ActiveRecord’s has_one association:

http://guides.rubyonrails.org/association_basics.html#the-has-one-association

We need to specify some types, so definitions are not as simple as ActiveRecord, but it’s easy to understand and simple enough.

case class Name(first: String, last: String, memberId: Long)
case class Member(id: Long, name: Option[Name] = None)

object Member extends SkinnyCRUDMapper[Member] {
  // basic settings ...

  lazy val name = hasOne[Name](
    right = Name, 
    merge = (member, name) => member.copy(name = name)
  ).byDefault
}

In this case, following tables are expected:

create table members (
  id bigint auto_increment primary key not null
);
create table names (
  member_id bigint primary key not null,
  first varchar(64) not null,
  last varchar(64) not null
);

HasMany

Same as ActiveRecord’s has_many association:

http://guides.rubyonrails.org/association_basics.html#the-has-many-association

We need to specify some types, so definitions are not as simple as ActiveRecord, but it’s easy to understand and simple enough.

case class Company(id: Long, name: String, members: Seq[Member] = Nil)
case class Member(id: Long, 
  companyId: Option[Long] = None, company: Option[Company] = None,
  skills: Seq[Skill] = Nil
)
case class Skill(id: Long, name: String)

// -----------------------
// hasMany example
object Company extends SkinnyCRUDMapper[Company] {
  // basic settings ...

  lazy val membersRef = hasMany[Member](
    // association's SkinnyMapper and alias
    many = Member -> Member.membersAlias,
    // defines join condition by using aliases
    on = (c, m) => sqls.eq(c.id, m.companyId),
    // function to merge associations to main entity
    merge = (company, members) => company.copy(members = members)
  )
}
Company.joins(Company.membersRef).findById(123) // with members

// -----------------------
// hasManyThrough example

// join table definition
case class MemberSkill(memberId: Long, skillId: Long)
object MemberSkill extends SkinnyJoinTable[MemberSkill] {
  override lazy val tableName = "members_skills"
  override lazy val defaultAlias = createAlias("ms")
}
// hasManyThrough
object Member extends SkinnyCRUDMapper[Member] {
  // basic settings ...

  lazy val skillsRef = hasManyThrough[Skill](
    through = MemberSkill, 
    many = Skill, 
    merge = (member, skills) => member.copy(skills = skills)
  )
}
Member.joins(Member.skillsRef).findById(234) // with skills

In this case, following tables are expected:

create table companies (
  id bigint auto_increment primary key not null,
  name varchar(255) not null
);
create table members (
  id bigint auto_increment primary key not null,
  company_id bigint
);
create table skills (
  id bigint auto_increment primary key not null,
  name varchar(255) not null
);
create table members_skills (
  member_id bigint not null,
  skill_id bigint not null
);

Entity Equality

Basically using case classes for entities is recommended. As you know, Scala (until 2.11) has 22 limitation, so you may need to use normal classes for entities to treat tables that have more than 22 columns.

In this case, entities should be defined like this (Skinny 0.9.21 or ScalikeJDBC 1.7.3 is required):

class LegacyData(val id: Long, val c2: String, val c3: Int, ..., val c23: Int)
  extends scalikejdbc.EntityEquality {
  // override val entityIdentity = id
  override val entityIdentity = s"$id $c2 $c3 ... $c23"
}

object LegacyData extends SkinnyCRUDMapper[LegacyData] {
  ...
}

If you don’t implement like this, one-to-many relationships won’t work as you expect.

See also the detailed explanation here: http://scalikejdbc.org/documentation/one-to-x.html

Eager Loading

When you enable eager loading using the includes API, you need to define both belongsTo and includes.

Note: eager loading of nested entities is not supported yet.

Indeed, it’s not incredibly simple. But we believe that what it does is so clear that you can easily write the definition.

object Member extends SkinnyCRUDMapper[Member] {

  // Unfortunately the combination of Scala macros and type-dynamic sometimes doesn't work as expected
  // when "val company" is defined in Scala 2.10.x.
  // If you suffer from this issue, use "val companyOpt" "companyRef" and so on instead.
  lazy val companyOpt = {
    // normal belongsTo
    belongsTo[Company](
      right = Company,
      merge = (member, company) => member.copy(company = company))
    // eager loading operation for this one-to-one relation
    .includes[Company](
      merge = (members, companies) => members.map { m =>
        companies.find(c => m.company.exists(_.id == c.id))
          .map(c => m.copy(company = Some(c)))
          .getOrElse(m)
      })
  }
}

Member.includes(Member.companyOpt).findAll()

Yet another example:

object Member extends SkinnyCRUDMapper[Member] {
  lazy val skills =
    hasManyThrough[Skill](
      MemberSkill, Skill, (m, skills) => m.copy(skills = skills)
    ).includes[Skill]((ms, skills) => ms.map { m =>
      m.copy(skills = skills.filter(_.memberId.exists(_ == m.id)))
    })
}

Member.includes(Member.skills).findById(123) // with skills

Example of eager loading of a nested entity:

Member -> Company -> Country

Note: can only eager load first level nested entities.

case class Country(id: Long, name: String)
case class Company(
  id: Long,
  name: String,
  countryId: Option[Long] = None
  country: Option[Country] = None
)
case class Member(
  id: Long,
  companyId: Option[Long] = None,
  company: Option[Company] = None
)

object Company extends SkinnyCRUDMapper[Company] {
  // company loads country eagerly using .byDefault
  val countryOpt =
    belongsTo[Country](
      right = Country,
      merge = (company, country) => company.copy(country = country)
    ).byDefault
}

object Member extends SkinnyCRUDMapper[Member] {
  lazy val companyOpt = {
    // normal belongsTo: loads company but not the nested field member.company.country
    belongsTo[Company](
      right = Company,
      merge = (member, company) => member.copy(company = company))
    // eager loading operation that'd load member.company.country
    .includes[Company](
      merge = (members, companiesWithCountries) => members.map { m =>
        companiesWithCountries.find(c => m.company.exists(_.id == c.id))
          .map(c => m.copy(company = Some(c)))
          .getOrElse(m)
      })
  }
}

Member
  .joins(Member.companyOpt)
  .includes(Member.companyOpt)
  .findById(123) // member.company.country is accessible

// joins: will join in company with the main member loading query
// includes: will create another query to load companies with their countries (remember countryOpt is .byDefault).
// the scala merge fn will then lookup members company and copy in the companyWithCountry

Note: when your entities have the same associations (e.g. Company has Employee, Country and Employee have Country), avoiding using the default alias for the associations is recommended because that may cause invalid join query generation when you use eager loading. We plan to provide more useful error messages for such cases in version 1.0.0.

Source code: orm/src/main/scala/skinny/orm/feature/IncludesFeature.scala

Other Configurations

Skinny ORM provides the following settings to overwrite.

object GroupMember 
  extends SkinnyCRUDMapper[Member]
  with TimestampsFeature[Member]
  with OptimisticLockWithTimestampFeature[Member] 
  with OptimisticLockWithVersionFeature[Member]
  with SoftDeleteWithBooleanFeature[Member]
  with SoftDeleteWithTimestampFeature[Member] {

  // default: 'default
  override def connectionPoolName = 'legacydb

  // default: None 
  override def schemaName = Some("public")

  // Basically tableName is loaded from jdbc metadata and cached on the JVM
  // However, if the name of object/class which extends SkinnyMapper is not the camelCase of table name,
  // you need to override tableName by yourself.
  override def tableName = "group_members" // default: group_member 

  // Basically columnNames are loaded from jdbc metadata and cached on the JVM
  override def columnNames = Seq("id", "name", "birthday", "created_at")

  // field name which represents the (single) primary key column
  // default: "id"
  override def primaryKeyFieldName = "uid" 

  // with SkinnyCRUDMapper[Member]
  // createWithAttributes will try to return generated id if true
  // default: true
  override def useAutoIncrementPrimaryKey = false

  // with SkinnyCRUDMapper[Member]
  // default scope for update operations
  // default: None
  override def defaultScopeForUpdateOperations = Some(sqls.isNull(column.deletedAt))

  // with TimestampsFeature[Member]
  // default: "createdAt"
  override def createdAtFieldName = "createdTimestamp"

  // with TimestampsFeature[Member]
  // default: "updatedAt"
  override def updatedAtFieldName = "updatedTimestamp"

  // with OptimisticLockWithTimestampFeature[Member] 
  // default: "lockTimestamp"
  override def lockTimestampFieldName = "lockedAt"

  // with OptimisticLockWithVersionFeature[Member]
  // default: "lockVersion"
  override def lockVersionFieldName = "ver"

  // with SoftDeleteWithBooleanFeature[Member]
  // default: "isDeleted"
  override def isDeletedFieldName = "deleted"

  // with SoftDeleteWithTimestampFeature[Member]
  // default: "deletedAt"
  override def deletedAtFieldName = "deletedTimestamp"

}

Callbacks

Callbacks allow you to trigger logic before or after record creation, modification and deletion.

You can register multiple handlers to same event. Handlers will be executed in order.

object Member extends SkinnyCRUDMapper[Member] {

  // ------------------------
  // before/after creation
  // ------------------------

  beforeCreate((session: DBSession, namedValues: Seq[(SQLSyntax, Any)]) => {
    // do something here
  })
  // it's possible to register multiple handlers
  beforeCreate((session: DBSession, namedValues: Seq[(SQLSyntax, Any)]) => {
    // second one
  })

  afterCreate((session: DBSession, namedValues: Seq[(SQLSyntax, Any)], generatedId: Option[Long]) => {
    // do something here
  })

  // ------------------------
  // before/after modification
  // ------------------------

  beforeUpdateBy((s: DBSession, where: SQLSyntax, params: Seq[(SQLSyntax, Any)]) => {
    // do something here
  })
  afterUpdateBy((s: DBSession, where: SQLSyntax, params: Seq[(SQLSyntax, Any)], count: Int) => {
    // do something here
  })

  // ------------------------
  // before/after deletion
  // ------------------------

  beforeDeleteBy((s: DBSession, where: SQLSyntax) => {
    // do something here
  })
  afterDeleteBy((s: DBSession, where: SQLSyntax, deletedCount: Int) => {
    // do something here
  })

}

Dynamic Table Name

#withTableName enables using another table name only for the current query.

object Order extends SkinnyCRUDMapper[Order] {
  override def defaultAlias = createAlias("o")
  override def tableName = "orders"
}

// default: orders
Order.count()

// other table: orders_2012
Order.withTableName("orders_2012").count()

Source code: orm/src/main/scala/skinny/orm/feature/DynamicTableNameFeature.scala

Adding Methods

If you need to add methods, just write methods that use #findBy, #countBy or ScalikeJDBC’s APIs directly.

object Member extends SkinnyCRUDMapper[Member] {
  private[this] lazy val m = defaultAlias

  def findAllByGroupId(groupId: Long)(implicit s: DBSession = autoSession): Seq[Member] = {
    findAllBy(sqls.eq(m.groupId, groupId))
  }
}

If you’re using Skinny-ORM with Skinny Framework, skinny.orm.servlet.TxPerRequestFilter simplifies your applications.

// src/main/scala/Bootstrap.scala
class Bootstrap extends SkinnyLifeCycle {
  override def initSkinnyApp(ctx: ServletContext) {
    ctx.mount(new TxPerRequestFilter, "/*")
  }
}

And then your ORM models can retrieve the current DB session as a thread-local value per request, so you don’t need to pass DBSession value as an implicit parameter in each method.

def findAllByGroupId(groupId: Long): List[Member] = findAllBy(sqls.eq(m.groupId, groupId))

On the other hand, if you work with multiple threads for single HTTP request, you should be aware that the thread-local DB session won’t be shared.

If you use an alternative Id generator instead of the RDB’s auto-incremental value, set useExternalIdGenerator as true and implement the generateId method.

case class Member(uuid: UUID, name: String)

object Member extends SkinnyCRUDMapperWithId[UUID, Member] 
  with SoftDeleteWithBooleanFeatureWithId[UUID, Member] {
  override def defaultAlias = createAlias("m")

  override def primaryKeyFieldName = "uuid"

  // use alternative id generator instead of DB's auto-increment
  override def useExternalIdGenerator = true
  override def generateId = UUID.randomUUID

  override def idToRawValue(id: UUID) = id.toString
  override def rawValueToId(value: Any) = UUID.fromString(value.toString)

  def extract(rs: WrappedResultSet, m: ResultName[Member]) = new Member(
    uuid = rawValueToId(rs.string(m.uuid)),
    name = rs.string(m.name)
  )
}

val m: Option[Member] = Member.findById(UUID.fromString("....."))

Don’t worry. Skinny-ORM does well at resolving associations even if you use custom primary keys.

ActiveRecord-like Timestamps

timestamps from ActiveRecord is available as the TimestampsFeature trait.

By default, this trait expects two columns on the table - created_at timestamp not null and updated_at timestamp. If you need customizing, override *FieldName methods as follows.

class Member(id: Long, name: String, createdAt: DateTime, updatedAt: DateTime)

object Member extends SkinnyCRUDMapper[Member] with TimestampsFeature[Member] {

  // created_timestamp
  override def createdAtFieldName = "createdTimestamp"
  // updated_timestamp
  override def updatedAtFieldName = "updatedTimestamp"
}

Source code: orm/src/main/scala/skinny/orm/feature/TimestampsFeature.scala

Soft Deletion

Soft delete support is also available.

By default, deleted_at timestamp column or is_deleted boolean not null is expected.

object Member extends SkinnyCRUDMapper[Member]
  with SoftDeleteWithTimestampFeature[Member] {

  // deleted_timestamp timestamp
  override val deletedAtFieldName = "deletedTimestamp"
}

Source code: orm/src/main/scala/skinny/orm/feature/SoftDeleteWithBooleanFeature.scala

Source code: orm/src/main/scala/skinny/orm/feature/SoftDeleteWithTimestampFeature.scala

Optimistic Lock

Furthermore, optimistic lock is also available.

By default, lock_version bigint not null or lock_timestamp timestamp is expected

object Member extends SkinnyCRUDMapper[Member]
  with OptimisticLockWithVersionFeature[Member]
// lock_version bigint

Source code: orm/src/main/scala/skinny/orm/feature/OptimisticLockWithVersionFeature.scala

Source code: orm/src/main/scala/skinny/orm/feature/OptimisticLockWithTimestampFeature.scala

SkinnyRecord

SkinnyRecord is a trait which extends entity classes. When an entity is a SkinnyRecord, it acts like a Rails ActiveRecord object.

class Member(id: Long, name: String, createdAt: DateTime, updatedAt: DateTime) 
  extends SkinnyRecord[Member] {

  def skinnyCRUDMapper = Member
}

object Member extends SkinnyCRUDMapper[Member] {
  ...
}

Member.findById(id).map { member =>
  member.copy(name = "Kaz").save()
  member.destroy()
}

Source code: orm/src/main/scala/skinny/orm/SkinnyRecordBaseWithId.scala

FactoryGirl

An easy-to-use fixture tool named FactoryGirl makes testing easy.

See in detail here: FactoryGirl

FAQ

Play Framework support

Logo

Skinny ORM is an independent library from Skinny environment. You can use Skinny ORM in your Play apps.

See the following example app:

https://github.com/skinny-framework/skinny-orm-in-play

Code generators are also available. They will help you when introducing Skinny ORM into your apps.

https://github.com/skinny-framework/skinny-orm-in-play/blob/master/task/TaskRunner.scala

UnexpectedNullValueException

If you see the following exception, your application code using Skinny ORM has a bug. This is not a framework bug.

Caused by: scalikejdbc.UnexpectedNullValueException: null
  at scalikejdbc.TypeBinder$.scalikejdbc$TypeBinder$$throwExceptionIfNull(TypeBinder.scala:140) ~[scalikejdbc_2.10-1.7.4.jar:1.7.4]
  at scalikejdbc.TypeBinder$$anonfun$38.apply(TypeBinder.scala:82) ~[scalikejdbc_2.10-1.7.4.jar:1.7.4]
  at scalikejdbc.TypeBinder$$anonfun$38.apply(TypeBinder.scala:82) ~[scalikejdbc_2.10-1.7.4.jar:1.7.4]
...

Typical Example

Typically, the following code will throw UnexpectedNullValueException when the groupId is null.

create table member (
  id serial primary key,
  name varchar(256) not null,
  groupId integer
  created_at timestamp not null
  updated_at timestamp not null
);

case class Member(
  id: Long,
  name: String,
  groupId: Int,
  createdAt: DateTime,
  updatedAT: DateTime)

object Member extends SkinnyCRUDMapper[Member] with TimestampsFeature[Member] {
  override def defaultAlias = createAlias("m")

  override def extract(rs: WrappedResultSet, n: ResultName[Member]) = new Member(
    id = rs.long(n.id), 
    name = rs.string(n.name),
    groupId = rs.int(n.groupId), // nullable but scala.Int is not nullable!!!
    createdAt = rs.dateTime(n.name),
    updatedAt = rs.dateTime(n.name),
  )
}

You must update Member class as follows:

case class Member(
  id: Long,
  name: String,
  //groupId: Int,
  groupId: Option[Int],
  createdAt: DateTime,
  updatedAT: DateTime)

Java SE 8 Date Time API (JSR-310)

Currently we still keep supporting Java SE 7 users, so the feature is not enabled by default. You can use them with scalikejdbc-jsr310 dependency. First, add the following dependency to libraryDependencies.

libraryDependencies += "org.scalikejdbc" %% "scalikejdbc-jsr310" % "4.0.0"

And then, import scalikejdbc.jsr310._ into your mapper classes. That’s all.

If you find a typo or mistake in this page, please report or fix it. How?

Skinny Framework - Rapid Development in Scala -

Skinny Micro:

Contents:

Older Versions:

ORM

Skinny-ORM

Minimum Setup

build.sbt

src/main/resources/application.conf

Understanding Basic Mapper Traits

SkinnyMapper

SkinnyCRUDMapper

SkinnyCRUDMapperWithId

SkinnyNoIdMapper, SkinnyNoIdCRUDMapper

SkinnyJoinTable

Transaction

Useful APIs by Skinny ORM

Associations

BelongsTo

HasOne

HasMany

Entity Equality

Eager Loading

Other Configurations

Callbacks

Dynamic Table Name

Adding Methods

ActiveRecord-like Timestamps

Soft Deletion

Optimistic Lock

SkinnyRecord

FactoryGirl

FAQ

Play Framework support

UnexpectedNullValueException

Typical Example

Java SE 8 Date Time API (JSR-310)