module Onyx::SQL::Model

Overview

Model (also record) is a business unit. Models usually have fields and relations with other models. The Model module allows to represent an SQL model as a plain Crystal object.

CREATE TABLE users (
  id        SERIAL  PRIMARY KEY,
  username  TEXT    NOT NULL
);

CREATE TABLE posts (
  id        SERIAL  PRIMARY KEY,
  content   TEXT    NOT NULL,
  cover     TEXT,
  author_id INT     NOT NULL  REFERENCES users (id),
);
class User
  include Onyx::SQL::Model

  schema users do
    pkey id : Int32
    type username : String, not_null: true
    type authored_posts : Array(Post), foreign_key: "author_id"
  end
end

class Post
  include Onyx::SQL::Model

  schema posts do
    pkey id : Int32
    type content : String, not_null: true
    type cover : String
    type author : User, key: "author_id", not_null: true
  end
end

In this example, User and Post are models. User has primary key id, field username and foreign enumerable reference authored_posts. Post also has primary key id, non-nilable field content and nilable field cover, and direct reference author. It's pretty simple and straightforward mapping. Read more about references in Serializable docs.

Serialization

Model module includes Serializable, which enables deserializing models from a DB::ResultSet, effectively allowing this:

db = DB.open(ENV["DATABASE_URL"])
users = User.from_rs(db.query("SELECT * FROM users"))

But it's more convenient to use Repository to interact with the database:

repo = Onyx::SQL::Repository.new(db)
users = repo.query(User, "SELECT * FROM users")

That's not much less code, but the repo, for example, handles query arguments (? -> $1 for PostrgreSQL queries) and also logs the requests. The real power of repository is handling Query arguments:

user = repo.query(User.where(id: 42)).first

Schema

Onyx::SQL is based on Crystal annotations to keep composition and simplify the underlying code. But since annotations are quite low-level, they are masked under the convenient .schema DSL. It's a good idea to understand what the .schema macro generates, but it's not mandatory for most of developers.

Included Modules

Defined in:

onyx-sql/model/changes.cr
onyx-sql/model/enums.cr
onyx-sql/model/instance_query_shortcuts.cr
onyx-sql/model/schema.cr
onyx-sql/model.cr

Class Method Summary

Instance Method Summary

Macro Summary

Class Method Detail

def self.table #

Return this model database table. It must be defined with Options annotation:

@[Onyx::SQL::Model::Options(table: "users")]
class User
end

pp User.table # => "users"

This method is defined upon the module inclusion.


[View source]

Instance Method Detail

def ==(other : self) #

Compare self against other model of the same type by their primary keys. Returns false if the self primary key is nil.


[View source]
def apply(changeset : Onyx::SQL::Model::Changeset(self, U)) : self forall U #

Apply a changeset, merging self values with the changeset's.


[View source]
def changeset #

Create a new changeset for this instance with snapshot of actual values. It is then likely to be passed to the #update method.

user = User.new(id: 42, name: "John")
changeset = user.changeset
pp changeset.initial_values # => {"id" => 42, "name" => "John"}
pp changeset.values         # => {"id" => 42, "name" => "John"}

changeset.update(name: "Jake")
pp changeset.values  # => {"id" => 42, "name" => "Jake"}
pp changeset.empty?  # => false
pp changeset.changes # => {"name" => "Jake"}

user.update(changeset) == User.update.set(name: "Jake").where(id: 42)

[View source]
def delete : Query #

A shortcut method to genereate a delete Query. See Query#delete.

user = User.new(id: 42)
user.delete == Query(User).new.delete.where(id: 42)

[View source]
def hash(hasher) #

[View source]
def insert : Query #

A shortcut method to genereate an insert Query pre-filled with actual self values. See Query#insert.

NOTE Will raise NilAssertionError in runtime if a field has not_null: true option and is actually nil. Conisder using ClassQueryShortcuts#insert instead.

user = User.new(id: 42, name: "John")
user.insert == Query(User).new.insert(id: 42, name: "John")

[View source]
def update(changeset : Onyx::SQL::Model::Changeset(self, U)) : Query forall U #

A shortcut method to genereate an update Query with changeset values. See Query#update and Query#set.

user = User.new(id: 42, name: "John")
changeset = user.changeset
changeset.update(name: "Jake")
user.update(changeset) == Query(User).new.update.set(name: "Jake").where(id: 42)

[View source]

Macro Detail

macro pkey(declaration, **options) #

Declare a model primary key, must be called within .schema block. It is equal to .type, but also passes not_null: true and defines the :primary_key option for the Options annotation. It's currently mandatory to have a primary key in a model, which may change in the future.

class User
  schema users do
    pkey id : Int32
  end
end

# Expands to

@[Onyx::SQL::Model::Options(primary_key: @id)]
class User
  @[Onyx::SQL::Field(not_null: true)]
  property! id : Int32
end

[View source]
macro schema(table, &block) #

.schema is a convenient DSL to avoid dealing with cumbersome (but extremely powerful) annotations directly. Consider this code:

@[Onyx::SQL::Model::Options(table: "users", primary_key: @id)]
class User
  include Onyx::SQL::Model

  property! id : Int32?

  @[Onyx::SQL::Field(not_null: true)]
  property! username : String

  @[Onyx::SQL::Reference(foreign_key: "author_id")]
  property! authored_posts : Array(Post)?
end

@[Onyx::SQL::Model::Options(table: "posts", primary_key: @id)]
class Post
  include Onyx::SQL::Model

  @[Onyx::SQL::Field(converter: PG::Any(Int32))]
  property! id : Int32?

  @[Onyx::SQL::Field(not_null: true)]
  property! content : String?

  property cover : String?

  @[Onyx::SQL::Reference(key: "author_id", not_null: true)]
  property! author : User?
end

With the DSL, it could be simplifed to:

class User
  schema users do
    pkey id : Int32
    type username : String, not_null: true
    type authored_posts : Array(Post), foreign_key: "author_id"
  end
end

class Post
  schema posts do
    pkey id : Int32
    type content : String, not_null: true
    type cover : String
    type author : User, key: "author_id", not_null: true
  end
end

This macro has a single mandatory argument table, which is, obviously, the model's table name. The schema currently requires a .pkey variable.

TODO Make the primary key optional.


[View source]
macro type(declaration, **options) #

Declare a model field or reference. Must be called within .schema block. Expands type to either nilable property or raise-on-nil property!, depending on the not_null option. The latter would raise in runtime if accessed or tried to be set with the nil value.

class User
  include Onyx::SQL::Model

  schema users do
    pkey id : Int32
    type name : String, not_null: true
    type age : Int32
  end
end

user = User.new

user.id   # => nil
user.name # => nil
user.age  # => nil

user.insert                  # Will raise in runtime, because name is `nil`
User.insert(name: user.name) # Safer alternative, would raise in compilation time instead

user = User.new(name: "John", age: 18)
user.name = nil # Would raise in compilation time, cannot set to `nil`
user.age = nil  # OK

[View source]