Qh - Ecto query helper for iex (Rails style)

I created a little helper q to easily query your database in iex.

Github: qh

It helps by namespacing the schema’s under the specified app name and integrating the Repo funtionality by chaining it to the query.

So instead of doing:

(from u in MyApp.User, order: u.name, limit: 1) |> MyApp.Repo.one()
(from u in MyApp.User, where: u.name == "Bob") |> MyApp.Repo.aggregate(:count)

You could do:

q User.order(name).first
q User.where(name == "Bob").count

Other examples:

q User.first
q User.order(name).last(3)
q User.order(name: :asc, age: :desc).last
q User.order("lower(?)", name).last
q User.where(age > 20 and age <= 30).count
q User.where(age > 20 and age <= 30).limit(10).all
q User.where(age > 20 or name == "Bob").all
q User.where(age > 20 and (name == "Bob" or name == "Anna")).all
q User.where(age: 20, name: "Bob").count
q User.where("nicknames && ?", ["Bobby", "Bobi"]).count
q User.where("? = ANY(?)", age, [20, 30, 40]).count
q User.find(21)
q User.find_by(name: "Bob Foo")
q User.find_by(name == "Bob" or name == "Anna")

q returns a query if you don’t call a repo function at the end, so you could even chain it:

q(User.where(age > 20 and age <= 30)) |> MyApp.Repo.all()

Not all Ecto functions are supported, but I’m looking to expand the library with the most common cases.

15 Likes

I just published v0.2.0:

  • Support most Ecto.Query functions:
    distinct,where,select,select_merge,group_by,having,lock,or_having,or_where,order_by,preload,windows,limit,except,except_all,exclude,intersect,intersect_all,union,union_all,reverse_order,join,inner_join,left_join,right_join,cross_join,full_join,inner_lateral_join,left_lateral_join

  • Support extra Ecto.Repo functions:
    all, one, stream, exists?

  • Optional binding supported for when needed:
    q User.where([u], u.name == "Bob").first

  • Added an aggr function and helpers to easily do an aggragation on a grouped_by query like:
    q User.group_by(name).aggr(count(), avg(age))
    and some helpers like:
    q User.group_by(name).count

  • Easy joins (defaults to assoc):
    q User.join(:messages).select([u, m], {u.id, m.message}).all

1 Like

Is there a way to convert this syntax to a full Ecto syntax quickly? Working things out in iex and then copying the code into the application is a common workflow and with the shorthand syntax I couldn’t do that as easily.

I like the approach a lot though.

1 Like

That would be hard to do currently, because it uses some internal convenience functions in the generated code. But I’ll keep it in mind for a next update. I might be able to make it work

2 Likes