Granite

Amber is a web framework written in the Crystal language.

This project is to provide an ORM in Crystal.

Installation

Add this library to your projects dependencies along with the driver in your shard.yml. This can be used with any framework but was originally designed to work with the amber framework in mind. This library will work with kemal or any other framework as well.

dependencies:
  granite:
    github: amberframework/granite

  # Pick your database
  mysql:
    github: crystal-lang/crystal-mysql

  sqlite3:
    github: crystal-lang/crystal-sqlite3

  pg:
    github: will/crystal-pg

Next you will need to create a config/database.yml You can leverage environment variables using ${} syntax.

mysql:
  database: "mysql://username:password@hostname:3306/database_${AMBER_ENV}"
pg:
  database: "postgres://username:password@hostname:5432/database"
sqlite:
  database: "sqlite3:./config/${DB_NAME}.db"

Or you can set the DATABASE_URL environment variable. This will override the config/database.yml

Usage

Here is an example using Granite Model

require "granite/adapter/mysql"

class Post < Granite::Base
  adapter mysql
  field name : String
  field body : String
  timestamps
end

You can disable the timestamps for SqlLite since TIMESTAMP is not supported for this database:

require "granite/adapter/sqlite"

class Comment < Granite::Base
  adapter sqlite
  table_name post_comments
  field name : String
  field body : String
end

id, created_at, updated_at

The primary key is automatically created for you and if you use timestamps they will be automatically updated for you.

Here are the MySQL field definitions for id, created_at, updated_at

  id BIGINT NOT NULL AUTO_INCREMENT
  # Your fields go here
  created_at TIMESTAMP
  updated_at TIMESTAMP
  PRIMARY KEY (id)

Custom Primary Key

For legacy database mappings, you may already have a table and the primary key is not named id or Int64.

We have a macro called primary to help you out:

class Site < Granite::Base
  adapter mysql
  primary custom_id : Int32
  field name : String
end

This will override the default primary key of id : Int64.

Natural Keys

For natural keys, you can set auto: false option to disable auto increment insert.

class Site < Granite::Base
  adapter mysql
  primary code : String, auto: false
  field name : String
end

UUIDs

For databases that utilize UUIDs as the primary key, the primary macro can be used again with the auto: false option. A before_create callback can be added to the model to randomly generate and set a secure UUID on the record before it is saved to the database.

class Book < Granite::Base
  require "uuid"
  adapter mysql
  primary ISBN : String, auto: false
  field name : String

  before_create :assign_isbn

  def assign_isbn
    @ISBN = UUID.random.to_s
  end
end

Bulk Insertions

Import

Note: Imports do not trigger callbacks automatically. See Running Callbacks.

Each model has an import class level method to import an array of models in one bulk insert statement.

models = [
  Model.new(id: 1, name: "Fred", age: 14),
  Model.new(id: 2, name: "Joe", age: 25),
  Model.new(id: 3, name: "John", age: 30),
]

Model.import(models)

update_on_duplicate

The import method has an optional update_on_duplicate + columns params that allows you to specify the columns (as an array of strings) that should be updated if primary constraint is violated.

models = [
  Model.new(id: 1, name: "Fred", age: 14),
  Model.new(id: 2, name: "Joe", age: 25),
  Model.new(id: 3, name: "John", age: 30),
]

Model.import(models)

Model.find!(1).name # => Fred

models = [
  Model.new(id: 1, name: "George", age: 14),
]

Model.import(models, update_on_duplicate: true, columns: %w(name))

Model.find!(1).name # => George

NOTE: If using PostgreSQL you must have version 9.5+ to have the on_duplicate_key_update feature.

ignore_on_duplicate

The import method has an optional ignore_on_duplicate param, that takes a boolean, which will skip records if the primary constraint is violated.

models = [
  Model.new(id: 1, name: "Fred", age: 14),
  Model.new(id: 2, name: "Joe", age: 25),
  Model.new(id: 3, name: "John", age: 30),
]

Model.import(models)

Model.find!(1).name # => Fred

models = [
  Model.new(id: 1, name: "George", age: 14),
]

Model.import(models, ignore_on_duplicate: true)

Model.find!(1).name # => Fred

batch_size

The import method has an optional batch_size param, that takes an integer. The batch_size determines the number of models to import in each INSERT statement. This defaults to the size of the models array, i.e. only 1 INSERT statement.

