### Example Usage of Standard Repository

Source: https://github.com/augustnagro/magnum/blob/master/README.md

Illustrates using the `Repo` class for a `User` entity. This example shows deleting a user by ID and then checking the updated count within a transaction.

```scala
@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
```

--------------------------------

### Example Usage of Immutable Repository

Source: https://github.com/augustnagro/magnum/blob/master/README.md

Demonstrates creating an `ImmutableRepo` instance for a `User` entity and performing basic operations like counting and finding by ID within a transaction.

```scala
@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)
```

--------------------------------

### SQL Logging Configuration

Source: https://context7.com/augustnagro/magnum/llms.txt

Configures SQL query logging using `java.util.logging` levels and provides examples for logging all queries, only slow queries, or implementing a custom logger.

```scala
import com.augustnagro.magnum.*
import scala.concurrent.duration.*
import java.util.logging.{Logger, Level}

// Enable DEBUG logging for all Magnum queries
Logger.getLogger("com.augustnagro.magnum").setLevel(Level.FINE)

// Log only slow queries (>= 100ms) at WARNING; defer all others to default behaviour
val xa = Transactor(
  dataSource = dataSource,
  sqlLogger  = SqlLogger.logSlowQueries(100.milliseconds)
)

// Custom logger implementation
val auditLogger: SqlLogger = new SqlLogger:
  def log(e: SqlSuccessEvent): Unit = 
    println(s"[AUDIT] ${e.sql} — ${e.execTime}")
  def exceptionMsg(e: SqlExceptionEvent): String = 
    s"[ERROR] ${e.sql}: ${e.cause.getMessage}"

val xaAudit = Transactor(dataSource, auditLogger)
```

--------------------------------

### Define Company with Foreign Key to Address

Source: https://github.com/augustnagro/magnum/blob/master/README.md

This example demonstrates expressing a relationship using a foreign key to an Address table, adhering to Magnum's flat entity structure.

```scala
@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
```

--------------------------------

### Custom Queries in Immutable Repository

Source: https://github.com/augustnagro/magnum/blob/master/README.md

Extend `ImmutableRepo` to encapsulate custom SQL queries. This example shows how to define a repository for `User` entities with a method to retrieve first names based on last name.

```scala
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()
```

--------------------------------

### Insert into Postgres Table with Point Array

Source: https://github.com/augustnagro/magnum/blob/master/README.md

Example of inserting data into a table with a `point[]` type column using Scala. Requires `PgCodec.given` for Geo/Array DbCodecs.

```sql
create table my_geo (
  id bigint primary key,
  pnts point[] not null
);
```

```scala
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))))
```

--------------------------------

### Define Flat Case Class for Company

Source: https://github.com/augustnagro/magnum/blob/master/README.md

Magnum only supports deriving flat entity class structures. This example shows a flat structure for a Company case class.

```scala
@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
```

--------------------------------

### Import ZIO Integration Utilities

Source: https://github.com/augustnagro/magnum/blob/master/README.md

Import these utilities to use the ZIO integration with Magnum.

```scala
import com.augustnagro.magnum.magzio.*
```

--------------------------------

### Custom Repository Implementation

Source: https://github.com/augustnagro/magnum/blob/master/README.md

Shows how to define a custom repository class by extending `Repo`. This is a best practice for organizing SQL queries related to a specific entity.

```scala
class UserRepo extends Repo[User, User, Long]
```

--------------------------------

### Create Database Connection

Source: https://github.com/augustnagro/magnum/blob/master/README.md

Use the `connect` function with a Transactor to obtain a `DbCon` for executing SQL queries. Requires importing Magnum's core functions.

```scala
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()
```

--------------------------------

### ZIO Integration with TransactorZIO

Source: https://context7.com/augustnagro/magnum/llms.txt

Shows how to use `TransactorZIO` for ZIO applications, providing the transactor via `ZLayer`. Repository methods remain synchronous, with ZIO managing execution context.

