Plug postgrex integration

Questions / Help
,
1

I want to write a json/rest server using just Elixir, Postgrex and Plug. I don’t want to use Ecto or Phoenix. I’ll handle migrations with sqitch (https://sqitch.org). (I have grown averse to ORMs in general). I can find helpful tutorials on how to use Plug to create basic endpoints, but I haven’t been able to find any examples on how to integrate the Postgrex queries with Plug.Router endpoints. Any help at all is appreciated.

2

ecto is not an ORM, ecto is a data validation tool, ecto_sql is an SQL query builder, it allows you to write close-to-SQL in elixir with support of your IDE, rather than just some string when writing SQL for postgrex directly.

And what do you mean by integrating the queries into endpoints?

The most naive way is probably opening the DB connection in each “action”, sending the query and then rendering the response while closing the connection to the DB.

A somewhat better thought through implementation would use a connection pool.

Next iteration are modules capsulating the queries behind structs and functions.

After that comes re-implementing ecto(_sql)…

So especially about the “integration” thing you need to be way more specific about what your problem is.

1 Like
3

Please pardon my vagueness. I don’t know enough about the target subject matter at this point to ask very good questions I’m afraid.

I used the article at (https://blog.lelonek.me/minimal-elixir-http2-server-64188d0c1f3a) to understand how to build “minimal” Plug.Router based endpoints. The finished example can be found at (https://github.com/KamilLelonek/elixir-http-json-api).

The router module from the example looks like this:

defmodule IzendorServer.Router do
  use Plug.Router

  plug(:match)
  plug(:dispatch)

  @content_type "application/json"

  get "/" do
    conn
    |> put_resp_content_type(@content_type)
    |> send_resp(200, message())
  end

  match _ do
    send_resp(conn, 404, "Requested page not found!")
  end

  defp message do
    Poison.encode!(%{
      response_type: "in_channel",
      text: "Hello from BOT :)"
    })
  end
end

Using the example at (https://hexdocs.pm/postgrex/readme.html) I can easily make SQL calls from iex using Postgrex.query!, but I don’t know how to integrate Postgrex.query calls into my endpoints.

How would I modify the code above to return JSON corresponding to a Postgrex.query of:

Postgrex.query!(pid, "SELECT user_id, text FROM comments", [])

Regarding Ecto not being an ORM, I grant you that. I just didn’t have another 3 letter acronym handy. It is like an ORM, however, in that it is an abstraction layer for accessing a relational database. And, for various reasons, I am wanting to avoid an abstraction layer at the moment.

Thanks!

4

In the returned struct, there will be a :rows key. It will contain a list of rows. Each row is a list of the cells from the row. They will appear in order of the columns from the select, there is also :columns key, which will tell you the names and order of columns if you forgot about them or used a * query.

Even though I haven’t used postgrex directly, skimming its README helped to understand the format of the result.

pid
|> Postgrex.Query!("SELECT user_id, text FROM comments", [])
|> (&(&1.rows)).()
|> Enum.map(fn [user_id, text] ->
  %{user_id: user_id, text: text}
end)

This should give you a list of maps, which will represent your users.

1 Like
5

How would the pid become visible within my router module? It seems like the Postgrex sever process should be accessible by name rather than a process ID. Unfortunately, the Postgrex documentation doesn’t have much material oriented towards a new user, especially a relatively new Elixir programmer such as myself.

I’m beginning to think my best bet is to use the Ecto functionality supporting raw SQL queries. Ecto is very well documented. There is even an Ecto book available.

Thanks

1 Like
6

The naiv way is to connect in each routers action and close again thereafter.

The more sound way were to use a connection pool like db_connection.

This is mainly due to the fact, that postgrex is an advanced library that isn’t meant to be used by new users. Instead its meant to be used through some higher level abstractions, like ecto_sql.

2 Likes