augustnagro / magnum   1.3.1

Apache License 2.0 GitHub

A 'new look' for database access in Scala

Scala versions: 3.x

Magnum

Yet another database client for Scala. No dependencies, high productivity.

Installing

"com.augustnagro" %% "magnum" % "1.3.0"

Magnum requires Scala >= 3.3.0

You must also install the JDBC driver for your database, for example:

"org.postgresql" % "postgresql" % "<version>"

And for performance, a JDBC connection pool like HikariCP

ScalaDoc

https://javadoc.io/doc/com.augustnagro/magnum_3

Documentation

connect creates a database connection.

connect takes two parameters; the database Transactor, and a context function with a given DbCon connection. For example:

import com.augustnagro.magnum.*

val dataSource: javax.sql.DataSource = ???
val xa = Transactor(dataSource)

val users: Vector[User] = connect(xa):
  sql"SELECT * FROM user".query[User].run()

transact creates a database transaction.

Like connect, transact accepts a Transactor and context function. The context function provides a DbTx instance. If the function throws, the transaction will be rolled back.

// update is rolled back
transact(xa):
  sql"UPDATE user SET first_name = $firstName WHERE id = $id".update.run()
  thisMethodThrows()

Type-safe Transaction & Connection Management

Annotate transactional methods with using DbTx, and connections with using DbCon.

Since DbTx <: DbCon, it's impossible to call a method with the wrong context.

For example, this compiles:

def runUpdateAndGetUsers()(using DbTx): Vector[User] =
  userRepo.deleteById(1L)
  getUsers

def getUsers(using DbCon): Vector[User] =
  sql"SELECT * FROM user".query.run()

But not this:

def runSomeQueries(using DbCon): Vector[User] =
  runUpdateAndGetUsers()

Customizing transactions

Transactor lets you customize the transaction (or connection) behavior.

val xa = Transactor(
  dataSource = ???,
  sqlLogger = SqlLogger.logSlowQueries(500.milliseconds),
  connectionConfig = con =>
    con.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ)
)

transact(xa):
  sql"SELECT id from myUser".query[Long].run()

Sql Interpolator, Frag, Query, and Update

The sql interpolator can express any SQL expression, returning a Frag sql fragment. You can interpolate values without the risk of SQL-injection attacks.

val firstNameOpt = Some("John")
val twoDaysAgo = OffsetDateTime.now.minusDays(2)

val frag: Frag =
  sql"""
    SELECT id, last_name FROM user
    WHERE first_name = $firstNameOpt
    AND created <= $twoDaysAgo
    """

Frags can be turned into queries with the query[T](using DbCodec[T]) method:

val query = frag.query[(Long, String)] // Query[(Long, String)]

Or updates via update

val update: Update =
  sql"UPDATE user SET first_name = 'Buddha' WHERE id = 3".update

Or an update with a RETURNING clause via returning:

val updateReturning: Returning =
  sql"""
     UPDATE user SET first_name = 'Buddha'
     WHERE last_name = 'Harper'
     RETURNING id
     """.returning[Long]

All are executed via run()(using DbCon):

transact(xa):
  val tuples: Vector[(Long, String)] = query.run()
  val updatedRows: Int = update.run()
  val updatedIds: Vector[Long] = updateReturning.run()

Batch Updates

Batch updates are supported via batchUpdate method in package com.augustnagro.magnum.

connect(xa):
  val users: Iterable[User] = ???
  val updateResult: BatchUpdateResult =
    batchUpdate(users): user =>
      sql"...".update

batchUpdate returns a BatchUpdateResult enum, which is Success(numRowsUpdated) or SuccessNoInfo otherwise.

Immutable Repositories

The ImmutableRepo class auto-generates the following methods at compile-time:

  def count(using DbCon): Long
  def existsById(id: ID)(using DbCon): Boolean
  def findAll(using DbCon): Vector[E]
  def findAll(spec: Spec[E])(using DbCon): Vector[E]
  def findById(id: ID)(using DbCon): Option[E]
  def findAllById(ids: Iterable[ID])(using DbCon): Vector[E]

Here's an example:

@Table(PostgresDbType, SqlNameMapper.CamelToSnakeCase)
case class User(
  @Id id: Long,
  firstName: Option[String],
  lastName: String,
  created: OffsetDateTime
) derives DbCodec

val userRepo = ImmutableRepo[User, Long]

transact(xa):
  val cnt = userRepo.count
  val userOpt = userRepo.findById(2L)

Importantly, class User is annotated with @Table, which defines the table's database type. The annotation optionally specifies the name-mapping between scala fields and column names. You can also use the @SqlName annotation on individual fields. Finally, The table must derive DbCodec, or otherwise provide an implicit DbCodec instance.