```scala
import com.augustnagro.magnum.*
import com.augustnagro.magnum.magzio.*
import zio.*

val transactorLayer: URLayer[DataSource, TransactorZIO] =
  TransactorZIO.layer(SqlLogger.logSlowQueries(200.milliseconds))

// Repository methods stay synchronous; ZIO manages the thread
def fetchUser(id: Long): RIO[TransactorZIO, Option[User]] = 
  ZIO.serviceWithZIO[TransactorZIO](_.connect:
    sql"SELECT * FROM app_user WHERE id = $id".query[User].run().headOption
  )

def transferFunds(fromId: Long, toId: Long, amount: Long): RIO[TransactorZIO, Unit] = 
  ZIO.serviceWithZIO[TransactorZIO](_.transact:
    sql"UPDATE account SET balance = balance - $amount WHERE id = $fromId".update.run()
    sql"UPDATE account SET balance = balance + $amount WHERE id = $toId".update.run()
  )

val program: ZIO[TransactorZIO, Throwable, Unit] = 
  for
    user <- fetchUser(1L)
    _    <- ZIO.logInfo(s"Found user: $user")
    _    <- transferFunds(1L, 2L, 100L)
  yield ()

val runnable = program.provide(transactorLayer, dataSourceLayer)
```

--------------------------------

### Create Transactor with HikariCP

Source: https://context7.com/augustnagro/magnum/llms.txt

Initialize a HikariCP DataSource and then create a Transactor instance. Customizations for logging and transaction isolation can be applied.

```scala
import com.augustnagro.magnum.*
import com.zaxxer.hikari.{HikariConfig, HikariDataSource}
import java.sql.Connection

// Build a HikariCP pool
val hikariConfig = HikariConfig()
hikariConfig.setJdbcUrl("jdbc:postgresql://localhost:5432/mydb")
hikariConfig.setUsername("postgres")
hikariConfig.setPassword("secret")
val dataSource = HikariDataSource(hikariConfig)

// Plain transactor
val xa = Transactor(dataSource)

// Transactor with slow-query logging and custom isolation
val xaFull = Transactor(
  dataSource       = dataSource,
  sqlLogger        = SqlLogger.logSlowQueries(500.milliseconds),
  connectionConfig = _.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ)
)
```

--------------------------------

### Add Magnum to build.sbt

Source: https://context7.com/augustnagro/magnum/llms.txt

Include Magnum core, optional modules (Postgres, ZIO), and necessary JDBC drivers and connection pool libraries in your build.sbt file.

```scala
"com.augustnagro" %% "magnum" % "1.3.0"

// Postgres extensions (arrays, geometric types)
"com.augustnagro" %% "magnumpg" % "1.3.0"

// ZIO integration
"com.augustnagro" %% "magnumzio" % "1.3.0"

// JDBC driver (example for Postgres)
"org.postgresql" % "postgresql" % "42.7.4"

// Connection pool (recommended)
"com.zaxxer" % "HikariCP" % "5.1.0"
```

--------------------------------

### Standard Repository Methods

Source: https://github.com/augustnagro/magnum/blob/master/README.md

The `Repo` class provides a comprehensive set of auto-generated CRUD methods, including insert, update, and delete operations. It extends `ImmutableRepo`.

```scala
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
```

--------------------------------

### Using the `sql` Interpolator for Queries, Updates, and Returning Clauses

Source: https://context7.com/augustnagro/magnum/llms.txt

Safely build SQL queries, updates, and returning clauses using the `sql` interpolator. Parameters are bound as prepared-statement parameters to prevent SQL injection. Frags can be composed.

