Phoenix で生 SQL を使って MariaDB に問い合わせる

Phoenix には Ecto と呼ばれるデータベース操作ライブラリがあり、PostgreSQL や MySQL（MariaDB）、SQLite といったものに対応しています。

Ecto を使うと定義したモデルを元に簡単に（SQL を書かずに）CRUD 操作ができるようなのですが、今回はあえて生の SQL を発行して MariaDB に問い合わせることをしたいと思います。

Phoenix アプリケーションの作成についてはコチラを参照してください。

なぜ生 SQL なのか

Ecto はモデル定義さえすれば、それを使った CRUD 操作やテーブルの作成、果ては JSON API や HTML までも生成してくれる優れものです。
ただ、自分の経験上、DB 操作を伴う大規模な WebAPI を開発していくと、以下のようなケースに遭遇し、どうしても生 SQL を書きたくなることが多いです。

• レコード数が増加するにつれパフォーマンスが低下し、SQL を細かくチューニングする必要がでてくる
• データベースが提供するコアな関数が使いたくなってくる
• テーブルのリレーションが複雑化し、フレームワークが提供する CRUD 操作では対応しきれなくなってくる
• 句が複雑化するにつれ、DSL の DSL という存在にイラ立ちを覚え始める

もちろん、フレームワークの範疇に収まるようにテーブルを設計していくのがベストではあるのですが、どうにもこうにもならなくなっていくのが世の常でもあります。
保険の意味も込めて、生 SQL も書けるようにしておきたいところです。

下準備: MariaDB をセットアップする

今回はローカルで MariaDB を起動させて使います。
インストールされていない場合は、公式サイトからダウンロードするか、Mac であれば homebrew を使ってインストールします。
（ちなみに執筆時点での最新安定バージョンは 10.0.21 です。）

インストールが完了したら、MariaDB サーバを起動して、ターミナルから操作していきます。
まずは phoenix_sample というデータベースを作成しましょう。

MariaDB [(none)]> CREATE DATABASE phoenix_sample;

次にテーブルを作成します。
今回は ipa_t というテーブルで IPA（インディア・ペールエール）の種類を管理してみます。

MariaDB [(none)]> use phoenix_sample
Database changed
MariaDB [phoenix_sample]> CREATE TABLE ipa_t (
    ->   id INT AUTO_INCREMENT,
    ->   name varchar(256) NOT NULL,
    ->   abv FLOAT NOT NULL,
    ->   PRIMARY KEY (id)
    -> );

以上で下準備は完了です。

Phoenix アプリケーションをセットアップする

mariadb_sample というアプリケーションを作成します。
オプションに --database mysql をつけることで、利用DB がデフォルトの PostgreSQL から MySQL（MariaDB）に変更されます。

$ mix phoenix.new mariadb_sample --database mysql

mix.exs に MariaDB 接続用ライブラリ mariaex が追加されていることが確認できます。

mix.exs

defmodule MariadbSample.Mixfile do
  ...
  defp deps do
    [{:phoenix, "~> 0.17"},
     {:phoenix_ecto, "~> 1.1"},
     {:mariaex, ">= 0.0.0"},
     {:phoenix_html, "~> 2.1"},
     {:phoenix_live_reload, "~> 1.0", only: :dev},
     {:cowboy, "~> 1.0"}]
  end
end

DB 接続情報は config/dev.exs というファイルに記載されています。
database の名前をデフォルトの mariadb_sample_dev から phoenix_sample に変えておきましょう。

dev.exs

...
# Configure your database
config :mariadb_sample, MariadbSample.Repo,
  adapter: Ecto.Adapters.MySQL,
  username: "root",
  password: "",
#  database: "mariadb_sample_dev",
  database: "phoenix_sample",
  pool_size: 10

以上でアプリケーションの設定は完了です。

Insert と Select のエンドポイントを作成する

IPA の情報を Insert する口と Select する口を作ります。
web/router.ex に以下のスコープを追加します。

router.ex

defmodule MariadbSample.Router do
  ...
  scope "/ipa", MariadbSample do
    pipe_through :api

    get "/", MariadbSample, :select_all
    get "/:id", MariadbSample, :select
    post "/", MariadbSample, :insert
  end
  ...
end

次に、web/controllers/page_controller.ex に各エンドポイントを実装します。

page_controller.ex

