SCALA : Connecting to a database

This time we will proceed to look at using Scala to connect to SQL server.

In .NET we have quite a few ORM choices available, as well as standard ADO.NET. For example we could use any of the following quite easily

• Linq to SQL
• Entity Framework
• Dapper
• NHibernate
• ADO .NET

In Scala things are a bit more tame on the ORM front. We basically only have one player, which is called “Slick”. The rest of this post will be about how to use Slick.

Slick

The good thing about Slick is that it works with a wide range of SQL dialects. For this post I will be using what I know which is MS SQL server. As such I will be using a MS SQL server driver, and there may be differences between the driver I use and other Slick drivers, but hopefully you will get the idea.

Notes on MS SQL Server

The following notes assume you are install

I found that I had to do the following to get Slick to work with MS SQL Server

• Turn on the TCP/IP
• Insure that the full set of SQL server services were running for the Slick Extension SQL Server driver to work.

Demo IntelliJ IDEA Project

As this one is quite a lot bigger than the previous Scala posts. I have decided to upload this one to GitHub.

You can grab the project from here :

https://github.com/sachabarber/ScalaSlickTest

But before you try and run it you should make sure you have done the following :

• Created a MS SQL Server DB
• Run  the schema creation scripts included in the IntelliJ IDEA project
• Changed the “application.conf” file to point to YOUR SQL Server installation

The rest of this post will deal with how to do various things using Slick such as:

• Use direct SQL commands (sql strings)
• Use the slick ORM for CRUD
• Use a store procedure with Slick

But before we get on to any of that lets just outline the schema we will be working with. The one and only table we will be using is this one :

So now that we know what the single (I know lame we should have had more, but meh) table looks like lets crack on

NOTE : In the examples shown in this post I am using the Scala Async Library that I have talked about before.

Using Direct SQL Commands

In this section we will see how we can use Slick to run arbitrary SQL commands. Lets see some examples

Return a Scalar value

Say we only want 1 value back. Perhaps count of the rows. We can just do this:

def selectScalarObject(db:Database) : Unit = {
 
  val action = sql"""Select count(*) as 'sysobjectsCount'  from sysobjects""".as[Int]
  val futureDB : Future[Vector[Int]] = db.run(action)
 
  async {
    val sqlData = await(futureDB)
    val count = sqlData.head
    println(s"PlainSQLHelper.selectScalarObject() sysobjectsCount: $count")
  } onFailure {
    case e => {
      println(s"ERROR : $e")
    }
  }
}

Return more than 1 value

We may of course want a couple of values, but we are not quite ready to return a brand new entity. So we can use a Tuple.

Here is an example:

def selectTupleObject(db: Database) : Unit = {
 
  val action = sql"""Select count(*)  as 'sysobjectsCount', count(*)/10  as 'sysobjectsCountDiv10' from sysobjects""".as[(Int,Int)]
  val futureDB : Future[Vector[(Int,Int)]] = db.run(action)
 
  async {
    val sqlData = await(futureDB)
    val (x,y) = sqlData.head
    println(s"PlainSQLHelper.selectTupleObject() sysobjectsCount: $x, sysobjectsCountDiv10: $y")
  } onFailure {
    case e => {
      println(s"ERROR : $e")
    }
  }
}

Return a case class

We can obviously make things more formal, and be nice and return  a nice case class. Here is an example of that:

def selectRawTableObject(db: Database) : Unit = {
 
  val action = sql"""Select * from Items""".as[(Int,String, Double, Int)]
  val futureDB : Future[Vector[(Int,String, Double, Int)]] = db.run(action)
 
  async {
    val sqlData = await(futureDB)
    val (id,desc, cost, location) = sqlData.head
    val item = RawSQLItem(id,desc, cost, location)
    println(s"PlainSQLHelper.selectRawTableObject() Id: ${item.id}, Description: ${item.description}, Cost: ${item.cost}, WarehouseLocation: ${item.warehouseLocationId}")
  } onFailure {
    case e => {
      println(s"ERROR : $e")
    }
  }
}
 
 
case class RawSQLItem(id: Int, description: String, cost: Double,  warehouseLocationId: Int)

Using The Slick ORM For CRUD

These examples show how you can do the basic CRUD operations with Slick.

However before we start to look at the CRUD operations, lets just see a bit of basic Slick code. Slick uses a trait called Table which you MUST mixin. It is also common practice that we use a companion object to create a TableQuery[T]. Here is the one for the CRUD operations we will be looking at next

package org.com.barbers.slicktest
 
import com.typesafe.slick.driver.ms.SQLServerDriver.api._
 
object Items {
  val items = TableQuery[Items]
}
 
case class DBItem(id: Int, description: String, cost: Double,  warehouseLocationId: Int)
 
class Items(tag: Tag) extends Table[DBItem](tag, "Products") {
  def id = column[Int]("Id", O.PrimaryKey, O.AutoInc)
  def description = column[String]("Description")
  def cost = column[Double]("Cost")
  def warehouseLocationId = column[Int]("WarehouseLocationId")
  def * = (id, description, cost, warehouseLocationId) <> (DBItem.tupled, DBItem.unapply)
}

Create

Ok so now we have seen that Slick uses a Table mixin, and that there is a TableQuery[T] at play. Let’s move on to see how we can create some data.

This is quite weird to do. Normally what we want from a INSERT is an Id. How Slick does that is a bit strange. We need to use the Slick DSL to say what we would like returned (the “Id”), which we do using the “returning” followed by the map of the Items table. This may sound weird but the example below may help to illustrate this a bit. Here is how we do it:

def saveItem(db: Database, item: DBItem) = {
 
  val action =(Items.items returning Items.items.map(_.id)) +=
    DBItem(-1, item.description, item.cost, item.warehouseLocationId)
  val futureDB : Future[Int] = db.run(action)
 
  async {
    val savedItemId = await(futureDB)
    println(s"TableResultRunner.saveItem() savedItem.Id ${savedItemId}")
  } onFailure {
    case e => {
      println(s"ERROR : $e")
    }
  }
}

And here is how we store several items. For a bulk insert, we can’t really get the inserted Ids. But we can add all Items in on go using the standard Scala collection operator ++=, which appends a new collection to the current collection.

Again an example will make this clearer

def insertSeveralItems(db: Database, items : List[DBItem]) : Unit = {
 
  implicit val session: Session = db.createSession()
  val insertActions = DBIO.seq(
    (Items.items ++= items.toSeq).transactionally
  )
  val sql = Items.items.insertStatement
  val futureDB : Future[Unit] = db.run(insertActions)
 
  async {
    await(futureDB)
    println(s"TableResultRunner.insertSeveralItems() DONE")
  } onFailure {
    case e => {
      println(s"ERROR : $e")
    }
  }
}

Retrieve

So we now have some Items, so how do we get them back from the DB?

There are many ways to do this with Slick. Let’s use a simple Take(2) operation to start with

def selectTwoItems(db: Database) : Unit = {
 
  implicit val session: Session = db.createSession()
  val q =  Items.items.take(2)
  val futureDB : Future[Seq[DBItem]] = db.run(q.result)
 
  async {
    val sqlData = await(futureDB)
    val item = sqlData.head
    println(s"TableResultRunner.selectTwoItems()[0] " +
      s"Id: ${item.id}, Description: ${item.description}, " +
      s"Cost: ${item.cost}, WarehouseLocationId: ${item.warehouseLocationId}")
  } onFailure {
    case e => {
      println(s"ERROR : $e")
    }
  }
}

We can also use Queries to filter out what we want from the DB. Here is an example of using a Query, where we use a filter to get all Items that have a Id that matches a Id

def findItemById(db: Database,id : Int) = {
 
  async {
    val q = for { p <- Items.items if p.id === id } yield p
    val futureDBQuery : Future[Option[DBItem]] = db.run(q.result.headOption)
    val item : Option[DBItem] = await(futureDBQuery)
    println(s"OPTION ${item}")
    item match {
      case Some(x) =>  println(s"TableResultRunner.findItemById The item is $x")
      case _ => ()
    }
  } onFailure {
    case e => {
      println(s"ERROR : $e")
    }
  }
}

Update

Update is a stranger on. Where we get out only the attributes we want from the DB using a query, and then use Slicks inbuilt update(..) function to perform the update on the columns we want. This is clearer with an example.

In this example we want to update ONLY the “cost” column of an Item.

def updateItemCost(db: Database, description : String, cost : Double) = {
 
  async {
    val q = Items.items
      .filter(_.description === description)
      .map(_.cost)
      .update(cost)
 
    val futureDB = db.run(q)
    val done = await(futureDB)
    println(s"Update cost of ${description}, to ${cost}")
 
    val q2 = for { p <- Items.items if p.description === description } yield p
    val futureDBQuery : Future[Seq[DBItem]] = db.run(q2.result)
    val items = await(futureDBQuery)
    items.map(item => println(s"TableResultRunner.updateItemCost The item is now $item") )
  } onFailure {
    case e => {
      println(s"ERROR : $e")
    }
  }
}

Delete

Lastly we would like to delete an Item. So let’ see how we can do that. Again we use some Slick magic for this, where we use the .delete() function. Here is an example where I delete a random Item from the DB.

def deleteRandomItem(db: Database) = {
 
  async {
    val q =  Items.items.take(1)
    val futureDB : Future[Seq[DBItem]] = db.run(q.result)
    val sqlData = await(futureDB)
    val item = sqlData.head
    val deleteFuture : Future[Unit] = db.run(
      Items.items.filter(_.id === item.id).delete).map(_ => ())
    await(deleteFuture)
    println(s"TableResultRunner.deleteRandomItem() deleted item.Id ${item.id}")
  } onFailure {
    case e => {
      println(s"ERROR : $e")
    }
  }
}

Calling A Stored Procedure

To call a stored procedure is a as simple as using the db session, and building out the call to the right stored procedure:

Say we have this stored procedure:

USE [SLICKTEST]
GO
 
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE PROCEDURE [dbo].[sp_SelectItemsByDescription]
    (
      @description NVARCHAR(MAX)
    )
AS
BEGIN
    SET NOCOUNT ON;
 
    select * from Items i where i.[Description] LIKE '%' + @description + '%'
 
END
 
GO

This is how we would call it using slick

def selectItems(db: Database, description: String): Unit = {
 
  val sqlStatement = db.source.createConnection().prepareCall(
    "{ call [dbo].[sp_SelectItemsByDescription](?) }",
    ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY)
 
  sqlStatement.setFetchDirection(ResultSet.FETCH_FORWARD);
  sqlStatement.setString("@desc", description)
 
  val rs = sqlStatement.executeQuery()
 
  while (rs.next()) {
    val item = new DBItem(
      rs.getInt("Id"),
      rs.getString("Description"),
      rs.getDouble("Cost"),
      rs.getInt("WarehouseLocationId"))
 
    println(s"StoredProcedureHelper.selectProducts " +
      "using description set to ${desc} got this result : " +
      s"Id: ${item.id}, Description: ${item.description}, " +
      s"Cost: ${item.cost}, WarehouseLocationId: ${item.warehouseLocationId}")
  }
 
  rs.close()
  sqlStatement.close()
}