```scala
import com.augustnagro.magnum.*
import java.time.OffsetDateTime

// Parameters bound safely via PreparedStatement
val minAge   = 18
val nameFilter = Some("Alice")
val since    = OffsetDateTime.now.minusDays(7)

// Build a Frag
val baseFrag: Frag =
  sql"""
    SELECT id, first_name, last_name
    FROM   app_user
    WHERE  age >= $minAge
    AND    created_at >= $since
  """

// Compose frags
val withName: Frag =
  nameFilter.fold(baseFrag)(n => baseFrag ++ sql" AND first_name = $n")

// Run as a Query
val users: Vector[(Long, Option[String], String)] =
  connect(xa):
    withName.query[(Long, Option[String], String)].run()

// Run as an Update
val rowsAffected: Int =
  transact(xa):
    sql"UPDATE app_user SET active = true WHERE id = $42".update.run()

// RETURNING clause (Postgres / H2 / Oracle)
val newId: Long =
  transact(xa):
    sql"""
      INSERT INTO app_user (first_name, last_name)
      VALUES ('Bob', 'Smith')
      RETURNING id
    ".returning[Long].run()
// newId: Long = 7
```

--------------------------------

### Execute Queries, Updates, and Returning Statements

Source: https://github.com/augustnagro/magnum/blob/master/README.md

Run `Query`, `Update`, or `Returning` statements within a transaction using `transact(xa)` and the `.run()` method. Results are returned based on the statement type.

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

--------------------------------

### Execute Read-Only Queries with connect

Source: https://context7.com/augustnagro/magnum/llms.txt

Use the `connect` function to open a single connection, execute read-only operations or non-transactional writes, and return the result. The connection is closed automatically.

```scala
import com.augustnagro.magnum.*

case class User(id: Long, firstName: Option[String], lastName: String)
  derives DbCodec

val xa = Transactor(dataSource)

// Returns Vector[User]
val users: Vector[User] = connect(xa):
  sql"SELECT id, first_name, last_name FROM app_user".query[User].run()

// Single column projection
val lastNames: Vector[String] = connect(xa):
  sql"SELECT last_name FROM app_user ORDER BY last_name".query[String].run()
```

--------------------------------

### Add ZIO Integration Dependency

Source: https://github.com/augustnagro/magnum/blob/master/README.md

Include this dependency to use Magnum with ZIO, providing ZIO effect implementations for `connect` and `transact`.

```scala
"com.augustnagro" %% "magnumzio" % "x.x.x"
```

--------------------------------

### Create Update with RETURNING Clause

Source: https://github.com/augustnagro/magnum/blob/master/README.md

Construct an `Update` statement with a `RETURNING` clause using the `.returning[T]` method to retrieve generated values after an update.

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

--------------------------------

### Create a Specification for User Queries

Source: https://github.com/augustnagro/magnum/blob/master/README.md

Use the Spec class to build dynamic queries for paginated data, including filtering and sorting. Supports seek and offset pagination.

```scala
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)
```

--------------------------------

### Postgres Arrays and Geometric Types

Source: https://context7.com/augustnagro/magnum/llms.txt

Demonstrates using Magnum's Postgres module to handle array columns and geometric types. Requires importing `PgCodec.given` and defining case classes with `derives DbCodec`.

```scala
import com.augustnagro.magnum.*
import com.augustnagro.magnum.pg.PgCodec.given     // array + geometric codecs
import org.postgresql.geometric.PGpoint

// Table with a point-array column
// SQL: CREATE TABLE geo_fence (id bigint primary key, vertices point[] not null);
@Table(PostgresDbType)
case class GeoFence(@Id id: Long, vertices: Vector[PGpoint]) derives DbCodec

val repo = Repo[GeoFence, GeoFence, Long]

transact(xa):
  repo.insert(
    GeoFence(1L, Vector(PGpoint(0, 0), PGpoint(1, 0), PGpoint(1, 1), PGpoint(0, 1)))
  )
  val fence: Option[GeoFence] = repo.findById(1L)
  // fence.get.vertices: Vector[PGpoint] with 4 points

// Arrays of Postgres enum type
// SQL: CREATE TYPE mood AS ENUM ('Happy', 'Sad', 'Neutral');
import com.augustnagro.magnum.pg.enums.PgEnumToScalaEnumSqlArrayCodec

enum Mood derives DbCodec:
  case Happy, Sad, Neutral

@Table(PostgresDbType)
case class Survey(@Id id: Long, moods: Vector[Mood]) derives DbCodec
```

