How can I write a raw sql query?

Questions / Help
,
1

Hi,

I have to write a raw query for one of my project. But till now I have used ecto queries and don’t have much experience writing raw queries in phoenix. So to understand

I have created a user schema that inserts an email in a database table.

So created the changeset the like this

def changeset(user, attrs) do
user
|> cast(attrs, [:email, :password])
end

Now I have a create function that I’m using in my model. So the model will basically talk to db.

def create_user(attrs \\ %{}) do
    %User{}
    |> User.changeset(attrs)
    |> Repo.insert()
end

I have inserted an email in my database. using this
iex(1) > user = User.changeset(%User{}, %{email: “”, password: “”})

Now what I want is to find that user by his email address.
Also If I wanted to fetch through id. I would just do this

def get_user!(id), do: Repo.get!(User, id)

But what if I want to write a raw query? How do I do that?

2

The best place to start is always the docs. Hint: it’s a function inside this module Ecto.Repo — Ecto v3.11.1

1 Like
3

You might also have a look at…

https://hexdocs.pm/ecto_sql/Ecto.Adapters.SQL.html#query!/4

…for raw queries

4 Likes
4

Ah yeah, I’m realizing my post wasn’t super helpful because query! isn’t actually in the Repo docs :grimacing:, it’s just added inside repos that actually use SQL adapters. That’s unfortunate.

1 Like
5

Thanks. This is helpful

6

FWIW, you can do this with Repo.get_by(User, email: email_to_find).

2 Likes
7

In case you are exercising raw SQL for learning, you can also directly use the psql command line client for Postgres (assuming that is your database of choice), and directly type SQL in the console.

8

Thanks

9

Can anyone show me some project where sql raw
query is being used heavily. Not ecto queries

10

Hi @kokolegorille

I’m confused about this. Because the database I’m querying is in postgres. But I want to write a raw sql query. Now phoenix comes with Postgres by default. So should I move my table to SQL Database in order to use raw queries ?

11

That makes no sense–if your table is not in a SQL database you can’t query with raw queries. I suspect you mean something else, but I can’t figure out what you’re asking…

12

Okay suppose I have a user table in my postgres dp(database name) which has some fields like Name and Id. Now I want to query my database using raw query.

So to do that what I did is I created a function

 def get_by_name(name) do
   query = """

   SELECT * FROM user WHERE name ILIKE '$1#%'
   """

   Ecto.Adapters.SQL.query!(Wizex.Repo, query, [name])
 end

Now suppose I have saved the name “rockstar” in the user table. Now when I call this function it should get me this name.

Also why can’t you query sql raw queries in postgres?
Postgres is a database right? But internally it uses sql only.

13

Postgres is a relational database, like MySQL and many others. Most relational databases, including Postgres, are queried with SQL. SQL stands for Structured Query Language, and is a standardized language to query relational databases. Even when you use Ecto, the Ecto query is eventually producing some SQL.

SQL is not an Elixir concept: any language that interacts with a relational database does so by sending SQL queries. In fact, if you want to try and learn SQL, the easiest way is to set aside Elixir for a moment, and directly use a database client, like the psql command line client for Postgres.

I suggest you take a step back and learn about relational databases and SQL. A lot of things will be much clearer then. There are many good SQL tutorials and courses online, maybe someone can recommend one.

14

Perhaps you want to use Postgrex directly instead of using Ecto? This is very doable, and I use it commonly in small utility type of applications.

Almost. To be precise, it’s going to give you some kind of result struct, with number/types/names of columns, number of rows, and the rows. (Or, more accurately, it will return a result struct like that, or an error struct.)

Again, I don’t understand what you mean by that. Of course Postgres uses SQL. So what are you asking? Perhaps an example, explaining what you expect that is different from what you get?

15

Hey,

 def get_by_name(name) do
   query = """

   SELECT * FROM user WHERE name ILIKE '$1#%'
   """

   Ecto.Adapters.SQL.query!(Wizex.Repo, query, [name])
 end

I already know that but thanks. I just want this function to work and I don’t want to use Ecto.Query. If this work out then I will already understand the situation behind this.

16

So if you have reviewed the function and yes it should return a struct but I’m getting an error something like this

** (ArgumentError) parameters must be of length 0 for query

But if I don’t provide any argument it’s giving me this

 Ecto.Adapters.SQL.query(Wizex.Repo, "SELECT * FROM game WHERE name ILIKE '$1#%'", [])        
[debug] QUERY OK db=33.9ms queue=5.2ms idle=9048.4ms
SELECT * FROM game WHERE name ILIKE '$1#%' []
{:ok,
 %Postgrex.Result{
   columns: ["id", "name"],
   command: :select,
   connection_id: 1605,
   messages: [],
   num_rows: 0,
   rows: []
 }}

So from the docs

In case of success, it must return an :ok tuple containing a map with at least two keys:

  • :num_rows - the number of rows affected
  • :rows - the result set as a list. nil may be returned instead of the list if the command does not yield any row as result (but still yields the number of affected rows, like a delete command without returning would)
17

The result you see is correct, it is indeed a tuple of {:ok, result}, and the result is a struct indicating that there is no row matching your SQL query (rows is an empty list and num_rows is zero). What is confusing you? What did you expect instead?

Your query selects rows from the game table where name starts with $1#, followed by zero or more characters. Do you have any row in your table that should match that?

18

This is my table.

select * from game;
 id |   name   | thumbnail_url | twitch_id
----+----------+---------------+-----------
  1 | siddhant |               |
19

Ah! Seeing your result, I maybe see your problem. You’re expected the $1 inside the quotes to get replaced with the argument? Maybe what you actually want is:

Ecto.Adapters.SQL.query(Wizex.Repo, "SELECT * FROM game WHERE name ILIKE $1", [name <> "%"])
3 Likes
20

Hey, thanks. It worked. I thought I have written the query correctly. But I’m curious about this

ILIKE ‘$1#%’ What will this do ?