models = [
  Model.new(id: 1, name: "Fred", age: 14),
  Model.new(id: 2, name: "Joe", age: 25),
  Model.new(id: 3, name: "John", age: 30),
  Model.new(id: 3, name: "Bill", age: 66),
]

Model.import(models, batch_size: 2)
# => First SQL INSERT statement imports Fred and Joe
# => Second SQL INSERT statement imports John and Bill

Running Callbacks

Since the import method runs on the class level, callbacks are not triggered automatically, they have to be triggered manually. For example, using the Item class with a UUID primary key:

require "uuid"

class Item < Granite::Base
  adapter mysql
  table_name items

  primary item_id : String, auto: false
  field item_name : String

  before_create :generate_uuid

  def generate_uuid
    @item_id = UUID.random.to_s
  end
end

items = [
  Item.new(item_name: "item1"),
  Item.new(item_name: "item2"),
  Item.new(item_name: "item3"),
  Item.new(item_name: "item4"),
]

# If we did `Item.import(items)` now, it would fail since the item_id wouldn't get set before saving the record, violating the primary key constraint.

# Manually run the callback on each model to generate the item_id.
items.each(&.before_create)

# Each model in the array now has a item_id set, so can be imported.
Item.import(items)

# This can also be used for a single record.
item = Item.new(item_name: "item5")
item.before_create
item.save

Note: Manually running your callbacks is mainly aimed at bulk imports. Running them before a normal .save, for example, would run your callbacks twice.

SQL

To clear all the rows in the database:

Post.clear #truncate the table

Find All

posts = Post.all
if posts
  posts.each do |post|
    puts post.name
  end
end

Find First

post = Post.first
if post
  puts post.name
end

post = Post.first! # raises when no records exist

Find

post = Post.find 1
if post
  puts post.name
end

post = Post.find! 1 # raises when no records found

Find By

post = Post.find_by :slug, "example_slug"
if post
  puts post.name
end

post = Post.find_by! :slug, "foo" # raises when no records found

Insert

post = Post.new
post.name = "Granite Rocks!"
post.body = "Check this out."
post.save

Update

post = Post.find 1
post.name = "Granite Really Rocks!"
post.save

Delete

post = Post.find 1
post.destroy
puts "deleted" unless post

Queries

The where clause will give you full control over your query.

All

When using the all method, the SQL selected fields will always match the fields specified in the model.

Always pass in parameters to avoid SQL Injection. Use a ? in your query as placeholder. Checkout the Crystal DB Driver for documentation of the drivers.

Here are some examples:

posts = Post.all("WHERE name LIKE ?", ["Joe%"])
if posts
  posts.each do |post|
    puts post.name
  end
end

# ORDER BY Example
posts = Post.all("ORDER BY created_at DESC")

# JOIN Example
posts = Post.all("JOIN comments c ON c.post_id = post.id
                  WHERE c.name = ?
                  ORDER BY post.created_at DESC",
                  ["Joe"])

First

It is common to only want the first result and append a LIMIT 1 to the query. This is what the first method does.

For example:

post = Post.first("ORDER BY posts.name DESC")

This is the same as:

post = Post.all("ORDER BY posts.name DESC LIMIT 1").first

Relationships

One to Many

belongs_to and has_many macros provide a rails like mapping between Objects.

class User < Granite::Base
  adapter mysql

  has_many :posts

  field email : String
  field name : String
  timestamps
end

This will add a posts instance method to the user which returns an array of posts.

class Post < Granite::Base
  adapter mysql

  belongs_to :user

  field title : String
  timestamps
end

This will add a user and user= instance method to the post.

For example:

user = User.find 1
user.posts.each do |post|
  puts post.title
end

post = Post.find 1
puts post.user

post.user = user
post.save

In this example, you will need to add a user_id and index to your posts table:

CREATE TABLE posts (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT,
  title VARCHAR,
  created_at TIMESTAMP,
  updated_at TIMESTAMP
);

CREATE INDEX 'user_id_idx' ON posts (user_id);

Many to Many

Instead of using a hidden many-to-many table, Granite recommends always creating a model for your join tables. For example, let's say you have many users that belong to many rooms. We recommend adding a new model called participants to represent the many-to-many relationship.

Then you can use the belongs_to and has_many relationships going both ways.

class User < Granite::Base
  has_many :participants

  field name : String
end

class Participant < Granite::Base
  belongs_to :user
  belongs_to :room
