slick/slick-examples

A sample project for getting started with Slick

Slick

Slick is an advanced, comprehensive database access library for Scala with strongly-typed, highly composable APIs.

Slick makes it easy to use your database in a way that's natural to it. It allows you to work with relational databases almost as if you were using Scala collections, while at the same time giving you full control over when the database is accessed and how much data is transferred. And by writing your queries in Scala you can benefit from compile-time safety and great compositionality, while retaining the ability to drop down to raw SQL when necessary for custom or advanced database features.

Its features include:

• Query API inspired by Scala's collections API
• Full control over how the database schema is seen by Slick, by using an explicit representation that can be code-generated from the actual database
• Asynchronous API using Future for complete results, and a streaming API that conforms to the Reactive Streams interface, for easy integration with any streaming library, such as Akka Streams, FS2, or ZIO.
• Composability at many levels
  • Compose actions (steps to run in a database context) with for comprehensions
  • Compose queries with for comprehensions or combinators
  • Compose row expressions (column sets, predicates, and column mappings)
• A database metadata introspection API

Slick features an advanced query compiler which can generate SQL for a variety of different database engines from the same Scala code, allowing you to focus on application logic without worrying about database-specific syntax and quirks.

Resources

Learn

• Full documentation, including Scaladocs and more complex examples, can be found on the website. For the latest release you can go straight to https://scala-slick.org/doc/stable/

• Our friends at underscore.io have written "Essential Slick", an excellent guide to using slick from first principles, and is available as a free download

• Videos by Rock the JVM

Get help

• Post questions and suggestions on GitHub Discussions
• Ask on the #slick channel on the official Scala Discord server
• There is a gitter channel too, but Discord is preferred
• Report bugs to GitHub issues

Example

As a simple example we will create a Scala object Coffee, and a table to store instances of this object in the database:

import slick.jdbc.PostgresProfile.api._

// First declare our Scala object
final case class Coffee(name: String, price: Double)

// Next define how Slick maps from a database table to Scala objects
class Coffees(tag: Tag) extends Table[Coffee](tag, "COFFEES") {
  def name  = column[String]("NAME")
  def price = column[Double]("PRICE")
  def * = (name, price).mapTo[Coffee]
}

// The `TableQuery` object gives us access to Slick's rich query API
val coffees = TableQuery[Coffees]

// Inserting is done by appending to our query object
// as if it were a regular Scala collection
// SQL: insert into COFFEES (NAME, PRICE) values ('Latte', 2.50)
coffees += Coffee("Latte", 2.50)

// Fetching data is also done using the query object
// SQL: select NAME from COFFEES
coffees.map(_.name)

// More complex queries can be chained together
// SQL: select NAME, PRICE from COFFEES where PRICE < 10.0 order by NAME
coffees.filter(_.price < 10.0).sortBy(_.name)

Database support

The following databases are directly supported by Slick, and are currently covered by a large suite of automated tests to ensure compatibility:

Database	JDBC Driver	Tested server version
PostgreSQL	"org.postgresql" % "postgresql" % "42.5.0"	Latest
MySQL	"com.mysql" % "mysql-connector-j" % "8.0.33"	Latest
SQLServer	"com.microsoft.sqlserver" % "mssql-jdbc" % "7.2.2.jre11"	2022
Oracle	"com.oracle.database.jdbc.debug" % "ojdbc8_g" % "21.6.0.0.1"	11g
DB2	"com.ibm.db2.jcc" % "db2jcc" % "db2jcc4"	11.5.7.0
Derby/JavaDB	"org.apache.derby" % "derby" % "10.14.2.0"
H2	"com.h2database" % "h2" % "1.4.200"
HSQLDB/HyperSQL	"org.hsqldb" % "hsqldb" % "2.5.2"
SQLite	"org.xerial" % "sqlite-jdbc" % "3.39.2.1"

Accessing other database systems is possible, although possibly with a reduced feature set.

Contributing

Slick is community-maintained: pull requests are very welcome, and we ask that all contributors abide by the Lightbend Community Code of Conduct.

Lightbend staff (such as @SethTisue) may be able to assist with administrative issues.

slick-examples's Issues

slick-examples 2.0.0-M3 do not compile on Dec 5, 2013

git clone ...
sbt update
sbt run

[info] Compiling 7 Scala sources to C:\Dev\projects\slick-examples\target\scala-2.10\classes...
[error] C:\Dev\projects\slick-examples\src\main\scala\com\typesafe\slick\examples\lifted\MultiDBCakeExample.sc
ala:26: reassignment to val
[error] Error occurred in an application involving default arguments.
[error] private val picturesAutoInc = pictures returning pictures.map(_.id) into { case (p, id) => p.copy(id
= id) }
[error]
^
[error] C:\Dev\projects\slick-examples\src\main\scala\com\typesafe\slick\examples\lifted\MultiDBCakeExample.sc
ala:27: type mismatch;
[error] found : com.typesafe.slick.examples.lifted.Picture
[error] required: (String, Option[Int])
[error] def insert(picture: Picture)(implicit session: Session): Picture = picturesAutoInc.insert(picture)
[error] ^
[error] two errors found
error Compilation failed
[error] Total time: 8 s, completed 05.12.2013 16:26:58

