ScalikeJDBC

Just write SQL and get things done!

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
• 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.

FAQ

See also FAQs here: /documentation/faq.html

Getting Started

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

ScalikeJDBC 4.x

ScalikeJDBC 4 requires Java SE 8 or higher. If you still need to run your applications on Java SE 7, keep using ScalikeJDBC 2.5.

// Scala 2.12, 2.13 and 3
libraryDependencies ++= Seq(
  "org.scalikejdbc" %% "scalikejdbc"       % "4.0.0",
  "com.h2database"  %  "h2"                % "1.4.200",
  "ch.qos.logback"  %  "logback-classic"   % "1.2.3"
)

ScalikeJDBC 2.x

See ScalikeJDBC 2.x Documentation for details.

// Scala 2.10, 2.11, 2.12
libraryDependencies ++= Seq(
  "org.scalikejdbc" %% "scalikejdbc"               % "2.5.2",
  "com.h2database"  %  "h2"                        % "1.4.200",
  "ch.qos.logback"  %  "logback-classic"           % "1.2.3"
)

First example

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

import scalikejdbc._

// 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 java.time._
case class Member(id: Long, name: Option[String], createdAt: ZonedDateTime)
object Member extends SQLSyntaxSupport[Member] {
  override val tableName = "members"
  def apply(rs: WrappedResultSet) = new Member(
    rs.long("id"), rs.stringOpt("name"), rs.zonedDateTime("created_at"))
}

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

// use paste mode (:paste) on the Scala REPL
val m = Member.syntax("m")
val name = "Alice"
val alice: Option[Member] = withSQL {
  select.from(Member as m).where.eq(m.name, name)
}.map(rs => Member(rs)).single.apply()

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

Quick Tour

Using only the 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: Seq[Programmer] = 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 which re-uses methods in both 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, but it 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 "scalikejdbcGen [table-name (class-name)]"

e.g.

sbt "scalikejdbcGen company"
sbt "scalikejdbcGen 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 used with scalikejdbc-mapper-generator.

See in detail: /documentation/playframework-support

Reactive Streams Support

ScalikeJDBC provides the Publisher interface of Reactive Streams to subscribe a stream from a database query.

See in detail: /documentation/reactivestreams-support

We’re using ScalikeJDBC!

Many companies are already use ScalikeJDBC in their business!

Read more..

License

Published binary files have the following copyright:

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

Related Products

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 jasync-sql.

dbconsole

dbconsole is an extended sbt console to connect database.

Mac OS X, Linux

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

Windows

https://git.io/dbcon.bat

See in detail: /documentation/dbconsole

Skinny ORM

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://skinny-framework.github.io/documentation/orm.html