Livebook から Ecto を使い、 Phoenix での実装と同じ方法でデータベースを操作する #PostgreSQL

はじめに

Livebook はブラウザ上で Elixir や Erlang などのコードを実行し、結果を視覚化できるツールです

Python における Jupyter に相当します

Livebook のはじめ方はこちら

Elixir には Phoenix という非常に堅牢で便利な Web フレームワークが存在します

Phoenix では Ecto というモジュールを使用することで、様々なデータベースを効率的に操作できるようになっています

今回は Livebook 上で Ecto を使って、 Phoenix での実装と同じ方法でのデータベース操作を実行します

実装したノートブックはこちら

環境の準備

Livebook と PostgreSQL をローカル起動している場合はそのまま進めて問題ありません

コンテナで起動したい場合、こちらの記事を参考にしてください

ノートブックの作成

Livebook のホーム画面、右上の + New notebook をクリックします

新しいノートブックが開きます

セットアップ

Notebook dependencies and setup と書いてある枠（セットアップセル）をクリックし、その中に以下のコードを入力します

Mix.install([
  {:ecto, "~> 3.10"},
  {:ecto_sql, "~> 3.10"},
  {:jason, "~> 1.4"},
  {:kino, "~> 0.11.0"},
  {:postgrex, "~> 0.17.3"}
])

DB接続

DB接続用モジュールの作成

Ecto を使って DB に接続するためのモジュール Repo を作成します

defmodule Repo do
  use Ecto.Repo,
    otp_app: :my_notebook,
    adapter: Ecto.Adapters.Postgres
end

adapter に Ecto.Adapters.Postgres を指定することにより、 PostgreSQL に接続できるようにしています

秘密情報の設定

DB接続のパスワードは秘密情報なので、 Livebook の秘密情報に登録しておきます

Livebook の左メニュー南京錠🔒のアイコンをクリックすると、 SECRETS のメニューが表示されます

+ New secrets をクリックしてください

表示されたモーダルの Name に DB_PASSWORD 、 Value にパスワードの値を入力し、 + Add をクリックします

これによって、 DB のパスワードは LB_DB_PASSWORD という環境変数に格納されました

Repo を子プロセスとして起動し、 DB に接続します

opts = [
  hostname: "postgres_for_livebook",
  port: 5432,
  username: "postgres",
  password: System.fetch_env!("LB_DB_PASSWORD"),
  database: "postgres"
]

Kino.start_child({Repo, opts})

