WARNING: This page is a guide for 1.x series.

ScalikeJDBC 1.7.7


Just write SQL and get things done!


Logo

ScalikeJDBC is a tidy SQL-based DB access library for Scala developers. This library naturally wraps JDBC APIs and provides you easy-to-use and very flexible APIs. What’s more, QueryDSL makes your code type-safe and reusable.

ScalikeJDBC is a practical and production-ready one. Use this library for your real projects.

Working on the JDBC layer

Whether you like it or not, JDBC is a stable standard interface. Since most of RDBMS supports JDBC interface, we can access RDBMS in the same way. We never release without passing all the unit tests with the following RDBMS.

  • PostgreSQL
  • MySQL
  • H2 Database Engine
  • HSQLDB

We believe that ScalikeJDBC basically works with any other RDBMS (Oracle, SQL Server and so on).

Amazon Redshift, Facebook Presto also supports JDBC

If you can access some datastore via JDBC interface, that means you can access them via ScalikeJDBC too.

Recently, Amazon Redshift and Facebook Presto support JDBC interface. You can access them via ScalikeJDBC!

Few dependencies

The core of ScalikeJDBC has so few dependencies that you won’t be bothered by dependency hell.

  • JDBC Drivers you need
  • Commons DBCP
  • Joda Time 2.x
  • SLF4J API

Of course, you can use c3p0 (or others) instead of commons-dbcp though ConnectionPool implementation for that isn’t provided by default.

Non-blocking?

Unfortunately, no. Indeed, JDBC drivers block on socket IO. So using them to talk with RDBMS in async event driven architecture may not be appropriate. However, actually most of real world applications don’t need event-driven architecture yet. JDBC is still important infrastructure for apps on the JVM.

If you really prefer non-blocking database access, take a look at ScalikeJDBC-Async. It provides non-blocking APIs to talk with PostgreSQL and MySQL in the JDBC way.

https://github.com/scalikejdbc/scalikejdbc-async

ScalikeJDBC-Async is still in the alpha stage. If you don’t have the motivation to investigate or fix issues yourself, we recommend waiting until the stable version is released someday.


Getting Started


NOTICE: Scala 2.9 incompatible changes will be backported only to ScalikeJDBC 1.8.x.

All you need to do is add ScalikeJDBC, JDBC driver and an slf4j implementation.

libraryDependencies ++= Seq(
  "org.scalikejdbc" %% "scalikejdbc"               % "1.7.7",
  "org.scalikejdbc" %% "scalikejdbc-interpolation" % "1.7.7",
  "com.h2database"  %  "h2"                        % "1.4.177",
  "ch.qos.logback"  %  "logback-classic"           % "1.1.2"
)

Try Typesafe Activator Template


Typesafe

You can try a Play framework example app which uses ScalikeJDBC on Typesafe Activator.

This is a good example to learn how to use ScalikeJDBC.

Activator page: Hello ScalikeJDBC!

See on GitHub: scalikejdbc/hello-scalikejdbc


First example


Put above dependencies into your build.sbt and run sbt console now.

import scalikejdbc._, SQLInterpolation._

// initialize JDBC driver & connection pool
Class.forName("org.h2.Driver")
ConnectionPool.singleton("jdbc:h2:mem:hello", "user", "pass")

// ad-hoc session provider on the REPL
implicit val session = AutoSession

// table creation, you can run DDL by using #execute as same as JDBC
sql"""
create table members (
  id serial not null primary key,
  name varchar(64),
  created_at timestamp not null
)
""".execute.apply()

// insert initial data
Seq("Alice", "Bob", "Chris") foreach { name =>
  sql"insert into members (name, created_at) values (${name}, current_timestamp)".update.apply()
}

// for now, retrieves all data as Map value
val entities: List[Map[String, Any]] = sql"select * from members".map(_.toMap).list.apply()

// defines entity object and extractor
import org.joda.time._
case class Member(id: Long, name: Option[String], createdAt: DateTime)
object Member extends SQLSyntaxSupport[Member] {
  override val tableName = "members"
  def apply(rs: WrappedResultSet) = new Member(
    rs.long("id"), rs.stringOpt("name"), rs.dateTime("created_at"))
}

// find all members
val members: List[Member] = sql"select * from members".map(rs => Member(rs)).list.apply()

How did it go? If you’d like to know more details or practical examples, see documentation.


ScalikeJDBC in 5 minutes


Here is a presentation for beginners which shows overview of ScalikeJDBC in 5 minutes.


Quick Tour


Using only Scala standard API & SQL

Library users don’t need to learn so many library-specific rules or conventions. If you’re already familiar with Scala’s standard library APIs and basic SQLs, that much should be enough.

