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
Latest comments (0)