{:ok, #PID<0.2572.0>} というような結果が表示されれば接続できています

マイグレーション（テーブル作成）

Ecto　ではマイグレーションによってテーブルの作成、変更を実行します

Phoenix プロジェクトの場合、 mix setup 実行時に自動実行され、データベースが最新の定義に更新される仕組みです

マイグレーションの定義

マイグレーションを以下のように定義します

このマイグレーションにより、 team_member というテーブルが指定した各列、型で作成されます

また、個別に指定しなくても以下の列が生成されます

id: bigint 型（主キーとして設定される）
inserted_at: タイムゾーンなしの timestamp 型（timestamps() の指定によるもの）
updated_at: タイムゾーンなしの timestamp 型（timestamps() の指定によるもの）

defmodule Migrations.CreateTeamMemberTable do
  use Ecto.Migration

  def change do
    create table(:team_member) do
      add(:name, :string)
      add(:age, :integer)
      add(:weight, :float)
      add(:has_license, :boolean)
      add(:hash, :bit, size: 8)
      add(:languages, {:array, :string})
      add(:skil_level, {:map, :integer})
      add(:salary, :decimal)
      add(:date_of_birth, :date)
      add(:starting_time_of_work, :time)

      timestamps()
    end
  end
end

マイグレーションの適用

定義したマイグレーションを DB に適用します

Ecto.Migrator.up(Repo, 1, Migrations.CreateTeamMemberTable)

実行すると、以下のように結果が表示されます

04:04:43.037 [info] == Running 1 Migrations.CreateTeamMemberTable.change/0 forward

04:04:43.043 [info] create table team_member

04:04:43.066 [info] == Migrated 1 in 0.0s

:ok

マイグレーション結果の確認

psql で DB に接続し、マイグレーション結果を確認しましょう

ローカルのターミナルから接続する場合、以下のコマンドを実行します

psql --host=localhost --username=postgres postgres

接続した DB 内で以下のコマンドを実行します

\dt

すると、現在のテーブル一覧が表示されます

               List of relations
 Schema |       Name        | Type  |  Owner
--------+-------------------+-------+----------
 public | schema_migrations | table | postgres
 public | team_member       | table | postgres
(2 rows)

マイグレーションで定義した team_member テーブルと、マイグレーションの状態を管理するための schema_migrations テーブルができています

以下のコマンドで team_member テーブルの定義を確認しましょう

\d team_member

結果は以下のようになります

                                               Table "public.team_member"
        Column         |              Type              | Collation | Nullable |                 Default
-----------------------+--------------------------------+-----------+----------+-----------------------------------------
 id                    | bigint                         |           | not null | nextval('team_member_id_seq'::regclass)
 name                  | character varying(255)         |           |          |
 age                   | integer                        |           |          |
 weight                | double precision               |           |          |
 has_license           | boolean                        |           |          |
 hash                  | bit(8)                         |           |          |
 languages             | character varying(255)[]       |           |          |
 skil_level            | jsonb                          |           |          |
 salary                | numeric                        |           |          |
 date_of_birth         | date                           |           |          |
 starting_time_of_work | time(0) without time zone      |           |          |
 inserted_at           | timestamp(0) without time zone |           | not null |
 updated_at            | timestamp(0) without time zone |           | not null |
Indexes:
    "team_member_pkey" PRIMARY KEY, btree (id)

マイグレーションでの型指定と PostgreSQL での型が以下のように対応していることが分かります

マイグレーション	PostgreSQL
string	character varying(255)
integer	integer
float	double precision
boolean	boolean
bit	bit
array	[]
map	jsonb
decimal	numeric
date	date
time	time(0) without time zone

schema_migrations テーブルのデータを参照してみます

SELECT * FROM schema_migrations;

結果は以下のようになります

 version |     inserted_at
---------+---------------------
       1 | 2023-10-31 04:04:43
(1 row)

Ecto.Migrator.up の第2引数で指定していた 1 が version の値になっています

Ecto のマイグレーションでは、このテーブルのデータを参照し、どのマイグレーションが実行済かを把握しています

例えばこの状態でもう一度 Ecto.Migrator.up(Repo, 1, Migrations.CreateTeamMemberTable) を実行しても、 version = 1 のマイグレーションは実行済なのでスキップされます

もう一度マイグレーションを適用したい場合、当該レコードを削除する必要があります

クエリの実行

スキーマの定義

Ecto ではスキーマにデータを格納して操作します

以下のコードで team_member テーブルのスキーマを定義します

changeset 関数により、与えられたデータからテーブルに存在する列だけを抽出し、必須項目のチェックもしています

defmodule TeamMember do
  use Ecto.Schema
  import Ecto.Changeset

  schema "team_member" do
    field(:name, :string)
    field(:age, :integer)
    field(:weight, :float)
    field(:has_license, :boolean)
    field(:hash, :binary)
    field(:languages, {:array, :string})
    field(:skil_level, {:map, :integer})
    field(:salary, :decimal)
    field(:date_of_birth, :date)
    field(:starting_time_of_work, :time)

    timestamps()
  end

  def changeset(team_member, attrs) do
    team_member
    |> cast(attrs, [
      :name,
      :age,
      :weight,
      :has_license,
      :hash,
      :languages,
      :skil_level,
      :salary,
      :date_of_birth,
      :starting_time_of_work
    ])
    |> validate_required([:name])
  end
end

データの追加

データの追加は以下のように実行します

%TeamMember{}
|> TeamMember.changeset(%{
  name: "Alice",
  age: 20,
  weight: 60.0,
  has_license: true,
  hash: <<0b11111111>>,
  languages: ["Japanese", "English"],
  skil_level: %{frontend: 5, backend: 3},
  salary: 1000_000,
  date_of_birth: ~D[2000-01-01],
  starting_time_of_work: ~T[08:30:00.0010]
})
|> Repo.insert()

実行結果は以下のようになります

ログ

04:37:51.908 [debug] QUERY OK db=10.1ms queue=2.9ms idle=1605.6ms
INSERT INTO "team_member" ("name","hash","age","weight","has_license","languages","skil_level","salary","date_of_birth","starting_time_of_work","inserted_at","updated_at") VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12) RETURNING "id" ["Alice", <<255>>, 20, 60.0, true, ["Japanese", "English"], %{frontend: 5, backend: 3}, Decimal.new("1000000"), ~D[2000-01-01], ~T[08:30:00], ~N[2023-10-31 04:37:51], ~N[2023-10-31 04:37:51]]

