Dynamic Queries in Ecto (Elixir Lang)

https://bartoszgorka.com/dynamic-queries-in-ecto

by bartoszgorka • 5 years ago

101 17 5 years ago

Ecto is the best query builder I’ve used and I believe it’s also the best overall.

rhn_mk1• 5 years ago

After using some Ecto, some Diesel and some sqlalchemy, Ecto as a query builder is on par with sqlalchemy (Diesel is limited by being strongly typed). Overall, when including migrations, reading and generating schemas, and performing arbitrary operations, sqlalchemy wins by not being opinionated. It can handle any database you throw at it without needing to fight it. Both Diesel and Ecto try to instill their own conventions on where the code should be and what the source of truth is even before you start.

namelosw• 5 years ago

Ecto really hits the sweet spot.

Usually, there are two camps on data accessing:

1. Anti-ORM because object-relational impedance mismatch: ORM will always be a leaky abstraction, and it introduces its own learning curve and mental overhead.

2. Pro-ORM: despite the impedance mismatch, the alternatives usually suck when it comes to writing: The boilerplate, the ad-hoc-ness. Very often people would use query builders to build yet another leaky abstraction so ORM seems to be the necessary evil.

Meanwhile Ecto:

1. It's one of the best query builders on the market. Thanks to Elixir macros, it's very flexible and you can just use functions to parameterize and reuse them very easily.

2. It's not an ORM but there's also a Schema as a shorthand counterpart for regular things.

3. By the way, it doesn't lazy load so n+1 is not a problem anymore.

realusername• 5 years ago

I would also mention the changesets which are the best way to represent data changes, it's so useful that I use it outside of databases.

The main issue for me with ecto is the lack of helpers with mass inserts, thinks like activerecord import & in_batches of activerecord would be nice.

nesarkvechnep2• 5 years ago

But there’s Multi which is useful in a lot of cases.

louissm_it2• 5 years ago

Changesets are such a great idea, I always find myself trying to replicate them when working in other environments.

If anyone is interested, I found this article showing how to use changesets outside the database:

https://elixirfocus.com/posts/ecto-schemaless-changesets/

yoran• 5 years ago

I agree. I adopted changesets in my Rails codebase after having used them in Phoenix.

latch• 5 years ago

I've used my share of data access libraries and patterns (e.g. hibernate, activerecord, ecto, ...). The only time I've been happy is when I use raw SQL for non-dynamic SQL and a lightweight query builder for everything else.

I feel like I always run into some thing that at best isn't intuitive to express/read and at worse, cannot be expressed. If I remember correctly, when I was learning Elixir/Ecto, https://github.com/elixir-ecto/ecto/issues/1616 issue and the lack of lateral join support caused me issues.

Want to create a user?

"insert into users (id, name, status) values ($1, $2, $3)"

Our query builder takes pretty raw SQL fragments:

    q = Query.new()
    |> Query.select("u.id, u.name")
    |> Query.from("users u")
    |> Query.from("join authentications a using (id)")

    q = case params[:status] do
     nil -> q
     status -> Query.where(q, "u.status", :eq, status)
    end

    rows = Query.rows!(q)

Looking through the docs, in Ecto, that join would be:

    |> join(:inner, [u], a in Authentiction, on: u.id == a.id)

Which I find needlessly abstract.

(I will admit that if you really do need to support multiple databases, the abstraction is useful).

dugmartin• 5 years ago

If you create an has_many authentications association in the User schema your query could be as simple as this if you wanted to pick what you want to select out of the join:

    from u in User, 
      join: a in assoc(u, :authentications),
      select: %{id: u.id, name: u.name, some_column: a.some_column}

or if you just want to load all the authentications

    from u in User, preload: [:authentications]

(Note: both of these queries are from memory and not tested, syntax errors may apply)

zapnuk• 5 years ago

Ecto is great. The only problem I've had with it was when I used it together with sqlite. It appears that by default, Ecto locks the sqlite-database file such that other clients cannot write to the same database.

With Python didn't encounter such problems. I assume this is more of a user problem - as I wouldn't describe myself as an Ecto or a sqlite expert.

A bit of a shame that Elixir isn't as popular as other languages. Solving problems can get a bit tedious because you can't just google it and find your answer in a stackoverflow post.

pablodavila• 5 years ago

From what I've seen, most Elixir questions are answered on Elixirforum. You might want to try searching there first or even asking yourself. You usually get fast, friendly and helpful responses (which sometimes turn into interesting discussions!).

louissm_it2• 5 years ago

Nice article! Ecto is such a wonderful, composable tool that really shows the benefits of FP.

A while back[1] I mentioned how I find it hard to live without Rails gems like Pagy, Ransack and Pundit.

But after a deep dive with Ecto I now find it almost silly that I was looking for packages to handle those things, they're dead simple with Elixir, Ecto and Phoenix.

[1] https://news.ycombinator.com/item?id=28066137

bartoszgorka• 5 years ago

Thank you! For me, Ecto & Phoenix are required part of each Elixir project.

I had similar case with migration between languages - you want to use already used tools but for this new language. However, sometimes you can use even better tools :)

thibaut_barrere• 5 years ago

If you want to play around with Ecto in isolation, you can (thanks to `Mix.install/2`) work in single-files, all containing the migrations, the schemas and configuration.

Check-out this repository for an example (and other single-file examples):

https://github.com/wojtekmach/mix_install_examples/blob/main...

jtwebman• 5 years ago

In my experience any system that is bigger then a demo raw SQL is the perfect abstraction unless you need to support multiple database types, which many systems do not. Complex queries are much harder to read in Ecto then SQL. You need to understand SQL anyways. I like working in and building systems using AyeSQL that allow you to compile SQL to functions. So use Ecto for the easy stuff, if you want, but the complex queries stick to SQL. It is already the perfect abstraction.

dgb23• 5 years ago

I think you want at least parametrized SQL and something that parses responses into a generic data-structure.

gregors• 5 years ago

I enjoy everything about Elixir/Phoenix except Ecto. Maybe it's just my personal taste since I also disliked Linq and Ecto is very similar. What I want is something closer to Rob Conery's https://github.com/robconery/moebius.

We're using Ecto, but I'm still looking for that easy tool that just seem wonderful to me. $.02

Dynamic Queries in Ecto (Elixir Lang)

Related Stories