--------------------------------

### Read-Write Repository with `Repo`

Source: https://context7.com/augustnagro/magnum/llms.txt

Extend `ImmutableRepo` with full CRUD operations including `insert`, `insertReturning`, `update`, `delete`, and their batch variants. The `EC` type parameter omits database-generated columns.

```scala
import com.augustnagro.magnum.*
import java.time.OffsetDateTime

// EC: omits db-generated fields (id, created_at)
case class NewProduct(name: String, priceUsd: BigDecimal) derives DbCodec

@Table(PostgresDbType, SqlNameMapper.CamelToSnakeCase)
case class Product(
  @Id id: Long,
  name: String,
  priceUsd: BigDecimal,
  createdAt: OffsetDateTime
) derives DbCodec

val repo = Repo[NewProduct, Product, Long]

transact(xa):
  // Insert and retrieve the full entity with generated fields populated
  val product: Product = repo.insertReturning(NewProduct("Widget", BigDecimal("4.99")))
  // product.id and product.createdAt are set by the database

  // Bulk insert without returning
  repo.insertAll(List(NewProduct("Gadget", BigDecimal("12.99")), NewProduct("Gizmo", BigDecimal("7.49"))))

  // Update by full entity
  repo.update(product.copy(priceUsd = BigDecimal("5.49")))

  // Delete
  repo.deleteById(product.id)

  // Batch delete
  val result: BatchUpdateResult = repo.deleteAllById(List(10L, 11L, 12L))
```

--------------------------------

### Define Future-Proof Queries with TableInfo

Source: https://github.com/augustnagro/magnum/blob/master/README.md

Use TableInfo to write SQL queries that are resilient to schema changes. This avoids manual find-and-replace and reduces runtime errors.

```scala
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()
```

```scala
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()
```

--------------------------------

### Add PostgreSQL JDBC Driver

Source: https://github.com/augustnagro/magnum/blob/master/README.md

Add the JDBC driver for your specific database, such as PostgreSQL, to your project dependencies.

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

--------------------------------

### Perform ACID Transactions with transact

Source: https://context7.com/augustnagro/magnum/llms.txt

Use the `transact` function to manage ACID transactions. It opens a connection, disables auto-commit, executes the provided function, commits on success, and rolls back on any exception.

```scala
import com.augustnagro.magnum.*

val xa = Transactor(dataSource)

// All three operations commit atomically, or all roll back
val newBalance: Long = transact(xa):
  sql"UPDATE account SET balance = balance - 100 WHERE id = $1".update.run()
  sql"UPDATE account SET balance = balance + 100 WHERE id = $2".update.run()
  sql"SELECT balance FROM account WHERE id = $2".query[Long].run().head
```

--------------------------------

### Repository with Auto-Generated Columns

Source: https://github.com/augustnagro/magnum/blob/master/README.md

Demonstrates using `Repo` with an `EntityCreator` for entities that have auto-generated fields, such as primary keys. The `UserCreator` class omits the `id` field, which is auto-generated.

```scala
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")
  )
```

--------------------------------

### Efficient Bulk Writes with `batchUpdate`

Source: https://context7.com/augustnagro/magnum/llms.txt

Utilize JDBC's `addBatch` and `executeBatch` for high-throughput bulk inserts and updates. Handles results indicating success with row counts or success without available counts.

```scala
import com.augustnagro.magnum.*

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

val newUsers = List(
  UserCreator("Alice", "Smith", 30),
  UserCreator("Bob",   "Jones", 25),
  UserCreator("Carol", "Brown", 35)
)

val result: BatchUpdateResult =
  transact(xa):
    batchUpdate(newUsers): u =>
      sql"""
        INSERT INTO app_user (first_name, last_name, age)
        VALUES (${u.firstName}, ${u.lastName}, ${u.age})
      ".update

result match
  case BatchUpdateResult.Success(count) => println(s"Inserted $count rows")
  case BatchUpdateResult.SuccessNoInfo  => println("Inserted (count unavailable)")
```

