Wanted: Good examples of Scala database persistence

Question

I'm would like to use Scala to persist data to a relational database, so what I am looking for are examples of CRUD operations using Scala.

I would like to code on a lower level of abstraction than an ORM like Hibernate/Toplink (read:JDBC), but between us, I would like to see examples of all types.

Thanks folks.

Answer 1

Whilst there are Scala JDBC solutions out there, the lack of documentation made me stick with Spring DAO.

Spring's DAO abstraction layer is an excellent and lightweight layer on top of JDBC which allows you to forget about the boilerplate code handling Connections, Statements etc. Put the template-based mechanism together with implicit conversions and first-order functions and it's even better and fits well into Scala. As well as this (Java) example of using stored procedures (useful for creating/updating data), here is an example of it using scala to read data:

import java.util.{List => JList}

val jdbcTemplate = new SimpleJdbcTemplate(dataSource)
val sql = "select name, age from my_obj"
val os: JList[MyObj] = jdbcTemplate.query(sql, new ParametrizedRowMapper[MyObj] {
  def mapRow(rs: ResultSet, row: Int) : MyObj = {
      //convert a row of a result set to a MyObj
      MyObj(rs.getString("name"), rs.getInt("age"))
  }
}

Obviously this can be improved upon by using implicit conversions to convert a Function2[ResultSet,Int,MyObj] into the ParametrizedRowMapper and the jcl collection conversions also.

Spring DAO can be used outside the Spring container if needed (i.e. just used as a library) and it's extremely reliable due to the massive usage of Spring in Java land. The main drawback is that it doesn't make any use of the ability of a scala library to define a DSL.

Answer 2

A quick google search for "scala database dsl" gave me.

Scala Query

• http://github.com/szeiger/scala-query
• http://szeiger.de/blog/category/scala/scala-query/
• www.cs.uwm.edu/~dspiewak/papers/scalaql.pdf

I didn't find any others out there, maybe there is if I kept searching but this one looks ok.

Answer 3

I know of four usable non-ORM database libraries for Scala. There's also one ORM which I mention below because it doesn't hide SQL, which might just make it a good fit.

ScalaQuery

The first one is ScalaQuery. It is the most mature one, and it tries to make queries use the same for-comprehension as Scala collections do. As an example of syntax style (which might be slightly out of date):

import java.lang.Integer
import com.novocode.squery._
import com.novocode.squery.Implicit._
import com.novocode.squery.session._
import com.novocode.squery.session.SessionFactory._

// Define table:
object Users extends Table[(Integer, String, String)]("users") {
  def id = intColumn("id", O.AutoInc, O.NotNull)
  def first = stringColumn("first")
  def last = stringColumn("last")
  def * = id ~ first ~ last
}

// Basic usage
val sf = new DriverManagerSessionFactory("org.h2.Driver", "jdbc:h2:mem:test1")
sf withSession {
  // Prepare a simple query
  val q1 = for(u <- Users) yield u

  // Print SQL statement to be executed:
  println(q1.selectStatement)  // displays SELECT t1.id,t1.first,t1.last FROM users t1

  // Print query result:
  for(t <- q1) println("User tuple: "+t)

  // Query statements can also be used with updates:
  val q = for(u <- Users if u.id is 42) yield u.first ~ u.last
  q.update("foo", "bar")
}

Querulous

The second one is Querulous, which is a open source project from Twitter. This one gives you direct access to SQL, while dealing with a bunch of jdbc annoyances. Here's a simple example:

import com.twitter.querulous.evaluator.QueryEvaluator
val queryEvaluator = QueryEvaluator("host", "username", "password")
val users = queryEvaluator.select("SELECT * FROM users WHERE id IN (?) OR name = ?", List(1,2,3), "Jacques") { row =>
  new User(row.getInt("id"), row.getString("name"))
}
queryEvaluator.execute("INSERT INTO users VALUES (?, ?)", 1, "Jacques")
queryEvaluator.transaction { transaction =>
  transaction.select("SELECT ... FOR UPDATE", ...)
  transaction.execute("INSERT INTO users VALUES (?, ?)", 1, "Jacques")
  transaction.execute("INSERT INTO users VALUES (?, ?)", 2, "Luc")
}

Squeryl

The third one is Squeryl. Style-wise, it sits midway between ScalaQuery -- which hides SQL behind Scala comprehensions as much as possible -- and Querulous -- which uses SQL strings directly.

Squeryl provides a SQL-like DSL, which gives you type safety and give you a strong likelyhood that the statements won't fail at run-time if they compile at all. Again, a simple example:

// Defining tables and a schema:
import org.squeryl.PrimitiveTypeMode._

class Author(var id: Long, 
             var firstName: String, 
             var lastName: String)

class Book(var id: Long, 
           var title: String,
           @Column("AUTHOR_ID") // the default 'exact match' policy can be overriden
           var authorId: Long,
           var coAuthorId: Option[Long]) {
  def this() = this(0,"",0,Some(0L))
}

object Library extends Schema {
  //When the table name doesn't match the class name, it is specified here :
  val authors = table[Author]("AUTHORS")
  val books = table[Book]
}

// Basic usage
Class.forName("org.postgresql.Driver"); 
val session = Session.create( 
  java.sql.DriverManager.getConnection("jdbc:postgresql://localhost:5432/squeryl", "squeryl", "squeryl"), 
  new PostgreSqlAdapter 
) 

//Squeryl database interaction must be done with a using block :  
import Library._
using(session) { 
  books.insert(new Author(1, "Michel","Folco"))            
  val a = from(authors)(a=> where(a.lastName === "Folco") select(a)) 
}

O/R Broker

The fourth is O/R Broker, which, despite the name, is not an ORM. Classes can be designed in any way desired. No interfaces/traits to implement, no conventions to uphold, no annotations needed.

case class Song(id: Option[Long], title: String, seconds: Short)
case class Album(id: Option[Long], title: String, year: Short, songs: IndexedSeq[Song])
case class Artist(id: Option[Long], name: String, albums: Set[Album])

Extractors are declarative, written in Scala. Can be reused in other queries that fit the expectation of the extractor.

object SongExtractor extends JoinExtractor[Song] {
  val key = Set("SONG_ID")

  def extract(row: Row, join: Join) = {
    new Song(
          row.bigInt("SONG_ID"), 
          row.string("TITLE").get, 
          row.smallInt("DURATION_SECONDS").get
        )
  }
}

object AlbumExtractor extends JoinExtractor[Album] {
  val key = Set("ALBUM_ID")

  def extract(row: Row, join: Join) = {
    new Album(
          row.bigInt("ALBUM_ID"),
          row.string("TITLE").get,
          row.smallInt("YEAR_ISSUED").get,
          join.extractSeq(SongExtractor, Map("TITLE"->"SONG_TITLE"))
        )  
  }
}

object ArtistExtractor extends JoinExtractor[Artist] {
  val key = Set("ARTIST_ID")

  def extract(row: Row, join: Join) = {
    new Artist(
          row.bigInt("ARTIST_ID"),
          row.string("NAME"),
          join.extractSeq(AlbumExtractor)
        )
  }
}

One could then use that like this:

val ds: javax.sql.DataSource = ...
val builder = new SQLFileBuilder(ds, new java.io.File("sql/"))
val broker = builder.build()

// Print all artists with their albums (if any)
val artists = broker.readOnly() { session =>
  session.selectAll[Artist]('selectArtist) // ' I wish they could fix the Scala Symbol formatting
}
for (ar <- artists) {
  println(a.name)
      if (ar.albums.isEmpty)
        println("\t<No albums>")
      else for (al <- ar.albums) {
        println("\t" + al.title)
        for (s <- al.songs) {
          println("\t\t" + (al.songs.indexOf(s)+1) + ". " + s.title)
        }
      }
}

Circumflex ORM

Finally, there's Circumflex ORM. I'm copying here a few examples from their site:

class Category extends Record[Category] {
  val id = field(Category.id)
  val name = field(Category.name)
  val books = oneToMany(Book.category)    // allows navigating between associations transparently
}

object Category extends Table[Category] with LongIdPK[Category] {
  val name = stringColumn("name")         // creates a column
      .notNull                            // creates NOT NULL constraint
      .unique                             // creates UNIQUE constraint
      .validateNotEmpty                   // adds NotEmpty validation
      .validatePattern("^[a-zA-Z]{1,8}$") // adds Pattern validation
}

class Book extends Record[Book] {
  val id = field(Book.id)
  val title = field(Book.title)
  val category = manyToOne(Book.category)
}

object Book extends Table[Book] with LongIdPK[Book] {
  val title = stringColumn("title")
      .notNull
      .validateNotEmpty
  val category = longColumn("category_id")
      .references(Category)     // creates an association with Category
      .onDeleteSetNull          // specifies a foreign-key action
      .onUpdateCascade
}

new DDLExport(Category, Book).create   // creates database schema

// find category by id
val c = Category.get(2l)
// find all books
val allBooks = Book.all
// find books for category
val cBooks = c.get.books
// find books by title
Book.criteria.add("title" like "a%").list

select()
      .from(Category as "c" join (Book as "b"), Category as "c1")
      .where("c1.name" like "a%")
      .addOrder(asc("c.name"))
      .list

select(count("b.id"), "c.name").from(Category as "c" join (Book as "b")).list

If I missed any existing project, just drop a comment and I'll add them to this answer. Don't bother with blogs, papers, wikis or the like, though.

Answer 4

Keep in mind that Querulous is hard-coded (in many places) to use MySQL.

Answer 5

This was recently announced on the Scala-Announce mailing list:

http://code.google.com/p/orbroker/

• Full freedom on class design. O/R Broker does not place any limitations on how you design your classes. No restrictions whatsoever.

• You write the SQL. This allows you to hand tune any query, even after deployment, is faster than configuring some obscure XML syntax.

• Full support for JOIN queries, both one-to-one and one-to-many.

• No N+1 select problem and no transactionally inconsistent lazy loading

• Support for stored procedure calls.

• You write the query-to-object extractor code in Scala (or Java). No tired old XML mapping needed.

• SQL can be in code or, preferably, in simple text files, ready for editing and optimizing if needed.

• Dynamic SQL using Velocity or FreeMarker template engines. Both are supported, but neither are required.

• Dealing with new database schema, legacy schema, JavaBeans, or immutable classes? All possible, full flexibility.

Answer 6

Here's a complete Scala + JDBC example, this worked out to be the simplest solution I found.

import java.sql.{Connection, DriverManager, ResultSet};

// Change to Your Database Config
val conn_str = "jdbc:mysql:/localhost:3306/DBNAME?user=DBUSER&password=DBPWD"

// Load the driver
classOf[com.mysql.jdbc.Driver]

// Setup the connection
val conn = DriverManager.getConnection(conn_str)
try {
    // Configure to be Read Only
    val statement = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY)

    // Execute Query
    val rs = statement.executeQuery("SELECT quote FROM quotes LIMIT 5")

    // Iterate Over ResultSet
    while (rs.next) {
      println(rs.getString("quote"))
    }
}
finally {
    conn.close
}

I wrote up my complete experience here: http://mkaz.com/archives/1259/using-scala-with-jdbc-to-connect-to-mysql/

Answer 7

IMO JPA2.0 is still one of the most flexible and advanced concepts (especially using it with BeanValidation, JTA, JNDI, existing/complex relational schema etc.). It is true that JPA (as well as the most Java and Java annotation based specifications) does not fit nicely into Scala's concepts (especially collections that have to be converted). Nevertheless it can be used rather easily with some wrapper Classes and Objects.

some Advantages:

• Pluggable Implementations
• Widely used standard
• Widely available experience
• Supported by application server vendors

Three major JPA 2.0 implementations:

• EclipseLink
• OpenJPA
• Hibernate

Examples using some simple wrapping:

Entity Manager and Entity Manager Factory

class MyClass extends Something
    with SimpleEntityManagerFactory
    with ThreadLocalEntityManager {

 def getPersistenceUnitName = "mip"
 . . .
}

Object Item Entity

Uses inheritance strategy joined and a sequence for primary key

@Entity
@Table(name = "obj_item")
@Inheritance(strategy = InheritanceType.JOINED)
@SequenceGenerator(name = "obj_item_id_seq", sequenceName = "obj_item_id_sequence",         allocationSize = 1)
class ObjectItem extends MIPEntity {
  @Id
  @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "obj_item_id_seq")
  @Column(name = "obj_item_id", nullable = false, length = 20)
  @BeanProperty
  var id: BigInteger = _

  @Column(name = "cat_code", nullable = false, length = 6)
  @BeanProperty
  var objItemCatCode: String = _
}

