Reference / SugarORM

# Give your data shape.

Declare each table as a typed schema, query it with checked names, guard writes with changesets, and derive migrations from the declaration.

| Contract | Value |
| --- | --- |
| Release | 0.15.0 |
| Needs | A Caramel application and its development database |
| Covers | Schemas, codecs, queries, preloads, changesets, migrations, branches |
| Proved by | `frappe check` and `frappe migrate` |

Learning it for the first time? The recipe [Create a resource](https://caramelize.dev/cookbook/0.23.0/create-a-resource/index.md) generates a model, a changeset and a migration. This page lists every rule and limit.

## Limits at a glance {#c-sugarorm-limits}

| Limit | Value |
| --- | --- |
| `primary: true` fields per schema | exactly 1 |
| `version: true` fields and `timestamps` per schema | at most 1 each |
| Check constraint name | 63 bytes |
| Index name | 63 bytes; a longer one is shortened |
| `numeric(P,S)` precision | 1,000 |
| Extra queries per `preload` | 1 |
| Migration name | a lowercase letter, then up to 62 lowercase letters, digits or underscores |
| Branch name | a lowercase letter, then up to 30 lowercase letters, digits or underscores |
| Statement, per connection | 30 s |
| Lock wait, per connection | 5 s |

## A model declares its table once {#c-sugarorm-schemas}

A model is a struct that extends `SugarORM::Schema` and declares its table once. The schema block defines the record type, its queries and its migrations. `frappe make resource` writes one for you.

[Create a resource →](https://caramelize.dev/cookbook/0.23.0/create-a-resource/index.md)

**app/models/book.cr · assumes an App::Author schema with has_many books : Book**

```crystal
module App
  struct Book < SugarORM::Schema
    schema "books" do
      field id : UUID, primary: true
      field title : String
      field isbn : String?, renamed_from: :code
      field copies : Int32 = 1
      field archived : Bool = false
      timestamps
      belongs_to author : Author
      index :isbn, unique: true
      check copies: 0..
      json :id, :title, :isbn, :copies, :archived
      drop_column :shelf
    end

    scope available { where(archived: false) }
    scope with_copies(count : Int32) { where("copies >= ?", count) }
  end
end
```

Fields take `String`, `Int32`, `Int64`, `UUID`, `Bool`, `Float64` or `Time`, or any type through `codec:`; add `?` for a nullable column. The key is `field id : UUID, primary: true`, a `uuid` column with the default `uuidv7()`, which needs PostgreSQL 18; an insert changeset mints the id when it is built, so `changeset.id` reads it before the insert. `field id : Int64, primary: true` is an identity column the database assigns, and `frappe make resource --int64` writes it.

| Condition | Outcome |
| --- | --- |
| An unsupported field type | fails compilation |
| A default that is not a literal (it becomes the column’s SQL default), or any default on a `Time` field | fails compilation |
| A schema with no `primary: true` field | fails compilation |
| A `primary: true` field that is nilable, not `UUID` or `Int64`, or has a default | fails compilation |
| The one `primary: true` field | is a non-nilable `UUID`, minted by the insert changeset, or `Int64`, stored as an identity column |
| A default on a `UUID` field | fails compilation |
| `param id` on an `Int64` key | fails compilation |
| `param id : UUID` given an id another row holds | adds `has already been taken` to `id` |
| An update changeset that changes the id | adds `cannot be changed` to `id` |
| `timestamps` | adds `created_at` and `updated_at` |
| `timestamps` declared twice, or a second `version: true` field | fails compilation |
| `json :id, :title` | writes a record as JSON with exactly those columns, in that order |
| A record that is written as JSON, such as an action result a JSON client may receive, whose schema has no `json` line | fails compilation, naming the schema |
| A `json` line that lists a name that is not a column (an association is not one), lists a name twice, or appears twice | fails compilation |
| An update that changes a field | also sets `updated_at` |
| A `has_many` or `has_one` whose foreign key the target lacks | fails compilation |
| `belongs_to` with an `on_delete:` other than `:cascade`, `:restrict` or `:set_null`, or with any other option | fails compilation |
| `on_delete: :set_null` on a reference that is not nilable, or on a key from a tenanted schema to a tenanted schema | fails compilation |
| A duplicate column | fails compilation |
| Two schemas that name one table | fails compilation |
| A reserved name such as `query` or `update` | fails compilation |
| A range check that is not an inclusive range of integer literals, an empty range such as `10..1`, a check declared twice, or a check name over 63 bytes | fails compilation |
| A record | is immutable: `book.with(title: "Dune Messiah")` returns a changed copy and never persists it |
| An insert or update that breaks a unique index or a CHECK the changeset did not map | raises `SugarORM::UniqueViolation` or `SugarORM::CheckViolation` |

- `belongs_to author : Author`: Adds the author_id column, uuid or bigint after Author’s key, its foreign key and its index. Author? makes it nullable.
- `has_many books : Book`: Reads the Book rows whose author_id points at this record.
- `has_one charter : Charter`: Reads the first matching row by primary key, or nil.
- `index :isbn, unique: true`: Creates index_books_on_isbn. Several columns join with _and_ in the name. A name over 63 bytes is cut to 54 and ends in an underscore and the FNV-1a hash of the whole name, as 8 hex digits.
- `check copies: 0..`: Adds the CHECK constraint check_books_copies. Range checks take Int32 and Int64 fields and an inclusive range of integer literals: 0.., ..10 or 1..10.
- `check :dates, "starts_at < ends_at"`: A named SQL expression, compared by name: rename it to change it.
- `renamed_from: :code`: Renames the old column instead of dropping it.
- `drop_column :shelf`: Records the decision to drop a column and its data.

`has_many` and `has_one` expect a foreign key named after the owner, such as `author_id`; pass `foreign_key: :column` when it differs.

## Codecs map other types to text {#c-sugarorm-codecs}

A field of any other type names a codec: `field name : Type, codec: Codec`. The codec maps the type to text. Rows read a codec column as text and writes bind the encoded text, so no value passes through a float.

| Codec method | Returns |
| --- | --- |
| `Codec.sql_type` | The column type, one of `numeric`, `numeric(P,S)`, `jsonb`, `text` or `date` |
| `Codec.encode(value)` | The text to store |
| `Codec.decode(text)` | Builds the value back |

`SugarORM::JSONB(T)` is a codec for any `JSON::Serializable` type, `JSON::Any`, or an Array or Hash of those. It stores the value in a `jsonb` column.

**app/models/invoice.cr**

```crystal
module App
  struct Snapshot
    include JSON::Serializable
    getter lines : Array(Hash(String, String))

    def initialize(@lines)
    end
  end

  struct Invoice < SugarORM::Schema
    schema "invoices" do
      field id : UUID, primary: true
      field snapshot : Snapshot, codec: SugarORM::JSONB(Snapshot)
      field notes : Snapshot?, codec: SugarORM::JSONB(Snapshot)
    end
  end
end
```

`SugarORM::DateCodec` stores a `SugarORM::Date` in a `date` column. A `Date` is a year, a month and a day, from year 1 to 9999, with no time of day and no zone, so a day written in one zone is the same day in another. `Date.new(2026, 10, 24)` and `Date.parse("2026-10-24")` raise ArgumentError for a day that does not exist, and `Date.parse?` returns nil. A `Date` compares with `<` and `==`, adds days with `plus_days`, and is a string in JSON.

**app/models/task.cr**

```crystal
module App
  struct Task < SugarORM::Schema
    schema "tasks" do
      field id : UUID, primary: true
      field title : String
      field due_on : SugarORM::Date, codec: SugarORM::DateCodec
      field done_on : SugarORM::Date?, codec: SugarORM::DateCodec
      json :id, :title, :due_on, :done_on
    end
  end
end
```

A numeric column keeps exact decimals. This codec holds the digits as text, so the application picks its own decimal type and checks the text in `decode`. An application that prefers `BigDecimal` requires `sugar_orm/decimal`, which links GMP, and writes `field rate : BigDecimal, codec: SugarORM::DecimalCodec(20, 8)`: the column is `numeric(20,8)`, the text written is plain digits with no exponent, `encode` raises ArgumentError for a value with more than 8 decimals or more than 12 digits before the point, and the codec declares `ORDERABLE = true`, so `where` takes a Range of it.

**app/models/rate.cr**

```crystal
module App
  record Rate, text : String

  module RateCodec
    def self.sql_type : String
      "numeric(20,8)"
    end

    def self.encode(rate : Rate) : String
      rate.text
    end

    def self.decode(text : String) : Rate
      raise ArgumentError.new("not a decimal: #{text}") unless text.matches?(/\A-?\d+(\.\d+)?\z/)
      Rate.new(text)
    end
  end

  struct Price < SugarORM::Schema
    schema "prices" do
      field id : UUID, primary: true
      field rate : Rate, codec: RateCodec
      field fee : Rate?, codec: RateCodec
    end
  end
end
```

| Condition | Outcome |
| --- | --- |
| A codec field with a default | fails compilation |
| A `sql_type` other than `numeric`, `numeric(P,S)` with S ≤ P ≤ 1000, `jsonb`, `text` or `date` | raises ArgumentError |
| A changeset `param` on a codec field | uses the field’s type, such as `Rate` |
| The number validators on a codec field | do not apply, because the stored value is text |
| `validate_inclusion` on a codec field | takes values of the field’s type |
| Raw SQL reading a codec column | selects it as text, as in `SELECT rate::text AS rate FROM prices` |
| `frappe db diff` on a codec field | derives the column from `sql_type` |
| Reading a `date` column that holds `infinity`, a BC date, or text that is not `YYYY-MM-DD` because the session’s `DateStyle` is not ISO | raises ArgumentError |
| A Range in `where` on a codec field whose codec does not declare `ORDERABLE = true` before the schema, or an Array on any codec field | fails compilation |
| Adding a required codec field to an existing table | halts the diff, because it has no default: add it nilable, backfill, then make it required |

In `where` a codec field takes a value or `nil`, never an Array. It takes a Range only when its codec declares `ORDERABLE = true`, written before the schema that names it, because the column then orders the way the values do; `SugarORM::DateCodec` and `SugarORM::DecimalCodec` do, and the hand-written numeric, `jsonb` and `text` codecs above do not. `order_by` takes every field. Managed builds link GMP, so a codec may decode to `BigDecimal` after `require "big"`.

**Inside an action · App::Task declares due_on and done_on as above**

```crystal
first = SugarORM::Date.new(2026, 10, 1)
last = SugarORM::Date.new(2026, 10, 31)
App::Task.query
  .where(due_on: first..last, done_on: nil)
  .order_by(:due_on)
  .to_a
App::Task.query.where(due_on: first...last).count
App::Task.query.where(due_on: first..).exists?
```

## Queries check names and types {#c-sugarorm-queries}

`Book.query` starts an immutable query, and each clause returns a new one. Keyword `where` accepts only the schema’s columns, with their types.

| where value | Matches |
| --- | --- |
| A value | Compares with `=` |
| An Array | Matches any element |
| A Range | Bounds a number, time or `ORDERABLE` codec column, such as a `SugarORM::Date` |
| The literal `nil` | Finds NULL in a nullable column, as `where_null(:isbn)` does |
| A string fragment | Binds one value to each `?`, in parentheses |
| `where_condition(SugarORM::Condition.new(sql, values))` | Adds a fragment built elsewhere with its values, in parentheses |

| Condition | Outcome |
| --- | --- |
| Keyword `where` with a name that is not a column, or a value of the wrong type | fails compilation |
| `where` with a value that may be nil, such as an optional contract field (it would match the NULL rows) | fails compilation; narrow it first (`if isbn = contract.isbn`) or use `where_null` |
| `where("copies >= ?", …)` with a number of values other than its `?` count | raises ArgumentError |
| `order_by` with a name that is not a field | fails compilation |
| `order_by` with a direction other than `:asc` or `:desc` | raises ArgumentError: `order_by direction must be :asc or :desc, not :sideways` |
| A negative `limit` or `offset` | raises ArgumentError |
| `find` and `first` when nothing matches | return nil |
| `find!` and `first!` when nothing matches | raise `SugarORM::NotFound` |
| `find(db, id)`, `first!(db)` and `create(db, …)` | run on the connection you pass, as the generated request spec does with Corretto’s |

**Inside an action · author is a loaded App::Author**

```crystal
books = App::Book.query
  .available
  .with_copies(2)
  .where(author_id: author.id, copies: 1..10)
  .order_by(:title, :desc)
  .limit(20)
books.to_a
App::Book.query.find(id)
App::Book.query.where(isbn: nil).first!
```

| Call | Result |
| --- | --- |
| `order_by(:title, :desc)` | Orders by a field, `:asc` or `:desc` |
| `offset`, `first`, `count`, `exists?`, `each`, `delete_all` | Also available on a query |
| `scope name { … }` | Becomes a chainable query method |
| `to_sql` | Shows the statement |

`lock` adds `FOR UPDATE`.

| Condition | Outcome |
| --- | --- |
| `lock` | adds `FOR UPDATE`: the rows the query returns stay locked until the transaction ends, and a competing transaction waits for them |
| A competing transaction that has waited 5 s (`lock_timeout`), or any statement that ran 30 s (`statement_timeout`) | fails with PostgreSQL’s error; [Crema reference](https://caramelize.dev/docs/0.23.0/crema/index.md) lists `CARAMEL_LOCK_TIMEOUT` and `CARAMEL_STATEMENT_TIMEOUT` |
| A locking query outside a transaction | raises `SugarORM::Error`, because the lock would end with the statement |
| `count`, `exists?` or `delete_all` on a locking query | raises ArgumentError |
| A tenanted model with `lock` | keeps its tenant scope |
| Preloaded associations under `lock` | are not locked |

Lock several rows in one order, as in `order_by(:id).lock`, so two transactions cannot wait on each other. A lock does not replace a permission check.

**Inside an action**

```crystal
SugarORM::Repo.transaction do
  book = App::Book.query.lock.find!(id)
  book.update!(copies: book.copies - 1)
end
```

## Queries join, aggregate and page {#c-sugarorm-reading}

A `belongs_to` parent narrows its children, a column totals as exact text, and a long table is read a batch or a page at a time.

**Inside an action · App::Book declares belongs_to author : Author**

```crystal
active = App::Author.query.where(archived: false)
books = App::Book.query.joins(:author, active).order_by_joined(:author, :name, :desc)
App::Book.query.min_text(:copies)
App::Book.query.avg_text(:copies)
App::Book.query.group_count(:author_id)
App::Book.query.each_batch(500) { |batch| batch.each { |book| puts book.title } }
first = App::Book.query.page(20)
second = App::Book.query.page(20, after: first.after)
```

| Condition | Outcome |
| --- | --- |
| `joins(:author)` or `joins(:author, query)` | keeps the books whose author the query matches, every author when none is given, with the bound tenant on both tables |
| `order_by_joined(:author, :name, :desc)` | orders by a column of the joined author |
| `joins` of a `has_many` or `has_one`, or `order_by_joined` with a field the parent lacks | fails compilation |
| The same association joined twice, `order_by_joined` before its `joins`, `lock` or `delete_all` on a query with joins, or a parent query with joins | raises ArgumentError; narrow with `where_in` to lock or delete |
| `min_text`, `max_text` and `avg_text` | return PostgreSQL’s text, or nil when no row has a value |
| `group_count(:author_id)` | returns each key as text with its row count, ordered by the key, NULL last |
| `group_count` after `order_by`, `limit` or `offset` | raises ArgumentError |
| `each_batch(size)` | yields arrays of `size` records in primary-key order, one statement per batch, and stops after a short one |
| `page(size, after:, direction:)` | returns `records` and `after`, the cursor for the next page, nil on the last; `direction: :desc` reads backwards |
| `each_batch` or `page` after `order_by`, `limit` or `offset`, `each_batch` after `lock`, a size below 1, or another direction | raises ArgumentError |

## Conflicts raise errors of their own {#c-sugarorm-conflicts}

A statement maps the codes an application answers, and keeps PostgreSQL’s error as the `cause`. Any other code raises PostgreSQL’s own error.

| SQLSTATE | Raises |
| --- | --- |
| 23503 | `SugarORM::ForeignKeyViolation`, with `table` and `constraint` |
| 23505 | `SugarORM::UniqueViolation` |
| 23514 | `SugarORM::CheckViolation` |
| 40001 | `SugarORM::SerializationFailure`, also when COMMIT fails with it |
| 40P01 | `SugarORM::DeadlockDetected` |

The last two are `SugarORM::TransactionConflict`: PostgreSQL rolled the transaction back, and running it again can succeed. `retries:` asks for that.

**Inside an action**

```crystal
SugarORM::Repo.transaction(retries: 2) do
  book = App::Book.query.lock.find!(id)
  book.update!(copies: book.copies - 1)
end
```

| Condition | Outcome |
| --- | --- |
| A `TransactionConflict` in a block given `retries: 2` | runs the block again on a fresh transaction, at most twice more, and raises the last conflict |
| `retries:` above 0 inside a transaction, or a negative number | raises ArgumentError: a savepoint cannot retry what its transaction already did |
| A block that sent an email or called an API | repeats it when it runs again; make the block safe to run twice |

## Associations load only when preloaded {#c-sugarorm-preload}

An association stays unloaded until its query preloads it.

**Inside an action · App::Author declares has_many books : Book**

```crystal
authors = App::Author.query.preload(:books).order_by(:name).to_a
authors.each { |loaded| puts "#{loaded.name}: #{loaded.books.size}" }
```

| Condition | Outcome |
| --- | --- |
| A `preload` | adds one query that loads the association for every record in the result, so a list never runs one query per row |
| A preloaded result | is a wrapper around each record that answers its fields and the association |
| An association that was not preloaded | fails compilation, not a hidden query: `frappe check` reports `N_PLUS_ONE` (`Association 'books' of App::Author was not preloaded.`) and its remediation names `.preload(:books)` |

## SugarORM.sql reads rows as named tuples {#c-sugarorm-sql}

For CTEs, window functions and aggregates, `SugarORM.sql` runs a query with `$1` binds and reads each row as a named tuple.

**Inside an action**

```crystal
totals = SugarORM.sql(<<-SQL, as: {author_id: Int64, total: Int64})
  SELECT author_id, count(*) AS total FROM books GROUP BY author_id
  SQL
totals.each { |row| puts "#{row[:author_id]}: #{row[:total]}" }
SugarORM.sql_exec("UPDATE books SET archived = true WHERE copies < $1", 1)
```

| Condition | Outcome |
| --- | --- |
| Result columns that differ from the `as:` keys, in order | raises `SugarORM::ShapeError` |
| `SugarORM.sql_exec` | returns the number of rows affected |
| `SugarORM.sql` and `SugarORM.sql_exec` | use the current connection, including an open transaction |
| Raw SQL on a tenanted model | is not tenant-scoped; add the tenant predicate yourself, or use `App::Book.query.lock` to lock a scoped row |

## Every write goes through a changeset {#c-sugarorm-changesets}

A changeset lists the fields a write may set with `param`, and its rules in `validate(cs)`.

**app/changesets/book.cr**

```crystal
module App
  class Book::Changeset < SugarORM::Changeset(App::Book)
    param title : String
    param isbn : String?
    param copies : Int32
    param author_id : UUID

    def validate(cs)
      cs.validate_presence(:title)
      cs.validate_length(:title, max: 200)
      cs.validate_greater_than(:copies, 0)
      cs.validate_format(:isbn, /\A[0-9-]+\z/, message: "needs only digits and dashes")
      cs.unique_constraint(:isbn)
    end
  end

  alias Book::CreateChangeset = Book::Changeset
  alias Book::UpdateChangeset = Book::Changeset
end
```

Before your rules run, SugarORM checks NOT NULL columns.

| Condition | Outcome |
| --- | --- |
| A `param` that names no schema field, or has an incompatible type | fails compilation |
| The primary key or a timestamp as a `param` | fails compilation |
| An insert that omits a NOT NULL column without a default | adds the error `is required` |
| A write that sets a NOT NULL column to nil | adds the error `is required` |
| Building the changeset | runs `validate(cs)` before any SQL |
| Every validator except `validate_length` | takes `message:` to replace its default |
| The number, length, format, inclusion and URL validators | skip values that did not change and nil values |
| A number validator on a column that is not numeric, or a string validator on one that is not a String | raises ArgumentError |
| `check_constraint(:name)` on an expression check without `on: :field` | raises ArgumentError |
| `cs.add_error(:title, "is checked out")` | adds your own error |

These are the validators and their default messages:

| Validator | Default message |
| --- | --- |
| `validate_required(:title, :isbn)` | is required. Checks the current value: nil or a blank string fails. |
| `validate_presence(:title)` | can't be blank. Checks a changed value only. |
| `validate_length(:title, min: 2, max: 200)` | should be at least 2 character(s), or should be at most 200 character(s). |
| `validate_greater_than(:copies, 0)` | must be greater than 0 |
| `validate_less_than(:copies, 100)` | must be less than 100 |
| `validate_format(:isbn, /\A[0-9-]+\z/)` | has invalid format |
| `validate_inclusion(:copies, in: [1, 2, 3])` | is invalid |
| `validate_url(:website)` | must be an absolute http or https URL. Caramel adds this validator. |
| `unique_constraint(:isbn)` | has already been taken. Maps a unique violation of the index that starts with isbn. |
| `check_constraint(:copies)` | must be at least 0, or must be at most N. Maps a violation of the field’s range check; an expression check takes on: :field and reads is invalid. |

`errors` maps each field name to its messages, and `"_base"` holds errors about the whole record.

Translated applications replace these messages through the catalogs’ `caramel.errors` section.

[Translate framework messages →](https://caramelize.dev/docs/0.23.0/internationalization/index.md)

## Saves return a changeset {#c-sugarorm-saving}

Each schema generates `create` and `update`. Their keywords are the changeset’s params.

**Inside an action · author is a loaded App::Author**

```crystal
changeset = App::Book.create(title: "Dune", author_id: author.id)
if changeset.saved?
  book = changeset.record
else
  errors = changeset.errors
end

book = App::Book.create!(title: "Emma", author_id: author.id)
book = book.update!(copies: 3)
book.delete

SugarORM::Repo.transaction do
  shelf = App::Book.create!(title: "Persuasion", author_id: author.id)
  shelf.update!(copies: 2)
end
```

| Condition | Outcome |
| --- | --- |
| `create` and `update` | use `CreateChangeset` and `UpdateChangeset` when the model defines them, as generated resources do; otherwise a default changeset that permits every field except the primary key and timestamps, and maps each unique index and range check |
| An unknown or mistyped keyword | fails compilation at the call |
| `create` and `update` | return the changeset: `saved?` says whether the write happened, `valid?` whether validation passed, and `record` holds the stored row |
| `create!` and `update!` | return the stored record or raise `SugarORM::Invalid` with the error messages |
| Updating a row that no longer exists | adds `Record no longer exists` under `_base` |
| An update with no changes | writes nothing and reads the row instead: a row gone meanwhile adds `Record no longer exists`, a versioned row changed meanwhile adds `Record changed since you loaded it` |
| `delete` on a row already gone | returns false |
| `SugarORM::Repo.transaction` | commits when its block returns and rolls back when it raises |
| A nested transaction | becomes a savepoint; `SugarORM::Repo.rollback` undoes the innermost block |
| A unique violation mapped by `unique_constraint` | leaves the transaction usable |

A changeset can say what to do when a row with the same key exists.

| Condition | Outcome |
| --- | --- |
| `upsert on: :tea_id, update: [:sold]` | makes the insert `INSERT … ON CONFLICT` on that unique index |
| An index the schema does not declare | fails compilation |
| A key or `update:` field that is not a param of the changeset | fails compilation |
| A tenanted schema | includes the tenant in the index |
| Defaults, validations and the tenant stamp | still apply |
| The `update:` fields the changeset writes | are set from the new row, along with `updated_at` and the version |
| No `update:` fields | leave the existing row as it is |
| `record` | is the stored row either way |
| Concurrent inserts of one key | leave one row |

**app/changesets/sale.cr · App::Sale declares index :tea_id, unique: true**

```crystal
module App
  class Sale::Record < SugarORM::Changeset(App::Sale)
    param tea_id : Int64
    param sold : Int32
    upsert on: :tea_id, update: [:sold]
  end
end
```

A version field stops two people from overwriting each other.

| Condition | Outcome |
| --- | --- |
| `field lock_version : Int32, version: true` that is nilable, not Int32 or Int64, or has a default | fails compilation |
| The version column | starts at 0; `frappe db diff` adds it as `integer NOT NULL DEFAULT 0` |
| Every changeset update | checks the version it loaded and adds one |
| A row that changed since you loaded it | writes nothing and adds `Record changed since you loaded it` under `_base`; `stale?` is true |
| A changeset with `param lock_version : Int32` given `lock_version:` | checks that version instead, so a form carries the version it was rendered from |
| An insert that names a version | raises ArgumentError |
| `upsert … update:` that names the version field | fails compilation |
| A delete | does not check the version |
| The update action below, given a stale form | answers 409 with the errors, by its own `render_errors` call |
| The update action below, given an `If-Match` header that names another version | answers 412, by its own `if_match?` check |
| The show action below | sets `self.etag`, so its JSON answer carries `ETag`; [Actions and responses](https://caramelize.dev/docs/0.23.0/actions-and-responses/index.md) lists the header rules |

**app/models/book.cr · schema excerpt**

```crystal
schema "books" do
  field id : UUID, primary: true
  field title : String
  field lock_version : Int32, version: true
  timestamps
end
```

**app/views/books/form.cr · beside the CSRF field**

```crystal
input type: "hidden", name: "_csrf", value: @csrf_token
input type: "hidden", name: "lock_version", value: @values["lock_version"]? || ""
```

The changeset takes the version as a param, and the update action declares `field lock_version : Int32` in its contract. The 409 and 412 below are this action’s code, not a framework default.

**app/changesets/book.cr · the version param**

```crystal
class Book::Changeset < SugarORM::Changeset(App::Book)
  param title : String
  param lock_version : Int32
end
```

**app/actions/books/update.cr**

```crystal
module App::Books
  struct Update < App::ApplicationAction
    contract do
      field id : UUID
      field title : String
      field lock_version : Int32
    end

    def handle(contract : Contract)
      book = App::Book.query.find(contract.id) || return not_found
      unless if_match?(book.lock_version)
        return render_errors({"_base" => [SugarORM::Wording.record_stale]}, 412)
      end
      changes = book.update(title: contract.title, lock_version: contract.lock_version)
      return render_errors(changes.errors, changes.stale? ? 409 : 422) unless changes.saved?
      changes.record
    end
  end
end
```

**app/actions/books/show.cr · inside handle**

```crystal
book = App::Book.query.find(contract.id) || return not_found
self.etag = book.lock_version
book
```

## Migrations derive from the schema {#c-sugarorm-migrations}

Change the schema first, then derive the SQL.

**Terminal · inside the application**

```bash
frappe db diff --name add_copies
```

**Expect:** Prints `Wrote PATH` for each migration file it derives under `db/migrations/`, and a `Note:` line for each CHECK it leaves in place. If the declared schema already matches, it prints `The development database already matches the declared schema; no migration was written.` A `--name` that is not a lowercase letter followed by up to 62 lowercase letters, digits or underscores is refused.

**Terminal · inside the application**

```bash
frappe migrate
```

**Expect:** Lints every pending migration, then prints `Applied N migrations.` and reports schema drift without changing anything; `The database matches the declared schema.` when there is none.

Review each file before you apply it. Adding `copies` with a default and a unique `isbn` index to an existing `books` table writes two files; the first is:

**db/migrations/20261002120000_add_copies.cr**

```crystal
# Derived from the declared schema. Once applied, a migration is immutable: change the schema and run frappe db diff again.
App::MIGRATIONS << SugarORM::Migration.new(20261002120000_i64, "add_copies", [
  <<-SQL,
    ALTER TABLE "books" ADD COLUMN "copies" integer NOT NULL DEFAULT 1
    SQL
])
```

| Condition | Outcome |
| --- | --- |
| `frappe db diff` | clones the development database into a scratch branch, applies pending migrations there, compares with the declared schema, writes the difference to `db/migrations/`, migrates the branch with the new files and compares again, removing them if anything still differs; the scratch branch is always dropped |
| An index on an existing table | goes in a second file, `…_add_copies_concurrently.cr`, as `CREATE UNIQUE INDEX CONCURRENTLY IF NOT EXISTS`; it builds without blocking writes and runs outside a transaction |
| A check on an existing table | splits in two: the transactional file adds it `NOT VALID`, which refuses new rows that break it, and the `_concurrently` file validates it |
| Existing rows that break the check | stop `frappe migrate`, which names the constraint; it stays `NOT VALID` until you fix or delete those rows and migrate again |
| A `check_…` constraint that no schema declares | is dropped |
| Every other CHECK | stays in place with a note |
| `frappe migrate` | lints every pending migration before it runs any statement, applies them, then reports schema drift without changing anything |
| An applied migration that changed | stops the next run |

`frappe make resource` renders its `create_` migration the same way a diff would. The linter checks these rules:

| Lint rule | What it refuses |
| --- | --- |
| `concurrent-index` | CREATE INDEX on an existing table blocks its writes. Use CONCURRENTLY in a migration of its own. |
| `concurrent-drop-index` | DROP INDEX without CONCURRENTLY locks the table against reads and writes while it waits for running queries. |
| `not-null-default` | ADD COLUMN … NOT NULL without a DEFAULT fails on a populated table. |
| `destructive-column` | DROP or RENAME COLUMN needs drop_column or renamed_from. Hand-written SQL needs a -- caramel:allow-drop or allow-rename table.column line. |
| `drop-table`, `truncate`, `rename-table` | DROP TABLE and TRUNCATE destroy rows; RENAME TO breaks the binary still serving the old name. |
| `alter-column-type` | ALTER COLUMN … TYPE rewrites the table under a lock that blocks reads and writes. `frappe db diff` annotates the text to date retype it checks first. |
| `set-not-null` | SET NOT NULL scans every row under that lock. Add a CHECK (col IS NOT NULL) NOT VALID, validate it, then SET NOT NULL. |
| `constraint-not-valid` | ADD CHECK or FOREIGN KEY without NOT VALID scans every row; ADD UNIQUE or PRIMARY KEY without USING INDEX builds the index under the lock. |
| `bulk-write` | UPDATE … SET and DELETE FROM an existing table hold row locks until the migration commits. Backfill from a job. |
| `hidden-ddl` | A DO block whose body breaks one of these rules. CREATE FUNCTION bodies are not read. |
| `mixed-concurrency` | CONCURRENTLY statements cannot share a migration with statements that need a transaction. |

A table created in the same migration is exempt from every rule but `destructive-column` and `mixed-concurrency`; so is an index it builds. Every rule but `mixed-concurrency` accepts the line `-- caramel:allow RULE SUBJECT` on its own line above the statement, where SUBJECT is the table, `table.column`, or the index for `concurrent-drop-index`. Each refusal names its rule, the statement and the remedy.

| Condition | Outcome |
| --- | --- |
| A NOT NULL column without a default on an existing table | halts the diff and prints its remediation |
| A column that no field declares | halts the diff |
| A type change | halts the diff, except a text column that the schema now declares `date` |
| A new NOT NULL constraint | halts the diff |
| A primary key change | is never derived |
| `frappe db diff --dev-override` | derives those changes anyway |
| `frappe migrate --dev-override` | turns lint violations into warnings |
| `frappe migrate --dev-override` outside `CARAMEL_ENV=development` | refuses (test and production always refuse) |

A text column that the schema now declares `date` is retyped instead of halting. The migration locks the table and counts the values that are not a calendar day written `YYYY-MM-DD` before it changes anything, so each environment checks its own rows.

| Condition | Outcome |
| --- | --- |
| A text column the schema now declares with `SugarORM::DateCodec` | derives a migration that checks every value, drops the column’s default, then runs `ALTER COLUMN … TYPE date USING "col"::date` |
| A value that is not `YYYY-MM-DD` or not a real day, such as `2026-02-30`, `10/11/2026`, `today` or an empty string | stops `frappe migrate` with the table and column, the number of rows and up to five distinct values; nothing changes |
| A NULL | passes |
| The retype | rewrites the table under an exclusive lock, and `frappe db diff` prints a note saying so |
| A `check_…` constraint on that column that no schema declares | is dropped first, as for any column |
| The check on a server older than PostgreSQL 16 | raises an error that says so |
| A `date` column declared as text | halts the diff, like any other type change |

## A branch is a full copy of the database {#c-sugarorm-branches}

A database branch is a full copy of the development database. Use one to try a migration or a data change without touching your development records.

**Terminal · inside the application**

```bash
frappe db branch create try_isbn
```

**Expect:** Prints the branch’s connection URL.

**Terminal · inside the application**

```bash
frappe dev --branch try_isbn
```

**Expect:** Serves the application against the branch; a branch that does not exist is refused.

**Terminal · inside the application**

```bash
frappe db branch list
frappe db branch delete try_isbn
```

**Expect:** `list` prints one branch name per line, or `APP has no database branches.` on stderr. `delete` prints `Deleted database branch try_isbn.`; a second run fails, because the branch is gone.

| Condition | Outcome |
| --- | --- |
| A branch name that is not a lowercase letter followed by up to 30 lowercase letters, digits or underscores | refuses: `branch name NAME must be a lowercase letter followed by up to 30 lowercase letters, digits or underscores` |
| While the copy is made | Latte closes other connections to the development database |
| `frappe dev --branch` with a branch that does not exist | refuses: `APP has no database branch NAME; create it with frappe db branch create NAME` |

Branches are for experiments. To keep a backup, use `frappe db dump` and `frappe db restore`.

[Back up the development database →](https://caramelize.dev/docs/0.23.0/local-environment/index.md)

Prove it

## Check the schema against the database {#c-sugarorm-test}

Run these in the development branch, with the database `frappe new` created.

**Terminal · inside the application**

```bash
frappe check
frappe migrate
```

**Passing:** `frappe check` exits 0, with no compile error and no `N_PLUS_ONE`, and `frappe migrate` ends with `The database matches the declared schema.`

[← Views](https://caramelize.dev/docs/0.23.0/views/index.md) · [Move work to the background →](https://caramelize.dev/docs/0.23.0/cold-brew/index.md)

Caramel 0.23.0 · https://caramelize.dev/docs/0.23.0/sugarorm/
