ScalaSql

ScalaSql is a Scala ORM library that allows type-safe low-boilerplate querying of SQL databases, using "standard" Scala collections operations running against typed Table descriptions.

import scalasql.*, SqliteDialect.*

// Define your table model classes
case class City[T[_]](
    id: T[Int],
    name: T[String],
    countryCode: T[String],
    district: T[String],
    population: T[Long]
)
object City extends Table[City]

// Connect to your database (example uses in-memory sqlite, org.xerial:sqlite-jdbc:3.43.0.0)
val dataSource = new org.sqlite.SQLiteDataSource()
dataSource.setUrl(s"jdbc:sqlite:file.db")
lazy val dbClient = new scalasql.DbClient.DataSource(
  dataSource,
  config = new scalasql.Config {
    override def nameMapper(v: String) = v.toLowerCase() // Override default snake_case mapper
    override def logSql(sql: String, file: String, line: Int) = println(s"$file:$line $sql")
  }
)

dbClient.transaction{ db =>

  // Initialize database table schema and data
  db.updateRaw(os.read(os.Path("scalasql/test/resources/world-schema.sql", os.pwd)))
  db.updateRaw(os.read(os.Path("scalasql/test/resources/world-data.sql", os.pwd)))

  // Adding up population of all cities in China
  val citiesPop = db.run(City.select.filter(_.countryCode === "CHN").map(_.population).sum)
  // SELECT SUM(city0.population) AS res FROM city city0 WHERE city0.countrycode = ?

  println(citiesPop)
  // 175953614

  // Finding the 5-8th largest cities by population
  val fewLargestCities = db.run(
    City.select
        .sortBy(_.population).desc
        .drop(5).take(3)
        .map(c => (c.name, c.population))
  )
  // SELECT city0.name AS res__0, city0.population AS res__1
  // FROM city city0 ORDER BY res__1 DESC LIMIT ? OFFSET ?

  println(fewLargestCities)
  // Seq((Karachi, 9269265), (Istanbul, 8787958), (Ciudad de México, 8591309))
}

ScalaSql supports database connections to PostgreSQL, MySQL, Sqlite, MSSql and H2 databases. Support for additional databases can be easily added.

ScalaSql is a relatively new library, so please try it out, but be aware you may hit bugs or missing features! Please open Discussions for any questions, file Issues for any bugs you hit, or send Pull Requests if you are able to investigate and fix them!

Getting Started

To get started with ScalaSql, add it to your build.mill file as follows:

ivy"com.lihaoyi::scalasql:0.3.0"

ScalaSql supports Scala >=3.6.2

SimpleTable variant based on named tuples

For Scala versions >=3.7.0 supporting named tuples, an alternative way to define tables is supported.

Add the following to your build.sc file as follows:

ivy"com.lihaoyi::scalasql-simple:0.3.0"

And taking the example above, the only thing that needs to change is the following:

-import scalasql.*, SqliteDialect.*
+import scalasql.simple.*, SqliteDialect.*

 // Define your table model classes
-case class City[T[_]](
-    id: T[Int],
-    name: T[String],
-    countryCode: T[String],
-    district: T[String],
-    population: T[Long]
-)
-object City extends Table[City]
+case class City(
+    id: Int,
+    name: String,
+    countryCode: String,
+    district: String,
+    population: Long
+)
+object City extends SimpleTable[City]

And you now have the option to return named tuples from queries:

 val fewLargestCities = db.run(
    City.select
        .sortBy(_.population).desc
        .drop(5).take(3)
-       .map(c => (c.name, c.population))
+       .map(c => (name = c.name, pop = c.population))
  )

ZIO integration

For ZIO users, the scalasql-zio module wraps ScalaSql's JDBC API in ZIO effects, providing type-safe database operations and transactions with savepoints. Scala 3 only.

ivy"com.lihaoyi::scalasql-zio:0.3.0"

Queries run as ZIO effects, with JDBC errors wrapped in DbException and committed/rolled back automatically by the enclosing transaction:

import scalasql.dbzio.*

ZDbClient.transaction:
  for
    _    <- DbOp.run(User.insert.columns(_.email := email, _.name := name))
    user <- DbOp.runSingle(User.select.filter(_.email `=` email))
  yield user

See the ZDbApiPlainSpec for transaction, rollback, and savepoint examples.

Documentation

