This is so cool! Thanks for sharing.

You're very welcome! Glad you enjoyed it :)

Excellent post again! These are great reading for someone getting into Phoenix/Elixir dev

Thanks for the kind words! I am glad that these tutorial style posts are helpful :)

I truly cannot understand in what way it is better to write parts of sql statement in your Elixir code, see the image below , then to write an "stored procedure" an call it directly. This is a common thing to do in Ecto world, to have parts of sql statements defined in elixir code and parts as text values that are combined all together in one request but I see no added value, none at all.
The argument that you can switch to a different database system is not valid here as well because you use postgres specific syntax and functions.

If I were new to programming I would be ok with this approach but after like 15 years with Linq to SQL and Entity Framework I see clearly the main problem with your approach. When I want to change something in a database I just run a simple alter, when you want to change something you have to recompile whole application because your application layers are mixed together.

Don't take this as criticism, you are one of many developers that just follow the "recommended" approach but from my perspective it is just silly.

https://uploads.disquscdn.c...

Hey Ondrej and thanks for the feedback. Unfortunately it is not really feasible to cover every single thing in depth and make every code snippet follow 100% best practices or to even address the trade-offs involved with every single decision. The goal of the articles/tutorials is to cover a few important topics and gloss over the other things in the interest of the reader's time.

That being said, I use stored procedures quite often in my own production code when relevant. At the end of the day it is usually a matter of trade-offs. By tweaking a stored proc, you are now coupling the release of the DB with the release of the application. If you don't deploy these in lock step you are in for a bad time. You can leverage Ecto migrations to deploy new versions of the proc, but during development that can be very cumbersome as you need to up and down your migration every time you tweak the query in the proc.

In addition, having worked on production systems with 100s of stored procs, it does become very difficult to manage these things from an operations perspective and the cognitive load on engineers is high in my opinion as things are scattered across different layers.

At the end of the day, it is all about choosing the best tool for the job. Sometimes that is a stored proc or a materialized view and sometimes that is inlining the query into the remainder of your business logic.

Cheers and thanks for stopping by :)!