Give your data shape.

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
      check copies: 0..
      drop_column :shelf
    end

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

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 : Int64, primary: true
      field snapshot : Snapshot, codec: SugarORM::JSONB(Snapshot)
      field notes : Snapshot?, codec: SugarORM::JSONB(Snapshot)
    end
  end
end

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 : Int64, primary: true
      field rate : Rate, codec: RateCodec
      field fee : Rate?, codec: RateCodec
    end
  end
end

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!

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

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

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)

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

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

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

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

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

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

module App::Books
  struct Update < App::ApplicationAction
    contract do
      field id : Int64
      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

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

frappe db diff --name add_copies

frappe migrate

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

frappe db branch create try_isbn

frappe dev --branch try_isbn

frappe db branch list
frappe db branch delete try_isbn