Creating a database and table

Before being able to talk to a database from Squeryl, you must create the database and table(s) you want to access. Assuming you have postgreSQL installed, this should fairly simple but can seem complex if you run into errors. First, open a terminal and run the following command:

createdb exampleDatabase

If you encounter errors running this command, refer back to this page to diagnose the issue. After this command completes you should have a database running called “exampleDatabase”. Enter this database using:

psql exampleDatabase

Then, create a table named emails by running:

CREATE TABLE emails(sender char(80), recipient char(80), content text, timestamp date);

This command creates a table with columns named sender, recipient, content, and timestamp. What the 80 inside char() signifies is that the value can only hold up to 80 characters. The text type, however, holds unlimited characters. To verify the table was created, run the command \dt to view all the tables in your database.

Build.sbt file

Your build.sbt file will need to include the postgres and squeryl libraries and use a version of scala that they’re compatible with. Here’s an example you can use:

name := "DatabaseExperimenting"

version := "1.0"

scalaVersion := "2.10.0"

libraryDependencies ++= Seq(
  "org.squeryl" %% "squeryl" % "0.9.5-6",
  "postgresql" % "postgresql" % "8.4-701.jdbc4"
)

Imports

You will need the folling imports to get the program to run:

import org.squeryl.PrimitiveTypeMode._
import org.squeryl.Schema
import org.squeryl.annotations.Column
import org.squeryl.Session
import org.squeryl.SessionFactory
import org.squeryl.adapters.PostgreSqlAdapter
import java.util.Calendar

Email Class

To easily interact with our database and provide useful values, we will create a case class called Email. This should store values that correspond to columns in the table we just created. Here is the code for such class:

case class Email(@Column("sender") sender: String,
		@Column("recipient") recipient: String,
		@Column("content") content: String,
		@Column("timestamp") val timeStamp:java.util.Date = Calendar.getInstance().getTime())

As you can see, we use the @Column syntax to map a value to a column in the database. Timestamp also has a default value. This is useful in the case that you are creating an email, it will automatically put the date on it. However, it can be overridden when creating an instance of Email so that if you are reading from the database it won’t just put the current date on the email.

Setting up a connection to the database

To be able to actually perform operations on the database, you must connect to the database. For this simple example you could put the following code inside of an object that extends Application so it will automatically run when you run the program.

Class.forName("org.postgresql.Driver")
SessionFactory.concreteFactory = Some(()=>
	Session.create(
  		java.sql.DriverManager.getConnection("jdbc:postgresql://localhost/exampleDatabase", "username", "password"),
  		new PostgreSqlAdapter()))

This creates a session that allows us to invoke transactions. Notice there is “exampleDatabase” within our path. This could be changed to correspond to different databases in different scenarios. There is also “username” and “password”. For the program to run, these need to be changed to the credentials you logged into your database with.

Creating an object to hold a reference to the table

To create a table in our program that corresponds with the table in the database, we need an object that extends Schema. We want an object since objects are singletons and will only ever have one instance of the table.

object EmailSystem extends Schema {
	  val emails = table[Email]("emails")
  }

Creating transactions

When interacting with a table, you must run your operation within a transaction. First we’ll create a transaction that inserts some dummy values into the table.

transaction{
	EmailSystem.emails.insert(Email("Joe", "John", "Hello John"))
	EmailSystem.emails.insert(Email("Ping", "Pong", "0ms response"))
}

This will place two rows into our table every time you run the program since we don’t check if they’re unique. To read all of the emails back from the database you could create a transaction like this:

transaction {
	val queriedEmails: List[Email] = from(EmailSystem.emails)(e => select(e)).toList

   	queriedEmails.foreach(printEmail)
}

This loads the whole database into a list of emails since we don’t have a “WHERE” clause. We then print the emails to the console using a custom printEmail function that you will have to create. If we want to be selective in which emails we get, a “WHERE” statement could be implemented like this:

transaction {
	val queriedEmails: List[Email] =
	  from(EmailSystem.emails)(e => where(e.sender === "Ping") select(e)).toList

    queriedEmails.foreach(printEmail)
}

You should have a simple postgres program now. Feel free to go beyond and try other things not covered in this example.