The optional @Id annotation denotes the table's primary key. Not setting @Id will default to using the first field. If there is no logical id, then remove the annotation and use Null in the ID type parameter of Repositories (see next).

It is a best practice to extend ImmutableRepo to encapsulate your SQL in repositories. This way, it's easier to maintain since they're grouped together.

class UserRepo extends ImmutableRepo[User, Long]:
  def firstNamesForLast(lastName: String)(using DbCon): Vector[String] =
    sql"""
      SELECT DISTINCT first_name
      FROM user
      WHERE last_name = $lastName
      """.query[String].run()
        
  // other User-related queries here

Repositories

The Repo class auto-generates the following methods at compile-time:

  def count(using DbCon): Long
  def existsById(id: ID)(using DbCon): Boolean
  def findAll(using DbCon): Vector[E]
  def findAll(spec: Spec[E])(using DbCon): Vector[E]
  def findById(id: ID)(using DbCon): Option[E]
  def findAllById(ids: Iterable[ID])(using DbCon): Vector[E]
  
  def delete(entity: E)(using DbCon): Unit
  def deleteById(id: ID)(using DbCon): Unit
  def truncate()(using DbCon): Unit
  def deleteAll(entities: Iterable[E])(using DbCon): BatchUpdateResult
  def deleteAllById(ids: Iterable[ID])(using DbCon): BatchUpdateResult
  def insert(entityCreator: EC)(using DbCon): Unit
  def insertAll(entityCreators: Iterable[EC])(using DbCon): Unit
  def insertReturning(entityCreator: EC)(using DbCon): E
  def insertAllReturning(entityCreators: Iterable[EC])(using DbCon): Vector[E]
  def update(entity: E)(using DbCon): Unit
  def updateAll(entities: Iterable[E])(using DbCon): BatchUpdateResult

Here's an example:

@Table(PostgresDbType, SqlNameMapper.CamelToSnakeCase)
case class User(
  @Id id: Long,
  firstName: Option[String],
  lastName: String,
  created: OffsetDateTime
) derives DbCodec

val userRepo = Repo[User, User, Long]

val countAfterUpdate = transact(xa):
  userRepo.deleteById(2L)
  userRepo.count

It is a best practice to encapsulate your SQL in repositories.

class UserRepo extends Repo[User, User, Long]

Also note that Repo extends ImmutableRepo. Some databases cannot support every method, and will throw UnsupportedOperationException.

Database generated columns

It is often the case that database columns are auto-generated, for example, primary key IDs. This is why the Repo class has 3 type parameters.

The first defines the Entity-Creator, which should omit any fields that are auto-generated. The entity-creator class must be an 'effective' subclass of the entity class, but it does not have to subclass the entity. This is verified at compile time.

The second type parameter is the Entity class, and the final is for the ID. If the Entity does not have a logical ID, use Null.

case class UserCreator(
  firstName: Option[String],
  lastName: String,
) derives DbCodec

@Table(PostgresDbType, SqlNameMapper.CamelToSnakeCase)
case class User(
  @Id id: Long,
  firstName: Option[String],
  lastName: String,
  created: OffsetDateTime
) derives DbCodec

val userRepo = Repo[UserCreator, User, Long]

val newUser: User = transact(xa):
  userRepo.insertReturning(
    UserCreator(Some("Adam"), "Smith")
  )

Specifications

Specifications help you write safe, dynamic queries. An example use-case would be a search results page that allows users to sort and filter the paginated data.

  1. If you need to perform joins to get the data needed, first create a database view.
  2. Next, create an entity class that derives DbCodec.
  3. Finally, use the Spec class to create a specification.

Here's an example:

val partialName = "Ja"
val lastNameOpt = Option("Brown")
val searchDate = OffsetDateTime.now.minusDays(2)
val idPosition = 42L

val spec = Spec[User]
  .where(sql"first_name ILIKE '$partialName%'")
  .where(lastNameOpt.map(ln => sql"last_name = $ln").getOrElse(sql""))
  .where(sql"created >= $searchDate")
  .seek("id", SeekDir.Gt, idPosition, SortOrder.Asc)
  .limit(10)

val users: Vector[User] = userRepo.findAll(spec)

Note that both seek pagination and offset pagination is supported.

Scala 3 Enum & NewType Support

Magnum supports Scala 3 enums (non-adt) fully, by default writing & reading them as Strings. For example,

@Table(PostgresDbType, SqlNameMapper.CamelToUpperSnakeCase)
enum Color derives DbCodec:
  case Red, Green, Blue

@Table(PostgresDbType, SqlNameMapper.CamelToSnakeCase)
case class User(
  @Id id: Long,
  firstName: Option[String],
  lastName: String,
  created: OffsetDateTime,
  favoriteColor: Color
) derives DbCodec

