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
  • Query DSL Methods
    • OR Queries
    • Pattern Matching
    • Comparison Operators
    • Range Queries
    • Combining Query Methods
  • Full-Text Search
    • Basic Search
    • Ranked Search
    • Weighted Search
    • Advanced Search Methods
    • Pre-computed tsvector Columns
    • Text Search Configuration
    • Combining with Query Methods
    • Query Optimization
  • Pagination
    • Page-based Pagination
    • Offset-based Pagination
    • Cursor-based Pagination
    • Memory Efficiency
    • Pagination with Joins
    • JSON Serialization
    • Pagination with Full-Text Search
  • 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

Query DSL Methods

PgORM provides a comprehensive set of ActiveRecord-style query methods for building complex database queries.

OR Queries

Combine two query scopes with OR logic (ActiveRecord-style):

# Basic OR
User.where(name: "John").or(User.where(name: "Jane"))
# => WHERE (name = 'John') OR (name = 'Jane')

# Complex OR with multiple conditions
User.where(active: true, role: "admin")
    .or(User.where(active: true, role: "moderator"))
# => WHERE (active = true AND role = 'admin') OR (active = true AND role = 'moderator')

# Chain with other query methods
User.where(name: "Alice")
    .or(User.where(name: "Bob"))
    .order(:name)
    .limit(10)

Pattern Matching

LIKE (Case-Sensitive)

# Suffix match
User.where_like(:email, "%@example.com")

# Prefix match
User.where_like(:name, "John%")

# Contains
Article.where_like(:domain, "%example%")

# Negation
User.where_not_like(:email, "%@spam.com")

ILIKE (Case-Insensitive)

# Case-insensitive matching
User.where_ilike(:email, "%@EXAMPLE.com")

# Domain partial matching (solves the tsvector limitation)
Article.where_ilike(:domain, "%example%")
# Matches: "api.example.com", "example.org", "test.EXAMPLE.net"

# Negation
User.where_not_ilike(:name, "%test%")

Comparison Operators

# Greater than
User.where_gt(:age, 18)

# Greater than or equal
User.where_gte(:age, 18)

# Less than
User.where_lt(:age, 65)

# Less than or equal
User.where_lte(:age, 65)

# Chaining comparisons
User.where_gte(:age, 18).where_lt(:age, 65)

Range Queries

# BETWEEN (inclusive)
User.where_between(:age, 18, 65)
# => WHERE age BETWEEN 18 AND 65

# Works with Time values
Article.where_between(:created_at, 1.week.ago, Time.utc)

# NOT BETWEEN
User.where_not_between(:age, 18, 65)

Combining Query Methods

All query methods can be chained together:

# Complex query with multiple DSL methods
Article.where(published: true)
       .where_ilike(:domain, "%example%")
       .where_gte(:views, 100)
       .or(Article.where(featured: true))
       .order(created_at: :desc)
       .limit(20)

# Pattern matching with OR
User.where_like(:email, "%@gmail.com")
    .or(User.where_like(:email, "%@yahoo.com"))

# Range with other conditions
Article.where(category: "tech")
       .where_between(:created_at, 1.month.ago, Time.utc)
       .where_gt(:views, 1000)

Full-Text Search

PgORM provides comprehensive full-text search capabilities using PostgreSQL's tsvector and tsquery features.

Basic Search

Search across one or more columns using PostgreSQL's text search:

class Article < PgORM::Base
  attribute title : String
  attribute content : String
end

# Basic search with symbols (developer-friendly)
Article.search("crystal", :title, :content)

# Basic search with strings
Article.search("crystal", "title", "content")

# Array of columns (e.g., from user selection)
columns = ["title", "content"]
Article.search("crystal", columns)

# Boolean operators
Article.search("crystal & programming", :title, :content)  # AND
Article.search("crystal | ruby", :title, :content)         # OR
Article.search("crystal & !ruby", :title, :content)        # NOT

Ranked Search

Order results by relevance using ts_rank or ts_rank_cd:

# Basic ranking (ts_rank)
Article.search_ranked("crystal programming", :title, :content)

# Cover density ranking (ts_rank_cd)
Article.search_ranked("crystal", :title,
  rank_function: PgORM::FullTextSearch::RankFunction::RankCD)

# Rank normalization (divide by document length)
Article.search_ranked("crystal", :title, :content, rank_normalization: 1)

Weighted Search

Assign different weights to columns (A=1.0, B=0.4, C=0.2, D=0.1):

# With symbols
weights = {
  :title   => PgORM::FullTextSearch::Weight::A,
  :content => PgORM::FullTextSearch::Weight::B,
}
Article.search_weighted("crystal", weights)

# With strings (useful for frontend input)
weights = {
  "title"   => PgORM::FullTextSearch::Weight::A,
  "content" => PgORM::FullTextSearch::Weight::B,
}
Article.search_weighted("crystal", weights)

# Weighted search with ranking
Article.search_ranked_weighted("crystal", weights)

Advanced Search Methods

# Phrase search (exact phrase matching)
Article.search_phrase("crystal programming language", :content)

# Proximity search (words within N positions)
Article.search_proximity("crystal", "programming", 5, :content)

# Prefix search (matches crystal, crystalline, etc.)
Article.search_prefix("cryst", :title, :content)

