Writing RawSQL in Ecto Phoenix

I have been using Ecto library for writing most of the database queries in Phoenix application, but there are certain limitations of using this library, as it doesn’t not support few database queries. So, for executing such queries we need to have something that can fulfils our use-case.

I had a scenario where I needed to create view as temporary table, so for this I had to use raw sql as there is no as such support or query api provided for Ecto. So, in order to resolve this I came across Ecto.Adapters.SQL.query

Ecto.Adapters.SQL.query! takes three arguments:

1. Repo Module
2. Raw sql in string format
3. optional list

And the final statement written for this is as follows:

query = """
  CREATE VIEW demo_user AS
  SELECT id, email
  FROM users
  WHERE role_id not in (2, 3)
"""
Ecto.Adapters.SQL.query!(MyApp.Repo, query, [])

Ecto.Adapters.SQL.query! returns Struct containing followings:

1. columns
2. command
3. connect_id
4. messages
5. num_rows
6. rows

%Postgrex.Result{
   columns: ["id", "first_name", "last_name", "email", "role_id"],
   command: :select,
   connection_id: 1446,
   messages: [],
   num_rows: 1,
   rows: [[1, "Bandana", "Pandey", "test@test.com", 1]]
}

Other example:

query = """ 
        SELECT * from users;
        """ 
Ecto.Adapters.SQL.query!(MyApp.Repo, query, [])

❤️ Like, Share or Leave A Comment!

Well, thats all for this blog, Hope you find it informative. If you liked it, please click the 👏🏼 button and share it with others! Feel free to leave any comments.
For more such blogs … stay tuned.

Happy Coding …

Thanks