Models

Effect SQL's Model system provides a way to define domain models that automatically generate different schema variants for database operations and JSON APIs. This reduces boilerplate while ensuring type safety.

Why Models?

When working with databases, you often need different shapes of the same data:

• Select - Full row with all columns
• Insert - Row without auto-generated fields (id, created_at)
• Update - Partial row for updates
• JSON - API representation (may differ from database)

Without Models, you'd define each variant manually:

// Tedious and error-prone
const UserSelect = Schema.Struct({ id: Schema.Number, name: Schema.String, createdAt: Schema.Date })
const UserInsert = Schema.Struct({ name: Schema.String })
const UserUpdate = Schema.Struct({ id: Schema.Number, name: Schema.optional(Schema.String) })
const UserJson = Schema.Struct({ id: Schema.Number, name: Schema.String, createdAt: Schema.String })

With Models, you define once:

class User extends Model.Class<User>("User")({
  id: Model.Generated(Schema.Number),
  name: Schema.NonEmptyString,
  createdAt: Model.DateTimeInsertFromDate
}) {}

// All variants are automatically available:
User          // Select schema
User.insert   // Insert schema (no id, createdAt auto-set)
User.update   // Update schema (all fields optional except id)
User.json     // JSON API schema

Defining Models

Basic Model

import { Schema } from "effect"
import { Model } from "@effect/sql"

class User extends Model.Class<User>("User")({
  id: Model.Generated(Schema.Number),
  name: Schema.NonEmptyString,
  email: Schema.NonEmptyString,
  createdAt: Model.DateTimeInsertFromDate,
  updatedAt: Model.DateTimeUpdateFromDate
}) {}

Field Types

Model.Generated

For fields generated by the database (auto-increment IDs, etc.):

// Available in: select, update, json
// NOT available in: insert (generated by DB)
id: Model.Generated(Schema.Number)

Model.GeneratedByApp

For fields generated by your application (UUIDs, etc.):

// Available in: select, insert, update, json
// NOT available in: jsonCreate, jsonUpdate (generated by app)
id: Model.GeneratedByApp(Schema.UUID)

Model.Sensitive

For fields that shouldn't be exposed in JSON APIs:

// Available in: select, insert, update
// NOT available in: json, jsonCreate, jsonUpdate
passwordHash: Model.Sensitive(Schema.String)

Model.FieldOption

For optional/nullable fields:

// Nullable in database, optional in JSON
bio: Model.FieldOption(Schema.String)

DateTime Fields

Effect SQL provides special fields for timestamp columns:

// Inserted once, never updated
createdAt: Model.DateTimeInsertFromDate  // Date object to DB
createdAt: Model.DateTimeInsert          // String to DB
createdAt: Model.DateTimeInsertFromNumber // Epoch ms to DB

// Updated on every change
updatedAt: Model.DateTimeUpdateFromDate  // Date object to DB
updatedAt: Model.DateTimeUpdate          // String to DB
updatedAt: Model.DateTimeUpdateFromNumber // Epoch ms to DB

These automatically set the current time on insert/update.

JSON Fields

Store JSON data in text columns:

class Settings extends Model.Class<Settings>("Settings")({
  id: Model.Generated(Schema.Number),
  userId: Schema.Number,
  preferences: Model.JsonFromString(
    Schema.Struct({
      theme: Schema.Literal("light", "dark"),
      language: Schema.String
    })
  )
}) {}

// In database: {"theme":"dark","language":"en"} (string)
// In TypeScript: { theme: "dark", language: "en" } (object)

Using Models

With SqlSchema

import { SqlSchema } from "@effect/sql"

// Find one user
const findUserById = SqlSchema.findOne({
  Request: Schema.Number,
  Result: User,
  execute: (id) => sql`SELECT * FROM users WHERE id = ${id}`
})

// Find all users
const findAllUsers = SqlSchema.findAll({
  Request: Schema.Void,
  Result: User,
  execute: () => sql`SELECT * FROM users`
})

// Insert user
const insertUser = SqlSchema.single({
  Request: User.insert,
  Result: User,
  execute: (user) => sql`INSERT INTO users ${sql.insert(user)} RETURNING *`
})

With Repositories

Create a simple CRUD repository:

const UserRepo = yield* Model.makeRepository(User, {
  tableName: "users",
  spanPrefix: "UserRepo",
  idColumn: "id"
})

// Now you have:
const user = yield* UserRepo.insert({ name: "Alice", email: "alice@example.com" })
const found = yield* UserRepo.findById(1)  // Option<User>
const updated = yield* UserRepo.update({ id: 1, name: "Alice Smith" })
yield* UserRepo.delete(1)

With Data Loaders

Create batched data loaders for the N+1 problem:

const UserLoaders = yield* Model.makeDataLoaders(User, {
  tableName: "users",
  spanPrefix: "UserLoader",
  idColumn: "id",
  window: "50 millis",  // Batch window
  maxBatchSize: 100
})

// These automatically batch requests
const user1 = yield* UserLoaders.findById(1)
const user2 = yield* UserLoaders.findById(2)
// Only one SQL query: SELECT * FROM users WHERE id IN (1, 2)

Schema Variants

Select (Default)

The full row schema:

User  // Same as User schema
// { id: number, name: string, email: string, createdAt: Date, updatedAt: Date }

Insert

Fields required for insertion:

User.insert
// { name: string, email: string }
// (id, createdAt, updatedAt are auto-generated)

Update

Fields for updates (id required, others optional):

User.update
// { id: number, name?: string, email?: string }

JSON Variants

For API serialization:

User.json       // Full JSON representation
User.jsonCreate // For POST /users
User.jsonUpdate // For PATCH /users/:id

Custom Field Definitions

Create custom field types using Model.Field:

import { Model, Field } from "@effect/sql"

// Field available only in specific variants
const AdminOnly = <S extends Schema.Schema.Any>(schema: S) =>
  Model.Field({
    select: schema,
    insert: schema,
    update: schema
    // NOT in json variants
  })

class User extends Model.Class<User>("User")({
  id: Model.Generated(Schema.Number),
  name: Schema.NonEmptyString,
  isAdmin: AdminOnly(Schema.Boolean)  // Not exposed in JSON
}) {}

Branded IDs

Use branded types for type-safe IDs:

// Define branded ID types
const UserId = Schema.Number.pipe(Schema.brand("UserId"))
const PostId = Schema.Number.pipe(Schema.brand("PostId"))

class User extends Model.Class<User>("User")({
  id: Model.Generated(UserId),
  name: Schema.NonEmptyString
}) {}

class Post extends Model.Class<Post>("Post")({
  id: Model.Generated(PostId),
  userId: UserId,  // Type-safe reference
  title: Schema.String
}) {}

// Type error: Can't pass PostId where UserId expected
const post = { userId: postId }  // Error!

Overriding Generated Values

Use Model.Override to provide values for generated fields:

const user = yield* UserRepo.insert({
  name: "Alice",
  email: "alice@example.com",
  // Override auto-generated createdAt
  createdAt: Model.Override(new Date("2020-01-01"))
})

Examples

Complete User Model

import { Schema } from "effect"
import { Model } from "@effect/sql"

const UserId = Schema.Number.pipe(Schema.brand("UserId"))

class User extends Model.Class<User>("User")({
  // Auto-generated primary key
  id: Model.Generated(UserId),
  
  // Required fields
  name: Schema.NonEmptyString,
  email: Schema.NonEmptyString,
  
  // Optional field
  bio: Model.FieldOption(Schema.String),
  
  // Sensitive - not in JSON
  passwordHash: Model.Sensitive(Schema.String),
  
  // Timestamps
  createdAt: Model.DateTimeInsertFromDate,
  updatedAt: Model.DateTimeUpdateFromDate
}) {}

// Variants:
// User (select): { id, name, email, bio, passwordHash, createdAt, updatedAt }
// User.insert:   { name, email, bio?, passwordHash }
// User.update:   { id, name?, email?, bio?, passwordHash? }
// User.json:     { id, name, email, bio, createdAt, updatedAt } (no passwordHash)

JSON API Model

class Post extends Model.Class<Post>("Post")({
  id: Model.Generated(Schema.Number),
  title: Schema.NonEmptyString,
  content: Schema.String,
  
  // Store structured data as JSON
  metadata: Model.JsonFromString(
    Schema.Struct({
      tags: Schema.Array(Schema.String),
      readTime: Schema.Number
    })
  ),
  
  createdAt: Model.DateTimeInsertFromDate
}) {}

// JSON variants automatically handle serialization
const postJson = yield* Schema.encode(Post.json)(post)

Next Steps

• Data Loaders - Batching with models
• Repository Pattern - Building repositories
• SqlSchema - Type-safe queries