"Hello World" example for Slick 2.0 with MySQL

Question

Is there an example of how to use the Slick 2.0.1 library for Scala to connect to a MySQL database? I could not find up-to-date examples with Googling...

Recently, I have been thinking about porting the code for the web application that I have been developing with PHP into Scala. I have been looking for a programming language that has (i) a web application framework with a good number of users, (ii) offers compile time error-checking, (iii) nice testing tools, and so on... After reading about some case studies (e.g., huffpost and cousera) of developing web applications using Scala and Play framework, it started looking appealing to me and I decided to give it a shot.

As all the data from the previous project reside in a MySQL database, I was looking at what Scala offers for my program to connect to MySQL. I came across Slick. I followed the Hello-Slick example in the Typesafe Activator, which guided me how to use an in-memory database (i.e., h2) and I liked its syntax and the level of abstraction. Now, I want to use Slick with MySQL. I tried changing the import statements

import scala.slick.driver.H2Driver.simple._

to

import scala.slick.driver.MySQLDriver.simple._

in both HelloSlick.scala and Tables.scala in the aforementioned example (please see the full copy of the code below). However, I am getting the following error message when I compile it:

/Users/kotaro/Documents/Scala/hello-slick/src/test/scala/TablesSuite.scala

value ddl is not a member of scala.slick.lifted.TableQuery[Suppliers]

def createSchema() = (suppliers.ddl ++ coffees.ddl).create

                              ^

Does anyone know what is causing this? According to this post (slick exception when trying to connect to MySql), I might be missing to add some dependencies though the error message I'm getting is different. If there is a good example that I can follow to get a grasp of how to use Slick with MySQL, that would help a lot too.

I am using Scala 2.10.3, MySQL in XAMPP 1.7.3 with OS X 10.8 and JRE 1.6.

Thank you very much for your help in advance!

Following is the full copy of the code.

HelloSlick.scala

import scala.slick.driver.MySQLDriver.simple._
//import scala.slick.driver.H2Driver.simple._

// The main application
object HelloSlick extends App {

  // The query interface for the Suppliers table
  val suppliers: TableQuery[Suppliers] = TableQuery[Suppliers]

  // the query interface for the Coffees table
  val coffees: TableQuery[Coffees] = TableQuery[Coffees]

