Ecto is pretty awesome. It will do just fine for majority of db-related tasks in Elixir applications. Sometimes, however, you want to - or need to - use SQL directly. If you use PostgreSQL, you can use underlying driver for Elixir: postgrex. This will come in handy when something you want to achieve in Ecto turns out to be difficult or unsupported. Examples that come to my mind are: connecting to databases on the fly as application is running, enforcing use of single connection to perform series of queries or using some more exotic SQL features implemented using PostgreSQL, that are not generic enough to find it’s support in Ecto. The same applies to MySQL/Mariadb. From time to time you will want to directly use mariaex.

Executing raw SQL from Ecto

If all you need to do is to execute raw SQL queries, you may want to stick to Ecto anyway and just use:

Ecto.Adapters.SQL.query!(
  MyApp.Repo, "SELECT * FROM users where name = $1", ["Hubert"]
)

The above code would check out a single connection from the pool reserved by Ecto for MyApp.Repo, execute the query (with optional query parameters), and return a raw response.

Connecting to database with Postgrex

Postgrex and Mariaex both use DBConnection library to establish and maintain connections to database. There are currently 3 modes:

1. Single database connection
2. Connection pool with Poolboy (or Sojourn)
3. Ownership pool

The difference between 2 and 3 is that ownership pool requires you to manually check in and check out connections from the pool as you want to use them. We will focus on Poolboy pool only in this post.

As I am writing this, DBConnection is being simplified, and the dependencies on Poolboy and Sojourn, as well as single database connection will be likely removed and replaced with own, built-in connection pool mechanism. But for now we have to use something like Poolboy. Check out this PR] for more details.

Single database connection

In order to connect to a database, you need to add Postgrex to your mix.exs file:

def deps() do
  [
    {:postgrex, "~> 0.13.5"}
    ...
  ]
end

And if you plan to use JSONB type also add jason:

def deps() do
  [
    {:postgrex, "~> 0.13.5"},
    {:jason, "~> 1.0"}
    ...
  ]
end

Finally, if you plan to use connection pooling, you will need Poolboy:

def deps() do
  [
    {:postgrex, "~> 0.13.5"},
    {:jason, "~> 1.0"},
    {:poolboy, "1.5.1"}
  ]
end

Run mix deps.get and start your app in iex session. To connect to a local database named “app” one would do:

{:ok, pid} = Postgrex.start_link(hostname: "localhost", username: "postgres", password: "postgres", database: "app")

This is going to start a process that you can use to query the database directly:

iex(24)> Postgrex.query!(pid, "SELECT * FROM users WHERE id = $1", [1])

%Postgrex.Result{
  columns: ["id", "email"],
  command: :select,
  connection_id: 4618,
  num_rows: 1,
  rows: [[1, "hubert.lepicki@amberbit.com"]]
}

Nice!

The started connection is also already supervised and will re-connect if something bad happens to your database server. Try stopping the local PostgreSQL process on your machine, and you will see some errors being printed on the console at time intervals notifying you that the database connection is down:

16:18:28.432 [error] Postgrex.Protocol (#PID<0.782.0>) failed to
connect: ** (DBConnection.ConnectionError) tcp connect (localhost:5432):
connection refused - :econnrefused

This means Postgrex wasn’t able to connect to the database for some reason. If you start your database now, the red messages will stop flooding your screen, and you will also be able to use the same pid to perform queries. Neat.

Using connections pool

Connection pools are a bit tricker, but here’s what I figured out.

First, you want to configure the connection somewhere. Since all I needed is one database to connect to, I used my config/config.exs file to place the following configuration:

config :app, db: [
  pool: DBConnection.Poolboy,
  pool_size: 20,
  host: "localhost",
  database: "app"
]

Next, we need to start a Postgrex connection pool. Good place to do it is our application’s callback module, which in my case is app/lib/app/application.ex:

defmodule App.Application do
  use Application

  def start(_type, _args) do
    children = [
      {Postgrex, Keyword.put(Application.get_env(:app, :db), :name, DB)}
    ]

    opts = [strategy: :one_for_one, name: App.Supervisor]
    Supervisor.start_link(children, opts)
  end
end

I am adding the :name parameter for my connection pool here so no one is able to mess it up by editing config. We will reference our Postgrex connection pool with DB instead of it’s pid now on if we want to make a query.

Restart the app in iex and let’s try it out:

iex(31)> Postgrex.query!(DB, "SELECT * FROM users WHERE id = $1", [1], [pool: DBConnection.Poolboy])

%Postgrex.Result{
  columns: ["id", "email"],
  command: :select,
  connection_id: 4917,
  num_rows: 1,
  rows: [[1, "hubert.lepicki@amberbit.com"]]
}

Notice that we need to specify again that we are using DBConnection.Poolboy here when querying, a minor inconvenience.

If you run the same command multiple times, you will notice that the connection_id changes randomly. There will be 20 connection_ids in use as this is what we specified in configuration.

Mariaex

For Mariaex, we need to include it in the deps:

def deps() do
  [
    {:mariaex, "~> 0.8.2"},
    {:poison, "~> 3.1"},
    {:poolboy, "1.5.1"}
    ...
  ]
end

Mariaex sticks to default to Poison as JSON library, I think you can replace it with jason but it is not something I did try doing. So for now we will stick with default. You need Poolboy as well if you want to do connection pooling.

Right now you can start the connection and do some querying:

iex(1)> {:ok, pid} = Mariaex.start_link(username: "app", password: "app", database: "app")
{:ok, #PID<0.352.0>}
iex(2)> Mariaex.query(pid, "SELECT * FROM users")
{:ok,
 %Mariaex.Result{
   columns: ["id", "email"],
   connection_id: #PID<0.352.0>,
   last_insert_id: nil,
   num_rows: 1,
   rows: [[1, "hubert.lepicki@amberbit.com"]]
 }}

For connection pooling support we will do precisely the same as we did with Postgrex: create configuration in our config.exs and start supervised connection pool from application callback module:

# config/config.exs
config :app, db: [
  pool: DBConnection.Poolboy,
  pool_size: 20,
  host: "localhost",
  database: "app",
  username: "app",
  password: "app"
]

defmodule App.Application do
  use Application

  def start(_type, _args) do
    children = [
      {Mariaex, Keyword.put(Application.get_env(:app, :db), :name, DB)}
    ]

    opts = [strategy: :one_for_one, name: App.Supervisor]
    Supervisor.start_link(children, opts)
  end
end

Now we can query our database using the connection pool and database name DB instead of pid:

iex(1)> Mariaex.query!(DB, "SELECT * FROM users", [], [pool: DBConnection.Poolboy])
%Mariaex.Result{
  columns: ["id", "email"],
  connection_id: #PID<0.240.0>,
  last_insert_id: nil,
  num_rows: 1,
  rows: [[1, "hubert.lepicki@amberbit.com"]]
}

Now all you have to do is to enjoy your raw SQL responsibly!

Post by Hubert Łępicki

Hubert is partner at AmberBit. Rails, Elixir and functional programming are his areas of expertise.