DOCSEARCHbyAlgolia
Submit Docs

Magnum Database Client for Scala

repository·master·Indexed 43 days ago

https://github.com/augustnagro/magnum

A high-productivity, zero-dependency database client for Scala 3.3.0+ providing type-safe transaction and connection management, SQL interpolation, and repository abstractions. Features include the `sql` interpolator for safe queries, `ImmutableRepo` and `Repo` for CRUD operations, dynamic query building with `Spec`, and specialized modules for PostgreSQL and ZIO integration.

Tokens
11.8K
Snippets
39
Records
42
Agent score
49%

What's inside Magnum

  1. How Magnum handles nested entities

    master

    Magnum does not support nested entity structures (e.g., a case class containing another case class as a field) via direct derivation. It only supports deriving flat entity class structures to ensure a clear mapping between Scala classes and SQL tables.

    To represent relationships between entities, use foreign keys and opaque types with custom DbCodec implementations.

    Example pattern for a relationship:

    1. Define an opaque type for the ID.
    2. Implement a DbCodec for that opaque type using biMap.
    3. Use the ID type in the parent entity and define the child entity as a separate @Table.
    @Table(H2DbType, SqlNameMapper.CamelToSnakeCase)
case class Company(
  name: String,
  addressId: AddressId,
)

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

  2. Manage transactions and connections using type-safe context annotations

    master

    Magnum uses Scala 3 context parameters to ensure type-safe management of connections and transactions.

    • Annotate methods requiring a transaction with using DbTx.
    • Annotate methods requiring a connection with using DbCon.

    Because DbTx <: DbCon, a method requiring a connection can be called within a transaction, but a method requiring a transaction cannot be called with only a connection. This prevents accidental non-transactional execution of transactional logic.

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

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

  3. Integrate Magnum with ZIO

    master

    Magnum provides a dedicated module magnum-zio that allows you to use connect and transact utilities within a ZIO effect context.

    To use this integration:

    1. Add the magnumzio dependency to your project.
    2. Import the ZIO-specific utilities from com.augustnagro.magnum.magzio.

    Note: The version x.x.x should be replaced with the appropriate version compatible with your Magnum installation.

    // Add dependency
"com.augustnagro" %% "magnumzio" % "x.x.x"

// Import utilities
import com.augustnagro.magnum.magzio.*

  4. Install Magnum

    master

    To use Magnum in your Scala project, add the following dependency to your build tool (e.g., build.sbt).

    Requirements:

    • Scala version: >= 3.3.0
    • A JDBC driver for your target database (e.g., org.postgresql:postgresql).
    • For production performance, it is recommended to use a JDBC connection pool like HikariCP.
    "com.augustnagro" %% "magnum" % "1.3.0"

  5. Use UUID codecs for databases without native UUID support

    master

    While databases like Postgres, Clickhouse, and H2 support java.util.UUID natively via the default DbCodec[UUID], other databases like MySql, Oracle, and Sqlite do not.

    For these databases, you must use a specific codec from com.augustnagro.magnum.UUIDCodec to map UUIDs to an appropriate storage type like varchar(36) or binary(16).

    Commonly, you will use VarCharUUIDCodec for MySql-compatible storage.

    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

  6. Use the Postgres Module for Arrays and Geometric Types

    master

    The magnumpg module adds support for PostgreSQL-specific types like Arrays and Geometric types.

    Dependency: "com.augustnagro" %% "magnumpg" % "1.3.0"

    Usage Requirements:

    • Import com.augustnagro.magnum.pg.PgCodec.given to bring the necessary codecs into scope.
    • For arrays of Postgres enums, import com.augustnagro.magnum.pg.enums.PgEnumToScalaEnumSqlArrayCodec.
    • For arrays of varchar/text mapped to enums, import com.augustnagro.magnum.pg.enums.PgStringToScalaEnumSqlArrayCodec.

    Example (Geometric Types):

    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

// ... setup xa and repo ...