  // Create a connection (called a "session") to an in-memory H2 database
  Database.forURL("jdbc:mysql://localhost:3306/test", driver="com.mysql.jdbc.Driver", user="root", password="").withSession { implicit session =>

  // Create the schema by combining the DDLs for the Suppliers and Coffees tables using the query interfaces
    (suppliers.ddl ++ coffees.ddl).create


    /* Create / Insert */

    // Insert some suppliers
    suppliers += (101, "Acme, Inc.", "99 Market Street", "Groundsville", "CA", "95199")
    suppliers += (49, "Superior Coffee", "1 Party Place", "Mendocino", "CA", "95460")
    suppliers += (150, "The High Ground", "100 Coffee Lane", "Meadows", "CA", "93966")

    // Insert some coffees (using JDBC's batch insert feature)
    val coffeesInsertResult: Option[Int] = coffees ++= Seq (
      ("Colombian",         101, 7.99, 0, 0),
      ("French_Roast",       49, 8.99, 0, 0),
      ("Espresso",          150, 9.99, 0, 0),
      ("Colombian_Decaf",   101, 8.99, 0, 0),
      ("French_Roast_Decaf", 49, 9.99, 0, 0)
    )

    val allSuppliers: List[(Int, String, String, String, String, String)] = suppliers.list

    // Print the number of rows inserted
    coffeesInsertResult foreach (numRows => println(s"Inserted $numRows rows into the Coffees table"))


    /* Read / Query / Select */

    // Print the SQL for the Coffees query
    println("Generated SQL for base Coffees query:\n" + coffees.selectStatement)

    // Query the Coffees table using a foreach and print each row
    coffees foreach { case (name, supID, price, sales, total) =>
      println("  " + name + "\t" + supID + "\t" + price + "\t" + sales + "\t" + total)
    }


    /* Filtering / Where */

    // Construct a query where the price of Coffees is > 9.0
    val filterQuery: Query[Coffees, (String, Int, Double, Int, Int)] = coffees.filter(_.price > 9.0)

    println("Generated SQL for filter query:\n" + filterQuery.selectStatement)

    // Execute the query
    println(filterQuery.list)


    /* Update */

    // Construct an update query with the sales column being the one to update
    val updateQuery: Query[Column[Int], Int] = coffees.map(_.sales)

    // Print the SQL for the Coffees update query
    println("Generated SQL for Coffees update:\n" + updateQuery.updateStatement)

    // Perform the update
    val numUpdatedRows = updateQuery.update(1)

    println(s"Updated $numUpdatedRows rows")


    /* Delete */

    // Construct a delete query that deletes coffees with a price less than 8.0
    val deleteQuery: Query[Coffees,(String, Int, Double, Int, Int)] = coffees.filter(_.price < 8.0)

    // Print the SQL for the Coffees delete query
    println("Generated SQL for Coffees delete:\n" + deleteQuery.deleteStatement)

    // Perform the delete
    val numDeletedRows = deleteQuery.delete

    println(s"Deleted $numDeletedRows rows")


    /* Selecting Specific Columns */

    // Construct a new coffees query that just selects the name
    val justNameQuery: Query[Column[String], String] = coffees.map(_.name)

    println("Generated SQL for query returning just the name:\n" + justNameQuery.selectStatement)

    // Execute the query
    println(justNameQuery.list)


    /* Sorting / Order By */

    val sortByPriceQuery: Query[Coffees, (String, Int, Double, Int, Int)] = coffees.sortBy(_.price)

    println("Generated SQL for query sorted by price:\n" + sortByPriceQuery.selectStatement)

    // Execute the query
    println(sortByPriceQuery.list)


    /* Query Composition */

    val composedQuery: Query[Column[String], String] = coffees.sortBy(_.name).take(3).filter(_.price > 9.0).map(_.name)

    println("Generated SQL for composed query:\n" + composedQuery.selectStatement)

    // Execute the composed query
    println(composedQuery.list)


    /* Joins */

    // Join the tables using the relationship defined in the Coffees table
    val joinQuery: Query[(Column[String], Column[String]), (String, String)] = for {
      c <- coffees if c.price > 9.0
      s <- c.supplier
    } yield (c.name, s.name)

    println("Generated SQL for the join query:\n" + joinQuery.selectStatement)

    // Print the rows which contain the coffee name and the supplier name
    println(joinQuery.list)


    /* Computed Values */

    // Create a new computed column that calculates the max price
    val maxPriceColumn: Column[Option[Double]] = coffees.map(_.price).max

    println("Generated SQL for max price column:\n" + maxPriceColumn.selectStatement)

    // Execute the computed value query
    println(maxPriceColumn.run)


    /* Manual SQL / String Interpolation */

    // Required import for the sql interpolator
    import scala.slick.jdbc.StaticQuery.interpolation

    // A value to insert into the statement
    val state = "CA"

    // Construct a SQL statement manually with an interpolated value
    val plainQuery = sql"select SUP_NAME from SUPPLIERS where STATE = $state".as[String]

    println("Generated SQL for plain query:\n" + plainQuery.getStatement)

    // Execute the query
    println(plainQuery.list)

  }
}

Tables.scala

import scala.slick.driver.MySQLDriver.simple._
import scala.slick.lifted.{ProvenShape, ForeignKeyQuery}


// A Suppliers table with 6 columns: id, name, street, city, state, zip
class Suppliers(tag: Tag) extends Table[(Int, String, String, String, String, String)](tag, "SUPPLIERS") {
  def id: Column[Int] = column[Int]("SUP_ID", O.PrimaryKey) // This is the primary key column
  def name: Column[String] = column[String]("SUP_NAME")
  def street: Column[String] = column[String]("STREET")
  def city: Column[String] = column[String]("CITY")
  def state: Column[String] = column[String]("STATE")
  def zip: Column[String] = column[String]("ZIP")
  
  // Every table needs a * projection with the same type as the table's type parameter
  def * : ProvenShape[(Int, String, String, String, String, String)] = (id, name, street, city, state, zip)
}

// A Coffees table with 5 columns: name, supplier id, price, sales, total
class Coffees(tag: Tag) extends Table[(String, Int, Double, Int, Int)](tag, "COFFEES") {
  def name: Column[String] = column[String]("COF_NAME", O.PrimaryKey)
  def supID: Column[Int] = column[Int]("SUP_ID")
  def price: Column[Double] = column[Double]("PRICE")
  def sales: Column[Int] = column[Int]("SALES")
  def total: Column[Int] = column[Int]("TOTAL")
  
  def * : ProvenShape[(String, Int, Double, Int, Int)] = (name, supID, price, sales, total)
  
  // A reified foreign key relation that can be navigated to create a join
  def supplier: ForeignKeyQuery[Suppliers, (Int, String, String, String, String, String)] = 
    foreignKey("SUP_FK", supID, TableQuery[Suppliers])(_.id)
}

Answer 1

You probably overlooked that the error message refers to `TableSuite.scala``

/Users/kotaro/Documents/Scala/hello-slick/src/test/scala/TablesSuite.scala

value ddl is not a member of scala.slick.lifted.TableQuery[Suppliers]

def createSchema() = (suppliers.ddl ++ coffees.ddl).create

You need to change the import in there as well.