objectify

Crystal micro-orm library, similar to what dapper supplies for .NET Core
Main features:
SQL result sets to be transformed into an object or array of object
SQL scripts to be injected with the correct variables from the passed object

For the mapping to work (rs -> object) the column name in the result set must match the class' attribute name

Uses the JSON library from stdlib to allow from_json to be used to prevent the need for messy custom initializers on each class.
Simply include Objectify::Mappable in classes that will be created from a SQL result set
This include is not needed if your class is using JSON.mapping

Installation

Add this to your application's shard.yml:

dependencies:
  objectify:
    github: drum445/objectify

Usage

require "objectify"

# include objectify's mappable in your class
class Foo
  include Objectify::Mappable
end

Result set to object (to_one)

require "db"
require "mysql"
require "objectify"

# Your class that is to be built from SQL, will raise exception if:
# A non-nillable field is not in the result set 
# Or a field in the result set does not match the attribute's type
class Note
  include Objectify::Mappable
  property note_id : String
  property content : String
  property likes : Int64
  property updated : Time

  def initialize(@note_id, @content, @likes, @updated)
  end  
end

db = DB.open "mysql://root:password@localhost:3306/test"

db.query "SELECT '123' as note_id, 'hello' as content, 4 as likes, NOW() as updated FROM DUAL;" do |rs|
    note = Objectify.to_one(rs, Note)

    puts note # => Note Object
end

Result set to array of object (to_many)

require "db"
require "mysql"
require "objectify"

# Your class that is to be built from SQL
class Note
  include Objectify::Mappable
  property note_id : String
  property content : String
  property likes : Int64
  property updated : Time? # nillable as it is not in the result set
end

db = DB.open "mysql://root:password@localhost:3306/test"

db.query "SELECT '123' as note_id, 'hello' as content, 4 as likes FROM DUAL
          UNION ALL
          SELECT '444', 'asd', 66 FROM DUAL;" do |rs|
    notes = Objectify.to_many(rs, Note)

    puts notes # => Array of Note
end

Alternative syntax

I prefer using the explicit "to_one" or "to_many" methods however if you like you can use Object.from_rs(rs) instead This will work with arrays or single objects

db.query "SELECT '123' as id, 'drum445' as username FROM DUAL;" do |rs|
  person = Person.from_rs(rs)
  people = Array(Person).from_rs(rs)

  puts person # => Person Object
  puts people # => Array of Person  
end

Custom Properties

As we are using the JSON::Serializable module we can take advantage of this:
https://crystal-lang.org/api/0.25.1/JSON/Serializable.html
Allowing us to have different column names in the result set map to our class attributes

require "db"
require "mysql"
require "objectify"

class Person
  include Objectify::Mappable
  
  @[JSON::Field(key: "id", emit_null: true)]
  property person_id : String
  property username : String
  property created : Time?

  def initialize(@person_id : String, @username)
  end  
end

db = DB.open "mysql://root:password@localhost:3306/test"

# id will map to Person.person_id due to JSON::Field settings
db.query "SELECT '123' as id, 'drum445' as username FROM DUAL;" do |rs|
    person = Objectify.to_one(rs, Person)

    puts person # => Person Object
end

Inserting object into DB

require "db"
require "mysql"
require "objectify"

db = DB.open "mysql://root:password@localhost:3306/test"

class Person
  property id : String
  property username : String

  def initialize(@id, @username)
  end
end

# create a person object and insert it into the db
p = Person.new("1234", "test")
query, args = Objectify.to_sql("INSERT INTO person (person_id, username) VALUES({id}, {username})", p)
db.exec query, args

Error Handling

Checks for required fields (non nillable) Result set is missing required field: foo
Checks for correct data type: Invalid data type for field: foo

Contributors

• drum445 ed - creator, maintainer