Version Matrix

Build Status Coverage Status scala-index Scala.js

Scala Commons Web SQL

Scala.js facade and quill bindings for WebSQL API

It's relying on the following reference implementation:

This API can be backed by SQLite on Node.js and react-native platforms.

How to add it to your project

val scommonsWebSqlVer = "1.0.0-SNAPSHOT"

libraryDependencies ++= Seq(
  "org.scommons.websql" %%% "scommons-websql-core" % scommonsWebSqlVer,
  "org.scommons.websql" %%% "scommons-websql-migrations" % scommonsWebSqlVer,
  "org.scommons.websql" %%% "scommons-websql-quill" % scommonsWebSqlVer

Latest SNAPSHOT version is published to Sonatype Repo, just make sure you added the proper dependency resolver to your build.sbt settings:

resolvers += "Sonatype Snapshots" at ""

How to use it

Open Database

import scommons.websql.WebSQL

// on react-native (see scommons-expo module)
val db = SQLite.openDatabase("myfirst.db")

// on Node.js
val db = WebSQL.openDatabase("myfirst.db")

// or in-memory DB, useful for testing
val db = WebSQL.openDatabase(":memory:")

Create DB Schema

You can use executeSql method to run raw SQL queries:

db.transaction { tx =>
      """CREATE TABLE IF NOT EXISTS categories (
        |  id              integer primary key,
        |  category_name   text NOT NULL,
        |  created_at      timestamp NOT NULL DEFAULT (strftime('%s','now') * 1000),
        |  UNIQUE (category_name)
      "INSERT INTO categories (category_name) VALUES (?), (?)",
        "test category 1",
        "test category 2"

Setup DB migrations

To automate DB schema versioning you can use scommons-websql-migrations library together with sbt-scommons-plugin.

First, set the scommonsBundlesFileFilter sbt build setting:

import scommons.sbtplugin.ScommonsPlugin.autoImport._

scommonsBundlesFileFilter := "*.sql"

Then add your SQL migrations scripts:

This setup will automatically generate single bundle.json file during the build with the all SQL scripts content inside.

Then you can read this file from the code:

import scala.scalajs.js
import scala.scalajs.js.annotation.JSImport
import scommons.websql.migrations.WebSqlMigrationBundle

@JSImport("./scommons/websql/migrations/bundle.json", JSImport.Namespace)
object TestMigrationsBundle extends WebSqlMigrationBundle

And run migrations at the start of your app:

import scommons.websql.migrations.WebSqlMigrations

val migrations = new WebSqlMigrations(db)


Create quill DB Context

To use quill bindings include scommons-websql-quill library.

Example quill context with pre-defined fields naming (snake_case):

import io.getquill.SnakeCase
import scommons.websql.Database
import scommons.websql.quill.SqliteContext

class ShowcaseDBContext(db: Database) extends SqliteContext(SnakeCase, db)

Create DB Entity

Example entity class with DB schema definition:

case class CategoryEntity(id: Int,
                          categoryName: String)

trait CategorySchema {

  val ctx: ShowcaseDBContext
  import ctx._

  implicit val categoriesInsertMeta = insertMeta[CategoryEntity](
  implicit val categoriesUpdateMeta = updateMeta[CategoryEntity](

  val categories = quote(querySchema[CategoryEntity]("categories"))

Create DAO

Example DAO class with DB queries/actions:

import scommons.websql.quill.dao.CommonDao
import showcase.domain._

import scala.concurrent.Future

class CategoryDao(val ctx: ShowcaseDBContext) extends CommonDao
  with CategorySchema {

  import ctx._

  def getByIdQuery(id: Int): IO[Seq[CategoryEntity], Effect.Read] = {
      .filter(c => == lift(id))

  def getById(id: Int): Future[Option[CategoryEntity]] = {
    getOne("getById", ctx.performIO(getByIdQuery(id)))

  def count(): Future[Int] = {

  def list(optOffset: Option[Int],
           limit: Int,
           symbols: Option[String]
          ): Future[(Seq[CategoryEntity], Option[Int])] = {

    val textLower = s"%${symbols.getOrElse("").trim.toLowerCase}%"
    val offset = optOffset.getOrElse(0)

    val countQuery = optOffset match {
      case Some(_) => IO.successful(None)
      case None =>
        .filter(c =>
    val fetchQuery =

    val q = for {
      maybeCount <- countQuery
      results <- fetchQuery
    } yield {

    // internally IO is always performed within transaction
    // so, explicitly specifying transactional has no additional effect

  def insertQuery(entity: CategoryEntity): IO[Int, Effect.Write] = {
  def insert(entity: CategoryEntity): Future[Int] = {

  def upsert(entity: CategoryEntity): Future[CategoryEntity] = {
    val q = for {
      maybeCategory <-
        .filter(c => c.categoryName == lift(entity.categoryName))
      id <- maybeCategory match {
        case None => insertQuery(entity)
        case Some(c) =>
          updateQuery(entity.copy(id =
            .map(_ =>
      res <- getByIdQuery(id).map(_.head)
    } yield res

  def updateQuery(entity: CategoryEntity): IO[Long, Effect.Write] = {
      .filter(c => == lift(
  def update(entity: CategoryEntity): Future[Boolean] = {

  def deleteAll(): Future[Long] = {

Check generated SQL

Quill will log generated SQLs during compilation. Example output (edited to fit on the screen):

 SELECT, c.category_name FROM categories c WHERE = ?
 SELECT COUNT(*) FROM categories x
 SELECT COUNT(*) FROM categories c WHERE LOWER (c.category_name) like ?
      case None =>
 SELECT, x3.category_name FROM categories x3
  WHERE LOWER (x3.category_name) like ?
    val fetchQuery =
 INSERT INTO categories (category_name) VALUES (?)
 UPDATE categories SET category_name = ? WHERE id = ?
 DELETE FROM categories
Done compiling.

Running queries

Example business logic/service layer:

import showcase.domain.CategoryEntity
import showcase.domain.dao.CategoryDao

import scala.concurrent.Future

class CategoryService(dao: CategoryDao) {

  def getById(id: Int): Future[CategoryEntity] = {
    dao.getById(id).map(ensureCategory(id, _))
  def add(entity: CategoryEntity): Future[CategoryEntity] = {
    for {
      insertId <- dao.insert(entity)
      entity <- dao.getById(insertId).map(ensureCategory(insertId, _))
    } yield entity
  private def ensureCategory(id: Int, maybeCat: Option[CategoryEntity]): CategoryEntity = {
    maybeCat.getOrElse {
      throw new IllegalArgumentException(s"Category is not found, categoryId: $id")

How to Build

To build and run all the tests use the following command:

sbt test


You can find more documentation here