defmodule MariadbSample.PageController do
  ...
  def to_column_map(columns, rows) do
    Enum.map(rows, fn row -> Enum.into(List.zip([columns, row]), %{}) end)
  end

  def select_all(conn, _params) do
    case Ecto.Adapters.SQL.query(MariadbSample.Repo, "SELECT id, name, abv from ipa_t", []) do
      {:ok, result} -> json conn, to_column_map(result[:columns], result[:rows])
      {:error, e} -> json conn, %{error: e}
    end
  end

  def select(conn, %{"id" => id}) do
    case Ecto.Adapters.SQL.query(MariadbSample.Repo, "SELECT id, name, abv from ipa_t where id = ?", [id]) do
      {:ok, result} -> json conn, to_column_map(result[:columns], result[:rows])
      {:error, e} -> json conn, %{error: e}
    end
  end

  def insert(conn, _params) do
    {:ok, data, _conn_details} = Plug.Conn.read_body(conn)
    posted = Poison.Parser.parse!(data)
    case Ecto.Adapters.SQL.query(MariadbSample.Repo, "INSERT INTO ipa_t(name, abv) VALUE (?, ?)", [posted["name"], posted["abv"]]) do
      {:ok, result} -> json conn, %{succeed: 1}
      {:error, e} -> json conn, %{error: e}
    end
    json conn, nil
  end
end

Select 系エンドポイントで発行している SQL は極めて単純で、select_all では全件取得、select では id を指定した検索を行います。

Ecto.Adapters.SQL.query/3 が生 SQL を発行する関数で、第1引数に Repo、第2引数に SQL（プリペアドステートメント）、第3引数にバインドするパラメータのリストを指定します。
返却は、成功したら {:ok, ...}、失敗したら {:error, ...} という形になっているので、case で処理を分岐させています。

成功時に呼び出される to_column_map/2 という関数が定義されていますが、これは「カラム名リスト」と「値リスト」の２つのリストを合体させてマップにするものです。
内部で呼ばれている各関数の働きは以下のような感じです。

iex(31)> keys = ["a", "b", "c"]
["a", "b", "c"]
iex(32)> values = ["1", "2,", "3"]
["1", "2,", "3"]
iex(33)> zipped = List.zip([keys, values])
[{"a", "1"}, {"b", "2,"}, {"c", "3"}]
iex(34)> Enum.into(zipped, %{})
%{"a" => "1", "b" => "2,", "c" => "3"}

組み込み関数でここまでできるのは、さすがモダン言語という感じがしますね。

Insert エンドポイントで発行されている SQL も、特に変わったことはしていません。
一点、今回は POST リクエストの body に詰められた JSON をパースして Insert 用のデータを作成しています。
（unofficial っぽいですが）Phoenix では {:ok, data, _conn_details} = Plug.Conn.read_body(conn) と書くだけで簡単に body の文字列を取得できますので、あとはそれを Poison を使ってパースしています。

例外処理を一切無視した状態ではありますが、Ecto を使った生 SQL 発行の実装はこんな感じになります。

API を叩いてみる

早速作成した API を叩いてみましょう。
まずは DHC を使ってデータを POST してみます。

Send ボタンを押して {"succeed": 1} と返却されれば成功です。
Top 10 IPAs の情報をもとに、適当に 2,3 種類の IPA を登録しましょう。

データを入れ終わったら、検索してみます。
全件検索するとこんな感じです。

$ curl -s "http://localhost:4000/ipa/" | jq .
[
  {
    "abv": 5,
    "id": 1,
    "name": "Samuel Smith's India Ale"
  },
  {
    "abv": 5.599999904632568,
    "id": 2,
    "name": "Toasted Oak India Pale Ale"
  },
  {
    "abv": 8.699999809265137,
    "id": 3,
    "name": "DirtWolf Double IPA"
  }
]

何やら小数値がアヤシイ感じになってしまいましたが、だいたい期待通りの結果となりました。
次に ID 引きをするとこんな感じです。

$ curl -s "http://localhost:4000/ipa/2" | jq .
[
  {
    "abv": 5.599999904632568,
    "id": 2,
    "name": "Toasted Oak India Pale Ale"
  }
]

こちらも小数値以外は期待通りの挙動です。

感想

• Ecto にも（一応）生 SQL を実行する API があって、ちゃんと使えた
• ただし、小数値の挙動をみる限り型の考慮がアヤシイ
  • このままだと日付とかの扱いが面倒なことになりそう
  • なんとか手をうったほうがよい
• SQL と関係ないけど Request Body を取得するスニペットの挙動もアヤシイ
  • curl コマンドで JSON 食わせるとうまく動かなかった