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

Build Status Join the chat at



Add this to your application's shard.yml:

    github: fridgerator/crecto

Include a database adapter:


Include crystal-pg in your project

in your application:

require "pg"
require "crecto"


Include crystal-mysql in your project

in your application:

require "mysql"
require "crecto"


~~Include crystal-sqlite3 in your project~~ Include zhomarts fork of of crystal-sqlite3 in your project

in your appplication:

require "sqlite3"
require "crecto"


Roadmap (in no particular order)


Micrate is recommended. It is used and supported by core crystal members.


# 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).

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 = 5342
    # you can also set initial_pool_size, max_pool_size, max_idle_pool_size,
    #  checkout_timeout, retry_attempts, and retry_delay

module SqliteRepo
  extend Crecto::Repo

  config do |conf|
    conf.adapter = Crecto::Adapters::SQLite3
    conf.database = "./path/to/database.db"

# shortcut variables, optional
Query = Crecto::Repo::Query
Multi = Crecto::Multi

# Define table name, fields and validations in your 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
    field :temporary_info, Float64, virtual: true
    has_many :posts, Post, dependent: :destroy

  validate_required [:name, :age]
  validate_format :name, /^[a-zA-Z]*$/

class Post < Crecto::Model

  schema "posts" do
    belongs_to :user, User

user = = "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} = "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
# = "new name"
changeset = Repo.update(user)
puts # "new name"

# Set Associations

post =
post.user = user

# Query syntax
query = Query
  .where(name: "new name")
  .where("users.age < ?", [124])
  .order_by(" ASC")
  .order_by("users.age DESC")

# All
users = Repo.all(User, query) unless users.nil?

# Get by primary key
user = Repo.get(User, 1) unless user.nil?

# Get by fields
Repo.get_by(User, name: "new name", id: 1121) 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 = Repo.get(Post,, 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: 


  1. Fork it ( )
  2. Create your feature branch (git checkout -b my-new-feature)
  3. Commit your changes (git commit -am 'Add some feature')
  4. Push to the branch (git push origin my-new-feature)
  5. 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/ and create a module name Repo to use for testing. There are example repos for each database type in the spec folder:,, and

When submitting a pull request, please test against all 3 databases.

Thanks / Inspiration