NewTypes and Opaque Type Alias can cause issues with derivation since given DbCodecs are not available. A simple way to provide them is using DbCodec.bimap:

opaque type MyId = Long

object MyId:
  def apply(id: Long): MyId =
    require(id >= 0)
    id

  extension (myId: MyId)
    def underlying: Long = myId

  given DbCodec[MyId] =
    DbCodec[Long].biMap(MyId.apply, _.underlying)

transact(xa):
  val id = MyId(123L)
  sql"UPDATE my_table SET x = true WHERE id = $id".update.run()

DbCodec: Typeclass for JDBC reading & writing

DbCodec is a Typeclass for JDBC reading & writing.

Built-in DbCodecs are provided for many types, including primitives, dates, Options, and Tuples. You can derive DbCodecs by adding derives DbCodec to your case class or enum.

val rs: ResultSet = ???
val ints: Vector[Int] = DbCodec[Int].read(rs)

val ps: PreparedStatement = ???
DbCodec[Int].writeSingle(22, ps)

Defining your own DbCodecs

To modify the JDBC mappings, implement a given DbCodec instance as you would for any Typeclass.

Future-Proof Queries

A common problem when writing SQL queries is that they're difficult to refactor. When a column or table name changes you have to do a global find & replace. And if you miss a query, it's discovered at runtime.

There's also lots of repetition when writing SQL. Magnum's repositories help scrap the boilerplate, but writing SELECT a, b, c, d, ... for a large table quickly gets tiring.

To help with this, Magnum offers a TableInfo class to enable 'future-proof' queries. An important caveat is that these queries are harder to copy/paste into SQL editors like PgAdmin or DbBeaver.

Here's some examples:

import com.augustnagro.magnum.*

case class UserCreator(firstName: String, age: Int) derives DbCodec

@Table(PostgresDbType, SqlNameMapper.CamelToSnakeCase)
case class User(id: Long, firstName: String, age: Int) derives DbCodec

object User:
  val Table = TableInfo[UserCreator, User, Long]

def allUsers(using DbCon): Vector[User] =
  val u = User.Table
  // equiv to 
  // SELECT id, first_name, age FROM user
  sql"SELECT ${u.all} FROM $u".query[User].run()

def firstNamesForLast(lastName: String)(using DbCon): Vector[String] =
  val u = User.Table
  // equiv to
  // SELECT DISTINCT first_name FROM user WHERE last_name = ?
  sql"""
    SELECT DISTINCT ${u.firstName} FROM $u
    WHERE ${u.lastName} = $lastName
  """.query[String].run()

def insertOrIgnore(creator: UserCreator)(using DbCon): Unit =
  val u = User.Table
  // equiv to
  // INSERT OR IGNORE INTO user (first_name, age) VALUES (?, ?)
  sql"INSERT OR IGNORE INTO $u ${u.insertCols} VALUES ($creator)".update.run()

It's important that val Table = TableInfo[X, Y, Z] is not explicitly typed, otherwise its structural typing will be destroyed.

In the case of multiple joins, you can use TableInfo.alias(String) to prevent name conflicts:

val c = TableInfo[Car].alias("c")
val p = TableInfo[Person].alias("p")

sql"""
   SELECT ${c.all}, ${p.firstName}
   FROM $c
   JOIN $p ON ${p.id} = ${c.personId}
   """.query.run()

Splicing Literal Values into Frags

To splice Strings directly into sql statements, you can interpolate SqlLiteral values. For example,

val table = SqlLiteral("beans")
  
sql"select * from $table"

This feature should be used sparingly and never with untrusted input.

Postgres Module

The Postgres Module adds support for Geometric Types and Arrays. Postgres Arrays can be decoded into Scala List/Vector/IArray, etc; multi-dimensionality is also supported.

"com.augustnagro" %% "magnumpg" % "1.3.0"

Example: Insert into a table with a point[] type column.

With table:

create table my_geo (
  id bigint primary key,
  pnts point[] not null
);
import org.postgresql.geometric.*
import com.augustnagro.magnum.*
import com.augustnagro.magnum.pg.PgCodec.given

@Table(PostgresDbType)
case class MyGeo(@Id id: Long, pnts: IArray[PGpoint]) derives DbCodec

val dataSource: javax.sql.DataSource = ???
val xa = Transactor(dataSource)

val myGeoRepo = Repo[MyGeo, MyGeo, Long]

transact(xa):
  myGeoRepo.insert(MyGeo(1L, IArray(PGpoint(1, 1), PGPoint(2, 2))))

The import of PgCodec.given is required to bring Geo/Array DbCodecs into scope.

Arrays of Enums

The pg module supports arrays of simple (non-ADT) enums.

