Postgres ORM for Crystal Lang

Extending ActiveModel for attribute definitions, callbacks and validations

• Postgres ORM for Crystal Lang
  • Configuration
  • Primary Key
  • Table Name
  • Callbacks
  • Associations
    • belongs_to
    • has_one
    • has_many
    • Dependency
  • Changefeeds
  • Advisory Locks
  • Column Types
  • Validations
    • ensure_unique
  • Timestamps
  • Join
  • Installation
  • Usage
  • Testing
    • With Docker
    • Without Docker
  • Compiling
  • Benchmark
    • Results

Configuration

# Below is the list of settings exposed by PgORM and their defaults
PgORM::Database.configure do |settings|
  setting host : String = ENV["PG_HOST"]? || "localhost"
  setting port : Int32 = (ENV["PG_PORT"]? || 5432).to_i
  setting db : String = ENV["PG_DB"]? || ENV["PG_DATABASE"]? || "test"
  setting user : String = ENV["PG_USER"]? || "postgres"
  setting password : String = ENV["PG_PASSWORD"]? || ""
  setting query : String = ENV["PG_QUERY"]? || ""
  # Postgresql Advisory Lock wait time-out
  setting lock_timeout : Time::Span = (ENV["PG_LOCK_TIMEOUT"]? || 5).to_i.seconds

end

# OR

PgORM::Database.parse(ENV["PG_DATABASE_URL"])

Primary Key

attribute named id of type Int64 is generated by default if you don't provide one for your model. You can change the default settings via macro

default_primary_key(name, autogenerated = true, converter = nil)

class BaseModel < PgORM::Base
  default_primary_key my_pk : String, autogenerated: true
end

class Foo < BaseModel
 attribute name : String
 attribute foo : Hash(String, String)
 attribute baz : Array(Float64)
 ......
end

If your models have different primary key, you can specify it using the primary_key directive.

class Bar < PgORM::Base
  primary_key :uuid

  attribute uuid : UUID
end

for composite keys

class UserOrganisation < PgORM::Base
  primary_key :user_id, :org_id

  attribute user_id : UUID
  attribute org_id : UUID
end

Table Name

Table name is inferred from class name if none is provided. You can override this behavior via table macro

class Baz < PgORM::Base
  table "awsome_table"

  # id is the default primary key
  attribute id : Int32
  attribute name : String
  attribute about : String? = nil
end

Callbacks

Register callbacks for save, update, create and destroy by setting the corresponding before/after callback handler.

class ModelWithCallbacks < PgORM::Base
  attribute id : Int32
  attribute address : String
  attribute age : Int32 = 10

  before_create :set_address
  after_update :set_age

  before_destroy do
    self.name = "joe"
  end

  def set_address
    self.address = "23"
  end

  def set_age
    self.age = 30
  end
end

Associations

Set associations with belongs_to, has_one, and has_many.

Access children in parent by accessing the method correpsonding to the name.

Note: The has_many association requires the belongs_to association on the child.

class Parent < PgORM::Base
  attribute name : String
  has_many :children, class_name: Child
end

class Child < PgORM::Base
  attribute age : Int32
  belongs_to :parent
  has_many :pet
end

class Pet < PgORM::Base
  attribute name : String
  belongs_to :child
end

parent = Parent.new(name: "Phil")
parent.children.to_a.empty? # => true

child = Child.new(age: 99)
child.pets.to_a.empty? # => true

belongs_to

This will add the following methods:

Note: association below refers to the name parameter provided when defining this association, e.g belongs_to :child here child is the association name):

• association returns the associated object (or nil);
• association= assigns the associated object, assigning the foreign key;
• build_association builds the associated object, assigning the foreign key if the parent record is persisted, or delaying it to when the new record is saved;
• create_association creates the associated object, assigning the foreign key, granted that validation passed on the associated object;
• create_association! same as create_association but raises a PgORM::Error::RecordNotSaved exception when validation fails;
• reload_association to reload the associated object.

For example a Child class declares belongs_to :parent which will add:

• Child#parent (similar to Parent.find(parent_id))
• Child#parent=(parent) (similar to child.parent_id = parent.id)
• Child#build_parent (similar to child.parent = Parent.new)
• Child#create_parent (similar to child.parent = Parent.create)
• Child#create_parent! (similar to child.parent = Parent.create!)
• Child#reload_parent (force reload child.parent)

has_one

Declares a has one relationship.

This will add the following methods:

• association returns the associated object (or nil).
• association= assigns the associated object, assigning the association's foreign key, then saving the association; permanently deletes the previously associated object;
• reload_association to reload the associated object.

For example an Account class declares has_one :supplier which will add:

• Account#supplier (similar to Supplier.find_by(account_id: account.id))
• Account#supplier=(supplier) (similar to supplier.account_id = account.id)
• Account#build_supplier
• Account#create_supplier
• Account#create_supplier!
• Account#reload_supplier

has_many

Declares a has many relationship.

This will add method

• association returns Relation object of the associated object.

For example a Parent class declares has_many :children, class_name: Child which will add:

• Parent#children : Relation(Child) (similar to child.find(parent_id))

Dependency

dependent param in the association definition macros defines the fate of the association on model destruction. Refer to descriptions in specific association for more details.

Changefeeds

Access the changefeed (CRUD Events) of a table through the changes class method.

Defaults to watch for change events on a table if no id provided.

Returns a ChangeFeed instance which provides methods for event based or blocking iterator

• ChangeFeed#on expects a block to be passed, which will get invoked asynchronously when an event is received.
• ChangeFeed#each an Iterator, whose next call will block till an event is received.
  

Emits Change instance consisting of event : Event and value : T where T is the model.

Events:

• ChangeReceiver::Event::Deleted events yield the deleted model
• ChangeReceiver::Event::Created events yield the created model
• ChangeReceiver::Event::Updated events yield the updated model

class Game < PgORM::Base
  attribute type : String
  attribute score : Int32, default: 0
end

ballgame = Game.create!(type: "footy")

# Observe changes on a single row
spawn do
  Game.changes(ballgame.id).each do |change|
    game = change.value
    puts "looks like the score is #{game.score}"
  end
end

# Observe changes on a table
spawn do
  Game.changes.each do |change|
    game = change.value
    puts "#{game.type}: #{game.score}" 
    puts "game event: #{change.event}"
  end
end

Advisory Locks

PgORM::PgAdvisoryLock class provides a means for creating PostgreSQL Advisory Locks.

 lock = PgORM::PgAdvisoryLock.new("name or label to uniquely identify this lock")
 lock.synchronize do
   # Do some work
 end

 # OR if you need control on when to release the lock
 lock.lock
 # do some work
 # some more work
 lock.unlock

Column Types

Shard doesn't impose any restrictions on the types used in attributes and you are free to use any of the standard library or custom data types. For complex or custom data types, you are provided with an option to either provide custom converter which will be invoked when reading and writing to the table or shard assumes your complex data type supports JSON serialization method and field in stored in Postgres as JSONB data type.

Below is a list of several Crystal type that shard maps to Postgres column types

Any of your columns can also define “nilable” types by adding Crystal Nil Union ?. This is to let shard knows that your database table column allows for a NULL value.

Validations

Builds on active-model's validation

ensure_unique

Fails to validate if field with duplicate value present in db. If scope is set, the callback/block signature must be a tuple with types matching that of the scope. The field(s) are set with the result of the transform block upon successful validation

Timestamps

Adds creates created_at and updated_at attributes.

• updated_at is set through the before_update callback, and initially set in the before_save callback.
• created_at is set through the before_create callback.

The generated timestamp is UTC.

class Timo < PgORM::Base
  # Simply include the module
  include PgORM::Timestamps

  attribute name : String
end

Join

Supports:

