Core Concepts

#Table Definitions

Define tables using case classes with the Table typeclass:

import saferis.*

@tableName("core_concepts_products")
case class Product(
  @generated @key id: Long,      // Auto-generated primary key
  name: String,
  sku: String,
  price: Double,
  inStock: Boolean = true,        // Has default value
  description: Option[String]     // Nullable
) derives Table

Annotations

For indexes, unique constraints, and foreign keys, use the Schema DSL.

Automatic Column Properties

Saferis infers column properties from your Scala types:

Schema[PropertyProduct].ddl().sql

create table if not exists core_concepts_property_products (id bigint generated always as identity primary key not null, product_name varchar(255) not null, quantity integer not null default 0, price double precision not null, notes varchar(255))

Compound Primary Keys

Use multiple @key annotations to create a composite primary key:

Schema[OrderItem].ddl().sql

create table if not exists core_concepts_order_items (orderId bigint not null, productId bigint not null, quantity integer not null, primary key (orderId, productId));
create index if not exists idx_core_concepts_order_items_compound_key on core_concepts_order_items (orderId, productId)

xa
  .run(for
    _     <- ddl.createTable[OrderItem](ifNotExists = true)
    _     <- dml.insert(OrderItem(1, 100, 2))
    _     <- dml.insert(OrderItem(1, 101, 1))
    _     <- dml.insert(OrderItem(2, 100, 3))
    items <- sql"SELECT * FROM ${Table[OrderItem]}".query[OrderItem]
  yield items)
  .either

Right(Chunk(OrderItem(1,100,2),OrderItem(1,101,1),OrderItem(2,100,3)))

#SQL Interpolation

The sql"..." interpolator is Saferis's primary defense against SQL injection. It automatically distinguishes between different types of interpolated values:

{
  val minPrice = 10.0
  sql"SELECT * FROM $products WHERE ${products.price} > $minPrice".sql
}

SELECT * FROM core_concepts_products WHERE price > ?

h type differently:

| Interpol

SELECT name, price FROM core_concepts_products WHERE inStock = ?

The interpolator handles each type differently:

See SQL Injection Prevention for the complete security model.

#The Transactor

The Transactor wraps a ConnectionProvider and executes SQL operations:

import saferis.*
import zio.*
import javax.sql.DataSource

// Assuming you have a DataSource
val dataSource: DataSource = ???

@tableName("core_concepts_users")
case class User(@generated @key id: Int, name: String) derives Table

// From a ConnectionProvider
val provider = ConnectionProvider.FromDataSource(dataSource)
val xa = Transactor(provider, _ => (), None)

// Execute operations
val result = xa.run(
  sql"SELECT * FROM ${Table[User]}".query[User]
)

Concurrency Limiting

The Transactor.layer method accepts an optional maxConcurrency parameter that limits concurrent database operations using a ZIO Semaphore:

import saferis.*

// Default: no concurrency limit (recommended for connection pools)
val defaultLayer = Transactor.layer()

// With concurrency limit (for SQLite or direct JDBC without pooling)
val limitedLayer = Transactor.layer(maxConcurrency = 1L)

Transactor.layer also accepts an optional defaultTimeout that applies a JDBC statement timeout to every query run through the Transactor, see Statement Timeouts.

When to use maxConcurrency:

• SQLite or other embedded databases without connection pooling

• Direct JDBC connections without a pool

• When you need concurrency limits below pool size for backpressure

When NOT to use maxConcurrency:

• With HikariCP or similar connection pools. The pool handles queuing more efficiently and HikariCP specifically recommends letting threads wait on the pool rather than limiting concurrency externally. Using a semaphore with a pool creates double-queuing and adds overhead in high-contention scenarios.