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

@projectColumns goes on the case class, beside @tableName. Query and getByKey then list that table's columns. Insert, update, and schema verify ignore it: a database column the case class does not name still fails verify. A sql"select *" you wrote is not rewritten. The driver still reads only the columns the row type names.

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

(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
  .provideLayer(DocsTransactor.layer)

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" > $1

sql"SELECT ${products.name}, ${products.price} FROM $products WHERE ${products.inStock} = ${true}".sql

SELECT "name", "price" FROM "core_concepts_products" WHERE "inStock" = $1

The interpolator handles each type differently:

fragment.sql is the Postgres inspection form ($1, $2). It is not the text a driver sends. See SQL Injection Prevention for the complete security model.

#Names and SQL are typed

Every name Saferis puts into SQL, and every piece of SQL it hands back, has its own type. Each reads as a String (interpolation, comparison, logging), but a plain String is none of them until its companion says so, and one cannot stand in for another:

SqlText(...), SqlFragment.text(...), and Placeholder.raw(...) are where a String becomes SQL you already trust. Names do not take that path. A TableName, ColumnName, IndexName, ConstraintName, or Alias is an Ident piece, and the dialect quotes it when the statement is rendered. A dot in a table name separates schema and table ("prd"."foo"). Placeholder.identifier(ColumnName(raw)) is the runtime hatch for a name that is not a literal.

{
  val index = IndexName("idx_products_sku")
  val sql   = summon[Dialect].createIndexSql(index, TableName("core_concepts_products"), Seq(ColumnName("sku")))
  val found = SqlState.parse("23505")
  (sql, found)
}

(create index if not exists "idx_products_sku" on "core_concepts_products" ("sku"),Some(23505))

#The session

SqlSession executes statements. On the JVM, PostgresJdbc.layer builds one from a DataSource. JdbcSession.layer is the same session with a JdbcAdapter you supply:

import saferis.*
import saferis.postgres.jdbc.PostgresJdbc
import zio.*
import javax.sql.DataSource

val dataSource: DataSource = ???

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

val session = ZLayer.succeed(dataSource) >>> PostgresJdbc.layer()

val result: ZIO[SqlSession, SaferisError, Chunk[User]] =
  sql"SELECT * FROM ${Table[User]}".query[User]

JdbcSessionConfig carries the session-wide statement timeout, a JDBC configure callback, and a vendor retry hook. Statement observation is SqlListener.observe, not a field on the driver config. A connection pool already queues callers. The session does not add a second semaphore.

Open a transaction with transact. Nested transact joins the outer transaction: one commit, one rollback. See Statement Timeouts for defaultTimeout.

Portable code does not continue after a statement error. Let the effect fail and transact rolls the transaction back. Postgres will not let you continue: the next command is Aborted with SQLSTATE 25P02, and COMMIT is rejected. MySQL, SQLite, and H2 will: a caught unique violation can be followed by another statement, and the commit persists.

Once COMMIT is sent, the session waits for the answer and an interrupt does not cut it off, because an abandoned commit leaves you not knowing whether it happened. The statement timeout does not cover COMMIT, so the bound on that wait is the socket. On Node the pool turns on TCP keepalive. On JDBC set your driver's socket timeout on the DataSource (pgjdbc socketTimeout, Connector/J socketTimeout) so a peer that vanished without a reset fails the commit instead of hanging it.