Slick 3 Guide: Best Practices, Tips & Examples

Slick 3 is a powerful database access library for Scala that makes working with relational databases easier and more intuitive. Here's what you need to know:

• Type-safe queries: Write database queries in Scala and catch errors at compile-time
• Functional approach: Use Functional Relational Mapping (FRM) for a Scala-friendly database interaction
• Asynchronous operations: Built on Reactive Streams for non-blocking database access

Key features:

1. Query API for Scala-like database operations
2. Async support with Futures and Reactive Streams
3. Query compiler for SQL generation
4. Plain SQL support when needed

This guide covers:

• Setting up Slick 3
• Basic concepts and schema design
• Writing efficient queries
• Advanced query techniques
• Data handling and transactions
• Performance optimization and testing
• Production deployment tips

Quick Comparison: Slick 3 vs Traditional ORMs

Whether you're new to Slick or looking to level up your skills, this guide will help you make the most of Slick 3 in your Scala projects.

Related video from YouTube

2. Getting Started with Slick 3

Let's set up Slick 3 for your Scala projects. It's pretty simple.

2.1 Installation Steps

Add Slick 3 to your project:

For sbt:

libraryDependencies ++= Seq(
  "com.typesafe.slick" %% "slick" % "3.0.0",
  "org.slf4j" % "slf4j-nop" % "1.6.4"
)

For Maven:

<dependency>
  <groupId>com.typesafe.slick</groupId>
  <artifactId>slick_2.10</artifactId>
  <version>3.0.0</version>
</dependency>
<dependency>
  <groupId>org.slf4j</groupId>
  <artifactId>slf4j-nop</artifactId>
  <version>1.6.4</version>
</dependency>

Don't forget: Slick uses SLF4J for logging. Make sure you include an SLF4J implementation.

2.2 Setting Up Database Connections

Slick uses a Database object to connect to databases. Here's how:

1. Configure your database in application.conf:

mydb = {
  dataSourceClass = "org.postgresql.ds.PGSimpleDataSource"
  properties = {
    databaseName = "mydb"
    user = "myuser"
    password = "secret"
  }
  numThreads = 10
}

2. Load it in your Scala code:

val db = Database.forConfig("mydb")

Different databases need different setups:

Pro tip: If you're using a connection pool, set its minimum size to match your thread pool size.

3. Basic Concepts and Schema Design

Slick 3 uses Functional Relational Mapping (FRM) to talk to databases. It's like speaking Scala to your database.

3.1 Functional Relational Mapping Explained

FRM lets you use Scala collections for database work. It turns database tables into Scala case classes. This means:

• Type-safe queries
• Fewer runtime errors

Here's how it works:

case class Movie(id: Long, name: String, releaseDate: LocalDate, lengthInMin: Int)

class MovieTable(tag: Tag) extends Table[Movie](tag, Some("movies"), "Movie") {
    def id = column[Long]("movie_id", O.PrimaryKey, O.AutoInc)
    def name = column[String]("name")
    def releaseDate = column[LocalDate]("release_date")
    def lengthInMin = column[Int]("length_in_min")
    override def * = (id, name, releaseDate, lengthInMin) <> (Movie.tupled, Movie.unapply)
}

Now you can query like this:

val movies = TableQuery[MovieTable]
val action = movies.filter(_.releaseDate > LocalDate.now()).result

3.2 Creating Database Schemas

To make a database schema in Slick:

1. Define a case class for each table
2. Create a Table class
3. Define columns
4. Map columns to case class fields

Let's look at a player table:

case class Player(id: Long, name: String, country: String, dob: Option[LocalDate])

class PlayerTable(tag: Tag) extends Table[Player](tag, None, "Player") {
    def id = column[Long]("PlayerId", O.AutoInc, O.PrimaryKey)
    def name = column[String]("Name")
    def country = column[String]("Country")
    def dob = column[Option[LocalDate]]("Dob")
    override def * = (id, name, country, dob).mapTo[Player]
}

Remember:

• Use Option[T] for nullable columns
• Set primary keys with O.PrimaryKey
• Use mapTo for easy mapping

Slick supports different data types:

To create tables:

val players = TableQuery[PlayerTable]
val schema = players.schema
val action = schema.create

This approach gives you type-safe queries and clear database interactions.

4. Writing Efficient Queries

Slick 3 packs a punch when it comes to crafting speedy database queries. Let's explore some key techniques to supercharge your queries and handle async operations like a pro.

4.1 Query Optimization Techniques

Want to make your queries zoom? Try these tricks:

take(1) > head