val name = "Alice"
// implicit session represents java.sql.Connection
val memberId: Option[Long] = DB readOnly { implicit session =>
  sql"select id from members where name = ${name}" // don't worry, prevents SQL injection
    .map(rs => rs.long("id")) // extracts values from rich java.sql.ResultSet
    .single                   // single, list, traversable
    .apply()                  // Side effect!!! runs the SQL using Connection
}

See in detail: /documentation/operations


Type-safe DSL

Since version 1.6, QueryDSL is available. It’s a SQL-like and type-safe DSL to build DRY SQLs.

Here is an example:

val (p, c) = (Programmer.syntax("p"), Company.syntax("c"))

val programmers: List[Long] = DB readOnly { implicit session =>
  withSQL {
    select
      .from(Programmer as p)
      .leftJoin(Company as c).on(p.companyId, c.id)
      .where.eq(p.isDeleted, false)
      .orderBy(p.createdAt)
      .limit(10)
      .offset(0)
  }.map(Programmer(p, c)).list.apply()
}

See in detail: /documentation/query-dsl

Test code: src/test/scala/scalikejdbc/QueryInterfaceSpec.scala


Flexible transaction control

ScalikeJDBC provides several APIs for session/transaction control.

  • DB autoCommit { implicit session => … }
  • DB localTx { implicit session => … }
  • DB withinTx { implicit session => … }
  • DB readOnly { implicit session => … }

Here is an example code which re-use methods in both of simple invocation and transactional operations.

object Product {
  def create(name: String, price: Long)(implicit s: DBSession = AutoSession): Long = {
    sql"insert into products values (${name}, ${price})"
      .updateAndReturnGeneratedKey.apply() // returns auto-incremeneted id
  }

  def findById(id: Long)(implicit s: DBSession = AutoSession): Option[Product] = {
    sql"select id, name, price, created_at from products where id = ${id}"
      .map { rs => Product(rs) }.single.apply()
  }
}

Product.findById(123) // borrows connection from pool and gives it back after execution

DB localTx { implicit session => // transactional session
  val id = Product.create("ScalikeJDBC Cookbook", 200) // within transaction
  val product = Product.findById(id) // within transaction
}

See in detail: /documentation/transaction


Useful Query Inspections

By default, ScalikeJDBC shows you what SQL is executed and where it is. We believe that is quite useful for debugging your apps. Logging only slow queries in production also helps you.

[debug] s.StatementExecutor$$anon$1 - SQL execution completed

  [Executed SQL]
   select id, name from users where email = 'alice@example.com'; (3 ms)

  [Stack Trace]
    ...
    models.User$.findByEmail(User.scala:26)
    controllers.Projects$$anonfun$index$1$$anonfun$apply$1$$anonfun$apply$2.apply(Projects.scala:20)
    controllers.Projects$$anonfun$index$1$$anonfun$apply$1$$anonfun$apply$2.apply(Projects.scala:19)
    controllers.Secured$$anonfun$IsAuthenticated$3$$anonfun$apply$3.apply(Application.scala:88)

See in detail: /documentation/query-inspector


Testing Support

Testing support which provides the following functionalities for ScalaTest and specs2.

  • Rollback automatically after each test
  • Testing with fixtures

See in detail: /documentation/testing


Reverse Engineering

You can easily get Scala code from existing database by using ScalikeJDBC’s reverse engineering tool.

sbt "scalikejdbc-gen [table-name (class-name)]"

e.g.

sbt "scalikejdbc-gen company"
sbt "scalikejdbc-gen companies Company"

See in detail: /documentation/reverse-engineering


Play Framework Support

You can use ScalikeJDBC with Play framework 2 seamlessly. We promise you that it becomes more productive when using together with scalikejdbc-mapper-generator.

See in detail: /documentation/playframework-support

Play framework


License

Published binary files have the following copyright:

Copyright 2013 ScalikeJDBC committers
Apache License, Version 2.0
http://www.apache.org/licenses/LICENSE-2.0.html


ScalikeJDBC-Async

ScalikeJDBC Extension: Non-blocking APIs in the JDBC way.

github.com/scalikejdbc/scalikejdbc-async

ScalikeJDBC-Async provides non-blocking APIs to talk with PostgreSQL and MySQL in the JDBC way.

This library is built with postgrsql-async and mysql-async,incredible works by @mauricio.


dbconsole

dbconsole is an extended sbt console to connect database.

Mac OS X, Linux

curl -L http://git.io/dbcon | sh

Windows

http://git.io/dbcon.bat

See in detail: /documentation/dbconsole


Skinny ORM

Skinny framework

Skinny ORM is the default DB access library of Skinny Framework. Skinny ORM is built upon ScalikeJDBC.

In most cases, ORM makes things easier.

https://github.com/skinny-framework/skinny-framework#orm


If this webpage has a typo or something wrong, Please report or fix it. How?