crecto/crecto

Database wrapper and ORM for Crystal, inspired by Ecto

Crecto

https://www.crecto.dev/

Robust database wrapper for Crystal. Inspired by Ecto for Elixir language.

With built in query composer, associations, transactions, validations, constraints, and more.

Website with guides and examples - https://www.crecto.dev/

Example

user = User.new
user.name = "Shakira"

changeset = Repo.insert(user)
changeset.errors.any?

inserted_user = changeset.instance
inserted_user.name = "Keanu"

changeset = Repo.update(user)
changeset.errors.any?

updated_user = changeset.instance

changeset = Repo.delete(updated_user)

Usage and Guides

New website and API docs coming soon!

Benchmarks

• VS raw crystal-pg

Development

Testing

Specs are located in the specs directory. Seeing as this is an ORM, running specs does require a database connection of some kind. Copy the spec/repo.example.cr file to spec/repo.cr and fill in the connection details for your database. Then run crystal spec to run the specs.

Specs for all three supported database types can be run using docker-compose. Simply run docker-compose up to start the database containers and run the specs.

Contributing

1. Fork it ( https://github.com/Crecto/crecto/fork )
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/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.

Thanks / Inspiration

• Ecto
• AciveRecord
• active_record.cr
• crystal-api-backend

crecto's Issues

Error with SQLite3 Repository when inserting data

Hello,

I got the message

Exception: cast from String to (Time | Nil) failed

when I try to insert a model object into SQLite3 db.

I set up everything as your README said but I always get that error. Any insight?

My model is

module Service
  module Entities
    class Transaction < Crecto::Model
      schema "transactions" do
        field :uuid, String
        field :user, String
      end
    end
  end
end

My repo is

module Service
  module Repo
    extend Crecto::Repo

    config do |conf|
      conf.adapter = Crecto::Adapters::SQLite3
      conf.database = "./development.db"
    end
  end
end

My schema is

DROP TABLE IF EXISTS transactions;

CREATE TABLE transactions(
    id INTEGER NOT NULL PRIMARY KEY,
    uuid varchar(255) NOT NULL,
    user varchar(255) NOT NULL,
    created_at DATETIME,
    updated_at DATETIME
);

Finally, I try to save a tuple with this snippet

t = Service::Entities::Transaction.new
t.uuid = data["uuid"].to_s
t.user = data["user"].to_s
changeset = Service::Repo.insert(t)
changeset.changes.to_json

Full stacktrace is here

update travis config to test both postgres and mysql

schema without an ID

I didn't see this as an option in the code, or in the docs.

Some tables might not have an ID or any sort of primary key because they could be used as join tables. It would be nice to have an option to specify (or have it documented if this exists)

schema "cars" do
  has_many :car_owners, CarOwner
  has_many :owners, Owner, through: :car_owners
end

# maybe something like this?
schema "car_owners", id: false do
  updated_at_field nil
  created_at_field nil
  belongs_to :car, Car
  belongs_to :owner, Owner
end

schema "owners" do
   has_many :car_owners, CarOwner
   has_many :cars, Car, through: :car_owners
end

Time zone calculation is applied whenever `Repo.update` called

Even created_at is changed.

# User is the same as defined in spec
u = User.new
...
p u.some_date = Time.now
# 2017-04-08 20:00:00 +0900

changeset = Repo.insert(u)
u = changeset.instance
p u.some_date
p u.updated_at
p u.created_at
# 2017-04-08 11:00:00
# 2017-04-08 11:44:11
# 2017-04-08 11:44:11

changeset = Repo.update(u)
u = changeset.instance
p u.some_date
p u.updated_at
p u.created_at
# 2017-04-08 02:00:00
# 2017-04-08 11:44:11
# 2017-04-08 02:44:11

`Repo.all` with `preload` option doesn't actually preload.

While trying to use the association changes from #81, I ended up with a fragment based on the "Preload associations" section from the README:

users = Repo.all(User, preload: [:memberships]) 


users.each do |user|
  user.memberships
  # ...
end

This compiles, but ends up raising a Crecto::AssociationNotLoaded error when I try to access user.memberships.

At first I thought it might be something wrong with the changes in that PR, but I wasn't able to see what could cause it, so I started going top down from the call to Repo.all and quickly found that the **opts hash with preload isn't being used.

To test this, I tried using Repo.all(User, Query.new.preload(:memberships)), which correctly pre-loaded the association, so the functionality exists, it's just that Repo.all isn't reading the preload argument.

I messed around for a bit and came up with this as a solution that solved the issue, but I'm not too confident in it.

preloads = query.preloads + opts.fetch(:preload, [] of Symbol)
if preloads.any?
  add_preloads(results, queryable, preloads)
end

blocking insert

Hello, i'm doing a Repo.insert but the execution stop on this instruction

begin
  changeset = Repo.insert(user) # stopped here none of the above code is reached
  puts changeset.errors
rescue
  puts "rescued!"
end
puts "after"

Obviously i have a DB error:

postgres_1  | ERROR:  duplicate key value violates unique constraint "users_email_key"
postgres_1  | DETAIL:  Key (email)=([email protected]) already exists.
postgres_1  | STATEMENT:  INSERT INTO users (email) VALUES ($1) RETURNING *

When i have no DB error the code works just find

Add support for `PG::Numeric` type

FIELDS can be conflicted using other lib

hi, regarding this issue FIELDS is a very generic name and should be renamed to something more specific, ie: __CRECTO_FIELDS

Design Question on `Crecto::Repo.all`

Why throw a NoResults error when the query comes back empty? Is it possible to pass something like an empty array of the type?

I'm wondering what your take is on using exceptions to communicate a non-error state.

Possible SQL Injection?

Was looking through the SQL generation code and I couldn't tell. Are the parameters getting sanitized before building the query?

[FR] Cannot input an array into the validate_length function

currently this works;
validate_length :title, min: 5, max: 255
but this doesnt.
validate_length [:slug, :thumb, :link, :title], min: 5, max: 255

However, other validations like validate_format, validate_inclusions etc accept arrays.

get_connection opens a new connection for each request

Following the gitter chat.

Here's the repo

module CrystalDemo
  module Repo
    extend Crecto::Repo

    config do |conf|
      conf.uri = ENV["DATABASE_URL"]
      conf.initial_pool_size = 100
      conf.max_pool_size = 200
    end
  end
end

Unexptected token ::

Hey, nice work with the library, though I have one problem

I am creating a model like this

    schema "shows" do
      field :name, String
      field :language, String
      field :rating, Float64
      field :imdb_id, String
      field :votes, Int32
      has_many :episodes, Models::Episode
    end

and it crashes on has_many relation

Syntax error in expanded macro: has_many:35: unexpected token: ::

              item.as(Episode).models::show_id.as(PkeyValue)

any idea why?

crystal-db type problem with `Int32 | Int64` definition

crystal: 0.20.3
crystal-db: 0.3.3
crecto: master

It seems our helper definition of Int32 | Int64 for Int64 fields is causing problems during schema definition in crystal-db. When DB.mapping is called, the type for the field gets defined as Int64 despite our prior definition. I haven't been able to test is yet but it might be possible to create a new BigInt type alias that defines Int32 | Int64 and that be used for Int64 fields. I'm guessing based on my read of the crystal-db code here.

assume foreign key exists when specifying belongs_to association

currently when using belongs_to, user also has to specify foreign key field, i.e.:

field :user_id, Int64
belongs_to :user, User

Should assume foreign key field exists so it doesn't have to be explicitly set

preloading through joins is currently throwing an error

seems like its trying to use the foreign key as the type?

Exception: type "test_plan_id" does not exist (PQ::PQError) test_plan_id being the foreign key on the join table

• triggered with Repo.get! with a preload query
• also happens with get_association

Nested preloads

I have a set of associations that form a many_to_many relationship:

schema "races" do
  has_many :memberships, Membership
  has_many :users, User, through: :memberships
end

schema "memberships" do
  belongs_to :user, User
  belongs_to :race, Race
end

schema "users" do
  has_many :memberships, Membership
  has_many :users, User, through: :memberships
end

In my views, I often need to get information from both the Membership and the User/Race for each membership. The solution I have currently looks like this:

# :users doesn't really do anything useful here, since it doesn't set `race.member.user`
race = Repo.get(Race, 1, preload: [:memberships, :users])

race.memberships.each do |member|
  member.user # => raises `AssociationNotLoaded`
  member.user = Repo.get_association!(member, :user)

  # now, the full content of both `user` and `member` are available.
  member.user.name
end

But, this is subject to the N+1 problem, so it's not an ideal solution.

I think it'd be nice to have something similar to Ecto's nested preloads but Crystal doesn't have keyword lists like Elixir's, so maybe something like this?

# `preload` is an `Array(Symbol | NamedTuple)`.
Repo.get(Race, 1, preload: [:locations, { memberships: [:users] }])

# Could look cleaner on Query.preload
Repo.get(Race, 1, Query.preload(:locations, memberships: [:users])

I haven't thought at all about what the implementation would look like, so all of this is speculative of an ideal world. It's also possible that I've just missed something completely :)

Issue with foreign keys

1. In the specs we have two connected models User with field id : PkValue, where PkValue is Int32 | Int64 and model Post with field user_id : Int32. It's in spec/spec_helper.cr:172.
   Should we users to force to provide correct types? Right now I had issue with this incompatibility.

2. In the macro belongs_to https://github.com/fridgerator/crecto/blob/master/src/crecto/schema/belongs_to.cr#L25 we have @{{foreign_key.id}} = val.pkey_value.as(Int32). Instead of Int32 it should be type of foreign_key. But I don't know how to get the type.
   How should we deal with this?

Updating record with multiple new values

Is there way of updating record with multiple values?

Example:

class User
  scheme do
    field :name, String
    field :age, Int32
  end
end

params = { name: "Crecto", age: 3 }

user = Crecto::Repo.get(User, 1)
user.update(params) # --- how to do it?
Crecto::Repo.update(user)

maximum prepared statements issue

In crystal-db, prepared statements are only closed when the connection is closed.

When it reach the max value, it raises exception

Error: Can't create more than max_prepared_stmt_count statements (current value: 16382)

crystal-lang/crystal-db#60
It is recommended that close the connection and open the new one.

Do you have idea to solve this issue?

Currently I exposed Repo::Config and close/open new connection manually in the loop which inserts millions of rows.

#delete connection leak

Supporting postgres array types

It's nice being able to store some data as an array as opposed to doing a CSV column. Being able to support something like would be nice.

CREATE TABLE users (
    id integer NOT NULL,
    name character varying(254),
    likes text[],
    created_at timestamp without time zone,
    updated_at timestamp without time zone
);

Then the model could have a schema like

class User < Crecto::Model
  schema "users" do
    field :name, String
    field :likes, Array(String)
  end
end

`Repo.get!` returns `Crecto::Model+` instead of given class

I'm using Repo.get! to load a belongs_to relationship, and expected it to return a restricted type. Here's a simplified version of the code I have:

class User < Crecto::Model
  schema "users" do
    field :name, String
    has_many :memberships, Membership
  end
end

class Membership < Crecto::Model
  schema "memberships" do
    belongs_to :user, User
  end
end

membership = Repo.all(Membership).first
user = Repo.get!(membership, :user)

With this, I would assume that user would be of the type User, but instead is the virtual type Crecto::Model+. If I try to read any properties of user (such as name), I get an error:

puts user.name
# ...: undefined method 'name' for Membership (compile-time type is Crecto::Model+)

It seems like get! (and get) should be able to cast the result to the appropriate type using Model#klass_for_association

JSON / JSONB data types for postgres

Any ideas on how to provide this or if it's possible already?

Hangs on a table without an auto incremented id

I ran into this by accident. I created a table without an auto incrementing id:

CREATE TABLE posts(id int primary key, payload text not null);

Then when inserting into it with some basic data:

post = Post.new
post.payload = "foobar"
Repo.insert(post)

It just sits there without erring out or continuing. Creating a table with the correct fields (serial, created_at, updated_at) works just fine.

Use crystal-db

Eventually you should consider using https://github.com/crystal-lang/crystal-db as a dependency, because that's what all drivers will use, and then very little work will be needed in each adapter. There's a pending PR in crystal-pg but it will eventually be merged.

can't perform LIKE query

.where("name LIKE '%?%'", [string]) is throwing an error

unexpected type PG::ResultSet#read

Hi, Performing a Repo.insert(user) i get the following error:

PG::ResultSet#read returned a String. A (Int32 | Int64 | Nil) was expected.

Despite the record is properly insert into the DB

has_many without sole association?

Right now I have something like:

class Role < Crecto::Model
  schema "roles" do
    field :name, String
  end
end


class User < Crecto::Model
  schema "users" do
    field :user, String
    has_many :roles, Role
  end
end

This fails to build because of the lack of a foreign key in the Role model.

Here's the problem: they should both essentially have multiple ones. By that I mean that each role should be able to have multiple users attached, and each user should be able to have multiple roles attached.

Is this possible with Crecto?

SQLite3 support

I started implemented it. It's still in early stage right now.

• insert command
• other things

https://github.com/Zhomart/crecto/tree/sqlite3

Crystal 0.23.0 breaking Crecto?

Hello,

Sorry repost, I was logged in to the wrong account.

Warning - I just started using Crystal last week, so forgive me if this isn't really an issue. It seems that after updating Crystal from 0.22.0 to 0.23.0 the shards build compiler is now throwing an error regarding Crecto.

I have a model extending Crecto::Model. After the update, it can no longer find the method 'schema'.

Error is as follows -

in src/app/models/review.cr:7: undefined method 'schema'

  schema "reviews" do

Setup looks something like this -

class Review < Crecto::Model
  schema "reviews" do
  end
end

Let me know if I'm doing anything wrong or if I can be of help.

`has_many` shouldn't be nilable

Currently, has_many defines a nilable array for its association:

macro has_many(association_name, klass, **opts)
  property {{association_name.id}} : Array({{klass}})?

  ...
end

But this has bitten me a few times when I want to use the association either in views or add/remove entries from the association. For example, say I have a class User that has many Memberships:

class User < Crecto::Model
  schema "users" do
    # defines property memberships : Array(Membership)?
    has_many :memberships, Membership
  end
end

Now, if I want to iterate the memberships in a view, I first have to check for nil:

user = User.new
# This fails because `memberships` is `Array(Membership) | Nil`
user.memberships.each{ }
# This compiles, but may cause a runtime error if `memberships` hasn't been set.
user.memberships.not_nil!.each{ }

# Even more verbose in ECR, with an added local variable
<%- if memberships = user.memberships -%>
  <%- memberships.each do |m| -%>
    ...
  <%- end -%>
<%- end -%>

I propose that has_many be non-nilable, and instead provide an empty array as the default value. I messed around with this idea last night and came up with this:

macro has_many(association_name, klass, **opts)
  def {{association_name.id}} : Array({{klass}})
    @{{association_name.id}} ||= [] of {{klass}}
  end

  def {{association_name.id}}=(o : Array({{klass}}))
    @{{association_name.id}} = o
  end

  ...
end

Which has worked for me thus far, and means I can easily do things like

user = User.new
user.memberships << Membership.new
user.memberships.each{ }

# No more nil-check logic in ECR!
<%- user.memberships.each do |m| -%>
  ...
<%- end -%>

without having to worry about memberships being nil.

I think this makes sense and better follows the general conventions for collection filters (e.g., Enumerable#select) of defaulting to an empty array when no elements are found.

I can make a full PR (specs, README, etc.) with this if you think it is a valid change.

currently can't perform IS NULL query

Query.where(field: nil) doesn't work

current workaround is to use Query.where("field IS NULL")

BIGINT => Int64 not supported as a type

Initially created schema with a column that had BIGINT defined. Return type in Crystal seems to be Int64. Crecto will throw runtime errors if trying to call .nil? on a field because it's cast as (Int32 | Nil) instead of (Int64 | Nil).

creating POOL is not necessary

DB.open is already pooled

Crystal 0.23.0 breaking Crecto?

Hello,

Warning - I just started using Crystal last week, so forgive me if this isn't really an issue. It seems that after updating Crystal from 0.22.0 to 0.23.0 the shards build compiler is now throwing an error regarding Crecto.

I have a model extending Crecto::Model. After the update, it can no longer find the method 'schema'.

Error is as follows -

in src/interop/models/review.cr:7: undefined method 'schema'

  schema "reviews" do

Setup looks something like this -

class Review < Crecto::Model
  schema "reviews" do
  end
end

Let me know if I'm doing anything wrong or if I can be of help.

support distinct query

Sometimes distinct query is needed when using join.
https://hexdocs.pm/ecto/Ecto.Query.html#distinct/3

Thank you for the great library!
:-D

inserting duplicate record with a unique constraint causes crecto to hang

constructor parameters

Hi, did i miss something or we can't do

u = User.new({attr1: "foo", attr2: "bar", attr3: "baz"})
Repo.insert(u)

The only choice is to apply value to each attribute:

u = User.new
u.attr1 = "foo"
u.attr2 = "bar"
u.attr3 = "baz"
Repo.insert(u)

right?

get! with a query breaks

in lib/crecto/src/crecto/repo.cr:110: variable 'query' already declared

      if result = get(queryable, id, query : Query)

regex matches are not inclusive

/[a-z]/ will match "123 test"

I would expect it to only allow a-z characters

Typo? Class variable @@crecto_db instead of instance

See

crecto/src/crecto/repo/config.cr

Line 7 in 8dce658

crecto_db : DB::Database?

&

crecto/src/crecto/repo/config.cr

Line 38 in 8dce658

@@crecto_db ||= DB.open(database_url).as(DB::Database)

When changed to @crecto_db ||= DB.open(database_url).as(DB::Database), it allows to specify explicit connection instances in Repo config:

config do |conf|
  conf.crecto_db = db
end

Type error on `Query.where(where_sym, param)`

First off, this is probably not a bug with crecto, but crecto is what brought this issue up, so I'm filing it here just in case. Second, I apologize for the verbosity of this issue; the stacktraces are pretty big and the types involved are rather long.

I have a simple User model inheriting Crecto::Model (Membership is also a simple Crecto::Model):

class User < Crecto::Model
  schema "users" do
    field :uid,   String, primary_key: true
    field :name,  String
    has_many :memberships, Membership, dependent: :destroy
  end

  validate_required [:uid, :name]
end

and I then in another view, I'm trying to load all Memberships for the User:

memberships = Repo.all(user, :memberships)

But, when I try to compile this, I get a pretty scary type error (I've left out the irrelevant frames):

in src/templates/users/index.html.ecr:20: instantiating 'Repo:Module#all(User, Symbol)'

          <%- memberships = Repo.all(user, :memberships) %>
                                 ^~~

in lib/crecto/src/crecto/repo.cr:41: instantiating 'Crecto::Repo::Query:Class#where(Symbol, (String | Nil))'

      query = Crecto::Repo::Query.where(queryable_instance.class.foreign_key_for_association(association_name), queryable_instance.pkey_value)
                                  ^~~~~

in lib/crecto/src/crecto/repo/query.cr:61: instantiating 'Crecto::Repo::Query#where(Symbol, (String | Nil))'

        self.new.where(where_sym, param)
                 ^~~~~

in lib/crecto/src/crecto/repo/query.cr:224: instantiating 'Array(Hash(Symbol, Array(Bool | Float32 | Float64 | Int16 | Int32 | Int64 | JSON::Any | String | Time | Nil)) | Hash(Symbol, Array(Int32 | Int64 | Nil)) | Hash(Symbol, Array(Int32)) | Hash(Symbol, Array(Int64)) | Hash(Symbol, Array(String)) | Hash(Symbol, Bool | Float32 | Float64 | Int16 | Int32 | Int64 | JSON::Any | String | Time | Nil) | Hash(Symbol, Int32 | Int64 | Nil) | Hash(Symbol, Int32 | Int64 | String | Nil) | Hash(Symbol, Int32 | Int64 | String) | Hash(Symbol, Int32 | String) | Hash(Symbol, Int32) | Hash(Symbol, Int64 | String) | Hash(Symbol, Int64) | Hash(Symbol, Nil) | Hash(Symbol, String) | NamedTuple(clause: String, params: Array(Bool | Float32 | Float64 | Int16 | Int32 | Int64 | JSON::Any | String | Time | Nil)))#push(Hash(Symbol, String | Nil))'

        @wheres.push(Hash.zip([where_sym], [param]))
                ^~~~

in /usr/local/Cellar/crystal-lang/0.22.0/src/array.cr:1335: instantiating 'Pointer(Hash(Symbol, Array(Bool | Float32 | Float64 | Int16 | Int32 | Int64 | JSON::Any | String | Time | Nil)) | Hash(Symbol, Array(Int32 | Int64 | Nil)) | Hash(Symbol, Array(Int32)) | Hash(Symbol, Array(Int64)) | Hash(Symbol, Array(String)) | Hash(Symbol, Bool | Float32 | Float64 | Int16 | Int32 | Int64 | JSON::Any | String | Time | Nil) | Hash(Symbol, Int32 | Int64 | Nil) | Hash(Symbol, Int32 | Int64 | String | Nil) | Hash(Symbol, Int32 | Int64 | String) | Hash(Symbol, Int32 | String) | Hash(Symbol, Int32) | Hash(Symbol, Int64 | String) | Hash(Symbol, Int64) | Hash(Symbol, Nil) | Hash(Symbol, String | Nil) | Hash(Symbol, String) | NamedTuple(clause: String, params: Array(Bool | Float32 | Float64 | Int16 | Int32 | Int64 | JSON::Any | String | Time | Nil)))#[]=(Int32, Hash(Symbol, String | Nil))'

    @buffer[@size] = value
           ^

in /usr/local/Cellar/crystal-lang/0.22.0/src/pointer.cr:129: instantiating 'Pointer(Hash(Symbol, Array(Bool | Float32 | Float64 | Int16 | Int32 | Int64 | JSON::Any | String | Time | Nil)) | Hash(Symbol, Array(Int32 | Int64 | Nil)) | Hash(Symbol, Array(Int32)) | Hash(Symbol, Array(Int64)) | Hash(Symbol, Array(String)) | Hash(Symbol, Bool | Float32 | Float64 | Int16 | Int32 | Int64 | JSON::Any | String | Time | Nil) | Hash(Symbol, Int32 | Int64 | Nil) | Hash(Symbol, Int32 | Int64 | String | Nil) | Hash(Symbol, Int32 | Int64 | String) | Hash(Symbol, Int32 | String) | Hash(Symbol, Int32) | Hash(Symbol, Int64 | String) | Hash(Symbol, Int64) | Hash(Symbol, Nil) | Hash(Symbol, String | Nil) | Hash(Symbol, String) | NamedTuple(clause: String, params: Array(Bool | Float32 | Float64 | Int16 | Int32 | Int64 | JSON::Any | String | Time | Nil)))#value=(Hash(Symbol, String | Nil))'

    (self + offset).value = value
                    ^~~~~

in /usr/local/Cellar/crystal-lang/0.22.0/src/primitives.cr:175: type must be (Hash(Symbol, Array(Bool | Float32 | Float64 | Int16 | Int32 | Int64 | JSON::Any | String | Time | Nil)) | Hash(Symbol, Array(Int32 | Int64 | Nil)) | Hash(Symbol, Array(Int32)) | Hash(Symbol, Array(Int64)) | Hash(Symbol, Array(String)) | Hash(Symbol, Bool | Float32 | Float64 | Int16 | Int32 | Int64 | JSON::Any | String | Time | Nil) | Hash(Symbol, Int32 | Int64 | Nil) | Hash(Symbol, Int32 | Int64 | String | Nil) | Hash(Symbol, Int32 | Int64 | String) | Hash(Symbol, Int32 | String) | Hash(Symbol, Int32) | Hash(Symbol, Int64 | String) | Hash(Symbol, Int64) | Hash(Symbol, Nil) | Hash(Symbol, String) | NamedTuple(clause: String, params: Array(Bool | Float32 | Float64 | Int16 | Int32 | Int64 | JSON::Any | String | Time | Nil))), not (Hash(Symbol, Array(Bool | Float32 | Float64 | Int16 | Int32 | Int64 | JSON::Any | String | Time | Nil)) | Hash(Symbol, Array(Int32 | Int64 | Nil)) | Hash(Symbol, Array(Int32)) | Hash(Symbol, Array(Int64)) | Hash(Symbol, Array(String)) | Hash(Symbol, Bool | Float32 | Float64 | Int16 | Int32 | Int64 | JSON::Any | String | Time | Nil) | Hash(Symbol, Int32 | Int64 | Nil) | Hash(Symbol, Int32 | Int64 | String | Nil) | Hash(Symbol, Int32 | Int64 | String) | Hash(Symbol, Int32 | String) | Hash(Symbol, Int32) | Hash(Symbol, Int64 | String) | Hash(Symbol, Int64) | Hash(Symbol, Nil) | Hash(Symbol, String | Nil) | Hash(Symbol, String) | NamedTuple(clause: String, params: Array(Bool | Float32 | Float64 | Int16 | Int32 | Int64 | JSON::Any | String | Time | Nil)))

  def value : T

It seemed like the issue was stemming from @wheres.push(Hash.zip([where_sym], [param])), where potentially the result of Hash.zip wasn't the right type for @wheres, so I attempted to forcibly cast the result to the appropriate type before the call to push:

# src/crecto/repo/query.cr:223
def where(where_sym : Symbol, param : DbValue)
-  @wheres.push(Hash.zip([where_sym], [param]))
+  @wheres.push(Hash.zip([where_sym], [param]).as(Hash(Symbol, DbValue))
  self
end

which yielded a different, more interesting error:

in lib/crecto/src/crecto/repo/query.cr:224: can't cast Hash(Symbol, String | Nil) to Hash(Symbol, Bool | Float32 | Float64 | Int16 | Int32 | Int64 | JSON::Any | String | Time | Nil)

        @wheres.push(Hash.zip([where_sym], [param]).as(Hash(Symbol, DbValue)))

This surprised me, because I would've thought the result of Hash.zip would be Hash(Symbol, DbValue), since that's what the types of the where_sym and param arguments are, and is where I think the bug lies.

I also tried avoiding Hash.zip entirely and just using a Hash Literal construct:

# src/crecto/repo/query.cr:223
def where(where_sym : Symbol, param : DbValue)
-  @wheres.push(Hash.zip([where_sym], [param]))
+  @wheres.push({ where_sym => param })
  self
end

but this yielded the same original error about value... type must be....

The last thing I tried was forcing param to be a DbValue again:

# src/crecto/repo/query.cr:223
def where(where_sym : Symbol, param : DbValue)
-  @wheres.push(Hash.zip([where_sym], [param]))
+  @wheres.push({ where_sym => param.as(DbValue) })
  self
end

which finally compiled successfully, and seems to work correctly with all of the testing I've done so far.

I haven't found anything about needing this kind of casting in Crystal elsewhere, but my searches also haven't been very effective for this issue in general. I'm wondering if param is actually a sub-type of DbValue, and @wheres for some reason thinks the result is incompatible with it's type.

Sorry again for the long description, but hopefully this can get figured out/resolved soon. It seems like just a one-line change, but I can make a PR for it if you'd like :)

Support smallint for Postgres (Int16)

I have a table that uses a smallint in postgres

installation | smallint                    | default 0
site_id      | character varying           | 
name         | character varying           | 
width        | integer                     | default 0

When I define this as a Int16 I get

:installation type must be one of String, Int64, Int32, Float32, Float64, Bool, Time, (Int32 | Int64), (Float32 | Float64), JSON::Any, (Int32 | Int64 | Nil) (Crecto::InvalidType)

but defining as a Int32, and I get cast from Int16 to (Int32 | Int64 | Nil).

Postgres Adapter: Invalid query being generated

I was using a where clause and the sql being generated by the Query class is invalid.

query = Crecto::Repo::Query.where("settings.user_id = $1", [1])
settings = Crecto::Repo.all(UserSetting, query)

Generated SQL:

SELECT user_settings.* FROM user_settings WHEREuser_settings.user_id = $1

Its missing spacing after the WHERE.

As a side note, it'd be great if we had some kind of logging middleware so that you could see what's being generated by crecto.

[FR] Interface to edit db config

Crecto should provide an interface where we can define db config elements like poolsize, connection count, retries, timeout etc.

Even if it is provided in the code (and not a separate config file), it will help a lot with dev tasks like data seeding.

move preloads to query

Issues using through option

This is loosely along the same lines as #98.

When using the through option, as I defined in the other issue, I would expect to use the plural name of the association like has_many :owners, through: :car_owners. The issue is that this line doesn't singularize that term which would mean it's looking for a foreign_key car_owners_id (unless the foreign key is specified).

Now, if I'm reading this correctly, since the through option is specified here, it will run this code which calls id. In my case, my "through" table has no id field.

Another example for this issue:

schema "facts" do
  has_many :fact_checkers, FactChecker
  has_many :checkers, Checker, through: :fact_checker # this has to be single to work or foreign_key would need to be specified
end

# only has a fact_id and checker_id
schema "fact_checkers" do
   belongs_to :fact, Fact
   belongs_to :checker, Checker
end

schema "checkers" do
   has_many :fact_checkers, FactChecker
   has_many :facts, through: :fact_checker # single again here too
end

Support for enums?

An application that I'm working on has a number of state machines (potentially even multiple per model) that I want to represent with an Enum in Crystal.

Right now, I have my state machines implemented with Strings like this:

class Thing < Crecto::Model
  schema "things" do
    field :state, String
  end

  STATES = ["created", "started", "finished"]

  validates_inclusion :state, STATES
end

This works, but now if I want to validate the state of the model somewhere later on, I have to do a string comparison:

def do_something_only_if_started
  return unless self.state == "started"
  ...
end

which is prone to typos and is inherently slow as a String comparison.

This could be implemented with constants to avoid the potential typos, but that doesn't solve the string comparison problem:

class Thing < Crecto::Model
  module State
    CREATED  = "created"
    STARTED  = "started"
    FINISHED = "finished"
  end
  
  STATES = [State::CREATED, State::STARTED, State::FINISHED]

  validates_inclusion :state, STATES

  def do_something_only_if_started
    return unless self.state == State::STARTED
    ...
  end
end

With Enums

Using an Enum, however, gets the benefits of typo-avoidance, faster comparisons (Enums default to Int32, can be even smaller), and looks cleaner, imo.

class Thing < Crecto::Model
  enum State
    CREATED
    STARTED
    FINISHED
  end

  schema "things" do
    field :state, State
  end

  # This wouldn't be necessary
  # validates_inclusion :state, STATES

  def do_something_only_if_started
    return unless self.state == State::STARTED
    ...
  end
end

There seems to be a general consensus that enums directly in databases are a bad practice (the validity of which I don't 100% agree with, but whatever). That, and limited support across SQL implementations means that trying to represent enums in the database should probably still be done with strings.

This shouldn't be too difficult to deal with. Enum has parse and to_s to convert to and from String values, so treating field definitions with an Enum type could pretty transparently convert to and from Strings to interact with the database.

Alternatives

There's the wonderful aasm.cr, but it is really just a direct copy of the Ruby implementation and uses symbols for the state values. Symbols are certainly better than Strings, but lack the compile-time benefits of Enums.

... I don't know of any other packaged state machine builders for Crystal.

'Repo.insert' throw 'IndexError' sometimes

Sometimes simple insert query result is empty array [],
though all data were inserted to table correctly.

Index out of bounds (IndexError)
0x10a325535: *CallStack::new:CallStack at ??
0x10a32848e: *raise<IndexError>:NoReturn at ??

https://github.com/Crecto/crecto/blob/master/src/crecto/repo.cr#L184

The error is coming from first.

query = config.adapter.run_on_instance(tx || config.get_connection, :insert, changeset)
...
new_instance = changeset.instance.class.from_rs(query.as(DB::ResultSet)).first

I couldn't find any pattern, it looks like randomly occur.

---

mysql-5.6