Simple MySQL with Elixir
Tags: elixir, mysql
Published: 2019-12-17 13:23:08

---
title: Simple MySQL with Elixir
published: true
description: My quest for simplicity with elixir and databases
tags: elixir, mysql
---

# Harder than I thought

As with my [last post on elixir](https://dev.to/kyleparisi/a-quick-dive-in-elixir-3196), 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:

```elixir
{: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](https://elixirforum.com/t/how-to-return-a-structured-json-with-column-names-and-values-in-mariaex/14657) by John Smith.  Here is how you might use this idea along with the MyXQL library which ecto uses for mysql.

```elixir
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

```elixir
# 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

```elixir
"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:

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

# insert
%{id: id}

# update/delete
:ok
```