Scala.js facade for WebSQL API
It's relying on the following reference implementation: https://github.com/nolanlawson/node-websql
This API can be backed by SQLite on Node.js
and react-native platforms.
val scommonsWebSqlVer = "1.0.0-SNAPSHOT"
libraryDependencies ++= Seq(
"org.scommons.websql" %%% "scommons-websql-core" % scommonsWebSqlVer,
// see migrations/README.md
"org.scommons.websql" %%% "scommons-websql-migrations" % scommonsWebSqlVer,
// high level IO effect API (already includes core)
"org.scommons.websql" %%% "scommons-websql-io" % 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 "https://oss.sonatype.org/content/repositories/snapshots/"On react-native project (using
scommons-expo
module):
import scommons.expo.sqlite.SQLite
val db = SQLite.openDatabase("myfirst.db")On Node.js project:
import scommons.websql.WebSQL
val db = WebSQL.openDatabase("myfirst.db")
// or in-memory DB, useful for testing
val db = WebSQL.openDatabase(":memory:")You can use tx.executeSql method to run raw SQL queries:
db.transaction { tx =>
tx.executeSql(
"""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)
|)
|""".stripMargin
)
tx.executeSql(
"INSERT INTO categories (category_name) VALUES (?), (?)",
Seq(
"test category 1",
"test category 2"
)
)
}It can be fully automated by using migrations module.
Example
SQLite DB context:
import scommons.websql.Database
import scommons.websql.io.SqliteContext
class ShowcaseDBContext(db: Database) extends SqliteContext(db) {
// example of custom encoder
implicit val categoryIdToInt: MappedEncoding[CategoryId, Int] = mappedEncoding[CategoryId, Int](_.value)
implicit val categoryIdEncoder: Encoder[CategoryId] = mappedEncoder[CategoryId, Int]
// example of custom decoder
implicit val intToCategoryId: MappedEncoding[Int, CategoryId] = mappedEncoding[Int, CategoryId](CategoryId)
implicit val categoryIdDecoder: Decoder[CategoryId] = mappedDecoder[Int, CategoryId]
}Example
DB entity class:
case class CategoryEntity(id: Int,
categoryName: String)Data Access Object (DAO) layer has very similar query IO API
interface as quill, except that SQL has
to be written explicitly rather than generated during the build.
Example 1
DAO class with basic DB queries/actions:
import scommons.websql.io.dao.CommonDao
import scommons.websql.io.showcase.domain._
import scala.concurrent.Future
class CategoryDao(val ctx: ShowcaseDBContext) extends CommonDao {
import ctx._
def getByIdQuery(id: Int): IO[Seq[CategoryEntity], Effect.Read] = {
ctx.runQuery(
sql = "SELECT id, category_name FROM categories WHERE id = ?",
args = id,
extractor = CategoryEntity.tupled
)
}
def getById(id: Int): Future[Option[CategoryEntity]] = {
getOne("getById", ctx.performIO(getByIdQuery(id)))
}
def count(): Future[Int] = {
ctx.performIO(
ctx.runQuerySingle("SELECT count(*) FROM categories", identity[Int])
)
}
def list(optOffset: Option[Int],
limit: Int,
symbols: Option[String]
): Future[(Seq[CategoryEntity], Option[Int])] = {
val text = s"%${symbols.getOrElse("")}%"
val offset = optOffset.getOrElse(0)
val countQuery = optOffset match {
case Some(_) => IO.successful(None)
case None => ctx.runQuerySingle(
sql = "SELECT count(*) FROM categories WHERE category_name LIKE ?",
args = text,
extractor = identity[Int]
).map(Some(_))
}
val fetchQuery = ctx.runQuery(
sql =
"""SELECT
| id,
| category_name
|FROM
| categories
|WHERE
| category_name LIKE ?
|ORDER BY
| category_name
|LIMIT ?
|OFFSET ?
|""".stripMargin,
args = (text, limit, offset),
extractor = CategoryEntity.tupled
)
val q = for {
maybeCount <- countQuery
results <- fetchQuery
} yield {
(results, maybeCount)
}
// internally IO is always performed within transaction
ctx.performIO(q)
}
def insertQuery(entity: CategoryEntity): IO[Int, Effect.Write] = {
ctx.runActionReturning(
"INSERT INTO categories (category_name) VALUES (?)", entity.categoryName
).map(_.toInt)
}
def insert(entity: CategoryEntity): Future[Int] = {
ctx.performIO(insertQuery(entity))
}
def insertMany(list: Seq[CategoryEntity]): Future[Seq[Int]] = {
ctx.performIO(IO.sequence(list.map(insertQuery)))
}
def upsert(entity: CategoryEntity): Future[CategoryEntity] = {
val q = for {
maybeCategory <- ctx.runQuery(
sql = "SELECT id, category_name FROM categories WHERE category_name = ?",
args = entity.categoryName,
extractor = CategoryEntity.tupled
).map(_.headOption)
id <- maybeCategory match {
case None => insertQuery(entity)
case Some(c) =>
updateQuery(entity.copy(id = c.id))
.map(_ => c.id)
}
res <- getByIdQuery(id).map(_.head)
} yield res
ctx.performIO(q)
}
def updateQuery(entity: CategoryEntity): IO[Long, Effect.Write] = {
ctx.runAction(
sql = "UPDATE categories SET category_name = ? WHERE id = ?",
args = (entity.categoryName, entity.id)
)
}
def update(entity: CategoryEntity): Future[Boolean] = {
isUpdated(ctx.performIO(updateQuery(entity)))
}
def updateMany(list: Seq[CategoryEntity]): Future[Seq[Boolean]] = {
ctx.performIO(IO.sequence(list.map(updateQuery)).map { results =>
results.map(_ > 0)
})
}
def deleteAll(): Future[Long] = {
ctx.performIO(ctx.runAction("DELETE FROM categories"))
}
}Example 2
DAO class with more advanced DB queries:
import scommons.websql.io.dao.CommonDao
import scommons.websql.io.showcase.domain._
import scala.concurrent.Future
class ProductDao(val ctx: ShowcaseDBContext) extends CommonDao {
import ctx._
def allProducts(): Future[Seq[ProductEntity]] = {
ctx.performIO(ctx.runQuery(
"SELECT id, name, category_id FROM products ORDER BY id",
ProductEntity.tupled
))
}
def joinProducts(): Future[Seq[(ProductEntity, CategoryEntity)]] = {
ctx.performIO(ctx.runQuery(
sql =
"""SELECT
| p.id AS _0, -- *******************************
| p.name AS _1, -- * NOTE:
| p.category_id AS _2, -- * for JOIN queries from different tables
| c.id AS _3, -- * ALWAYS specify custom unique fields names !!!
| c.category_name AS _4 -- *******************************
|FROM (
| SELECT id, name, category_id FROM products ORDER BY id
|) AS p
|INNER JOIN categories c ON p.category_id = c.id
|""".stripMargin,
extractor = { case (p, c) =>
(ProductEntity.tupled(p), CategoryEntity.tupled(c))
}: (((Int, String, Option[Int]), (Int, String))) =>
(ProductEntity, CategoryEntity)
))
}
def leftJoinProducts(): Future[Seq[(ProductEntity, Option[CategoryEntity])]] = {
ctx.performIO(ctx.runQuery(
sql =
"""SELECT
| p.id AS _0, -- *******************************
| p.name AS _1, -- * NOTE:
| p.category_id AS _2, -- * for JOIN queries from different tables
| c.id AS _3, -- * ALWAYS specify custom unique fields names !!!
| c.category_name AS _4 -- *******************************
|FROM (
| SELECT id, name, category_id FROM products ORDER BY id
|) AS p
|LEFT JOIN categories c ON p.category_id = c.id
|""".stripMargin,
extractor = { case (p, c) =>
(ProductEntity.tupled(p), c.map(CategoryEntity.tupled))
}: (((Int, String, Option[Int]), Option[(Int, String)])) =>
(ProductEntity, Option[CategoryEntity])
))
}
}Example business logic / service layer:
import scommons.websql.io.showcase.domain.CategoryEntity
import scommons.websql.io.showcase.domain.dao.CategoryDao
import scala.concurrent.ExecutionContext.Implicits.global
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")
}
}
}You can find more documentation here