⚠️ WARNING Rewrite in progress. DO NOT USE. ⚠️
Updating to work with Crystal 1.2.2 and focusing more on SQLite support.
Sandstone
A simple Object Relational Model (ORM) for Crystal based on a fork of Sandstone ORM. This fork includes specific changes for SQLite more flexible "has many through" relationships, custom column names for foreign keys and more.
This assumes familiarity with a Rails-style ORM.
It is a personal fork created to address limitations found in Sandstone. I'm happy to help with questions and feauters, but it is not even remotely recommended for production use. I haven't even gotten around to replacing all the mentions of Sandstone.
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.
WARNING There's a bug in shards
that prevents renaming of a shard (this is a renamed fork) unless you destroy historical git tags.
For now you'll have to download it and require it using a local path. Note that the
current version of shards no longer supports paths starting with tilde ( ~
).
dependencies:
sandstone:
# commented out version that doesn't work with current shards bug.
#github: masukomi/sandstone
#version: 0.8.4-sandstone
path: /absolute/path/to/where/you/cloned/sandstone
sqlite3:
github: crystal-lang/crystal-sqlite3
Next you will need to create a config/database.yml
You can leverage environment variables using ${}
syntax.
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 Sandstone ORM Model
require "sandstone/adapter/mysql"
class Post < Sandstone::ORM::Base
adapter mysql
field name : String
field body : String
timestamps # will create and update the created_at and updated_at columns
# DOES NOT WORK WITH SQLITE (yet)
end
You can disable the timestamps for SqlLite since TIMESTAMP is not supported for this database:
require "sandstone/adapter/sqlite"
class Comment < Sandstone::ORM::Base
adapter sqlite
no_timestamps # you'll have to manage timestamp columns yourself currently
table_name post_comments
field name : String
field body : String
end
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 < Sandstone::ORM::Base
adapter mysql
primary custom_id : Int32
field name : String
end
Custom foreign key
Specify what columns will be called that reference a foreign key for the current class
class Event
...
set_foreign_key event_id
end
Custom default ordering column
Specify what column results will be ordered by by default.
class event
...
set_order_column created_at
end
Custom Table Name
Specify the name of your table if it can't simply be pluralized by adding an s. This is leveraged by other code to support a more flexible table naming system.
class Cactus
table_name cacti
end
This will override the default primary key of id : Int64
.
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
Find Last
post = Post.last
puts post.name if post
Find
post = Post.find 1
if post
puts post.name
end
Find By
post = Post.find_by :slug, "example_slug"
if post
puts post.name
end
Find or Create
A simplistic find or create. Useful if the object can be created with data in
only one column (other than the id
column).
class Person
find_or_creatable Person, name
end
Allows you to
Person.find_or_create_with(names_array)
Creates a bunch of people using the names in the array.
Insert
post = Post.new
post.name = "Sandstone ORM Rocks!"
post.body = "Check this out."
post.save
Update
post = Post.find 1
post.name = "Sandstone 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
owned_by
and has_some
macros provide association handling between objects.
class User < Sandstone::ORM::Base
adapter mysql
has_some Post
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 < Sandstone::ORM::Base
adapter mysql
owned_by User
# optionally specify the foreign key column if it isn't just the lower case
# class name followed by _id
# owned_by User, column: user_id
field title : String
timestamps
end
You can also have some of X through Y. In this case a User
has some Posts
through the UserPost
class. The UserPost
class must, of course, also be
managed by Sandstone.
class User
has_some Post, through: UserPost
...
end
class UserPost
owned_by User, column: user_id
owned_by Post, column: post_id
end
class Post
has_some User, through: UserPost
end
Many to Many
Instead of using a hidden many-to-many table, Sandstone 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 < Sandstone::ORM::Base
has_many :participants
field name : String
end
class Participant < Sandstone::ORM::Base
belongs_to :user
belongs_to :room
end
class Room < Sandstone::ORM::Base
has_many :participants
field name : String
end
Callbacks
There is support for callbacks on certain events.
Here is an example:
require "sandstone/adapter/pg"
class Post < Sandstone::ORM
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
DEPRECATED original Sandstone ORM functionality
Warning: This code is still present in the codebase only because it hasn't been deleted yet. It probably still works.... probably.
One To Many
belongs_to
and has_many
macros provide a rails like mapping between Objects.
class User < Sandstone::ORM::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 < Sandstone::ORM::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, Sandstone 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 < Sandstone::ORM::Base
has_many :participants
field name : String
end
class Participant < Sandstone::ORM::Base
belongs_to :user
belongs_to :room
end
class Room < Sandstone::ORM::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 < Sandstone::ORM::Base
has_many :participants
has_many :rooms, through: participants
field name : String
end
class Participant < Sandstone::ORM::Base
belongs_to :user
belongs_to :room
end
class Room < Sandstone::ORM::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 Sandstone::ORM::Validators with the symbol ':base'
post = Post.new
post.save
post.errors[0].to_s.should eq "ERROR: name cannot be null"
Contributing
- Fork it ( https://github.com/masukomi/sandstone/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