If you want to map an array of Postgres enums to a sequence of Scala enums, use the following import when deriving the DbCodec:

import com.augustnagro.magnum.pg.PgCodec.given
import com.augustnagro.magnum.pg.enums.PgEnumToScalaEnumSqlArrayCodec

// in postgres: `create type Color as enum ('Red', 'Green', 'Blue');`
enum Color derives DbCodec:
  case Red, Green, Blue

@Table(PostgresDbType)
case class Car(@Id id: Long, colors: Vector[Color]) derives DbCodec

If instead your Postgres type is an array of varchar or text, use the following import:

import com.augustnagro.magnum.pg.enums.PgStringToScalaEnumSqlArrayCodec

Logging SQL queries

If you set the java.util Logging level to DEBUG, all SQL queries will be logged. Setting to TRACE will log SQL queries and their parameters.

Logging Slow Queries

You can log slow queries by using the Transactor class in conjunction with SqlLogger.logSlowQueries(FiniteDuration). See Customizing Transactions for an example. You can also implement your own SqlLogger subclass as desired.

Motivation

Historically, database clients on the JVM fall into three categories.

  • Object Oriented Repositories (Spring-Data, Hibernate)
  • Functional DSLs (JOOQ, Slick, quill, zio-sql)
  • SQL String interpolators (Anorm, doobie, plain jdbc)

Magnum is a Scala 3 library combining aspects of all three, providing a typesafe and refactorable SQL interface, which can express all SQL expressions, on all JDBC-supported databases.

Like in Zoolander (the movie), Magnum represents a 'new look' for Database access in Scala.

Feature List

  • Supports any database with a JDBC driver, including Postgres, MySql, Oracle, ClickHouse, H2, and Sqlite
  • Efficient sql" " interpolator
  • Purely-functional API
  • Common queries (like insert, update, delete) generated at compile time
  • Difficult to hit N+1 query problem
  • Type-safe Transactions
  • Supports database-generated columns
  • Easy to use, Loom-ready API (no Futures or Effect Systems)
  • Easy to define entities. Easy to implement DB support & codecs for custom types.
  • Scales to complex SQL queries
  • Specifications for building dynamic queries, such as table filters with pagination
  • Supports high-performance Seek pagination
  • Performant batch-queries

Developing

The tests are written using TestContainers, which requires Docker be installed.

Talks and Blogs

Frequently Asked Questions

Does Magnum support nested entities like:

@Table(H2DbType, SqlNameMapper.CamelToSnakeCase)
case class Company(
  name: String,
  address: Address,
  ) derives DbCodec

case class Address(
  street: String,
  city: String,
  zipCode: String,
  country: String
) derives DbCodec

NO; Magnum only supports deriving flat entity class structures. This keeps things simple and makes it obvious how the Scala entity class maps to the SQL table.

We may add support for SQL UDTs (user defined types) in the future; however at the time of writing, UDTs are not well-supported by JDBC drivers.

You could also express the above example using a foreign key to an Address table, like so:

@Table(H2DbType, SqlNameMapper.CamelToSnakeCase)
case class Company(
  name: String,
  addressId: AddressId,
) derives DbCodec

opaque type AddressId = Long
object AddressId:
  def apply(id: Long): AddressId = id
  extension (id: AddressId)
    def underlying: Long = id
  given DbCodec[AddressId] =
    DbCodec[Long].biMap(AddressId.apply, _.underlying)

@Table(H2DbType, SqlNameMapper.CamelToSnakeCase)
case class Address(
  @Id id: AddressId,
  street: String,
  city: String,
  zipCode: String,
  country: String
) derives DbCodec

UUID DbCodec doesn't work for my database

Some databases directly support the UUID type; these include Postgres, Clickhouse, and H2. When using the built-in DbCodec[UUID], defined in DbCodec.scala, serialization and deserialization of java.util.UUID will work as expected.

Other databases like MySql, Oracle, and Sqlite, however, do not natively support UUID columns. Users have to choose an alternate datatype to store the UUID: most commonly varchar(36) or binary(16). The JDBC drivers for these databases do not support direct serialization and deserialization of java.util.UUID, therefore the default DbCodec[UUID] will not be sufficient. Instead, import the appropriate codec from com.augustnagro.magnum.UUIDCodec. For example,

import com.augustnagro.magnum.*
import com.augustnagro.magnum.UUIDCodec.VarCharUUIDCodec
import java.util.UUID

@Table(MySqlDbType)
case class Person(@Id id: Long, name: String, tracking_id: Option[UUID]) derives DbCodec

Todo

  • JSON / XML support
  • Support MSSql
  • Cats Effect & ZIO modules
  • Explicit Nulls support

Statistics

3 Dependencies

No Dependent