--------------------------------

### Type-Safe Context Management

Source: https://github.com/augustnagro/magnum/blob/master/README.md

Annotate methods with `using DbTx` for transactional context or `using DbCon` for connection context. This ensures type safety, preventing incorrect context usage.

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

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

--------------------------------

### Add Magnum Dependency

Source: https://github.com/augustnagro/magnum/blob/master/README.md

Include this dependency in your build.sbt file to use Magnum. Ensure your Scala version is 3.3.0 or higher.

```scala
"com.augustnagro" %% "magnum" % "1.3.0"
```

--------------------------------

### Perform Batch Updates with Magnum

Source: https://github.com/augustnagro/magnum/blob/master/README.md

Use the `batchUpdate` method from `com.augustnagro.magnum` for efficient batch updates. It returns a `BatchUpdateResult` indicating success or the number of rows updated.

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

--------------------------------

### Create Query from Fragment

Source: https://github.com/augustnagro/magnum/blob/master/README.md

Convert a `Frag` SQL fragment into a `Query` object using the `query[T]` method, specifying the expected return type with a `DbCodec`.

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

--------------------------------

### Read-Only Repository with `ImmutableRepo`

Source: https://context7.com/augustnagro/magnum/llms.txt

Implement compile-time generated read-only repository methods like `count`, `existsById`, `findAll`, `findById`, and `findAllById`. Extend to co-locate custom queries.

```scala
import com.augustnagro.magnum.*
import java.time.OffsetDateTime

@Table(PostgresDbType, SqlNameMapper.CamelToSnakeCase)
case class Product(
  @Id id: Long,
  name: String,
  priceUsd: BigDecimal,
  createdAt: OffsetDateTime
) derives DbCodec

class ProductRepo extends ImmutableRepo[Product, Long]:
  def findCheaperThan(maxPrice: BigDecimal)(using DbCon): Vector[Product] =
    sql"SELECT * FROM product WHERE price_usd < $maxPrice ORDER BY price_usd"
      .query[Product].run()

val productRepo = ProductRepo()

connect(xa):
  val total: Long            = productRepo.count            // SELECT count(*) FROM product
  val exists: Boolean        = productRepo.existsById(42L)  // SELECT ... WHERE id = 42
  val one: Option[Product]   = productRepo.findById(1L)
  val all: Vector[Product]   = productRepo.findAll()
  val cheap: Vector[Product] = productRepo.findCheaperThan(BigDecimal("9.99"))
```

--------------------------------

### Dynamic Filtered Queries with Spec

Source: https://context7.com/augustnagro/magnum/llms.txt

Use Spec to build dynamic WHERE, ORDER BY, LIMIT, and OFFSET clauses. Supports cursor pagination with seek() for high performance on large tables.

```scala
import com.augustnagro.magnum.*

val nameFilter  = Some("Ali%")
val minAge      = 18
val pageSize    = 20
val lastSeenId  = 100L

val spec = Spec[User]
  .where(sql"active = true")
  .where(nameFilter.map(n => sql"first_name ILIKE $n").getOrElse(sql"" ))
  .where(sql"age >= $minAge")
  // Seek (cursor) pagination — faster than OFFSET for large tables
  .seek("id", SeekDir.Gt, lastSeenId, SortOrder.Asc)
  .limit(pageSize)

val page: Vector[User] = connect(xa):
  userRepo.findAll(spec)

// Offset pagination alternative
val offsetSpec = Spec[User]
  .orderBy("last_name", SortOrder.Asc)
  .limit(pageSize)
  .offset(40L)
```

--------------------------------

### Create Update Statement

Source: https://github.com/augustnagro/magnum/blob/master/README.md

Generate an `Update` SQL statement from a `Frag` using the `.update` method. This is used for modifying data in the database.

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

--------------------------------

### SQL Interpolator for Fragments

Source: https://github.com/augustnagro/magnum/blob/master/README.md