• INNER JOIN: Returns records that have matching values in both tables
• LEFT JOIN: Returns all records from the left table, and the matched records from the right table
• RIGHT JOIN: Returns all records from the right table, and the matched records from the left table
• FULL JOIN: Returns all records when there is a match in either left or right table

When a join SQL is performed, model associated records will be cached and accessing linked relations will use the cached result instead of hitting the database.

Default behavior of associations is to perform a lazy load. So linked associations aren't fetched unless accessed.

class Parent < PgORM::Base
  attribute name : String
  has_many :children, class_name: Child, serialize: true # serialize tag will serialize `children` when `to_json` is invoked on parent object
end

class Child < PgORM::Base
  attribute age : Int32
  belongs_to :parent
  has_many :pet
end

class Pet < PgORM::Base
  attribute name : String
  belongs_to :child
end

parent = Parent.new(name: "Phil")
parent.save!

child1 = parent.children.create(age: 6)
child2 = parent.children.create(age: 3)

parent.save!

result = Parent.where(id: parent.id).join(:left, Child, :parent_id).to_a.first
children = JSON.parse(result.to_json).as_h["children"]?
children.should_not be_nil
children.try &.size.should eq(2)

Installation

1. Add the dependency to your shard.yml:

   dependencies:
     pg-orm:
       github: spider-gazelle/pg-orm
   

2. Run shards install

Usage

require "pg-orm"

Testing

Given you have the following dependencies...

• docker
• docker-compose

It is simple to develop the service with docker.

With Docker

• Run specs, tearing down the docker-compose environment upon completion.

$ ./test

• Run specs on changes to Crystal files within the src and spec folders.

$ ./test --watch

Without Docker

• To run tests

$ crystal spec

NOTE: The upstream dependencies specified in docker-compose.yml are required...

Compiling

$ shards build

Benchmark

Using script from Benchmark different ORMs for crystal and postgres , modified to add PgORM to the suite.

Results

Specs:

Machine: Apple MBP M1 Max 32GB RAM
OS: macOS 15.2
Crystal 1.14.0
PG: 17.2
DATE: 2024-12-20

BENCHMARKING simple_insert
                             user     system      total        real
Avram simple_insert      0.041759   0.072528   0.114287 (  0.955047)
Crecto simple_insert     0.029846   0.026625   0.056471 (  0.427655)
Granite simple_insert    0.017180   0.026954   0.044134 (  0.413282)
Jennifer simple_insert   0.033598   0.071590   0.105188 (  0.937873)
PgORM simple_insert      0.026143   0.068314   0.094457 (  0.916158)
BENCHMARKING simple_select
                             user     system      total        real
Avram simple_select      0.721817   0.090855   0.812672 (  2.037978)
Crecto simple_select     0.817780   0.095371   0.913151 (  1.754842)
Granite simple_select    0.652766   0.079596   0.732362 (  1.562638)
Jennifer simple_select   0.515536   0.075688   0.591224 (  1.363594)
PgORM simple_select      0.187846   0.045833   0.233679 (  0.867318)
BENCHMARKING simple_update
                             user     system      total        real
Avram simple_update      0.073312   0.101071   0.174383 (  1.371658)
Crecto simple_update     0.044117   0.046060   0.090177 (  0.754066)
Granite simple_update    0.022183   0.036054   0.058237 (  0.755027)
Jennifer simple_update   0.027320   0.050211   0.077531 (  0.759229)
PgORM simple_update      0.008919   0.023123   0.032042 (  0.447199)
BENCHMARKING simple_delete
                             user     system      total        real
Avram simple_delete      0.033921   0.049466   0.083387 (  0.765764)
Crecto simple_delete     0.031095   0.050755   0.081850 (  0.718908)
Granite simple_delete    0.023333   0.045345   0.068678 (  0.693689)
Jennifer simple_delete   0.038370   0.089591   0.127961 (  1.255163)
PgORM simple_delete      0.008872   0.025366   0.034238 (  0.442181)