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.

Declare the schema

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.

module App
  struct Book < SugarORM::Schema
    schema "books" do
      field id : Int64, 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
      drop_column :shelf
    end

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

Fields take String, Int32, Int64, Bool, Float64 or Time; add ? for a nullable column. A default must be a literal, because it becomes the column’s SQL default; Time fields take none. The one primary: true field is a non-nilable Int64 without a default, stored as an identity column. timestamps adds created_at and updated_at; an update that changes a field also sets updated_at.

has_many and has_one expect a foreign key named after the owner, such as author_id; pass foreign_key: :column when it differs. Unsupported types, non-literal defaults, missing foreign keys, duplicate columns and reserved names such as query or update fail compilation at the declaration.

Records are immutable. book.with(title: "Dune Messiah") returns a changed copy and never persists it.

Query with checked names

Book.query starts an immutable query, and each clause returns a new one. Keyword where accepts only the schema’s columns, with their types. A value compares with =, an Array matches any element, a Range bounds a number or time column, and nil finds NULL in a nullable column. A string fragment binds one value to each ?.

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!

order_by takes a field and :asc or :desc. Queries also have offset, first, count, exists?, each and delete_all. find and first return nil when nothing matches; find! and first! raise SugarORM::NotFound. A scope becomes a chainable query method. to_sql shows the statement.

Preload associations

An association stays unloaded until its query preloads it. Each preload adds one query that loads the association for every record in the result, so a list never runs one query per row.

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

A preloaded result wraps each record and still answers its fields. Using an association that was not preloaded is a compile error, not a hidden query. frappe check reports it as N_PLUS_ONE: Association 'books' of App::Author was not preloaded. Its remediation names .preload(:books).

Write SQL by hand

For CTEs, window functions and aggregates, SugarORM.sql runs a query with $1 binds and reads each row as a named tuple. The result columns must equal the as: keys in order, or it raises SugarORM::ShapeError. SugarORM.sql_exec runs a statement and returns the number of rows affected. Both use the current connection, including an open transaction.

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)

Check every write with a changeset

A changeset lists the fields a write may set with param, and its rules in validate(cs). Each param must name a field of the schema with a compatible type. The primary key and timestamps cannot be params. Validations run when the changeset is built.

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

    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. An insert must supply every one that has no default, and no write may set one to nil. Each miss reads is required. These are the validators and their default messages:

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. cs.add_error(:title, "is checked out") adds your own error. 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.

Save through the model

Each schema generates create and update. They use CreateChangeset and UpdateChangeset when the model defines them, as generated resources do. Otherwise they use a default changeset that permits every field except the primary key and timestamps, and maps each unique index. Their keywords are the changeset’s params, so an unknown or mistyped keyword fails at the call.

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

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. delete returns false when the row was already gone.

SugarORM::Repo.transaction commits when its block returns and rolls back when it raises. A nested transaction becomes a savepoint, and SugarORM::Repo.rollback undoes the innermost block. A unique violation mapped by unique_constraint leaves the transaction usable.

Derive migrations

Change the schema first, then derive the SQL. frappe db diff clones the development database into a scratch branch and applies pending migrations there. It compares the result with the declared schema and writes the difference to db/migrations/. It then migrates the branch with the new files and compares again; if anything still differs, it removes them. The scratch branch is always dropped.

frappe db diff --name add_copies
frappe migrate

Review each file before you apply it. Adding copies with a default and a unique isbn index to an existing books table writes this migration:

# 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
])

The index goes in a second file, …_add_copies_concurrently.cr, as CREATE UNIQUE INDEX CONCURRENTLY IF NOT EXISTS. It builds without blocking writes, so it runs outside a transaction.

frappe migrate lints every pending migration before it runs any statement, applies them, then reports schema drift without changing anything. An applied migration must not change; a changed one stops the next run. frappe make resource renders its create_ migration the same way a diff would.

Tables created in the same migration are exempt from the first two rules. The diff also halts instead of writing risky SQL: a NOT NULL column without a default on an existing table, a column that no field declares, a type change or a new NOT NULL constraint. Each halt prints its remediation. Primary key changes are never derived.

In development, frappe db diff --dev-override derives those changes anyway, and frappe migrate --dev-override turns lint violations into warnings. The migrate override applies only when CARAMEL_ENV=development; test and production always refuse.

Try changes on a branch

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.

frappe db branch create try_isbn
frappe dev --branch try_isbn
frappe db branch list
frappe db branch delete try_isbn

create prints the branch’s connection URL. A branch name starts with 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 serves the application against the branch and refuses a branch that does not exist.

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