Use the `sql` interpolator to create `Frag` SQL fragments, safely interpolating values to prevent SQL injection. Supports options and date/time types.

```scala
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
    """
```

--------------------------------

### Immutable Repository Methods

Source: https://github.com/augustnagro/magnum/blob/master/README.md

The `ImmutableRepo` class auto-generates common data access methods at compile-time. These methods are suitable for entities that are not modified after creation.

```scala
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]
```

--------------------------------

### Add Postgres Module Dependency

Source: https://github.com/augustnagro/magnum/blob/master/README.md

Include this dependency to add support for Postgres Geometric Types and Arrays.

```scala
"com.augustnagro" %% "magnumpg" % "1.3.0"
```

--------------------------------

### Execute Database Transaction

Source: https://github.com/augustnagro/magnum/blob/master/README.md

Use the `transact` function with a Transactor to execute operations within a database transaction. If the context function throws an exception, the transaction is rolled back.

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

--------------------------------

### UUID DbCodec for Databases with Native Support

Source: https://github.com/augustnagro/magnum/blob/master/README.md

When using databases that natively support the UUID type (e.g., Postgres, Clickhouse, H2), the built-in `DbCodec[UUID]` works as expected.

```scala
import com.augustnagro.magnum.*
import java.util.UUID

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

--------------------------------

### Custom Type Mappings with DbCodec

Source: https://context7.com/augustnagro/magnum/llms.txt

DbCodec controls Scala type to JDBC mapping. Derive automatically for case classes/enums or create custom instances using biMap. Handles opaque types and custom storage formats like UUID as VARCHAR.

```scala
import com.augustnagro.magnum.*
import java.util.UUID

// Opaque type with a custom codec via biMap
opaque type UserId = Long
object UserId:
  def apply(l: Long): UserId = { require(l > 0); l }
  extension (id: UserId) def value: Long = id
  given DbCodec[UserId] = DbCodec[Long].biMap(UserId.apply, _.value)

// UUID stored as VARCHAR in MySQL (no native UUID type)
import com.augustnagro.magnum.UUIDCodec.VarCharUUIDCodec

@Table(MySqlDbType)
case class Session(@Id id: Long, token: UUID) derives DbCodec

// Scala 3 enum — stored as String column by default
enum Status derives DbCodec:
  case Active, Suspended, Deleted

@Table(PostgresDbType, SqlNameMapper.CamelToSnakeCase)
case class User(
  @Id id: UserId,
  email: String,
  status: Status
) derives DbCodec

transact(xa):
  sql"UPDATE app_user SET status = ${Status.Suspended} WHERE id = ${UserId(7L)}"
    .update.run()
```

--------------------------------

### Read and Write JDBC Data with DbCodec

Source: https://github.com/augustnagro/magnum/blob/master/README.md

DbCodec is a typeclass for reading and writing JDBC data. Built-in codecs are provided for common types, and custom codecs can be defined.

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

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

--------------------------------

### UUID DbCodec for Databases without Native Support

Source: https://github.com/augustnagro/magnum/blob/master/README.md

For databases like MySql, Oracle, and Sqlite that do not natively support UUID columns, import `VarCharUUIDCodec` from `com.augustnagro.magnum.UUIDCodec` to store UUIDs as `varchar(36)`.

```scala
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
```

--------------------------------

### Splice Literal Values into SQL Fragments

Source: https://github.com/augustnagro/magnum/blob/master/README.md

Interpolate SqlLiteral values to splice Strings directly into SQL statements. Use sparingly and never with untrusted input.

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

--------------------------------

### Injecting Literal SQL Fragments with SqlLiteral

Source: https://context7.com/augustnagro/magnum/llms.txt

SqlLiteral wraps a trusted String to be spliced verbatim into a sql"" expression. Use only with static, trusted input to prevent SQL injection.

```scala
import com.augustnagro.magnum.*

def findInTable(tableName: String, id: Long)(using DbCon): Option[String] =
  val tbl = SqlLiteral(tableName)   // must be a trusted, static value
  sql"SELECT name FROM $tbl WHERE id = $id".query[String].run().headOption