Using Id Class Identity

More complex association using Id Class for Entity Identity Fields.

@Entity
@Table(name = "org_struct")
@IdClass(classOf[OrganisationStructureId])
@SequenceGenerator(name = "org_struct_index_seq", sequenceName = "org_struct_index_sequence", allocationSize = 1)
class OrganisationStructure extends MIPEntity {
  @Id
  @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "org_struct_index_seq")
  @Column(name = "org_struct_ix", nullable = false, length = 20)
  @BeanProperty
  protected var ix: BigInteger = _

  @Id
  @ManyToOne(fetch = FetchType.EAGER)
  @JoinColumn(name = "org_struct_root_org_id", nullable = false, updatable = false)
  @BeanProperty
  protected var orgStructRootOrg: Organisation = _

  . . .
}

Id Class for Entity Identity Fields:

class OrganisationStructureId {
  @BeanProperty
  var orgStructRootOrg: BigInteger = _
  @BeanProperty
  var ix: BigInteger = _
. . .
}

All this is provided by ScalaJPA and JPA-for-Scala (see Github). Both are rather small wrapper around usual JPA classes. The latter one provides some ideas for externalized query strings, filter objects and transaction scope wrappers. F.e.:

Using Filter and executing query:

. . .
val filter: NameFilter = newFilterInstance(QueryId("FindObjectItemFromNameWithFilter"))
filter.name = "%Test%"

var i = 0
forQueryResults {
  oi: ObjectItem =>
    i = i + 1
} withQuery (filter)
i must_== 10
. . .

Remove user:

withTrxAndCommit {
 findAndApply(id ) {
   u:User => remove(u)
 }
}

Execute a native PostGIS SQL query and expect one result:

withTrxAndCommit {
 oneResultQueryAndApply {
  d: Double =>
   eStatRet.setDistance(d)
  } withNativeQuery (QueryId("DistancePointFromTextToLocID"), postGISPoint, user.getUsersLocation.getId)
}

Answer 8

Since version 2.0 Querydsl supports Scala as well. It works well with JPA, JDO, Mongodb and SQL.

• for JPA, JDO and Mongodb see Querying with Scala
• for SQL see SQL queries in Scala

The SQL example is an example of a low abstraction level, and Querying with Scala for a high ORM-style abstraction.

I am the maintainer of Querydsl, so this answer is biased.