Crecto

This section is based on Crecto's README

Database wrapper for Crystal. Inspired by Ecto for Elixir language.

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 application:

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.

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. By default, Crecto assumes your table has the following columns defined id, created_at, updated_at. These are in addition to whatever columns you decide to add.

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

If your schema doesn't require the default fields (id, created_at, updated_at), you can omit them.

class UserTags < Crecto::Model
  set_created_at_field nil # or you can set the name of your created_at field
  set_updated_at_field nil # ditto

  # primary_key: false tells the schema there's no `id` field
  schema "user_tags", primary_key: false do
    belongs_to :user, User
    belongs_to :tag, Tag
  end
end

For a schema with an ID that is custom (UUID, Random String, etc...)

class Comment <  Crecto::Model
  schema "comments" do
    field :id, String, primary_key: true
    field :content, String
  end
end

You can check out the spec/spec_helper.cr for more examples.

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 record into table.

Repo returns a new changeset.

changeset = Repo.insert(user)
puts changeset.errors # []

Use Repo to update record in table.

user.name = "new name"
changeset = Repo.update(user)
puts changeset.instance.name # "new name"

Use Repo to delete record from table.

changeset = Repo.delete(user)

Use Repo to delete all records from table.

Repo.delete_all(User)

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)

If you need to query through a join table, Query also has a join method.

# SELECT * FROM users INNER JOIN user_tags ON user_tags.user_id = users.id WHERE user_tags.tag_id = ?
query = Query
  .join(:user_tags)
  .where("user_tags.tag_id = ?", 123)

One thing to note about the query syntax is that the table you query on isn't decided until you pass the query to Repo.

query = Query.where(x: 1)

# SELECT * FROM things WHERE x = 1
Repo.all(Thing, query)

# SELECT * FROM weebls WHERE x = 1
Repo.all(Weebl, query)

All

users = Repo.all(User, query)
users.as(Array) unless users.nil?

Or you can just get all the records

users = Repo.all(User)
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?

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?

Non-nillable attributes

If you wish to access attributes of a model without having to check for nil, in the case that you are using a NOT NULLdatabase constraint you can use the non-nillable attribute accessors. CAUTION: Mis-use of this could lead to Nil reference runtime errors.

user.name!
user.age!

JSON type

PostgreSQL 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

PostgreSQL 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)

Benchmarks

• Crecto vs raw crystal-pg