title: "Hello World" example for Slick 2.0 with MySQL
body: <p>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...</p>
  <p>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 (<em>e.g.,</em> <a href="http://www.typesafe.com/blog/huffington-post-case-study-building-a-solid-platform-with-typesafe" rel="noreferrer">huffpost</a> and <a href="http://www.typesafe.com/blog/making-online-education-accessible-with-typesafe" rel="noreferrer">cousera</a>) of developing web applications using Scala and Play framework, it started looking appealing to me and I decided to give it a shot.</p>
  <p>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 <a href="https://typesafe.com/activator/template/hello-slick" rel="noreferrer">Hello-Slick example</a> in the Typesafe Activator, which guided me how to use an in-memory database (<em>i.e.,</em> 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</p>
  <p><code>import scala.slick.driver.H2Driver.simple._</code></p>
  <p>to</p>
  <p><code>import scala.slick.driver.MySQLDriver.simple._</code></p>
  <p>in both <code>HelloSlick.scala</code> and <code>Tables.scala</code> 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:</p>
  <blockquote>
  <p>/Users/kotaro/Documents/Scala/hello-slick/src/test/scala/TablesSuite.scala</p>
  <p>value ddl is not a member of scala.slick.lifted.TableQuery[Suppliers]</p>
  <p>def createSchema() = (suppliers.ddl ++ coffees.ddl).create</p>
  <pre><code>                              ^
  </code></pre>
  </blockquote>
  <p>Does anyone know what is causing this? According to this post (<a href="https://stackoverflow.com/questions/22229561/slick-exception-when-trying-to-connect-to-mysql/22230129#22230129">slick exception when trying to connect to MySql</a>), 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.</p>
  <p>I am using Scala 2.10.3, MySQL in XAMPP 1.7.3 with OS X 10.8 and JRE 1.6.</p>
  <p>Thank you very much for your help in advance!</p>
  <p>Following is the full copy of the code.</p>
  <p>HelloSlick.scala</p>
  <pre><code>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 &quot;session&quot;) to an in-memory H2 database
    Database.forURL(&quot;jdbc:mysql://localhost:3306/test&quot;, driver=&quot;com.mysql.jdbc.Driver&quot;, user=&quot;root&quot;, password=&quot;&quot;).withSession { implicit session =&gt;
  
    // 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, &quot;Acme, Inc.&quot;, &quot;99 Market Street&quot;, &quot;Groundsville&quot;, &quot;CA&quot;, &quot;95199&quot;)
      suppliers += (49, &quot;Superior Coffee&quot;, &quot;1 Party Place&quot;, &quot;Mendocino&quot;, &quot;CA&quot;, &quot;95460&quot;)
      suppliers += (150, &quot;The High Ground&quot;, &quot;100 Coffee Lane&quot;, &quot;Meadows&quot;, &quot;CA&quot;, &quot;93966&quot;)
  
      // Insert some coffees (using JDBC's batch insert feature)
      val coffeesInsertResult: Option[Int] = coffees ++= Seq (
        (&quot;Colombian&quot;,         101, 7.99, 0, 0),
        (&quot;French_Roast&quot;,       49, 8.99, 0, 0),
        (&quot;Espresso&quot;,          150, 9.99, 0, 0),
        (&quot;Colombian_Decaf&quot;,   101, 8.99, 0, 0),
        (&quot;French_Roast_Decaf&quot;, 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 =&gt; println(s&quot;Inserted $numRows rows into the Coffees table&quot;))
  
  
      /* Read / Query / Select */
  
      // Print the SQL for the Coffees query
      println(&quot;Generated SQL for base Coffees query:\n&quot; + coffees.selectStatement)
  
      // Query the Coffees table using a foreach and print each row
      coffees foreach { case (name, supID, price, sales, total) =&gt;
        println(&quot;  &quot; + name + &quot;\t&quot; + supID + &quot;\t&quot; + price + &quot;\t&quot; + sales + &quot;\t&quot; + total)
      }
  
  
      /* Filtering / Where */
  
      // Construct a query where the price of Coffees is &gt; 9.0
      val filterQuery: Query[Coffees, (String, Int, Double, Int, Int)] = coffees.filter(_.price &gt; 9.0)
  
      println(&quot;Generated SQL for filter query:\n&quot; + 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(&quot;Generated SQL for Coffees update:\n&quot; + updateQuery.updateStatement)
  
      // Perform the update
      val numUpdatedRows = updateQuery.update(1)
  
      println(s&quot;Updated $numUpdatedRows rows&quot;)
  
  
      /* 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 &lt; 8.0)
  
      // Print the SQL for the Coffees delete query
      println(&quot;Generated SQL for Coffees delete:\n&quot; + deleteQuery.deleteStatement)
  
      // Perform the delete
      val numDeletedRows = deleteQuery.delete
  
      println(s&quot;Deleted $numDeletedRows rows&quot;)
  
  
      /* Selecting Specific Columns */
  
      // Construct a new coffees query that just selects the name
      val justNameQuery: Query[Column[String], String] = coffees.map(_.name)
  
      println(&quot;Generated SQL for query returning just the name:\n&quot; + justNameQuery.selectStatement)
  
      // Execute the query
      println(justNameQuery.list)
  
  
      /* Sorting / Order By */
  
      val sortByPriceQuery: Query[Coffees, (String, Int, Double, Int, Int)] = coffees.sortBy(_.price)
  
      println(&quot;Generated SQL for query sorted by price:\n&quot; + sortByPriceQuery.selectStatement)
  
      // Execute the query
      println(sortByPriceQuery.list)
  
  
      /* Query Composition */
  
      val composedQuery: Query[Column[String], String] = coffees.sortBy(_.name).take(3).filter(_.price &gt; 9.0).map(_.name)
  
      println(&quot;Generated SQL for composed query:\n&quot; + 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 &lt;- coffees if c.price &gt; 9.0
        s &lt;- c.supplier
      } yield (c.name, s.name)
  
      println(&quot;Generated SQL for the join query:\n&quot; + 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(&quot;Generated SQL for max price column:\n&quot; + 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 = &quot;CA&quot;
  
      // Construct a SQL statement manually with an interpolated value
      val plainQuery = sql&quot;select SUP_NAME from SUPPLIERS where STATE = $state&quot;.as[String]
  
      println(&quot;Generated SQL for plain query:\n&quot; + plainQuery.getStatement)
  
      // Execute the query
      println(plainQuery.list)
  
    }
  }
  </code></pre>
  <p>Tables.scala</p>
  <pre><code>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, &quot;SUPPLIERS&quot;) {
    def id: Column[Int] = column[Int](&quot;SUP_ID&quot;, O.PrimaryKey) // This is the primary key column
    def name: Column[String] = column[String](&quot;SUP_NAME&quot;)
    def street: Column[String] = column[String](&quot;STREET&quot;)
    def city: Column[String] = column[String](&quot;CITY&quot;)
    def state: Column[String] = column[String](&quot;STATE&quot;)
    def zip: Column[String] = column[String](&quot;ZIP&quot;)
    
    // 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, &quot;COFFEES&quot;) {
    def name: Column[String] = column[String](&quot;COF_NAME&quot;, O.PrimaryKey)
    def supID: Column[Int] = column[Int](&quot;SUP_ID&quot;)
    def price: Column[Double] = column[Double](&quot;PRICE&quot;)
    def sales: Column[Int] = column[Int](&quot;SALES&quot;)
    def total: Column[Int] = column[Int](&quot;TOTAL&quot;)
    
    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(&quot;SUP_FK&quot;, supID, TableQuery[Suppliers])(_.id)
  }
  </code></pre>