saferis · v0.19.1

Data Definition Layer (DDL)

The DDL layer provides type-safe schema management operations.

Creating Tables

xa.run(ddl.createTable[Customer](ifNotExists = true)).either
Right(0)

Other DDL Operations

import saferis.*

@tableName("ddl_customers")
case class Customer(
  @generated @key id: Long,
  name: String,
  email: String,
  status: String = "active",
  notes: Option[String]
) derives Table

// Drop table
ddl.dropTable[Customer](ifExists = true)

// Truncate table
ddl.truncateTable[Customer]()

// Add column
ddl.addColumn[Customer, String]("new_column")

// Drop column
ddl.dropColumn[Customer]("old_column")

// Drop index
ddl.dropIndex("idx_name", ifExists = true)

createTable Options

The createTable function accepts optional parameters:

import saferis.*

@tableName("ddl_my_table")
case class MyTable(@key id: Int, name: String) derives Table

// Default: creates table and indexes for compound primary keys
ddl.createTable[MyTable]()

// Skip table creation if it already exists
ddl.createTable[MyTable](ifNotExists = true)

// Create table without indexes (create them separately later)
ddl.createTable[MyTable](createIndexes = false)

Schema DSL for Indexes and Constraints

Use the Schema DSL to define indexes, unique constraints, and foreign keys with full DDL generation:

Schema[SchemaUser]
  .withIndex(_.name)
  .ddl()
  .sql
create table if not exists ddl_schema_users (id integer generated always as identity primary key not null, name varchar(255) not null, email varchar(255) not null, status varchar(255) not null);
create index "idx_ddl_schema_users_name" on "ddl_schema_users" ("name")
Schema[SchemaUser]
  .withUniqueIndex(_.email)
  .ddl()
  .sql
create table if not exists ddl_schema_users (id integer generated always as identity primary key not null, name varchar(255) not null, email varchar(255) not null, status varchar(255) not null);
create unique index "idx_ddl_schema_users_email" on "ddl_schema_users" ("email")
Schema[SchemaUser]
  .withIndex(_.name)
  .and(_.status)
  .named("idx_name_status")
  .ddl()
  .sql
create table if not exists ddl_schema_users (id integer generated always as identity primary key not null, name varchar(255) not null, email varchar(255) not null, status varchar(255) not null);
create index "idx_name_status" on "ddl_schema_users" ("name", "status")
Schema[SchemaUser]
  .withIndex(_.name)
  .where(_.status)
  .eql("active")
  .ddl()
  .sql
create table if not exists ddl_schema_users (id integer generated always as identity primary key not null, name varchar(255) not null, email varchar(255) not null, status varchar(255) not null);
create index "idx_ddl_schema_users_name" on "ddl_schema_users" ("name") where status = 'active'
Schema[SchemaUser]
  .withUniqueIndex(_.email)
  .where(_.status)
  .eql("active")
  .ddl()
  .sql
create table if not exists ddl_schema_users (id integer generated always as identity primary key not null, name varchar(255) not null, email varchar(255) not null, status varchar(255) not null);
create unique index "idx_ddl_schema_users_email" on "ddl_schema_users" ("email") where status = 'active'
Schema[SchemaUser]
  .withIndex(_.name)
  .withUniqueIndex(_.email)
  .ddl()
  .sql
create table if not exists ddl_schema_users (id integer generated always as identity primary key not null, name varchar(255) not null, email varchar(255) not null, status varchar(255) not null);
create index "idx_ddl_schema_users_name" on "ddl_schema_users" ("name");
create unique index "idx_ddl_schema_users_email" on "ddl_schema_users" ("email")
Schema[SchemaUser]
  .withUniqueConstraint(_.name)
  .and(_.status)
  .ddl()
  .sql
create table if not exists ddl_schema_users (id integer generated always as identity primary key not null, name varchar(255) not null, email varchar(255) not null, status varchar(255) not null, constraint uq_name_status unique (name, status))

Creating Tables with Schema

Use .build to get an Instance for ddl.createTable:

{
  // Build schema with indexes and create table
  val schemaUsers = Schema[SchemaUser]
    .withIndex(_.name)
    .withUniqueIndex(_.email)
    .build

  xa.run(ddl.createTable(schemaUsers)).either
}
Right(0)

Partial Indexes via Runtime API

Create partial indexes programmatically using ddl.createIndex:

xa.run(for
  _ <- ddl.createTable[Job](createIndexes = false)
  // Create a partial index for pending jobs with retry times
  _ <- ddl.createIndex[Job](
    "idx_pending_retry",
    Seq("retryat"),
    where = Some("status = 'pending'"),
  )
  _    <- dml.insert(Job(-1, "pending", Some(java.time.Instant.now())))
  _    <- dml.insert(Job(-1, "completed", None))
  jobs <- sql"SELECT * FROM ${Table[Job]}".query[Job]
yield jobs)
  .either
Right(Chunk(Job(1,pending,Some(2026-07-30T00:59:19.416471Z)),Job(2,completed,None)))