実行結果

{:ok,
 %TeamMember{
   __meta__: #Ecto.Schema.Metadata<:loaded, "team_member">,
   id: 1,
   name: "Alice",
   age: 20,
   weight: 60.0,
   has_license: true,
   hash: <<255>>,
   languages: ["Japanese", "English"],
   skil_level: %{frontend: 5, backend: 3},
   salary: Decimal.new("1000000"),
   date_of_birth: ~D[2000-01-01],
   starting_time_of_work: ~T[08:30:00],
   inserted_at: ~N[2023-10-31 04:37:51],
   updated_at: ~N[2023-10-31 04:37:51]
 }}

ログを見ると、実際に DB に発行された SQL を確認することができます

以下のようにして複数レコードを一気に追加することも可能です

now =
  NaiveDateTime.utc_now()
  |> NaiveDateTime.truncate(:second)

entries =
  [
    %{name: "Bob", age: 20},
    %{name: "John", age: 30},
    %{name: "Ryo", age: 39}
  ]
  |> Enum.map(fn attr ->
    Map.merge(attr, %{
      inserted_at: now,
      updated_at: now
    })
  end)

Repo.insert_all(TeamMember, entries)

結果は以下のようになります

ログ

05:11:15.876 [debug] QUERY OK db=2.8ms queue=4.1ms idle=279.6ms
INSERT INTO "team_member" ("name","age","inserted_at","updated_at") VALUES ($1,$2,$3,$4),($5,$6,$7,$8),($9,$10,$11,$12) ["Bob", 20, ~N[2023-10-31 05:11:15], ~N[2023-10-31 05:11:15], "John", 30, ~N[2023-10-31 05:11:15], ~N[2023-10-31 05:11:15], "Ryo", 39, ~N[2023-10-31 05:11:15], ~N[2023-10-31 05:11:15]]

実行結果

{3, nil}

データの取得

データを全て取得する場合、以下のようにします

team_members = Repo.all(TeamMember)

結果は以下のようになります

ログ

04:56:22.512 [debug] QUERY OK source="team_member" db=4.3ms queue=1.3ms idle=1282.0ms
SELECT t0."id", t0."name", t0."age", t0."weight", t0."has_license", t0."hash", t0."languages", t0."skil_level", t0."salary", t0."date_of_birth", t0."starting_time_of_work", t0."inserted_at", t0."updated_at" FROM "team_member" AS t0 []

実行結果

[
  %TeamMember{
    __meta__: #Ecto.Schema.Metadata<:loaded, "team_member">,
    id: 1,
    name: "Alice",
    age: 20,
    weight: 60.0,
    has_license: true,
    hash: <<255>>,
    languages: ["Japanese", "English"],
    skil_level: %{"backend" => 3, "frontend" => 5},
    salary: Decimal.new("1000000"),
    date_of_birth: ~D[2000-01-01],
    starting_time_of_work: ~T[08:30:00],
    inserted_at: ~N[2023-10-31 04:37:51],
    updated_at: ~N[2023-10-31 04:37:51]
  },
  ...
]

データを見やすいようにテーブル表示します

ただし、 :hash だけはテーブル表示できないため項目を削除します