# Plain text search (automatically converts to tsquery)
Article.search_plain("crystal programming", :title, :content)

Pre-computed tsvector Columns

For production use, create a tsvector column with a GIN index for better performance:

-- Add tsvector column
ALTER TABLE articles ADD COLUMN search_vector tsvector;

-- Create GIN index
CREATE INDEX articles_search_idx ON articles USING GIN(search_vector);

-- Auto-update trigger
CREATE TRIGGER articles_search_update
  BEFORE INSERT OR UPDATE ON articles
  FOR EACH ROW EXECUTE FUNCTION
  tsvector_update_trigger(search_vector, 'pg_catalog.english', title, content);

Then search using the pre-computed column:

# Basic search on tsvector column (symbol or string)
Article.search_vector("crystal & programming", :search_vector)
Article.search_vector("crystal & programming", "search_vector")

# Ranked search on tsvector column
Article.search_vector_ranked("crystal", :search_vector)

# Plain text search on tsvector column
Article.search_vector_plain("crystal programming", :search_vector)

Text Search Configuration

Specify language configuration for stemming and stop words:

# English (default)
Article.search("running", :content, config: "english")  # matches "run", "runs"

# Simple (no stemming)
Article.search("running", :content, config: "simple")   # exact match only

Combining with Query Methods

Full-text search integrates seamlessly with other query methods:

Article
  .where(published: true)
  .search("crystal", :title, :content)
  .limit(10)
  .order(:created_at)

Query Optimization

Use the explain method to analyze query performance:

query = Article.search_ranked("crystal", :title, :content)
puts query.explain  # Shows PostgreSQL query execution plan

Pagination

PgORM provides comprehensive pagination support with three strategies: page-based, offset-based, and cursor-based pagination. All pagination methods use lazy loading to minimize memory usage.

Page-based Pagination

Standard page number pagination with complete metadata:

# Basic pagination
result = Article.where(published: true).paginate(page: 2, limit: 20)

# Access metadata (no records loaded yet)
result.total        # => 150
result.page         # => 2
result.total_pages  # => 8
result.has_next?    # => true
result.has_prev?    # => true
result.next_page    # => 3
result.prev_page    # => 1
result.from         # => 21
result.to           # => 40

# Load records (lazy - only loaded when accessed)
articles = result.records

# Works with all query methods
Article.where(published: true)
  .order(created_at: :desc)
  .paginate(page: 1, limit: 10)

Offset-based Pagination

Direct control over offset and limit:

# Paginate with offset
result = Article.paginate_by_offset(offset: 40, limit: 20)

result.offset  # => 40
result.limit   # => 20
result.page    # => 3 (calculated as offset / limit + 1)

Cursor-based Pagination

Efficient pagination for large datasets using cursor-based navigation:

# First page
result = Article.order(:id).paginate_cursor(limit: 20)

result.records       # => Array of 20 articles
result.has_next?     # => true
result.next_cursor   # => "123" (ID of last record)

# Next page using cursor
next_result = Article.order(:id)
  .paginate_cursor(after: result.next_cursor, limit: 20)

# Previous page using cursor
prev_result = Article.order(:id)
  .paginate_cursor(before: next_result.prev_cursor, limit: 20)

# Custom cursor column
Article.order(:created_at)
  .paginate_cursor(after: cursor, limit: 20, cursor_column: :created_at)

Memory Efficiency

Pagination uses lazy loading to minimize memory usage:

# Create pagination result (only metadata loaded, ~200 bytes)
result = Article.paginate(page: 1, limit: 1000)

# Check metadata without loading records
if result.total_pages >= 5
  # Records only loaded when accessed
  articles = result.records
end

# Stream records without loading all into memory
result.each do |article|
  process(article)  # Process one at a time
end

Pagination with Joins

Automatically handles joined queries with correct counting:

# Correctly counts distinct records in joins
result = Author.join(:left, Book, :author_id)
  .where("books.published = ?", true)
  .paginate(page: 1, limit: 10)

# Uses COUNT(DISTINCT authors.id) to avoid duplicate counts
result.total  # => 25 (distinct authors, not total join results)

JSON Serialization

Pagination results include complete metadata in JSON:

result = Article.where(published: true).paginate(page: 2, limit: 10)

result.to_json
# {
#   "data": [...],
#   "pagination": {
#     "total": 150,
#     "limit": 10,
#     "offset": 10,
#     "page": 2,
#     "total_pages": 15,
#     "has_next": true,
#     "has_prev": true,
#     "next_page": 3,
#     "prev_page": 1,
#     "from": 11,
#     "to": 20
#   }
# }

Cursor pagination JSON:

result = Article.order(:id).paginate_cursor(limit: 10)

result.to_json
# {
#   "data": [...],
#   "pagination": {
#     "limit": 10,
#     "has_next": true,
#     "has_prev": false,
#     "next_cursor": "123",
#     "prev_cursor": null
#   }
# }

Pagination with Full-Text Search

Pagination works seamlessly with all search methods:

# Paginate search results
Article.search("crystal", :title, :content)
  .paginate(page: 1, limit: 20)

# Paginate ranked search
Article.search_ranked("crystal programming", :title, :content)
  .paginate(page: 1, limit: 20)

# Paginate with filters
Article.where(published: true)
  .search("crystal", :title, :content)
  .paginate(page: 1, limit: 20)

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)