crecto
Crecto
Database wrapper for Crystal. Inspired by Ecto for Elixir language.
See documentation on http://docs.crecto.com
Installation
Add this to your application's shard.yml
:
dependencies:
crecto:
github: fridgerator/crecto
Include a database adapter:
Postgres
Include crystal-pg in your project before crecto
in your application:
require "pg"
require "crecto"
Mysql
Include crystal-mysql in your project before crecto
in your application:
require "mysql"
require "crecto"
Sqlite
Include crystal-sqlite3 in your project before crecto
in your appplication:
require "sqlite3"
require "crecto"
Migrations
Micrate is recommended. It is used and supported by core crystal members.
Usage
First create a Repo. The Repo maps to the datastore and the database adapter and is used to run queries. You can even create multiple repos if you need to access multiple databases.
Note: For those coming from Active Record: Repo provides a level of abstraction between database logic (Repo) and business logic (Model).
Let's create a repo for Postgres:
module Repo
extend Crecto::Repo
config do |conf|
conf.adapter = Crecto::Adapters::Postgres # or Crecto::Adapters::Mysql or Crecto::Adapters::SQLite3
conf.database = "database_name"
conf.hostname = "localhost"
conf.username = "user"
conf.password = "password"
conf.port = 5432
# you can also set initial_pool_size, max_pool_size, max_idle_pool_size,
# checkout_timeout, retry_attempts, and retry_delay
end
end
And another for SQLite:
module SqliteRepo
extend Crecto::Repo
config do |conf|
conf.adapter = Crecto::Adapters::SQLite3
conf.database = "./path/to/database.db"
end
end
Shortcut variables
Optionally you can use constants shorcuts using:
Query = Crecto::Repo::Query
Multi = Crecto::Multi
Definitions
Define table name, fields, validations, and constraints in your model
Defining a new class using Crecto::Model
:
class User < Crecto::Model
schema "users" do
field :age, Int32 # or use `PkeyValue` alias: `field :age, PkeyValue`
field :name, String
field :is_admin, Bool, default: false
field :temporary_info, Float64, virtual: true
field :email, String
has_many :posts, Post, dependent: :destroy
end
validate_required [:name, :age]
validate_format :name, /^[a-zA-Z]*$/
unique_constraint :email
end
Defining another one:
class Post < Crecto::Model
schema "posts" do
belongs_to :user, User
end
end
Creating a new User
:
user = User.new
user.name = "123"
user.age = 123
Check the changeset to see changes and errors
changeset = User.changeset(user)
puts changeset.valid? # false
puts changeset.errors # {:field => "name", :message => "is invalid"}
puts changeset.changes # {:name => "123", :age => 123}
user.name = "test"
changeset = User.changeset(user)
changeset.valid? # true
Use Repo to insert into database.
Repo returns a new changeset.
changeset = Repo.insert(user)
puts changeset.errors # []
User Repo to update database
user.name = "new name"
changeset = Repo.update(user)
puts changeset.instance.name # "new name"
Set Associations
post = Post.new
post.user = user
Repo.insert(post)
Query syntax
query = Query
.where(name: "new name")
.where("users.age < ?", [124])
.order_by("users.name ASC")
.order_by("users.age DESC")
.limit(1)
All
users = Repo.all(User, query)
users.as(Array) unless users.nil?
Get by primary key
user = Repo.get(User, 1)
user.as(User) unless user.nil?
Get by fields
Repo.get_by(User, name: "new name", id: 1121)
user.as(User) unless user.nil?
Delete
changeset = Repo.delete(user)
Associations
user = Repo.get(User, id).as(User)
posts = Repo.get_association(user, :posts)
post = Repo.get(Post, id).as(Post)
user = Repo.get_association(post, :user)
Preload associations
users = Repo.all(User, preload: [:posts])
users[0].posts # has_many relation is preloaded
posts = Repo.all(Post, preload: [:user])
posts[0].user # belongs_to relation preloaded
Nil-check associations
If an association is not loaded, the normal accessor will raise an error.
users = Repo.all(User)
users[0].posts? # => nil
users[0].posts # raises Crecto::AssociationNotLoaded
For has_many
preloads, the result will always be an array.
users = Repo.all(User, preload: [:posts])
users[0].posts? # => Array(Post)
users[0].posts # => Array(Post)
For belongs_to and has_one preloads, the result may still be nil if no
record exists. If the association is nullable, always use association?
.
post = Repo.insert(Post.new).instance
post = Repo.get(Post, post.id, preload: [:user])
post.user? # nil
post.user # raises Crecto::AssociationNotLoaded
Aggregate functions
Can use the following aggregate functions: :avg
, :count
, :max
, :min:
, :sum
Repo.aggregate(User, :count, :id)
Repo.aggregate(User, :avg, :age, Query.where(name: 'Bill'))
Multi / Transactions
Create the multi instance
multi = Multi.new
Build the transactions
multi.insert(insert_user)
multi.delete(post)
multi.delete_all(Comment)
multi.update(update_user)
multi.update_all(User, Query.where(name: "stan"), {name: "stan the man"})
multi.insert(new_user)
Insert the multi using a transaction
Repo.transaction(multi)
Check for errors
If there are any errors in any of the transactions, the database will rollback as if none of the transactions happened
multi.errors.any?
JSON type
(Postgres only)
class UserJson < Crecto::Model
schema "users_json" do
field :settings, Json
end
end
user = UserJson.new
user.settings = {"one" => "test", "two" => 123, "three" => 12321319323298}
Repo.insert(user)
query = Query.where("settings @> '{\"one\":\"test\"}'")
users = Repo.all(UserJson, query)
Array type
(Postgres only)
class UserArray < Crecto::Model
schema "users_array" do
field :string_array, Array(String)
field :int_array, Array(Int32)
field :float_array, Array(Float64)
field :bool_array, Array(Bool)
end
end
user = UserArray.new
user.string_array = ["one", "two", "three"]
Repo.insert(user)
query = Query.where("? = ANY(string_array)", "one")
users = Repo.all(UserArray, query)
Database Logging
By default nothing is logged. To enable pass any type of IO to the logger. For STDOUT
use:
Crecto::DbLogger.set_handler(STDOUT)
Write to a file
f = File.open("database.log", "w")
f.sync = true
Crecto::DbLogger.set_handler(f)
Contributing
- Fork it ( https://github.com/fridgerator/crecto/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
Development Notes
When developing against crecto, the database must exist prior to
testing. There are migrations for each database type in spec/migrations
,
and references on how to migrate then in the .travis.yml
file.
Create a new file spec/repo.cr
and create a module name Repo
to use for testing.
There are example repos for each database type in the spec folder: travis_pg_repo.cr
,
travis_mysql_repo.cr
, and travis_sqlite_repo.cr
When submitting a pull request, please test against all 3 databases.