Elixir Without Ecto - Dynamic Queries

In the last post, we looked at the basic mechanism for using elixir without ecto. One thing is obviously missing from that post: support for dynamic queries. For example, to get users based on their status, we can do:

MyApp.DB.maps!("select * from users where status = $1", [user_status])

What about getting all users when user_status is nil? We'll need to be able to dynamically build our select statement while ensuring that we protect against SQL injections. What we're going to do is leverage our knowledge of iolists to build a query builder. Our end goal is to be able to write code like this:

q = Query.new()
|> Query.select("*")
|> Query.from("users")

q = case user_status == nil do
  true -> q
  false -> Query.where(q, "status", :eq, user_status)
end

DB.maps!(q)

This is different than Ecto in that its only purpose is to help us safely generate SQL, we're not concerned with mapping, migrations, models, callbacks or anything else.

The goal of this post isn't to provide a complete solution. If you're looking for that, you might want to consider Mobieus, which I've not used, but looks solid. The goal is to let you know that it's ok to use Elixir and Phoenix without Ecto. In fact, I think it's a lot better. The only thing you'll probably need is a query builder, and you can either use this post as a rough guide or use an existing tool.

We're going to build this up slowly, starting with selecting columns from tables:

defmodule A.DB.Query do
  # same as doing alias A.DB.Query
  alias __MODULE__

  defstruct [
    :from,
    :select
  ]

  def new() do
    %Query{
      from: [],
      select: nil,
    }
  end

  # first select we add shouldn't be prefixed with a comma
  def select(%{select: nil} = q, select), do: %Query{q | select: [select]}

  # subsequent selects should be prefixed with a comma
  def select(q, select), do: %Query{q | select: [q.select, ", ", select]}

  def from(q, from), do: %Query{q | from: [q.from, " ", from]}

  def to_sql(q) do
    ["select ", q.select || "*", " from", q.from]
  end
end

The above skeleton captures the approach that we'll be taking; we just need to add more of the same. If you want you can extend this to suit your needs. For example, select/2 could optionally take a list of columns, or instead of relying on multiple calls to from/2 you could add a join/2 (and join_left/2, etc). Make it your own.

For completeness here's a test that covers some of the above code:

test "builds a simple query" do
  Query.new()
  |> Query.from("users")
  |> assert_query("select * from users")

  Query.new()
  |> Query.select("u.id, c.name")
  |> Query.select("u.created")
  |> Query.from("users u")
  |> Query.from("join clients c on u.client_id = c.id")
  |> assert_query("""
    select u.id, c.name, u.created
    from users u join clients c on u.client_id = c.id
  """)
end

defp assert_query(q, expected) do
  # normalize the spaces
  actual_sql = sql
  |> :erlang.iolist_to_binary()
  |> String.replace(~r/\s+/, " ")

  # normalize the spaces
  expected_sql = expected_sql
  |> String.replace(~r/\s+/, " ")
  |> String.trim()

  assert actual_sql == expected_sql
end

Lets add support for group by. The simplest thing we can do is:

defmodule A.DB.Query do
  alias __MODULE__

  defstruct [
    # ... existing fields ...
    :group
  ]

  def new() do
    %Query{
      # ... existing fields ...
      group: nil
    }
  end

  # ... existing select/2 and from/2 ...

  def group(q, group), do: %Query{q | group: group}

  def to_sql(q) do
    sql = ["select ", q.select || "*", " from", q.from]
    case q.group do
      nil -> sql
      group -> [sql, " group by ", group]
    end
  end

Along with a basic sanity test:

test "group by" do
  Query.new()
  |> Query.from("x")
  |> Query.group("id")
  |> assert_query("select * from x group by id")

  Query.new()
  |> Query.from("x")
  |> Query.group("1, id having count(*) > 1")
  |> assert_query("select * from x group by 1, id having count(*) > 1")
end

Our grouping API is bareboned: group/2 is meant to be called once with [optionally] a having clause. As-is, it's pretty much as close to raw SQL as we can make it. But if we want, or need, we can make the API slightly more abstract, while remaining minimal and explicit. Consider this expanded implementation:

# first group we add shouldn't be prefixed with a comma
def group(%{group: nil} = q, group), do: %Query{q | group: [group]}

# subsequent groups should be prefixed with a comma
def group(q, group), do: %Query{q | group: [q.group, ", ", group]}

def having(q, having), do: %Query{q | having: having}

We could spend more time tweaking this API and supporting various use-cases. At the very least, we'd need to add support for order by, limit and offset. But that largely comes down to more of what we've already done.

To make this useful, we need to support the where clause, and more generally, parameters.

defmodule A.DB.Query do
  alias __MODULE__

  defstruct [
    # ... existing fields ...
    :op,
    :where,
    :values,
    :value_count
  ]

  def new() do
    %Query{
      # ... existing fields ...
      op: " and "
      where: nil,
      values: [],
      value_count: 0,
    }
  end

  # ... existing select/2, from/2, group/2 ...

  # for things like Query.where(q, "deleted is null")
  def where(q, predicate), do: add_where(q, predicate)

  def where(q, left, :eq, value) do
    {q, placeholder} = add_value(q, value)
    add_where(q, [left, " = ", placeholder])
  end

  def where(q, left, :ne, value) do
    {q, placeholder} = add_value(q, value)
    add_where(q, [left, " != ", placeholder])
  end

  def where(q, left, :gt, value) do
    {q, placeholder} = add_value(q, value)
    add_where(q, [left, " > ", placeholder])
  end

  def where(q, left, :lt, value) do
    {q, placeholder} = add_value(q, value)
    add_where(q, [left, " < ", placeholder])
  end

  # Adds the value to our query and returns the placeholder (e.g. $1) to use
  # in the SQL. We're only using this for where, but it can be used in any
  # part of the query where we want to inject a value/placeholder.
  def add_value(q, value) do
    count = q.value_count + 1
    q = %Query{q | values: [value | q.values], value_count: count}
    {q, placeholder(count)}
  end

  defp add_where(%{where: nil} = q, predicate), do: %Query{q | where: [predicate]}
  defp add_where(q, predicate), do: %Query{q | where: [q.where, q.op, predicate]}

  def to_sql(q) do
    sql = ["select ", q.select || "*", " from", q.from]

    sql = case q.where do
      nil -> sql
      where -> [sql, " where ", where]
    end

    sql = case q.group do
      nil -> sql
      group -> [sql, " group by ", group]
    end

    {sql, Enum.reverse(q.values)}
  end

  # pre-generate 100 placehoders, so we end up with;
  #   defp placeholder(1), do: "$1"
  #   defp placeholder(2), do: "$2"
  #   defp placeholder(100), do: "$100"
  for i <- 1..100 do
    s = "$#{i}"
    defp placeholder(unquote(i)), do: unquote(s)
  end

  # fall back to dynamically creating a placeholder
  defp placeholder(i), do: "$#{i}"

We need to add a lot of where/3 functions in order to support operations like in, any, gte, like, etc. But they all work the same: using add_value/2 to add the value to query.values and getting a placeholder (e.g. $1) in return to place into the SQL.

Another test:

test "where" do
  Query.new()
  |> Query.from("x")
  |> Query.where("status", :eq, "normal")
  |> assert_query("select * from x where status = $1", ["normal"])

  Query.new()
  |> Query.from("x")
  |> Query.where("deleted is null")
  |> Query.where("type", :ne, "monkey")
  |> Query.where("power", :gt, 9000)
  |> assert_query("""
    select * from x
    where deleted is null
      and type != $1
      and power > $2
    """, ["monkey", 9000])
end

# extend this to also assert the expected values
defp assert_query(q, expected_sql, expected_values \\ []) do
  {sql, values} = Query.to_sql(q)

  # normalize the spaces
  actual_sql = sql
  |> :erlang.iolist_to_binary()
  |> String.replace(~r/\s+/, " ")

  # normalize the spaces
  expected_sql = expected_sql
  |> String.replace(~r/\s+/, " ")
  |> String.trim()

  assert actual_sql == expected_sql
  assert values == expected_values
end

What's nice about add_value/2 is that it can be used in any part of query. Obviously we'd want to improve the API to make this more intuitive, but as-is, the following test passes:

q = Query.new()
{q, p1} = Query.add_value(q, true)

q
|> Query.select(p1)
|> Query.from("x")
|> assert_query("select $1 from x", [true])

The last feature we're going to add is some basic grouping for or and and. As you've probably noticed we currently join all where statements with and. Let's see how we can expand this (there's a lot of ways to do this):

defmodule A.DB.Query do
  alias __MODULE__

  # ... existing defstruct & functions ...

  def where_or(q, fun), do: where_fun(q, fun, " or ")
  def where_and(q, fun), do: where_fun(q, fun, " and ")

  defp where_fun(q, fun, op) do
    restore_op = q.op
    existing = case q.where do
      nil -> []
      where -> [where, q.op]
    end

    q = fun.(%Query{q | op: op, where: nil})
    %Query{q | op: restore_op, where: [existing, "(", q.where, ")"]}
  end

  ...
end

The above code captures our existing where, changes the op, executes the function, and then merges the newly generated where with the original.

And our test:

test "filter groups" do
  Query.new()
  |> Query.from("x")
  |> Query.where("a", :eq, 1)
  |> Query.where_or(fn q ->
    q
    |> Query.where("b", :lt, 2)
    |> Query.where("c", :gt, 3)
    |> Query.where_and(fn q ->
      q |> Query.where("d", :ne, 4) |> Query.where("e", :eq, 5)
    end)
  end)
  |> Query.where("f", :ne, 6)
  |> assert_query("""
    select * from x
    where a = $1
      and (b < $2 or c > $3 or (d != $4 and e = $5))
      and f != $6
    """, [1, 2, 3, 4, 5, 6])
end

Where you take the code next is up to you. Personally, I'd suggest you start with something similar to what we've explored so far and expand it as needed. The module is easy to test, so you should a robust safety net.

As I said earlier, you might want to look at Mobieus for something more than this quarter-baked solution. If you found this code intimidating, being comfortable with iolists both useful and important.

Elixir without Ecto isn't just possible, it's awesome. Don't be afraid.