end

class Room < Granite::Base
  has_many :participants

  field name : String
end

The Participant class represents the many-to-many relationship between the Users and Rooms.

Here is what the database table would look like:

CREATE TABLE participants (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT,
  room_id BIGINT,
  created_at TIMESTAMP,
  updated_at TIMESTAMP
);

CREATE INDEX 'user_id_idx' ON TABLE participants (user_id);
CREATE INDEX 'room_id_idx' ON TABLE participants (room_id);

has_many through:

As a convenience, we provide a through: clause to simplify accessing the many-to-many relationship:

class User < Granite::Base
  has_many :participants
  has_many :rooms, through: participants

  field name : String
end

class Participant < Granite::Base
  belongs_to :user
  belongs_to :room
end

class Room < Granite::Base
  has_many :participants
  has_many :users, through: participants

  field name : String
end

This will allow you to find all the rooms that a user is in:

user = User.first
user.rooms.each do |room|
  puts room.name
end

And the reverse, all the users in a room:

room = Room.first
room.users.each do |user|
  puts user.name
end

Errors

All database errors are added to the errors array used by Granite::Validators with the symbol ':base'

post = Post.new
post.save
post.errors[0].to_s.should eq "ERROR: name cannot be null"

Callbacks

There is support for callbacks on certain events.

Here is an example:

require "granite/adapter/pg"

class Post < Granite::Base
  adapter pg

  before_save :upcase_title

  field title : String
  field content : String
  timestamps

  def upcase_title
    if title = @title
      @title = title.upcase
    end
  end
end

You can register callbacks for the following events:

Create

before_save
before_create
save
after_create
after_save

Update

before_save
before_update
save
after_update
after_save

Destroy

before_destroy
destroy
after_destroy

Migration

migrator provides drop, create and drop_and_create methods

class User < Granite::Base
  adapter mysql
  field name : String
end

User.migrator.drop_and_create
# => "DROP TABLE IF EXISTS `users`;"
# => "CREATE TABLE `users` (id BIGSERIAL PRIMARY KEY, name VARCHAR(255));"

User.migrator(table_options: "ENGINE=InnoDB DEFAULT CHARSET=utf8").create
# => "CREATE TABLE ... ENGINE=InnoDB DEFAULT CHARSET=utf8;"

Contributing

Fork it ( https://github.com/amberframework/granite/fork )
Create your feature branch (git checkout -b my-new-feature)
Commit your changes (git commit -am 'Add some feature')
Push to the branch (git push origin my-new-feature)
Create a new Pull Request

Running tests

Granite uses Crystal's built in test framework. The tests can be run with $ crystal spec.

The test suite depends on access to a PostgreSQL, MySQL, and SQLite database to ensure the adapters work as intended.

Docker setup

There is a self-contained testing environment provided via the docker-compose.yml file in this repository.

After you have docker installed do the following to run tests:

First run

$ docker-compose build spec
$ docker-compose run spec

Subsequent runs

$ docker-compose run spec

Cleanup

If you're done testing and you'd like to shut down and clean up the docker dependences run the following:

$ docker-compose down

Local setup

If you'd like to test without docker you can do so by following the instructions below:

Install dependencies with $ crystal deps
Update .env to use appropriate ENV variables, or create appropriate databases.
Setup databases:

PostgreSQL

CREATE USER granite WITH PASSWORD 'password';

CREATE DATABASE granite_db;

GRANT ALL PRIVILEGES ON DATABASE granite_db TO granite;

MySQL

CREATE USER 'granite'@'localhost' IDENTIFIED BY 'password';

CREATE DATABASE granite_db;

GRANT ALL PRIVILEGES ON granite_db.* TO 'granite'@'localhost' WITH GRANT OPTION;

Export .env with $ source .env
$ crystal spec

stndrs/granite

Granite

Installation

Usage

id, created_at, updated_at

Custom Primary Key

Natural Keys

UUIDs

Bulk Insertions

Import

update_on_duplicate

ignore_on_duplicate

batch_size

Running Callbacks

SQL

Find All

Find First

Find

Find By

Insert

Update

Delete

Queries

All

First

Relationships

One to Many

Many to Many

has_many through:

Errors

Callbacks

Create

Update

Destroy

Migration

Contributing

Running tests

Docker setup

First run

Subsequent runs

Cleanup

Local setup

PostgreSQL

MySQL