On version 2.10....

How do I use with scala 2.9.x?

Project cannot be run more than once

When configured for a non-temporary database such as MySQL, the project cannot be run a second time unless the tables are manually dropped. Also, the database must be manually created.

Package should be changed

The package for the code in this project is scala.slick.examples. I suggest that it be changed to a package that does not start with scala, such as slick.examples. My fork has this change, and also drops pre-existing tables in ForExample.scala

fix description of this github project

currently the description reads "A sample project for getting started with ScalaQuery
http://scalaquery.org" which of course is an artifact from the original project, can you please update it to point to slick

Using slick extensions for Oracle, MSSQL, DB2

An example for the access to an Oracle, MSSQL or DB2 database using the slick enxtensions you be very usefull I think. There are only little (and with little I mean none at all) resources on the web for that.

Adjust the build.sbt libraryDependencies

Since apparently to get a PR accepted to slick, I need to sign a CLA, I'll create an issue instead.

The build.sbt file has version 2.0.0-RC1 in the library dependencies. Could you adjust it so that it really uses the final version?

Deprecation warning

This is a model code example, so there should not be any deprecation warnings.

[warn] C:\slick-examples\src\main\scala\scala\slick\examples\lifted\UseInvoker.scala:34: method orderBy
in object Query is deprecated: Use .sortBy on a query instead of mixing in Query.orderBy
[warn]       _ <- Query orderBy t.k

running MultiDBExample 2 or more times produces: libsqlitejdbc.jnilib already loaded in another classloader

if you run the MultiDBExample 2 times or more from the same sbt session you get the following output:

Version of slick-examples: 79eb153

Repro steps inside of sbt:

run-main scala.slick.examples.lifted.MultiDBExample
[info] Running scala.slick.examples.lifted.MultiDBExample
Running test against H2
Value for key 'foo': Some(bar)
Value for key 'baz': None
Using the helper: (foo,bar)
Running test against SQLite
Value for key 'foo': Some(bar)
Value for key 'baz': None
Using the helper: (foo,bar)
[success] Total time: 3 s, completed Oct 24, 2012 4:49:20 PM
run-main scala.slick.examples.lifted.MultiDBExample
[info] Running scala.slick.examples.lifted.MultiDBExample
Running test against H2
Value for key 'foo': Some(bar)
Value for key 'baz': None
Using the helper: (foo,bar)
Running test against SQLite
java.lang.UnsatisfiedLinkError: Native Library /private/var/folders/x1/9zmqtm7173v_f861bbd_19300000gn/T/sqlite-3.6.20-libsqlitejdbc.jnilib already loaded in another classloader
Value for key 'foo': Some(bar)
Value for key 'baz': None
Using the helper: (foo,bar)
[success] Total time: 5 s, completed Oct 24, 2012 4:49:27 PM

Specifying DB driver and URL

Ideally the project would allow the user to select one of several database drivers in some fashion without altering any code. Perhaps command-line arguments might be used to specify a driver and url, with the current H2 configuration as the default.

Lifted delete sample

Hi,

Could you please make a lifted parameterized delete sample?

Exception on create table (HsqlDb)

I tried the example code "FirstExample" with Hsqldb instead of H2.
So I replaced the H2 import with:
import org.scalaquery.ql.extended.HsqldbDriver.Implicit._