transact(xa):
  myGeoRepo.insert(MyGeo(1L, IArray(PGpoint(1, 1), PGpoint(2, 2))))
    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))))

  7. How TableInfo and SQL interpolation work together

    master

    Magnum uses TableInfo to bridge the gap between Scala types and SQL syntax.

    1. Metadata Extraction: At compile time, TableInfo extracts the table name, column names (both Scala and SQL versions), and primary key information from your annotated case classes.
    2. Interpolation: When you use sql"... ${u.fieldName} ...", the selectDynamic method on TableInfo is invoked. This looks up the ColumnName metadata for that specific field.
    3. SQL Generation: The ColumnName contains a queryRepr (e.g., u.first_name). When the SQL interpolator processes the expression, it injects this queryRepr directly into the string, ensuring the resulting SQL is valid and correctly qualified.

  8. Use the DbCodec typeclass for JDBC mapping

    master

    The DbCodec[E] typeclass is used to map Scala types E to and from JDBC ResultSet and PreparedStatement objects. It handles both reading values from a result set and writing values into a prepared statement, including support for complex types like tuples and case classes.

    Key Capabilities

    • Reading: Extract single values (readSingle), optional values (readSingleOption), or entire collections of rows (read) from a ResultSet.
    • Writing: Write single values (writeSingle) or perform batch updates (write) using a PreparedStatement.
    • Query Representation: Provides queryRepr to generate the appropriate SQL placeholder string (e.g., ? or (?, ?)).
    • Type Safety: Uses cols to track the number of JDBC columns required for a specific type, ensuring correct position offsets during multi-column operations.
    import com.augustnagro.magnum.DbCodec
import java.sql.ResultSet

// Example of using a codec to read a single value from a ResultSet
// Note: ResultSet must have had .next() called first.
val value: Int = codec.readSingle(resultSet, pos = 1)

  9. Create a database transaction with `transact`

    master

    Use transact to execute SQL within a database transaction. It requires a Transactor and a context function that provides a DbTx instance. If the context function throws an exception, the transaction is automatically rolled back.

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

  10. Perform batch updates with `batchUpdate`

    master

    Use the batchUpdate method from the com.augustnagro.magnum package to perform efficient batch operations. It returns a BatchUpdateResult which is either Success(numRowsUpdated) or SuccessNoInfo.

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

  11. Use `ImmutableRepo` for read-only operations

    master

    The ImmutableRepo[E, ID] class provides auto-generated compile-time methods for common read operations:

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

    Requirements:

    1. The entity class must be annotated with @Table (specifying database type and optional name mapping).
    2. The entity class must derive DbCodec.
    3. The primary key must be annotated with @Id (defaults to the first field if omitted).
    @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)

  12. Use `Repo` for full CRUD operations

    master

    The Repo[EC, E, ID] class extends ImmutableRepo and adds write/delete capabilities. It uses three type parameters:

    1. EC: The Entity-Creator (a class/case class used for inserts that omits auto-generated fields like IDs).
    2. E: The full Entity class.
    3. ID: The type of the primary key.

    Generated Methods:

    • All ImmutableRepo methods.
    • delete(entity: E)(using DbCon): Unit
    • deleteById(id: ID)(using DbCon): Unit
    • truncate()(using DbCon): Unit
    • deleteAll(entities: Iterable[E])(using DbCon): BatchUpdateResult
    • deleteAllById(ids: Iterable[ID])(using DbCon): BatchUpdateResult
    • insert(entityCreator: EC)(using DbCon): Unit
    • insertAll(entityCreators: Iterable[EC])(using DbCon): Unit
    • insertReturning(entityCreator: EC)(using DbCon): E
    • insertAllReturning(entityCreators: Iterable[EC])(using DbCon): Vector[E]
    • update(entity: E)(using DbCon): Unit
    • updateAll(entities: Iterable[E])(using DbCon): BatchUpdateResult

    Example with Entity-Creator:

    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]

transact(xa):
  val newUser: User = userRepo.insertReturning(UserCreator(Some("Adam"), "Smith"))
    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]

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