kemal-orm

name	kemal-orm
description	Object-Relational Mapping (ORM) with Crecto and SQLite in Kemal, following established project patterns.
license	MIT

Kemal ORM Integration (Crecto)

This skill provides expert guidance on using Crecto ORM with Kemal applications and SQLite, strictly following patterns from kemal-by-example/budget-management-orm.

Core Mandates

• Dependencies: Include kemal, crecto, and sqlite3 in shard.yml and require them in the main application file.

• Repository Setup: Centralize Crecto configuration in a config/repo.cr module:

  module MyProject
    module Repo
      extend Crecto::Repo
  
      config do |conf|
        conf.adapter = Crecto::Adapters::SQLite3
        conf.db = ENV["DATABASE_URL"]? || "sqlite3:./db/app.db"
      end
    end
  end
  

• Schema Initialization: Set up database schema tables with Repo.db_adapter.exec in a Schema.setup module:

  module MyProject
    module Schema
      extend self
  
      def setup
        Repo.db_adapter.exec <<-SQL
          CREATE TABLE IF NOT EXISTS budget_entries (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            title TEXT NOT NULL,
            kind TEXT NOT NULL,
            amount_cents INTEGER NOT NULL,
            notes TEXT NOT NULL DEFAULT '',
            created_at DATETIME,
            updated_at DATETIME
          );
        SQL
      end
    end
  end
  

• Model Definition: Subclass Crecto::Model, declare fields inside schema, and set validation rules:

  module MyProject
    class BudgetEntry < Crecto::Model
      schema "budget_entries" do
        field :title, String
        field :kind, String
        field :amount_cents, Int64
        field :notes, String
      end
  
      validate_required :title
      validate_inclusion :kind, %w[income expense]
    end
  end
  

• Repository Operations:

  • Querying all: query = Crecto::Repo::Query.new.order_by("id DESC"); Repo.all(BudgetEntry, query)
  • Finding by ID: Repo.get(BudgetEntry, id)
  • Creating: entry = BudgetEntry.new; entry.title = title; Repo.insert(entry)
  • Updating: entry.title = new_title; Repo.update(entry)
  • Deleting: Repo.delete(entry)

Patterns from Source Code

Repo Configuration (config/repo.cr)

require "crecto"

module BudgetManagementOrm
  module Repo
    extend Crecto::Repo

    config do |conf|
      conf.adapter = Crecto::Adapters::SQLite3
      conf.db = ENV["DATABASE_URL"]? || "sqlite3:./db/budget.db"
    end
  end
end

Schema Setup (config/schema.cr)

require "./repo"

module BudgetManagementOrm
  module Schema
    extend self

    def setup
      Repo.db_adapter.exec <<-SQL
        CREATE TABLE IF NOT EXISTS budget_entries (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          title TEXT NOT NULL,
          kind TEXT NOT NULL,
          amount_cents INTEGER NOT NULL,
          notes TEXT NOT NULL DEFAULT '',
          created_at DATETIME,
          updated_at DATETIME
        );
      SQL
    end
  end
end

Model with Validations & Domain Logic (models/budget_entry.cr)

require "../config/repo"

module BudgetManagementOrm
  class BudgetEntry < Crecto::Model
    schema "budget_entries" do
      field :title, String
      field :kind, String
      field :amount_cents, Int64
      field :notes, String
    end

    validate_required :title
    validate_inclusion :kind, %w[income expense]

    def income? : Bool
      kind == "income"
    end

    def expense? : Bool
      kind == "expense"
    end

    def self.all_ordered : Array(BudgetEntry)
      query = Crecto::Repo::Query.new.order_by("id DESC")
      Repo.all(BudgetEntry, query)
    end

    def self.find(id : Int64) : BudgetEntry?
      Repo.get(BudgetEntry, id)
    end

    def self.create(title : String, kind : String, notes : String, amount_cents : Int64)
      entry = BudgetEntry.new
      entry.title = title
      entry.kind = kind
      entry.notes = notes
      entry.amount_cents = amount_cents
      Repo.insert(entry)
    end

    def update_fields(title : String, kind : String, notes : String, amount_cents : Int64)
      self.title = title
      self.kind = kind
      self.notes = notes
      self.amount_cents = amount_cents
      Repo.update(self)
    end

    def destroy
      Repo.delete(self)
    end
  end
end

Route Integration with Crecto Models (routes/entries.cr)

get "/entries" do
  entries = BudgetManagementOrm::BudgetEntry.all_ordered
  render "src/views/entries/index.ecr", "src/views/layouts/application.ecr"
end

get "/entries/:id/edit" do |env|
  id = env.params.url["id"]?.try(&.to_i64?)
  entry = id ? BudgetManagementOrm::BudgetEntry.find(id) : nil

  if entry
    render "src/views/entries/edit.ecr", "src/views/layouts/application.ecr"
  else
    env.response.status = :not_found
    "Entry not found"
  end
end

post "/entries" do |env|
  title = env.params.body["title"]?.try(&.strip) || ""
  raw_kind = env.params.body["kind"]? || "expense"
  kind = raw_kind == "income" ? "income" : "expense"
  notes = env.params.body["notes"]?.try(&.strip) || ""
  cents = BudgetManagementOrm::Money.parse_cents(env.params.body["amount"]? || "")

  if !title.empty? && cents && cents > 0
    BudgetManagementOrm::BudgetEntry.create(title, kind, notes, cents)
  end

  env.redirect "/entries"
end

post "/entries/:id/delete" do |env|
  id = env.params.url["id"]?.try(&.to_i64?)
  entry = id ? BudgetManagementOrm::BudgetEntry.find(id) : nil
  entry.try(&.destroy)
  env.redirect "/entries"
end

Best Practices

• Centwise Currency Storage: Always store monetary values as integers (Int64 cents) to avoid floating-point rounding errors.
• Repository Pattern: Delegate database access to Crecto's Repo object rather than executing raw SQL strings directly in route handlers.
• Model Validation: Use validate_required, validate_format, and validate_inclusion inside model definitions for declarative data integrity.
• Initialization Order: Run Schema.setup in your main entrypoint file before calling Kemal.run.

When to Use

• When building Kemal applications that require ORM abstraction (Crecto) instead of raw SQL strings.
• When organizing complex domain models with validations and relations.
• When managing structured database queries with ordering, filtering, and transactions.