• ScalaSql Quickstart Examples: self-contained files showing how to set up ScalaSql to connect your Scala code to a variety of supported databases and perform simple DDL and SELECT/INSERT/UPDATE/DELETE operations:

  • Postgres
  • MySql
  • Sqlite
  • H2
  • Microsoft SQL Server
  • HikariCP (and other connection pools)

• ScalaSql Tutorial: a structured walkthrough of how to use ScalaSql, connecting to a database and writing queries to SELECT/INSERT/UPDATE/DELETE against it to perform useful work. Ideal for newcomers to work through from top to bottom when getting started with the library.

• ScalaSql Cheat Sheet: a compact summary of the main features of ScalaSql and the syntax to make use of them.

• ScalaSql Reference: a detailed listing of ScalaSql functionality, comprehensively covering everything that ScalaSql supports, in a single easily searchable place. Ideal for looking up exactly methods/operators ScalaSql supports, looking up how ScalaSql code translates to SQL, or looking up SQL syntax to find out how to express it using ScalaSql. Useful subsections include:

  • DbApi, covering the main methods you can all to execute queries
  • Transaction, covering usage of transactions and savepoints
  • Select, Insert, Update, Delete: covering operations on the primary queries you are likely to use
  • Join, covering different kinds of joins
  • Returning, On Conflict: covering these modifiers on INSERT and UPDATE for the databases that support them
  • Expression Operations, covering the different types of Expr[T] values and the different operations you can do on each one
  • Option Operations, operations on Expr[Option[T]
  • Window Functions, With-Clauses/Common-Table-Expressions
  • Postgres, MySql, Sqlite, H2 Dialects: operations that are specific to each database that may not be generally applicable

• ScalaSql Design: discusses the design of the ScalaSql library, why it is built the way it is, what tradeoffs it makes, and how it compares to other common Scala database query libraries. Ideal for contributors who want to understand the structure of the ScalaSql codebase, or for advanced users who may need to understand enough to extend ScalaSql with custom functionality.

• Developer Docs: things you should read if you want to make changes to the com-lihaoyi/scalasql codebase

Changelog

0.3.1

• Upgrade to mill 1.0.5 #110
• Deprecate scala 2 #111
• Strict equality in operations #112

0.3.0

• Add support for asynchronous transaction API #107

0.2.7

• scalasql-namedtuples artifact is now named scalasql-simple

0.2.3

• Bump Sourcecode version so queries work in REPL #101

0.2.2

• Adding COUNT and COUNT(DISTINCT) aggregation support #97

0.2.1

• Initial support for Microsoft SQL Server via scalasql.dialects.MsSqlDialect #94

0.1.20

• Support for SimpleTable classes which do not need a T[_] higher kinded parameter, and support for use of Scala 3.7.0 named tuples in queries #81. See this blog post for more details: Making ScalaSql boring again (with interesting new internals)

0.1.19

• Escape table in returning clause #80

0.1.18

• Fix for escaped update with where clause #79

0.1.17

• Add filterIf and filterOpt utility methds to Select #77
• Escape table names #73

0.1.16

• Add TransactionListener interface #71
• JoinNullable deserialisation fix #70
• Insert.select on conflict #68
• Add OnConflict to InsertValues #61

0.1.15

• Support schemaName in non-SELECT queries #57

0.1.14

• Remove uneeded Numeric constraint from ordering-related functions #47
• Add SELECT FOR UPDATE support for Postgres and MySQL #45

0.1.11

• Support null value passthrough for all TypeMappers #39
• Fix optional null values failing to put #42

0.1.10

• Fix a typo in isNormalCharacter #37
• Instant type mapper now allows NULL values #38

0.1.9

• Add support for Float data type #32

0.1.8

• Introduce TypeMapper#bimap to make creating related TypeMappers easier #27

0.1.7

• Add support for columns of type java.util.Date #24

0.1.6

• Add support for non-default database schemas in Postgres #23

0.1.5

• Properly pass ON CONFLICT column names through columnNameMapper #19

0.1.4

• Second attempt at fixing invalid version of scala-reflect dependency

0.1.3

• Support for Scala 3.4.2 and #11

0.1.2

• Support .getGeneratedKeys[R] #9

0.1.1

• Fix invalid version of scala-reflect dependency

0.1.0

• First release!

TODO

• JSON columns
• Add datetime functions
• Make implicit ctx => for defining sql"..." snippets optional