Need just one result? take(1) is your best friend:

// Slow poke
val slowQuery = users.result.head

// Speed demon
val fastQuery = users.take(1).result.head

Why? take(1) tells the database to grab just one result. head grabs everything, then picks the first one. Big difference!

Pagination is your friend

Got a ton of data? Slice it up:

def findAll(userId: Long, limit: Int, offset: Int) = db.run {
  query.filter(_.creatorId === userId)
       .drop(offset)
       .take(limit)
       .result
}

Compiled queries for the win

Cache that SQL for a speed boost:

val compiledQuery = Compiled { (name: Rep[String]) =>
  coffees.filter(_.name === name)
}

// Use it like this:
db.run(compiledQuery("Espresso").result)

4.2 Async Operations

Slick 3 loves async. Here's how to play nice:

Embrace Futures

Slick ops return Futures. Work with them:

val query = coffees.filter(_.price < 10.0).result
val f: Future[Seq[Coffee]] = db.run(query)

f.onSuccess { case coffees =>
  println(s"Found ${coffees.length} cheap coffees")
}

Compose actions

Chain database actions with for comprehensions:

val action = for {
  coffee <- coffees.filter(_.name === "Espresso").result.headOption
  _ <- coffee.map(c => coffees.filter(_.id === c.id).delete).getOrElse(DBIO.successful(()))
} yield ()

db.run(action.transactionally)

This finds and deletes an "Espresso" coffee in one go.

Stream for big data

Got a mountain of results? Stream 'em:

val q = coffees.map(_.name)
val p: DatabasePublisher[String] = db.stream(q.result)
p.foreach { name => println(s"Coffee: $name") }

sbb-itb-bfaad5b

5. Advanced Query Writing

Let's explore some advanced Slick query techniques.

5.1 Complex Joins

Slick offers two main join types: Applicative and Monadic.

Applicative Joins

These use explicit JOIN statements:

val joinQuery = for {
  (actor, movie) <- actorTable join movieTable on (_.movieId === _.id)
} yield (actor.name, movie.title)

This creates an inner join between actor and movie tables.

Monadic Joins

These use flatMap for relationships:

val query = for {
  movie <- movieTable if movie.title === "Inception"
  actor <- actorTable if actor.movieId === movie.id
} yield (movie.title, actor.name)

This finds all actors in "Inception".

Outer Joins

Need all records, even without matches? Try:

val leftJoinQuery = for {
  (movie, actor) <- movieTable joinLeft actorTable on (_.id === _.movieId)
} yield (movie.title, actor.map(_.name))

This left join returns all movies, even those without actors.

5.2 Subqueries

Subqueries nest one query inside another. They're perfect for complex data retrieval.

IN Clause with Subquery

val subquery = addresses.filter(_.city === "New York City").map(_.id)
val query = people.filter(_.addressId in subquery)

This finds all New York City residents.

Correlated Subqueries

These subqueries depend on the outer query:

val query = for {
  p <- people if p.age > people.map(_.age).avg
} yield p

This query finds people older than the average age.

Sometimes, raw SQL is clearer for complex queries. Slick supports both:

val complexQuery = sql"""
  SELECT m.title, COUNT(a.id) as actor_count
  FROM movies m
  LEFT JOIN actors a ON m.id = a.movie_id
  GROUP BY m.id
  HAVING COUNT(a.id) > 5
""".as[(String, Int)]

This finds movies with more than 5 actors.

6. Data Handling and Transactions

Let's look at how to handle data changes and manage transactions in Slick 3.

6.1 Secure Data Changes

Here's how to modify data in Slick:

Inserting Records

Add a new record with the += operator:

def create(bankInfo: BankInfo): Future[Int] = db.run { bankTableInfoAutoInc += bankInfo }

Updating Records

Update with the update method:

def update(bankInfo: BankInfo): Future[Int] = db.run {
  bankInfoTableQuery.filter(_.id === bankInfo.id.get).update(bankInfo)
}

Deleting Records

Delete using the delete method:

def deleteById(id: Option[Int]): Unit = db.run {
  tableQuery.filter(_.id === id).delete
}

6.2 Managing Transactions

Transactions keep your data consistent. Here's how to use them:

Basic Transaction

Wrap operations in transactionally:

val transaction = (for {
  _ <- coffees.filter(_.name.startsWith("ESPRESSO")).delete
  _ <- suppliers.filter(_.name === "Acme, Inc.").delete
} yield ()).transactionally

Error Handling

For rollbacks, use DBIO.failed:

val rollbackAction = (coffees ++= Seq(
  ("Cold_Drip", new SerialBlob(Array[Byte](101))),
  ("Dutch_Coffee", new SerialBlob(Array[Byte](49)))
)).flatMap { _ =>
  DBIO.failed(new Exception("Roll it back"))
}.transactionally

Performance Tips

For high latencies:

• Use stored procedures for server-side logic
• Improve indexing to reduce row locks
• Try lower isolation levels like READ UNCOMMITTED

Careful transaction management is crucial. A Sumo Logic outage showed how high garbage collection in one JVM can cause lock wait timeouts in another.

7. Improving Performance and Testing

7.1 Performance Improvements

Want to make Slick 3 faster? Focus on these two areas:

Query Optimization

Slick's DSL is great, but it can slow things down if you're not careful. Here's a big no-no:

// DON'T do this:
val q1 = users.result.head

// DO this instead:
val q2 = users.take(1).result.head

Why? The first one grabs ALL rows, then picks the first. The second one tells the database to grab just one row. Big difference.

We tested this on a table with 500,000 records:

Ouch. To catch these sneaky performance killers:

1. Log your SQL
2. Use println(yourQuery.selectStatement) to see what SQL Slick is creating
3. If needed, write the SQL yourself

Connection Pooling

Good connection pooling = faster Slick. While Slick is as quick as JDBC, compiling queries can slow things down. Fix this by caching your compiled queries:

val compiledQuery = Compiled(query)

7.2 Effective Testing

Testing Slick? You need both unit tests and integration tests.

Unit Testing

For unit tests, fake the database. Here's how with ScalaMock:

val mockDb = mock[Database]
val usersDao = new UsersDao(mockDb)

(mockDb.run _).expects(*).returning(Future.successful(Seq(sampleUser)))

val result = Await.result(usersDao.findAll(), 5.seconds)
assert(result == Seq(sampleUser))

Integration Testing

For the real deal, use Slick TestKit. It runs your tests against your actual database setup:

1. Grab the Slick TestKit Example template
2. Extend ProfileTest and implement TestDB
3. Set up your test database in test-dbs/testkit.conf
4. Run sbt test

This makes sure your Slick setup works in all sorts of situations.

8. Using Slick in Production

When deploying your Slick app, it's all about performance, stability, and data integrity.

8.1 Monitoring and Scaling Tips

Keep your Slick app running smoothly:

Connection Pooling

Use HikariCP for efficient database connections:

val db = Database.forConfig("mydb")

In application.conf:

mydb = {
  dataSourceClass = "org.postgresql.ds.PGSimpleDataSource"
  properties = {
    serverName = "localhost"
    portNumber = "5432"
    databaseName = "mydb"
    user = "myuser"
    password = "mypassword"
  }
  numThreads = 10
}

Logging and Metrics

Slick uses SLF4J. Pair with Logback:

import org.slf4j.LoggerFactory
val logger = LoggerFactory.getLogger(getClass)

logger.info("Query executed successfully")

Use Kamon or Prometheus for performance tracking.

Scaling Strategies

8.2 Data Backup and Recovery

Don't skimp on data protection:

Regular Backups

For PostgreSQL:

pg_dump dbname > backup.sql

Run this daily or hourly.

Point-in-Time Recovery

Enable Write-Ahead Logging (WAL). For PostgreSQL:

wal_level = replica
archive_mode = on
archive_command = 'cp %p /path/to/archive/%f'

Testing Backups

Regularly restore your backups in a test environment. If you can't restore it, it's not a backup.

9. Wrap-up and Future Outlook

Key Takeaways

Slick 3 is a game-changer for database operations in Scala. Here's why:

• It lets you write database queries using Scala's collections API
• You can use Scala's functional programming features
• It supports async operations with Future
• It keeps mapping tables and queries separate

What's Next for Slick?

Slick is always improving. Here's what's happening:

The Slick team is working hard on Scala 3 compatibility. They've made progress with Slick 3.5.0-M3, but some features are still catching up.

"Slick will get better with Scala 3 over time. They might even add support for NoSQL and other data sources like web services." - virtualeyes, Scala Developer

If you're thinking about using Slick:

1. Use Slick for complex queries and simpler ORM tools for basic CRUD
2. Keep an eye out for Scala 3 support updates
3. Help out if you can implement missing features

As databases get more complex, Slick's functional approach will become even more useful for Scala developers.

Related posts

• 10 Best Practices for Multi-Region Database Deployment
• Query Data Efficiently: Best Practices
• Build REST API with Scala Play Framework