team_members
|> Enum.map(fn team_member ->
  Map.drop(team_member, [:hash])
end)
|> Kino.DataTable.new()

データの射影、選択をする場合、 Ecto.Query をインポートし、マクロを使います

import Ecto.Query

例えば、 age < 25 を条件として選択し、 name と age で射影する場合、以下のようなコードになります

Repo.all from tm in TeamMember,
  select: %{member_name: tm.name, member_age: tm.age},
  where: tm.age < 25

結果は以下のようになります

ログ

05:23:26.045 [debug] QUERY OK source="team_member" db=1.5ms queue=0.3ms idle=1622.0ms
SELECT t0."name", t0."age" FROM "team_member" AS t0 WHERE (t0."age" < 25) []

実行結果

[%{member_name: "Alice", member_age: 20}, %{member_name: "Bob", member_age: 20}]

データの更新

id = 2 のデータについて、 age と weight を更新する例です

TeamMember
|> Repo.get!(2)
|> TeamMember.changeset(%{
  age: 21,
  weight: 62.0
})
|> Repo.update()

ログ

05:35:37.953 [debug] QUERY OK source="team_member" db=5.7ms queue=0.1ms idle=713.6ms
SELECT t0."id", t0."name", t0."age", t0."weight", t0."has_license", t0."hash", t0."languages", t0."skil_level", t0."salary", t0."date_of_birth", t0."starting_time_of_work", t0."inserted_at", t0."updated_at" FROM "team_member" AS t0 WHERE (t0."id" = $1) [2]

05:35:37.964 [debug] QUERY OK db=10.5ms queue=0.1ms idle=718.0ms
UPDATE "team_member" SET "age" = $1, "weight" = $2, "updated_at" = $3 WHERE "id" = $4 [21, 62.0, ~N[2023-10-31 05:35:37], 2]

実行結果

{:ok,
 %TeamMember{
   __meta__: #Ecto.Schema.Metadata<:loaded, "team_member">,
   id: 2,
   name: "Bob",
   age: 21,
   weight: 62.0,
   has_license: nil,
   hash: nil,
   languages: nil,
   skil_level: nil,
   salary: nil,
   date_of_birth: nil,
   starting_time_of_work: nil,
   inserted_at: ~N[2023-10-31 05:35:23],
   updated_at: ~N[2023-10-31 05:35:37]
 }}

データの削除

name = 'Alice' のデータを削除する例です

TeamMember
|> Repo.get_by!(name: "Alice")
|> Repo.delete()

ログ

05:43:58.799 [debug] QUERY OK source="team_member" db=4.8ms queue=0.5ms idle=255.5ms
SELECT t0."id", t0."name", t0."age", t0."weight", t0."has_license", t0."hash", t0."languages", t0."skil_level", t0."salary", t0."date_of_birth", t0."starting_time_of_work", t0."inserted_at", t0."updated_at" FROM "team_member" AS t0 WHERE (t0."name" = $1) ["Alice"]

05:43:58.810 [debug] QUERY OK db=5.0ms queue=6.1ms idle=261.1ms
DELETE FROM "team_member" WHERE "id" = $1 [1]

実行結果

{:ok,
 %TeamMember{
   __meta__: #Ecto.Schema.Metadata<:deleted, "team_member">,
   id: 1,
   name: "Alice",
   age: 20,
   weight: 60.0,
   has_license: true,
   hash: <<255>>,
   languages: ["Japanese", "English"],
   skil_level: %{"backend" => 3, "frontend" => 5},
   salary: Decimal.new("1000000"),
   date_of_birth: ~D[2000-01-01],
   starting_time_of_work: ~T[08:30:00],
   inserted_at: ~N[2023-10-31 05:35:21],
   updated_at: ~N[2023-10-31 05:35:21]
 }}

まとめ

Livebook 上で Ecto によるデータベース操作を実行できました

Livebook はログや実行結果を見やすく表示できるため、検証や学習に向いています

Livebook を入り口として、 Elixir の学習を進めていきましょう