Defining database table columns with Slick 1.0.1

This tutorial shows you how to define different kinds of database \ columns (a.k.a. attributes) in a [Slick](http://slick.typesafe.com) \ application.

Using the Scala console with Play and Slick shows how to use a Play Framework application’s Scala console to inspect the SQL that Slick generates for a database table definition. The example used a trivial table definition, with simple columns.

In practice, you need to know how to specify additional properties and constraints. After all, although relational databases aren’t especially strongly typed, not everything is a VARCHAR NOT NULL.

Summary

You’ll need to know how to complete the following tasks.

Defining a nullable column
Defining a primary key column
Defining a foreign key relationship
Defining a unique key constraint
Using database-specific types

Source code: https://github.com/lunatech-labs/play-slick-examples.

Defining a nullable column

A trivial table definition with a single String column results in a VARCHAR NOT NULL column. To see this, use the instructions for using the Scala console with Play and Slick to start a Play application on the console. Then paste the following console transcript into the Play application’s Scala console.

scala> import play.api.db.slick.Config.driver.simple._
import play.api.db.slick.Config.driver.simple._

scala> class Cocktails extends Table[(String)]("COCKTAIL") {
	 |   def name = column[String]("NAME")
	 |   def * = name
	 | }
defined class Cocktails

scala> (new Cocktails).ddl.createStatements.mkString
res1: String = create table "COCKTAIL" ("NAME" VARCHAR NOT NULL)

This example is using a Play application whose configuration defines db.default.driver=org.h2.Driver, so this is the H2 database’s SQL dialect.

For a nullable column, you need SQL DDL without the NOT NULL option on the NAME column. In the Slick table definition, declare the column’s type as Option[String].

scala> class Cocktails extends Table[(Option[String])]("COCKTAIL") {
	 |   def name = column[Option[String]]("NAME")
	 |   def * = name
	 | }
defined class Cocktails

scala> (new Cocktails).ddl.createStatements.mkString
res2: String = create table "COCKTAIL" ("NAME" VARCHAR)

Don’t forget that the column types in the Table type parameter must match the individual column type parameters, or you’ll get a type mismatch' compilation error for the `* projection.

Defining a primary key column

You probably want all of your database tables to have a synthetic primary key - a generated numeric key that is independent of application data and has no meaning other than entity identity. In database terms, this means declaring a column with a primary key constraint and a generation strategy.

Slick provides column options for primary keys and auto-incrementing values: O.PrimaryKey and O.AutoInc, where O is the Table class’ column options field. Add these column options as additional parameters to the id column definition.

class Cocktails extends Table[(Long, String)]("COCKTAIL") {
  def id = column[Long]("ID", O.PrimaryKey, O.AutoInc)
  def name = column[String]("NAME")
  def * = id ~ name
}

This results in the following SQL DDL, for H2.

create table "COCKTAIL" (
  "ID" BIGINT GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,
  "NAME" VARCHAR NOT NULL)

For comparison, here’s the equivalent syntax that Slick’s MySQL driver generates.

create table `COCKTAIL` (
  `ID` BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `NAME` VARCHAR(254) NOT NULL)

There’s a benefit of Slick here: column options like O.PrimaryKey and O.AutoInc are easier to read and remember than the corresponding database-specific SQL DDL syntax. This is especially helpful if you don’t always use the same database.

Defining a foreign key relationship

Perhaps the next addition in any non-trivial database is a foreign key relationship, which you need for efficient join queries. After all, a database without joins is just a spreadsheet, which is less cool.

First, use what you’ve learned so far to define another table to use as the target of your foreign key relationship. Continuing with the mixed drinks example, define a table for the main ingredient in each cocktail. Tequila, for example.

class Ingredients extends Table[(Long, String)]("INGREDIENT") {
  def id = column[Long]("ID", O.PrimaryKey, O.AutoInc)
  def name = column[String]("NAME")
  def * = id ~ name
}

To start with, you just do the obvious thing: add a mainIngredientId column to the cocktails table to store an Ingedients.id value:

class Cocktails extends Table[(Long, String, Long)]("COCKTAIL") {
  def id = column[Long]("ID", O.PrimaryKey, O.AutoInc)
  def name = column[String]("NAME")
  def mainIngredientId = column[Long]("MAIN_INGREDIENT_ID")
  def * = id ~ name ~ mainIngredientId
}

Note that the mainIngredientId column’s type is Long, which matches the ID type that you used for Ingedients.id. The additional column is not enough, though; you also need to add a foreign key constraint. For that, you need an additional member in the Cocktails table definition.

def mainIngredient = foreignKey("MAIN_INGREDIENT_FK", mainIngredientId, new Ingredients)(_.id)

You need four parameters to define the foreign key:

"MAIN_INGREDIENT_FK" - the constraint name
mainIngredientId - the foreign key column
new Ingredients - the foreign key reference’s target table
_.id - the referenced column in the target table - its primary key.

Bringing this all together, by pasting the definitions in the Scala console as before, you get the following DDL (for H2).

create table "COCKTAIL" (
  "ID" BIGINT GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,
  "NAME" VARCHAR NOT NULL,
  "MAIN_INGREDIENT_ID" BIGINT NOT NULL)

alter table "COCKTAIL" add constraint "MAIN_INGREDIENT_FK"
  foreign key("MAIN_INGREDIENT_ID") references "INGREDIENT"("ID")
  on update NO ACTION on delete NO ACTION

Now there are two SQL statements: a Slick table’s DDL is not necessarily a single SQL statement. Don’t forget to add a semicolon at the end of each statement if you want to execute them as an SQL script.

Defining a unique key constraint

You may want to constrain cocktail names to be unique, which makes sense, because there’s only one (right) way to make a Margarita. For this, you need to add a database-level uniqueness constraint.

Unique key constraints are similar to foreign key contraints - essentially another database index. The Slick definition is similar to the foreign key declaration, but in this case is just a named index for the name column, with an option for the uniqueness constraint.

def uniqueName = index("IDX_NAME", name, unique = true)

The resulting H2 SQL syntax turns out to be equally straightforward.

create unique index "IDX_NAME" on "COCKTAIL" ("NAME")

Using database-specific types

So far, you’ve only used the default mapping from Scala types to database types. However, this is not always what you want. For example, the default mapping for String columns is a limited-size VARCHAR type.

If you add a recipe column to the Cocktails table, you’ll need to allow for thousands of characters (because making good cocktails is all about getting the details right). In this case, you need to use the SQL standard CLOB (character large object) type.

You specify an alternative column type using the same kind of column option that you use to specify a primary key:

def recipe = column[Option[String]]("RECIPE", O.DBType("CLOB"))

In this definition, "CLOB" is the database-specific SQL DDL syntax, not Slick syntax. This is just passed-through to the resulting SQL DDL without being checked by Slick:

create table "COCKTAIL" (
  "ID" BIGINT GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,
  "NAME" VARCHAR NOT NULL,
  "RECIPE" CLOB,
  "MAIN_INGREDIENT_ID" BIGINT NOT NULL)

Similarly, you might use this to specify a type with a particular precision, such as mapping money values to a MySQL fixed-precision numeric type, with O.DBType("DECIMAL(13,2)").

Next steps

Now you have used Slick to define the usual types of table columns, you can: