ElixirでPostgreSQLのテーブル情報を取得しカラムの検証をする

はじめに

目的
migrateで作ったDBのカラムが正しく作られているか検証をする
DBを作るにphx.newが楽なため今回はphx.serverを立ち上げませんが、phx.newを使います

この記事の実行環境

$ elixir -v
Erlang/OTP 24 [erts-12.0] [source] [64-bit] [smp:8:8] [ds:8:8:10] [async-threads:1] [jit]

Elixir 1.12.2 (compiled with Erlang/OTP 22)

$ mix phx.new -v
Phoenix installer v1.6.0

$ psql
psql (12.8 (Ubuntu 12.8-0ubuntu0.20.04.1))
Type "help" for help.

プロジェクト作成

$ mix phx.new sample
* creating sample/config/config.exs
* creating sample/config/dev.exs
        〜  中略　〜
* creating sample/priv/static/images/phoenix.png
* creating sample/priv/static/favicon.ico

Fetch and install dependencies? [Yn] y

* running mix deps.get
* running mix deps.compile

We are almost there! The following steps are missing:

    $ cd sample

Then configure your database in config/dev.exs and run:

    $ mix ecto.create

Start your Phoenix app with:

    $ mix phx.server

You can also run your app inside IEx (Interactive Elixir) as:

    $ iex -S mix phx.server

ビルド〜テスト

$ cd sample
$ mix ecto.create
Compiling 14 files (.ex)
Generated sample app
The database for Sample.Repo has been created
$ mix test
==> connection
Compiling 1 file (.ex)
Generated connection app
==> gettext
Compiling 1 file (.erl)
Compiling 20 files (.ex)
Generated gettext app
        〜  中略　〜
Generated sample app
...

Finished in 0.04 seconds (0.03s async, 0.01s sync)
3 tests, 0 failures

Randomized with seed 704118

実験用のテーブルを作る

$ mix phx.gen.context Accounts User users name:string
* creating lib/sample/accounts/user.ex
* creating priv/repo/migrations/20211011073708_create_users.exs
* creating lib/sample/accounts.ex
* injecting lib/sample/accounts.ex
* creating test/sample/accounts_test.exs
* injecting test/sample/accounts_test.exs
* creating test/support/fixtures/accounts_fixtures.ex
* injecting test/support/fixtures/accounts_fixtures.ex

Remember to update your repository by running migrations:

    $ mix ecto.migrate

$  mix ecto.migrate
Compiling 2 files (.ex)
Generated sample app

16:38:25.004 [info]  == Running 20211011073708 Sample.Repo.Migrations.CreateUsers.change/0 forward

16:38:25.011 [info]  create table users

16:38:25.020 [info]  == Migrated 20211011073708 in 0.0s

検証するプログラムを作成する

test/priv/repo/migrations/20211011073708_create_users_test.exs

defmodule CreateUsersTest do
  use Sample.DataCase

  test "create users test" do
    [table: "users", col: "name", type: "varchar", notnull: :false, length: 255]
    |> assert_col()
  end

  defp assert_col(param) do
    {:ok, %{rows: result}} = Ecto.Adapters.SQL.query(Sample.Repo, create_sql(), Keyword.values(param))

    result
    |> List.first()
    |> List.first()
    |> assert()
  end

  defp create_sql() do
    """
    SELECT count(*) = 1 AS result
    FROM pg_attribute AS a
        INNER JOIN pg_type AS b on a.atttypid = b.oid
        INNER JOIN pg_class AS c on a.attrelid = c.oid
    WHERE c.relname = $1
        AND a.attname = $2
        AND b.typname = $3
        AND a.attnotnull = $4::boolean
        AND a.atttypmod =  $5::integer + 4;

    """
  end
end

実行

正常に終了します

$ mix test
............

Finished in 0.4 seconds (0.2s async, 0.2s sync)
12 tests, 0 failures

Randomized with seed 468455

エラーを発生させる

col:name をcol:name1に変更します

test/priv/repo/migrations/20211011073708_create_users_test.exs

  #〜  中略　〜
  test "create users test" do
    %{table: :users, col: :name1, type: :varchar, notnull: :FALSE, length: 255}

    |> assert_col()
  end
 #〜  中略　〜

エラーになりました

$ mix test
..........

  1) test create users test (CreateUsersTest)
     test/priv/repo/migrations/20211011073708_create_users_test.exs:4
     Expected truthy, got false
     code: assert List.first(List.first(ret))
     arguments:

         # 1
         [false]

     stacktrace:
       test/priv/repo/migrations/20211011073708_create_users_test.exs:16: CreateUsersTest.assert_col/1
       test/priv/repo/migrations/20211011073708_create_users_test.exs:6: (test)

.

Finished in 0.4 seconds (0.2s async, 0.2s sync)
12 tests, 1 failure

Randomized with seed 542539