Table creation in play 2.4 with play-slick 1.0

Question

I got the play-slick module up and running and am also using evolution in order to create the required tables in the database during application start.

For evolution to work it is required to write a 1.sql script which contains the table definitions that I want to create. At the moment it looks like this:

# --- !Ups

CREATE TABLE Users (
  id UUID NOT NULL,
  email varchar(255) NOT NULL,
  password varchar(255) NOT NULL,
  firstname varchar(255),
  lastname varchar(255),
  username varchar(255),
  age varchar(255),
  PRIMARY KEY (id)
);

# --- !Downs

DROP TABLE Users;

So far so good but for Slick to work correctly it also need to know the definition of my table. So I have a UserDAO object which looks like this:

class UserDAO @Inject()(protected val dbConfigProvider: DatabaseConfigProvider) extends HasDatabaseConfigProvider[JdbcProfile] {
  import driver.api._

  private val Users = TableQuery[UsersTable]

  def all(): Future[Seq[User]] = db.run(Users.result)

  def insert(user: User): Future[User] = db.run(Users += user).map { _ => user }

  //Table definition
  private class UsersTable(tag:Tag) extends Table[User](tag,"users"){
    def id = column[UUID]("id", O.PrimaryKey)
    def email = column[String]("email")
    def password = column[String]("password")
    def firstname = column[Option[String]]("firstname")
    def lastname = column[Option[String]]("lastname")
    def username = column[Option[String]]("username")
    def age = column[Int]("age")

    def * = (id, email,password,firstname,lastname,username,age) <> ((User.apply _).tupled, User.unapply)
  }
}

I basically have the same table definition in two different places now. Once in the 1.sql script and once in the UserDAO class.

I really don’t like this design at all! Having the same table definitions in two different places doesn't seem to be right.

Is there some way to generate the evolution scripts from the table definitions inside UserDAO classes? Or is there a completely different way to generate the table definitions during startup (perhaps only using slick)? I really would like to only use the slick table definition and get rid of the annoying SQL scripts.

I am using play-2.4 and play-slick-1.0

Thanks a lot.

Answer 1

Great question - I was in the same boat as you!

I'd have just the DAO and this code:

TableQuery[UsersTable].schema.create

which'll create the database table for you. No need for the .sql.

Correspondingly, to drop, use .drop instead of .create.

You can also combine table creation of several tables using reduceLeft. Here's how I do it:

lazy val allTables = Array(
  TableQuery[AcceptanceTable].schema,
  [... many more ...]
  TableQuery[UserTable].schema
).reduceLeft(_ ++ _)

/** Create all tables in database */
def create = {
  allTables.create
}

/** Delete all tables in database */
def drop = {
  allTables.drop
}

All that will need the driver API in scope such as:

val profile = slick.driver.H2Driver
import profile.api._