Scala – Connecting a Scala app to MS SQL Server with Squeryl

For all of our Scala applications up until now, we have being using MongoDB. However, recently we needed to connect one of our Scala applications to Microsoft SQL Server database. One of my excellent co-workers pointed us at a really nice project called Squeryl which provides a very simple library for doing just that. The documentation is excellent and great examples are provided to help get you up and running quickly.

Let’s look at a simple example.

Create a table in your SQL Server database:

CREATE TABLE [dbo].[Customer](
[id] [int] NOT NULL,
[name] [varchar] (100) NOT NULL,
CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED
(
[id] ASC
)) ON [PRIMARY]

Insert a row:

INSERT INTO dbo.Customer(id, name)
VALUES(1,'John Smith')

Get all of the libraries you will need to make the program run:

• Download Squeryl jar (2.9.0)
• Download jtds driver jar (1.2.5)
• Download cglib jar (2.2.2)
• Download asm jar (3.1)

Now, let’s run our test code to select a row from our Customer table:

package spikes

import org.squeryl.Session
import org.squeryl.adapters.MSSQLServer
import org.squeryl.SessionFactory
import org.squeryl.PrimitiveTypeMode._
import org.squeryl.Schema

// Create a Customer class which has the same fields as the Customer table in SQL Server
case class Customer(id: Long, name: String) {}

object SqlSpike extends App with Schema {
val databaseConnectionUrl = "jdbc:jtds:sqlserver://myservername;DatabaseName=mydatabasename"
val databaseUsername = "myusername"
val databasePassword = "password"

// Set the jtds driver
Class.forName("net.sourceforge.jtds.jdbc.Driver")

// Connect to the database
SessionFactory.concreteFactory = Some(()=>
Session.create(
java.sql.DriverManager.getConnection(databaseConnectionUrl, databaseUsername, databasePassword),
new MSSQLServer))

// Setup the Customer class to be mapped to the "Customer" table in SQL Server
val customers = table[Customer]("Customer")

// Select Customer with id=1 from the Customer table
transaction {
val customer = customers.where(c=> c.id === 1).single
println("Customer name: " + customer.name)
}
}

Finally, the output of the program:

[info] Running spikes.SqlSpike
Customer name: John Smith
[success] Total time: 4 s, completed 29-Feb-2012 8:42:24 PM

Overall we have found the Squeryl library to be very useful. If you need to connect a Scala application to a SQL database I recommend you give it a try, it should save you some aggravation.