and the Database.forUrl line with:
Database.forURL("jdbc:hsqldb:mem:example", user ="sa", password="", driver = "org.hsqldb.jdbcDriver") withSession {

If I try to run it I get the following exception:

Exception in thread "main" java.sql.SQLException: Unexpected token: NOT in statement [CREATE TABLE "SUPPLIERS" ("SUP_ID" INTEGER PRIMARY KEY NOT] at org.hsqldb.jdbc.Util.throwError(Unknown Source) at org.hsqldb.jdbc.jdbcPreparedStatement.execute(Unknown Source) at org.scalaquery.ql.DDL$$anonfun$create$1$$anonfun$apply$mcV$sp$1$$anonfun$apply$1.apply(DDL.scala:23) at org.scalaquery.ql.DDL$$anonfun$create$1$$anonfun$apply$mcV$sp$1$$anonfun$apply$1.apply(DDL.scala:23) at org.scalaquery.session.Session$class.withPreparedStatement(Session.scala:53) at org.scalaquery.session.BaseSession.withPreparedStatement(Session.scala:92) at org.scalaquery.ql.DDL$$anonfun$create$1$$anonfun$apply$mcV$sp$1.apply(DDL.scala:23) at org.scalaquery.ql.DDL$$anonfun$create$1$$anonfun$apply$mcV$sp$1.apply(DDL.scala:22) at scala.collection.Iterator$class.foreach(Iterator.scala:651) at scala.collection.Iterator$$anon$19.foreach(Iterator.scala:343) at org.scalaquery.ql.DDL$$anonfun$create$1.apply$mcV$sp(DDL.scala:22) at org.scalaquery.ql.DDL$$anonfun$create$1.apply(DDL.scala:22) at org.scalaquery.ql.DDL$$anonfun$create$1.apply(DDL.scala:22) at org.scalaquery.session.BaseSession.withTransaction(Session.scala:127) at org.scalaquery.ql.DDL$class.create(DDL.scala:21) at org.scalaquery.ql.DDL$$anon$1.create(DDL.scala:42) at nl.paulhildebrand.scalaquery.FirstExample$$anonfun$main$1.apply$mcV$sp(Example.scala:55) at nl.paulhildebrand.scalaquery.FirstExample$$anonfun$main$1.apply(Example.scala:50) at nl.paulhildebrand.scalaquery.FirstExample$$anonfun$main$1.apply(Example.scala:50) at scala.util.DynamicVariable.withValue(DynamicVariable.scala:57) at org.scalaquery.session.Database$$anonfun$withSession$1.apply(Database.scala:44) at org.scalaquery.session.Database$$anonfun$withSession$1.apply(Database.scala:44) at org.scalaquery.session.Database.withSession(Database.scala:36) at org.scalaquery.session.Database.withSession(Database.scala:44) at nl.paulhildebrand.scalaquery.FirstExample$.main(Example.scala:50) at nl.paulhildebrand.scalaquery.FirstExample.main(Example.scala)

Looks like the NOT NULL part in the CREATE TABLE statement is not coming through correctly.

I am using scalaquery_2.9.0.RC1-0.9.2.jar with hsqldb-1.8.0.7.jar

Unnecessary usage of Session in MultiDBCakeExample.scala

In UserComponent.User the below session argument is not necessary.

private def autoInc(implicit session: Session) = name ~ pictureId returning id into {
case (_, id) => id
}

Embedding example would be helpful

How about an example along these lines?

    @table("COFFEES") case class Coffee(
    @column("COF_NAME") name: String,
    @column("SUP_ID") supID: Int,
    @column("PRICE") price: Double
    )
    val coffees = Queryable[Coffee]

    // for inserts use lifted embedding or SQL

    val l = for {
    c <- coffees if c.supID == 101
    // ^ comparing Int to Int!
    } yield (c.name, c.price)

    backend.result( l, session )
    .foreach { case (n, p) => println(n + ": " + p) }

Generated MySQL DDL is not right

I forked the slick-examples project to use MySQL (https://github.com/mslinn/slick-examples) and found problems with the generated SQL.

• It is safer to use lower case table names and field names with MySQL
• The generated DDL uses double quotes instead of back ticks. MySQL does not like double quotes unless it is started with the --ansi option.
• The generated DDL does not specify a length for VARCHAR so the DDL is rejected. Other options for generating similar code exist for MySQL. Here is what is generated:

create table SUPPLIERS (
  SUP_ID INTEGER NOT NULL PRIMARY KEY,
  SUP_NAME VARCHAR NOT NULL,
  STREET VARCHAR NOT NULL,
  CITY VARCHAR NOT NULL,
  STATE VARCHAR NOT NULL,
  ZIP VARCHAR NOT NULL)

... and this is what works:

use slickexamples;
create table SUPPLIERS (
  SUP_ID INTEGER NOT NULL PRIMARY KEY,
  SUP_NAME VARCHAR(255) NOT NULL,
  STREET VARCHAR(255) NOT NULL,
  CITY VARCHAR(255) NOT NULL,
  STATE VARCHAR(255) NOT NULL,
  ZIP VARCHAR(255) NOT NULL)

• I also found that the automatic lifting to a Query blows up with MySQL:

  You have an error in your SQL syntax; check the manual that corresponds to
  your MySQL server version for the right syntax to use near
  'VARCHAR))||' ')||cast(x2."PRICE" as VARCHAR))||' ')||cast(x2."SALES" as VARCHAR)' at line 1

Foreign key for MultiDBCake example

I followed the MultiDBCake example to be able to get different databases used between testing and production. In the table definition, I would like to set foreign key between Users and Pictures, which should be similar to below.

def pictureKey = foreignKey("FK_USER_PICTURE", pictureId, PictureComponent.Pictures)(_.id.get)

However due to the fact that the Pictures object is inside PictureComponent trait, there is no way for me to reference PictureComponent.Pictures. What will be your suggestion on adjusting this example to be able to achieve this?

Thanks,
Suriyanto

Please make a test case for the Enumeration as I dont see how to use Enums with Scala Slick

error: illegal cyclic inheritance involving type Table

run the sample,
"error: illegal cyclic inheritance involving type Table
object Suppliers extends Table(Int, String, String, String, String, String) {"
I have no idea, help~~