Data Definition Layer (DDL)

The DDL layer provides type-safe schema management operations.

Creating Tables

(ddl
  .createTable[Customer](ifNotExists = true))
  .either
  .provideLayer(DocsTransactor.layer)

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](ColumnName("new_column"))

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

// Drop index
ddl.dropIndex(IndexName("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(IndexName("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

  (ddl
    .createTable(schemaUsers))
    .either
    .provideLayer(DocsTransactor.layer)
}

Right(0)

Partial Indexes via Runtime API

Create partial indexes programmatically using ddl.createIndex:

(for
  _ <- ddl.createTable[Job](createIndexes = false)
  // Create a partial index for pending jobs with retry times
  _ <- ddl.createIndex[Job](
    IndexName("idx_pending_retry"),
    Seq(ColumnName("retryAt")),
    where = Some(SqlText("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
  .provideLayer(DocsTransactor.layer)

Right(Chunk(Job(1,pending,Some(2026-10-14T21:42:13.385040Z)),Job(2,completed,None)))

Data Definition Layer (DDL)

#Creating Tables

#Other DDL Operations

#createTable Options

#Schema DSL for Indexes and Constraints