Simple MySQL with Elixir

Harder than I thought

As with my last post on elixir, I found the default database driver to be painful. Granted this might have been because I have been so trained into model based database interactions or even prepared query interactions. I'm still not a fan of the ecto system; it prevents me from being able to prototype my application before hooking up the datastore.

After a bit of Google research, I found a few options. Unfortunately, they were very sparse in detail. The confusing part is for a functional language, the library authors seem to want to mask this with fancy macros. For a beginner like myself, it makes mental adoption very hard.

I'm also not a fan of the Result struct that the library myxql provides. I just want the data from the database as a table. Instead you'll get something like:

{:ok,
 %MyXQL.Result{
   columns: nil,
   connection_id: 135386,
   last_insert_id: 0,
   num_rows: 1,
   num_warnings: 0,
   rows: nil
 }}

A simple solution

Thankfully I stumbled upon this post by John Smith. Here is how you might use this idea along with the MyXQL library which ecto uses for mysql.

defmodule DB do

  def paginate(query) do
    query = String.replace(query, ";", "") |> String.trim
    query <> " LIMIT 0,100;"
  end

  def paginate(query, page) do
    query = String.replace(query, ";", "") |> String.trim
    query <> " LIMIT #{page * 100},#{page * 100 + 100};"
  end

  def query(query, repo, params \\ []) do
    MyXQL.query(repo, query, params) |> to_maps
  end

  # Insert
  def to_maps({:ok, %MyXQL.Result{last_insert_id: id, columns: nil, rows: nil}}) when id > 0 do
    %{id: id}
  end

  # Update/Delete
  def to_maps({:ok, %MyXQL.Result{last_insert_id: 0, columns: nil, rows: nil}}) do
    :ok
  end

  # Select
  def to_maps({:ok, %MyXQL.Result{columns: columns, rows: rows}}) do
    Enum.map(rows, fn row ->
      columns
      |> Enum.zip(row)
      |> Enum.into(%{})
    end)
  end
end

How to use

Establish your connection

# if in interactive shell
iex> {:ok,:myapp_db} = MyXQL.start_link(username: "root", password; "bleepbloop", hostname: "localhost", database: "myapp")

# if using supervision tree
defmodule MyApp.Application do
  use Application

  def start(_type, _args) do
    children = [
      {MyXQL, username: "root", password: "bleepbloop", hostname: "localhost", database: "myapp", name: :myapp_db}
    ]

    Supervisor.start_link(children, opts)
  end
end

Use the library

"select * from user" |> DB.query(:myapp_db)
# LIMIT 0,100
"select * from user" |> DB.paginate |> DB.query(:myapp_db)
# LIMIT 100,200
"select * from user" |> DB.paginate(1) |> DB.query(:myapp_db)
# use parameters
"select * from user where id = ?" |> DB.query(:myapp_db, [1]) |> hd

The above queries all return a list of maps or a map which is easily converted into json:

# select:
[%{}, ...]
# or
%{}

# insert
%{id: id}

# update/delete
:ok