Elixir Advent Calendar 2021、昨日は一緒にpiyopiyo.exを運営する@kn339264で「Elixir/Phoenix入門者向けコミュニティpiyopiyo.exを立ち上げた話」でした
##はじめに
今回はPhoenix1.6でEctoを使ってみたいと思います。
Ectoとは簡単にElixirでデータベースを使うライブラリーです。
Phoenix1.6を使う理由としてはSQLite3を簡単に使うことができるからです。
今回はPhoenixのことについては触れません。
SQLの知識があれば、[debug]出力しているSQL文を確認すれば理解できると思います。
あえて説明を書きません。
SQLが読めることを前提に記述してます。
検証環境
cat /etc/os-release | grep VERSION=
VERSION="20.04.3 LTS (Focal Fossa)"
$ mix phx.new -v
Phoenix installer v1.6.0
$ iex
Erlang/OTP 24 [erts-12.0] [source] [64-bit] [smp:8:8] [ds:8:8:10] [async-threads:1] [jit]
Interactive Elixir (1.12.2) - press Ctrl+C to exit (type h() ENTER for help)
sqlite3が使えるプロジェクトを用意する
$ mix phx.new sample --database sqlite3
* creating sample/config/config.exs
:
* 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
##プロジェクトのフォルダーに移動してDBを初期化する
$ cd sample
$ mix ecto.create
Compiling 14 files (.ex)
Generated sample app
The database for Sample.Repo has been created
$ ls
README.md assets deps mix.exs priv test
_build config lib mix.lock sample_dev.db
##テーブルを作成する
$ mix phx.gen.context Users User users name:string
* creating lib/sample/users/user.ex
* creating priv/repo/migrations/20211112044236_create_users.exs
* creating lib/sample/users.ex
* injecting lib/sample/users.ex
* creating test/sample/users_test.exs
* injecting test/sample/users_test.exs
* creating test/support/fixtures/users_fixtures.ex
* injecting test/support/fixtures/users_fixtures.ex
Remember to update your repository by running migrations:
$ mix ecto.migrate
##マイグレーションを実行
$ mix ecto.migrate
Compiling 2 files (.ex)
Generated sample app
13:43:37.788 [info] == Running 20211112044236 Sample.Repo.Migrations.CreateUsers.change/0 forward
13:43:37.794 [info] create table users
13:43:37.795 [info] == Migrated 20211112044236 in 0.0s
##iexで起動
$ iex -S mix
Erlang/OTP 24 [erts-12.0] [source] [64-bit] [smp:8:8] [ds:8:8:10] [async-threads:1] [jit]
Interactive Elixir (1.12.2) - press Ctrl+C to exit (type h() ENTER for help)
##データを追加
iex(1)> import Ecto.Query, warn: false
iex(5)> %{name: "hoge"} |> Sample.Users.create_user()
[debug] QUERY OK db=1.5ms decode=1.1ms idle=1012.3ms
INSERT INTO "users" ("name","inserted_at","updated_at") VALUES (?,?,?) RETURNING "id" ["hoge", "2021-11-12T04:52:31", "2021-11-12T04:52:31"]
{:ok,
%Sample.Users.User{
__meta__: #Ecto.Schema.Metadata<:loaded, "users">,
id: 1,
inserted_at: ~N[2021-11-12 04:52:31],
name: "hoge",
updated_at: ~N[2021-11-12 04:52:31]
}}
##追加したデータを確認
iex(6)> Sample.Users.list_users()
[debug] QUERY OK source="users" db=0.1ms queue=0.1ms idle=137.4ms
SELECT u0."id", u0."name", u0."inserted_at", u0."updated_at" FROM "users" AS u0 []
[
%Sample.Users.User{
__meta__: #Ecto.Schema.Metadata<:loaded, "users">,
id: 1,
inserted_at: ~N[2021-11-12 04:52:31],
name: "hoge",
updated_at: ~N[2021-11-12 04:52:31]
}
]
iex(7)> %{name: "hoge2"} |> Sample.Users.create_user()
[debug] QUERY OK db=1.7ms queue=0.1ms idle=1278.7ms
INSERT INTO "users" ("name","inserted_at","updated_at") VALUES (?,?,?) RETURNING "id" ["hoge2", "2021-11-12T04:55:49", "2021-11-12T04:55:49"]
{:ok,
%Sample.Users.User{
__meta__: #Ecto.Schema.Metadata<:loaded, "users">,
id: 2,
inserted_at: ~N[2021-11-12 04:55:49],
name: "hoge2",
updated_at: ~N[2021-11-12 04:55:49]
}}
##さらにデータを追加する
iex(8)> Sample.Users.list_users()
[debug] QUERY OK source="users" db=0.6ms queue=0.1ms idle=1766.4ms
SELECT u0."id", u0."name", u0."inserted_at", u0."updated_at" FROM "users" AS u0 []
[
%Sample.Users.User{
__meta__: #Ecto.Schema.Metadata<:loaded, "users">,
id: 1,
inserted_at: ~N[2021-11-12 04:52:31],
name: "hoge",
updated_at: ~N[2021-11-12 04:52:31]
},
%Sample.Users.User{
__meta__: #Ecto.Schema.Metadata<:loaded, "users">,
id: 2,
inserted_at: ~N[2021-11-12 04:55:49],
name: "hoge2",
updated_at: ~N[2021-11-12 04:55:49]
}
]
##追加したデータをIDを指定して確認
iex(10)> Sample.Users.get_user!(1)
[debug] QUERY OK source="users" db=0.1ms queue=0.1ms idle=1622.1ms
SELECT u0."id", u0."name", u0."inserted_at", u0."updated_at" FROM "users" AS u0 WHERE (u0."id" = ?) [1]
%Sample.Users.User{
__meta__: #Ecto.Schema.Metadata<:loaded, "users">,
id: 1,
inserted_at: ~N[2021-11-12 04:52:31],
name: "hoge",
updated_at: ~N[2021-11-12 04:52:31]
}
iex(11)> Sample.Users.get_user!(2)
[debug] QUERY OK source="users" db=0.5ms queue=0.1ms idle=1281.2ms
SELECT u0."id", u0."name", u0."inserted_at", u0."updated_at" FROM "users" AS u0 WHERE (u0."id" = ?) [2]
%Sample.Users.User{
__meta__: #Ecto.Schema.Metadata<:loaded, "users">,
id: 2,
inserted_at: ~N[2021-11-12 04:55:49],
name: "hoge2",
updated_at: ~N[2021-11-12 04:55:49]
}
##自動生成されたソースを見てみよう
lib/sample/users.ex
defmodule Sample.Users.User do
use Ecto.Schema
import Ecto.Changeset
schema "users" do
field :name, :string
timestamps()
end
@doc false
def changeset(user, attrs) do
user
|> cast(attrs, [:name])
|> validate_required([:name])
end
end
lib/sample/users/user.ex
defmodule Sample.Users do
import Ecto.Query, warn: false
alias Sample.Repo
alias Sample.Users.User
def list_users do
Repo.all(User)
end
def get_user!(id), do: Repo.get!(User, id)
def create_user(attrs \\ %{}) do
%User{}
|> User.changeset(attrs)
|> Repo.insert()
end
def update_user(%User{} = user, attrs) do
user
|> User.changeset(attrs)
|> Repo.update()
end
def delete_user(%User{} = user) do
Repo.delete(user)
end
def change_user(%User{} = user, attrs \\ %{}) do
User.changeset(user, attrs)
end
end
##SQL風に使ってみる from |> Repo.all()
iex(15)> alias Sample.Repo
Sample.Repo
iex(16)> alias Sample.Users.User
Sample.Users.User
iex(17)> from(User) |> Repo.all()
[debug] QUERY OK source="users" db=0.8ms idle=1143.4ms
SELECT u0."id", u0."name", u0."inserted_at", u0."updated_at" FROM "users" AS u0 []
[
%Sample.Users.User{
__meta__: #Ecto.Schema.Metadata<:loaded, "users">,
id: 1,
inserted_at: ~N[2021-11-12 04:52:31],
name: "hoge",
updated_at: ~N[2021-11-12 04:52:31]
},
%Sample.Users.User{
__meta__: #Ecto.Schema.Metadata<:loaded, "users">,
id: 2,
inserted_at: ~N[2021-11-12 04:55:49],
name: "hoge2",
updated_at: ~N[2021-11-12 04:55:49]
}
]
##条件指定 where:
iex(18)> from(u in User, where: u.id == 1) |> Repo.all()
[debug] QUERY OK source="users" db=0.1ms queue=0.1ms idle=1797.3ms
SELECT u0."id", u0."name", u0."inserted_at", u0."updated_at" FROM "users" AS u0 WHERE (u0."id" = 1) []
[
%Sample.Users.User{
__meta__: #Ecto.Schema.Metadata<:loaded, "users">,
id: 1,
inserted_at: ~N[2021-11-12 04:52:31],
name: "hoge",
updated_at: ~N[2021-11-12 04:52:31]
}
]
##データを追加 Repo.insert()
iex(21)> attrs = %{name: "hoge3"}
iex(25)> %User{} |> User.changeset(attrs) |> Repo.insert()
[debug] QUERY OK db=0.3ms idle=1352.6ms
INSERT INTO "users" ("name","inserted_at","updated_at") VALUES (?,?,?) RETURNING "id" ["hoge3", "2021-11-12T05:37:24", "2021-11-12T05:37:24"]
{:ok,
%Sample.Users.User{
__meta__: #Ecto.Schema.Metadata<:loaded, "users">,
id: 3,
inserted_at: ~N[2021-11-12 05:37:24],
name: "hoge3",
updated_at: ~N[2021-11-12 05:37:24]
}}
##Elixir上で表現するテーブルの形式を確認
iex(26)>%User{}
%Sample.Users.User{
__meta__: #Ecto.Schema.Metadata<:built, "users">,
id: nil,
inserted_at: nil,
name: nil,
updated_at: nil
}
##出力項目を指定 select:
iex(34)> from(u in User, select: %{id: u.id, name: u.name}) |> Repo.all()
[debug] QUERY OK source="users" db=0.1ms idle=553.7ms
SELECT u0."id", u0."name" FROM "users" AS u0 []
[%{id: 1, name: "hoge"}, %{id: 2, name: "hoge2"}, %{id: 3, name: "hoge3"}]
##並び替え order_by:
iex(35)> from(u in User, order_by: [desc: u.id], select: %{id: u.id, name: u.name}) |> Repo.all()
[debug] QUERY OK source="users" db=0.1ms queue=0.1ms idle=1543.0ms
SELECT u0."id", u0."name" FROM "users" AS u0 ORDER BY u0."id" DESC []
[%{id: 3, name: "hoge3"}, %{id: 2, name: "hoge2"}, %{id: 1, name: "hoge"}]
iex(46)> from(u in User, order_by: [desc: u.name, asc: u.id ], select: %{id: u.id, name: u.name}) |> Repo.all()
[debug] QUERY OK source="users" db=0.2ms idle=1714.7ms
SELECT u0."id", u0."name" FROM "users" AS u0 ORDER BY u0."name" DESC, u0."id" []
[%{id: 3, name: "hoge3"}, %{id: 2, name: "hoge2"}, %{id: 1, name: "hoge"}]
##更新 Repo.update()
iex(54)> user = from(u in User, where: u.id == 3) |> Repo.one
[debug] QUERY OK source="users" db=0.6ms queue=0.2ms idle=1336.1ms
SELECT u0."id", u0."name", u0."inserted_at", u0."updated_at" FROM "users" AS u0 WHERE (u0."id" = 3) []
%Sample.Users.User{
__meta__: #Ecto.Schema.Metadata<:loaded, "users">,
id: 3,
inserted_at: ~N[2021-11-12 05:37:24],
name: "hoge3",
updated_at: ~N[2021-11-12 05:37:24]
}
iex(55)> attrs = %{name: "hoge"}
%{name: "hoge"}
iex(56)> user |> User.changeset(attrs) |> Repo.update()
[debug] QUERY OK db=1.6ms idle=1910.5ms
UPDATE "users" SET "name" = ?, "updated_at" = ? WHERE "id" = ? ["hoge", "2021-11-12T06:53:58", 3]
{:ok,
%Sample.Users.User{
__meta__: #Ecto.Schema.Metadata<:loaded, "users">,
id: 3,
inserted_at: ~N[2021-11-12 05:37:24],
name: "hoge",
updated_at: ~N[2021-11-12 06:53:58]
}}
iex(57)> user = from(u in User, where: u.id == 3) |> Repo.one
[debug] QUERY OK source="users" db=0.2ms idle=1763.3ms
SELECT u0."id", u0."name", u0."inserted_at", u0."updated_at" FROM "users" AS u0 WHERE (u0."id" = 3) []
%Sample.Users.User{
__meta__: #Ecto.Schema.Metadata<:loaded, "users">,
id: 3,
inserted_at: ~N[2021-11-12 05:37:24],
name: "hoge",
updated_at: ~N[2021-11-12 06:53:58]
}
##グループ化 group_by:
iex(61)> user = from(u in User, select: u.name) |> Repo.all()
[debug] QUERY OK source="users" db=0.1ms queue=0.1ms idle=1286.9ms
SELECT u0."name" FROM "users" AS u0 []
["hoge", "hoge2", "hoge"]
iex(62)> user = from(u in User, select: u.name, group_by: [u.name] ) |> Repo.all()
[debug] QUERY OK source="users" db=0.1ms queue=0.1ms idle=1795.9ms
SELECT u0."name" FROM "users" AS u0 GROUP BY u0."name" []
["hoge", "hoge2"]
iex(63)>
user = from(u in User, select: [u.name, count(u.name)] , group_by: [u.name] ) |> Repo.all()
[debug] QUERY OK source="users" db=0.1ms queue=0.1ms idle=1589.5ms
SELECT u0."name", count(u0."name") FROM "users" AS u0 GROUP BY u0."name" []
[["hoge", 2], ["hoge2", 1]]
##テーブル連結
###テーブル連結の為に新しいテーブルを準備
$ mix phx.gen.context Memos Memo memos text:string user_id:integer
* creating lib/sample/memos/memo.ex
* creating priv/repo/migrations/20211112080730_create_memos.exs
* creating lib/sample/memos.ex
* injecting lib/sample/memos.ex
* creating test/sample/memos_test.exs
* injecting test/sample/memos_test.exs
* creating test/support/fixtures/memos_fixtures.ex
* injecting test/support/fixtures/memos_fixtures.ex
Remember to update your repository by running migrations:
$ mix ecto.migrate
$ mix ecto.migrate
17:09:02.360 [info] == Running 20211112080730 Sample.Repo.Migrations.CreateMemos.change/0 forward
17:09:02.369 [info] create table memos
17:09:02.371 [info] == Migrated 20211112080730 in 0.0s
###自動生成されたソースを見てみよう
lib/sample/memos.ex
defmodule Sample.Memos do
@moduledoc """
The Memos context.
"""
import Ecto.Query, warn: false
alias Sample.Repo
alias Sample.Memos.Memo
@doc """
Returns the list of memos.
## Examples
iex> list_memos()
[%Memo{}, ...]
"""
def list_memos do
Repo.all(Memo)
end
@doc """
Gets a single memo.
Raises `Ecto.NoResultsError` if the Memo does not exist.
## Examples
iex> get_memo!(123)
%Memo{}
iex> get_memo!(456)
** (Ecto.NoResultsError)
"""
def get_memo!(id), do: Repo.get!(Memo, id)
@doc """
Creates a memo.
## Examples
iex> create_memo(%{field: value})
{:ok, %Memo{}}
iex> create_memo(%{field: bad_value})
{:error, %Ecto.Changeset{}}
"""
def create_memo(attrs \\ %{}) do
%Memo{}
|> Memo.changeset(attrs)
|> Repo.insert()
end
@doc """
Updates a memo.
## Examples
iex> update_memo(memo, %{field: new_value})
{:ok, %Memo{}}
iex> update_memo(memo, %{field: bad_value})
{:error, %Ecto.Changeset{}}
"""
def update_memo(%Memo{} = memo, attrs) do
memo
|> Memo.changeset(attrs)
|> Repo.update()
end
@doc """
Deletes a memo.
## Examples
iex> delete_memo(memo)
{:ok, %Memo{}}
iex> delete_memo(memo)
{:error, %Ecto.Changeset{}}
"""
def delete_memo(%Memo{} = memo) do
Repo.delete(memo)
end
@doc """
Returns an `%Ecto.Changeset{}` for tracking memo changes.
## Examples
iex> change_memo(memo)
%Ecto.Changeset{data: %Memo{}}
"""
def change_memo(%Memo{} = memo, attrs \\ %{}) do
Memo.changeset(memo, attrs)
end
end
lib/samole/memos/memo.ex
defmodule Sample.Memos.Memo do
use Ecto.Schema
import Ecto.Changeset
schema "memos" do
field :text, :string
field :user_id, :integer
timestamps()
end
@doc false
def changeset(memo, attrs) do
memo
|> cast(attrs, [:text, :user_id])
|> validate_required([:text, :user_id])
end
end
###iexで起動し新しく作ったテーブルの準備
iex -S mix
iex(1)> import Ecto.Query, warn: false
Ecto.Query
iex(2)> alias Sample.Repo
Sample.Repo
iex(3)> alias Sample.Memos.Memo
Sample.Memos.Memo
iex(4)> alias Sample.Memos
Sample.Memos
iex(5)> %{text: "Test memo", user_id: 1} |> Memos.create_memo()
[debug] QUERY OK db=6.9ms decode=2.0ms idle=1719.5ms
INSERT INTO "memos" ("text","user_id","inserted_at","updated_at") VALUES (?,?,?,?) RETURNING "id" ["Test memo", 1, "2021-11-12T08:14:12", "2021-11-12T08:14:12"]
{:ok,
%Sample.Memos.Memo{
__meta__: #Ecto.Schema.Metadata<:loaded, "memos">,
id: 1,
inserted_at: ~N[2021-11-12 08:14:12],
text: "Test memo",
updated_at: ~N[2021-11-12 08:14:12],
user_id: 1
}}
iex(6)> %{text: "Test memo hogehoge", user_id: 2} |> Memos.create_memo()
[debug] QUERY OK db=0.2ms idle=1980.7ms
INSERT INTO "memos" ("text","user_id","inserted_at","updated_at") VALUES (?,?,?,?) RETURNING "id" ["Test memo hogehoge", 2, "2021-11-12T08:14:41", "2021-11-12T08:14:41"]
{:ok,
%Sample.Memos.Memo{
__meta__: #Ecto.Schema.Metadata<:loaded, "memos">,
id: 2,
inserted_at: ~N[2021-11-12 08:14:41],
text: "Test memo hogehoge",
updated_at: ~N[2021-11-12 08:14:41],
user_id: 2
}}
iex(7)> %{text: "Test memo 2", user_id: 1} |> Memos.create_memo()
[debug] QUERY OK db=0.2ms idle=1683.2ms
INSERT INTO "memos" ("text","user_id","inserted_at","updated_at") VALUES (?,?,?,?) RETURNING "id" ["Test memo 2", 1, "2021-11-12T08:15:06", "2021-11-12T08:15:06"]
{:ok,
%Sample.Memos.Memo{
__meta__: #Ecto.Schema.Metadata<:loaded, "memos">,
id: 3,
inserted_at: ~N[2021-11-12 08:15:06],
text: "Test memo 2",
updated_at: ~N[2021-11-12 08:15:06],
user_id: 1
}}
##データの確認
iex(8)> Memos.list_memos()
[debug] QUERY OK source="memos" db=0.1ms queue=0.1ms idle=1898.2ms
SELECT m0."id", m0."text", m0."user_id", m0."inserted_at", m0."updated_at" FROM "memos" AS m0 []
[
%Sample.Memos.Memo{
__meta__: #Ecto.Schema.Metadata<:loaded, "memos">,
id: 1,
inserted_at: ~N[2021-11-12 08:14:12],
text: "Test memo",
updated_at: ~N[2021-11-12 08:14:12],
user_id: 1
},
%Sample.Memos.Memo{
__meta__: #Ecto.Schema.Metadata<:loaded, "memos">,
id: 2,
inserted_at: ~N[2021-11-12 08:14:41],
text: "Test memo hogehoge",
updated_at: ~N[2021-11-12 08:14:41],
user_id: 2
},
%Sample.Memos.Memo{
__meta__: #Ecto.Schema.Metadata<:loaded, "memos">,
id: 3,
inserted_at: ~N[2021-11-12 08:15:06],
text: "Test memo 2",
updated_at: ~N[2021-11-12 08:15:06],
user_id: 1
}
]
##内部結合 join:
iex(10)> alias Sample.Users.User
Sample.Users.User
iex(12)> from(m in Memo, join: u in User, on: m.user_id == u.id) |> Repo.all()
[debug] QUERY OK source="memos" db=0.3ms queue=0.4ms idle=1328.8ms
SELECT m0."id", m0."text", m0."user_id", m0."inserted_at", m0."updated_at" FROM "memos" AS m0 INNER JOIN "users" AS u1 ON m0."user_id" = u1."id" []
[
%Sample.Memos.Memo{
__meta__: #Ecto.Schema.Metadata<:loaded, "memos">,
id: 1,
inserted_at: ~N[2021-11-12 08:14:12],
text: "Test memo",
updated_at: ~N[2021-11-12 08:14:12],
user_id: 1
},
%Sample.Memos.Memo{
__meta__: #Ecto.Schema.Metadata<:loaded, "memos">,
id: 2,
inserted_at: ~N[2021-11-12 08:14:41],
text: "Test memo hogehoge",
updated_at: ~N[2021-11-12 08:14:41],
user_id: 2
},
%Sample.Memos.Memo{
__meta__: #Ecto.Schema.Metadata<:loaded, "memos">,
id: 3,
inserted_at: ~N[2021-11-12 08:15:06],
text: "Test memo 2",
updated_at: ~N[2021-11-12 08:15:06],
user_id: 1
}
]
##内部結合 フィールド指定
iex(14)> from(m in Memo, join: u in User, on: m.user_id == u.id, select: [u.id, u.name, m.text] ) |> Repo.all()
[debug] QUERY OK source="memos" db=0.4ms queue=0.4ms idle=1722.6ms
SELECT u1."id", u1."name", m0."text" FROM "memos" AS m0 INNER JOIN "users" AS u1 ON m0."user_id" = u1."id" []
[
[1, "hoge", "Test memo"],
[2, "hoge2", "Test memo hogehoge"],
[1, "hoge", "Test memo 2"]
]
##内部結合 フィールド指定と駆動テーブルを反転
iex(16)> from(u in User, join: m in Memo, on: m.user_id == u.id, select: [u.id, u.name, m.text] ) |> Repo.all()
[debug] QUERY OK source="users" db=0.2ms queue=0.3ms idle=1429.9ms
SELECT u0."id", u0."name", m1."text" FROM "users" AS u0 INNER JOIN "memos" AS m1 ON m1."user_id" = u0."id" []
[
[1, "hoge", "Test memo"],
[2, "hoge2", "Test memo hogehoge"],
[1, "hoge", "Test memo 2"]
]
##外部結合 left_join:
from(u in User, left_join: m in Memo, on: m.user_id == u.id, select: [u.id, u.name, m.text] ) |> Repo.all()
[debug] QUERY OK source="users" db=0.3ms queue=0.3ms idle=1095.7ms
SELECT u0."id", u0."name", m1."text" FROM "users" AS u0 LEFT OUTER JOIN "memos" AS m1 ON m1."user_id" = u0."id" []
[
[1, "hoge", "Test memo"],
[1, "hoge", "Test memo 2"],
[2, "hoge2", "Test memo hogehoge"],
[3, "hoge", nil]
]
明日は、オカザキリンビームのオーガナイザ@pojiroさんで「苦節二年の時を経て職業錬金術師になれた話」です