connect(xa):
  findInTable("product", 1L)   // SELECT name FROM product WHERE id = ?
```

--------------------------------

### Customizing Transactor Behavior

Source: https://github.com/augustnagro/magnum/blob/master/README.md

Configure `Transactor` with custom `sqlLogger` or `connectionConfig` to modify logging behavior or set specific connection properties like transaction isolation level.

```scala
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()
```

--------------------------------

### Map Postgres Varchar/Text Array to Scala Enum

Source: https://github.com/augustnagro/magnum/blob/master/README.md

Use `PgStringToScalaEnumSqlArrayCodec` when your Postgres type is an array of varchar or text, and you want to map it to a Scala enum.

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

--------------------------------

### Type-Safe DbCon and DbTx Contexts

Source: https://context7.com/augustnagro/magnum/llms.txt

Leverage compiler-enforced type safety for database contexts. Methods requiring a transaction context (`DbTx`) cannot be called from a read-only context (`DbCon`), preventing runtime errors.

```scala
import com.augustnagro.magnum.*

// Safe: DbTx satisfies using DbCon
def getBalance(id: Long)(using DbCon): Long =
  sql"SELECT balance FROM account WHERE id = $id".query[Long].run().head

def transfer(fromId: Long, toId: Long, amount: Long)(using DbTx): Long =
  sql"UPDATE account SET balance = balance - $amount WHERE id = $fromId".update.run()
  sql"UPDATE account SET balance = balance + $amount WHERE id = $toId".update.run()
  getBalance(toId)   // compiles: DbTx <: DbCon

// Compile error: cannot call transfer (needs DbTx) from connect (gives DbCon)
// def badCall(using DbCon): Long = transfer(1, 2, 50)  // ← won't compile

val result: Long = transact(xa):
  transfer(1L, 2L, 50L)
// result: Long = 150
```

--------------------------------

### Derive DbCodec for Postgres Enum Array

Source: https://github.com/augustnagro/magnum/blob/master/README.md

Map an array of Postgres enums to a sequence of Scala enums. Requires `PgEnumToScalaEnumSqlArrayCodec` import.

```scala
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
```

--------------------------------

### Refactoring-Safe Column References with TableInfo

Source: https://context7.com/augustnagro/magnum/llms.txt

TableInfo provides type-safe column references that are resolved at compile time. Renaming a Scala field will result in a compile error.

```scala
import com.augustnagro.magnum.*

case class OrderLine(quantity: Int, unitPrice: BigDecimal) derives DbCodec

@Table(PostgresDbType, SqlNameMapper.CamelToSnakeCase)
case class Order(@Id id: Long, customerId: Long, orderLine: String) derives DbCodec

object Order:
  // Do NOT add an explicit type annotation; structural refinement must be preserved
  val T = TableInfo[Order, Order, Long]

def ordersForCustomer(customerId: Long)(using DbCon): Vector[Order] =
  val o = Order.T
  // Generates: SELECT id, customer_id, order_line FROM order WHERE customer_id = ?
  sql"SELECT ${o.all} FROM $o WHERE ${o.customerId} = $customerId"
    .query[Order].run()

// Aliases for joins
val o = Order.T.alias("o")
val u = TableInfo[User, User, Long].alias("u")
val joined: Vector[(Order, String)] = connect(xa):
  sql"""
    SELECT ${o.all}, ${u.firstName}
    FROM $o
    JOIN $u ON ${u.id} = ${o.customerId}
  """.query[(Order, String)].run()
```

--------------------------------

### Scala 3 Enum and NewType Support with DbCodec

Source: https://github.com/augustnagro/magnum/blob/master/README.md

Magnum supports Scala 3 enums by default, mapping them to Strings. For NewTypes or Opaque Type Aliases, use DbCodec.biMap to provide custom DbCodec instances.

```scala
@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
```

```scala
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()
```

=== COMPLETE CONTENT === This response contains all available snippets from this library. No additional content exists. Do not make further requests.