Ecto, PostgreSQL and View

ecto
postgresql
#1

Hi everyone!

I am playing with Ecto and PostgreSQL for my project, and, usually, I like to create some views to make my life easier when I when to create some complex query. I have some many small splitted table and when I need to retrieve all information, I write a long query with join.

So, is it possible to manage SQL views with Ecto.SQL or another kind of Elixir module? Or is it better to create an embedded schema to solve this problem? What is the best way in Ecto?

#2

Ecto does not care if you query a table or a view. There’s no difference at all. Just use the names of views you have and you’re good to go. For creating/managing views you’ll need to resort to Ecto.Migration.execute and raw sql though.

2 Likes
#3

Okay, I was thinking Ecto had some magic function to manage that. Thanks for the quick answer! :slight_smile:

#4

It sounds like you’ve got a plan, but if it would be helpful to see one way this works in practice, I wrote about how I use views with Ecto: https://work.stevegrossi.com/2017/01/12/using-ecto-schemas-with-database-views/

2 Likes
#5

Your article is great! I think I will use it to make some test on my side! I really liked it! :slight_smile: