LoginSignup
5
2

More than 3 years have passed since last update.

@im_miolab(imaima || im)

ElixirでMySQLを使う #3（Ecto/CRUD・クエリ作成）

Last updated at Posted at 2020-05-24

概要

  1. ElixirでMySQLを使う #1（Ecto/MyXQLセットアップ〜DB作成）
  2. ElixirでMySQLを使う #2（Ecto/マイグレーション〜スキーマ作成〜カラム修正）

の続きです。

前回、Ectoマイグレーション〜スキーマ作成を行って、Elixir側からデータをMySQLに渡しました。
本記事では、ElixirでMySQLのCRUD操作（データ作成・読み出し・更新・削除）を実行していきます。

  • 本記事シリーズにおける実行環境等については、シリーズ #1 の記事をご参照ください。

（事前準備）データベース再作成

事前準備として、前回つくったデータベースを削除して、あたらしく作り直します。

terminal
  $ mix ecto.drop
  Compiling 1 file (.ex)
  The database for Friendsmysql.Repo has been dropped
terminal
  $ mix ecto.create
  The database for Friendsmysql.Repo has been created
terminal
  $ mix ecto.migrate

  13:36:23.224 [info]  == Running 20200521003013 Friendsmysql.Repo.Migrations.CreatePeople.change/0 forward

  13:36:23.226 [info]  create table people

  13:36:23.241 [info]  == Migrated 20200521003013 in 0.0s

  13:36:23.265 [info]  == Running 20200522004701 Friendsmysql.Repo.Migrations.ApplyNotnullToNames.change/0 forward

  13:36:23.265 [info]  alter table people

  13:36:23.278 [info]  == Migrated 20200522004701 in 0.0s
  • 結果確認（MySQL）
terminal(MySQL)
  mysql> desc people;
  +------------+---------------------+------+-----+---------+----------------+
  | Field      | Type                | Null | Key | Default | Extra          |
  +------------+---------------------+------+-----+---------+----------------+
  | id         | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
  | first_name | varchar(255)        | NO   |     | NULL    |                |
  | last_name  | varchar(255)        | NO   |     | NULL    |                |
  | age        | int(11)             | YES  |     | NULL    |                |
  +------------+---------------------+------+-----+---------+----------------+
  4 rows in set (0.00 sec)

  mysql> select * from people;
  Empty set (0.00 sec)

作りなおしたデータベースと、からっぽのpeopleテーブルが準備できました。

CRUD

それでは、IEx（$ iex -S mix）でCRUD操作を実行していきます。

Create（データ作成）

  • Repo.insert()

はじめに、データの作成（Create）です。

以下の計4レコードを用意します。

terminal
iex(1)> people = [
...(1)> %Friendsmysql.Person{first_name: "Ryan", last_name: "Bigg", age: 28},
...(1)> %Friendsmysql.Person{first_name: "John", last_name: "Smith", age: 27},
...(1)> %Friendsmysql.Person{first_name: "Jane", last_name: "Smith", age: 26},
...(1)> %Friendsmysql.Person{first_name: "im", last_name: "miolab", age: 28},
...(1)> ]
[
  %Friendsmysql.Person{
    __meta__: #Ecto.Schema.Metadata<:built, "people">,
    age: 28,
    first_name: "Ryan",
    id: nil,
    last_name: "Bigg"
  },
  %Friendsmysql.Person{
    __meta__: #Ecto.Schema.Metadata<:built, "people">,
    age: 27,
    first_name: "John",
    id: nil,
    last_name: "Smith"
  },
  %Friendsmysql.Person{
    __meta__: #Ecto.Schema.Metadata<:built, "people">,
    age: 26,
    first_name: "Jane",
    id: nil,
    last_name: "Smith"
  },
  %Friendsmysql.Person{
    __meta__: #Ecto.Schema.Metadata<:built, "people">,
    age: 28,
    first_name: "im",
    id: nil,
    last_name: "miolab"
  }
]

Enum.eachでinsert()していきます。

terminal
iex(2)> Enum.each(people, fn(p) -> Friendsmysql.Repo.insert(p) end)

13:45:40.520 [debug] QUERY OK db=5.9ms decode=1.3ms queue=7.0ms idle=1457.6ms
INSERT INTO `people` (`age`,`first_name`,`last_name`) VALUES (?,?,?) [28, "Ryan", "Bigg"]

13:45:40.528 [debug] QUERY OK db=1.3ms queue=4.8ms idle=1475.8ms
INSERT INTO `people` (`age`,`first_name`,`last_name`) VALUES (?,?,?) [27, "John", "Smith"]

13:45:40.531 [debug] QUERY OK db=0.4ms queue=1.8ms idle=1482.1ms
INSERT INTO `people` (`age`,`first_name`,`last_name`) VALUES (?,?,?) [26, "Jane", "Smith"]
:ok

13:45:40.534 [debug] QUERY OK db=2.3ms queue=1.0ms idle=1484.5ms
INSERT INTO `people` (`age`,`first_name`,`last_name`) VALUES (?,?,?) [28, "im", "miolab"]

peopleテーブルにINSERTできました。

ちなみに、前回の記事みたいに1レコードだけをシンプルにinsert()実行するには、以下の通りです。

terminal
iex(3)> iiii = %Friendsmysql.Person{first_name: "iiii", last_name: "mimimi", age: 18}
%Friendsmysql.Person{
  __meta__: #Ecto.Schema.Metadata<:built, "people">,
  age: 18,
  first_name: "iiii",
  id: nil,
  last_name: "mimimi"
}

iex(4)> Friendsmysql.Repo.insert(iiii)

13:46:54.648 [debug] QUERY OK db=2.5ms queue=3.0ms idle=1575.2ms
INSERT INTO `people` (`age`,`first_name`,`last_name`) VALUES (?,?,?) [18, "iiii", "mimimi"]
{:ok,
 %Friendsmysql.Person{
   __meta__: #Ecto.Schema.Metadata<:loaded, "people">,
   age: 18,
   first_name: "iiii",
   id: 5,
   last_name: "mimimi"
 }}
  • 結果確認（MySQL）
terminal(MySQL)
  mysql> select * from people;
  +----+------------+-----------+------+
  | id | first_name | last_name | age  |
  +----+------------+-----------+------+
  |  1 | Ryan       | Bigg      |   28 |
  |  2 | John       | Smith     |   27 |
  |  3 | Jane       | Smith     |   26 |
  |  4 | im         | miolab    |   28 |
  |  5 | iiii       | mimimi    |   18 |
  +----+------------+-----------+------+
  5 rows in set (0.02 sec)

Create操作ができました。

Read（データ読み出し）

つづいて、登録データの読み出し・抽出（Read）を実行していきます。

全レコード 取得

  • Repo.all()
terminal
  iex(5)> Friendsmysql.Person |> Friendsmysql.Repo.all

  13:51:56.586 [debug] QUERY OK source="people" db=0.3ms queue=5.9ms idle=1513.0ms
  SELECT p0.`id`, p0.`first_name`, p0.`last_name`, p0.`age` FROM `people` AS p0 []
  [
    %Friendsmysql.Person{
      __meta__: #Ecto.Schema.Metadata<:loaded, "people">,
      age: 28,
      first_name: "Ryan",
      id: 1,
      last_name: "Bigg"
    },
    %Friendsmysql.Person{
      __meta__: #Ecto.Schema.Metadata<:loaded, "people">,
      age: 27,
      first_name: "John",
      id: 2,
      last_name: "Smith"
    },
    %Friendsmysql.Person{
      __meta__: #Ecto.Schema.Metadata<:loaded, "people">,
      age: 26,
      first_name: "Jane",
      id: 3,
      last_name: "Smith"
    },
    %Friendsmysql.Person{
      __meta__: #Ecto.Schema.Metadata<:loaded, "people">,
      age: 28,
      first_name: "im",
      id: 4,
      last_name: "miolab"
    },
    %Friendsmysql.Person{
      __meta__: #Ecto.Schema.Metadata<:loaded, "people">,
      age: 18,
      first_name: "iiii",
      id: 5,
      last_name: "mimimi"
    }
  ]

レコード全件を読み出しました。
（SELECT * FROM table相当）

idを指定してレコード取得

  • Repo.get_by()
terminal
  iex(6)> Friendsmysql.Person |> Friendsmysql.Repo.get(3)

  13:53:31.176 [debug] QUERY OK source="people" db=6.2ms queue=0.3ms idle=1102.8ms
  SELECT p0.`id`, p0.`first_name`, p0.`last_name`, p0.`age` FROM `people` AS p0 WHERE (p0.`id` = ?) [3]
  %Friendsmysql.Person{
    __meta__: #Ecto.Schema.Metadata<:loaded, "people">,
    age: 26,
    first_name: "Jane",
    id: 3,
    last_name: "Smith"
  }

id: 3に該当するレコードを抽出しました。

カラム名とフィールドに基づいてレコードを取得

  • Repo.where()
terminal
  iex(7)> Friendsmysql.Person |> Friendsmysql.Repo.get_by(first_name: "im")

  13:54:58.257 [debug] QUERY OK source="people" db=1.8ms queue=2.0ms idle=1185.3ms
  SELECT p0.`id`, p0.`first_name`, p0.`last_name`, p0.`age` FROM `people` AS p0 WHERE (p0.`first_name` = ?) ["im"]
  %Friendsmysql.Person{
    __meta__: #Ecto.Schema.Metadata<:loaded, "people">,
    age: 28,
    first_name: "im",
    id: 4,
    last_name: "miolab"
  }

  iex(8)> Friendsmysql.Person |> Friendsmysql.Repo.get_by(age: 18)

  13:56:15.218 [debug] QUERY OK source="people" db=1.9ms queue=6.1ms idle=1269.7ms
  SELECT p0.`id`, p0.`first_name`, p0.`last_name`, p0.`age` FROM `people` AS p0 WHERE (p0.`age` = ?) [18]
  %Friendsmysql.Person{
    __meta__: #Ecto.Schema.Metadata<:loaded, "people">,
    age: 18,
    first_name: "iiii",
    id: 5,
    last_name: "mimimi"
  }
  • first_name: imに該当するレコードを1件
  • age: 18に該当するレコードを1件

それぞれ抽出しました。

Ecto.Queryでフィルタリング抽出

もう少しつっこんだ条件抽出のクエリを書いてみます。

require Ecto.Queryで事前準備をします。

terminal
  iex(9)> require Ecto.Query
  Ecto.Query

カラム名とフィールドに基づいて重複レコードを取得

それでは、Ecto.Queryでまず重複するレコードを抽出します。
具体例として、last_nameがSmithさんが2人いるので、こちらのお二方を抽出することとします。

なお、先ほどのget_by()ではレコード1件の抽出しかできないため、以下のようにEcto.Queryでのオペレーションが必要となります。

terminal
  iex(10)> Friendsmysql.Person |> Ecto.Query.where(last_name: "Smith") |> Friendsmysql.Repo.all

  14:00:10.899 [debug] QUERY OK source="people" db=0.2ms queue=5.0ms idle=1827.0ms
  SELECT p0.`id`, p0.`first_name`, p0.`last_name`, p0.`age` FROM `people` AS p0 WHERE (p0.`last_name` = 'Smith') []
  [
    %Friendsmysql.Person{
      __meta__: #Ecto.Schema.Metadata<:loaded, "people">,
      age: 27,
      first_name: "John",
      id: 2,
      last_name: "Smith"
    },
    %Friendsmysql.Person{
      __meta__: #Ecto.Schema.Metadata<:loaded, "people">,
      age: 26,
      first_name: "Jane",
      id: 3,
      last_name: "Smith"
    }
  ]

もしくは、

terminal
  iex(11)> Ecto.Query.from(p in Friendsmysql.Person, where: p.last_name == "Smith") |> Friendsmysql.Repo.all

  14:00:57.013 [debug] QUERY OK source="people" db=5.5ms idle=1940.3ms
  SELECT p0.`id`, p0.`first_name`, p0.`last_name`, p0.`age` FROM `people` AS p0 WHERE (p0.`last_name` = 'Smith') []
  [
    %Friendsmysql.Person{
      __meta__: #Ecto.Schema.Metadata<:loaded, "people">,
      age: 27,
      first_name: "John",
      id: 2,
      last_name: "Smith"
    },
    %Friendsmysql.Person{
      __meta__: #Ecto.Schema.Metadata<:loaded, "people">,
      age: 26,
      first_name: "Jane",
      id: 3,
      last_name: "Smith"
    }
  ]

2人のSmithさんを抽出しました。

絞り込み抽出

複数の抽出条件を組み合わせ実行します。
クエリをパイプライン演算子で繋いでいきます。

last_name条件抽出クエリに、first_name条件抽出クエリをつないで、AND検索っぽくします。

terminal
iex(12)> Friendsmysql.Person |> Ecto.Query.where(last_name: "Smith") |> Ecto.Query.where(age: 27) |> Friendsmysql.Repo.all

14:02:44.259 [debug] QUERY OK source="people" db=0.6ms queue=3.9ms idle=1184.8ms
SELECT p0.`id`, p0.`first_name`, p0.`last_name`, p0.`age` FROM `people` AS p0 WHERE (p0.`last_name` = 'Smith') AND (p0.`age` = 27) []
[
  %Friendsmysql.Person{
    __meta__: #Ecto.Schema.Metadata<:loaded, "people">,
    age: 27,
    first_name: "John",
    id: 2,
    last_name: "Smith"
  }
]

last_nameがSmithで、かつageが27の人を抽出しました。

その他、抽出いろいろ

適当にいくつか例を実行します。
参考のため、それぞれに、相当するSQLを添えています。

  • 事前準備
terminal
  iex(13)> import Ecto.Query, only: [from: 2]
  Ecto.Query
  • 全レコードのage平均値を抽出
    （SELECT AVG(age) FROM people）
terminal
  iex(14)> query_avg = from p in "people",
  ...(14)> select: avg(p.age)
  #Ecto.Query<from p0 in "people", select: avg(p0.age)>

  iex(15)> Friendsmysql.Repo.all(query_avg)

  14:14:19.725 [debug] QUERY OK source="people" db=18.2ms queue=1.8ms idle=1914.1ms
  SELECT avg(p0.`age`) FROM `people` AS p0 []
  [#Decimal<25.4000>]
  • age > 20の人のfirst_nameを抽出
    （SELECT first_name FROM people WHERE age > 20）
terminal
  iex(16)> query_over_twenty = from p in "people",
  ...(16)> where: p.age > 20,
  ...(16)> select: p.first_name
  #Ecto.Query<from p0 in "people", where: p0.age > 20, select: p0.first_name>

  iex(17)> Friendsmysql.Repo.all(query_over_twenty)

  14:17:21.281 [debug] QUERY OK source="people" db=1.1ms queue=3.6ms idle=1491.4ms
  SELECT p0.`first_name` FROM `people` AS p0 WHERE (p0.`age` > 20) []
  ["Ryan", "John", "Jane", "im"]
  • age > 26の人をage降順で並びかえて抽出
    （SELECT id, first_name, age FROM people WHERE age > 26 ORDER BY age DESC）
terminal
  iex(18)> query_over_twentysix = from p in "people",
  ...(18)> where: p.age > 26,
  ...(18)> order_by: [desc: :age],
  ...(18)> select: [:id, :first_name, :age]
  #Ecto.Query<from p0 in "people", where: p0.age > 26, order_by: [desc: p0.age],
  select: [:id, :first_name, :age]>

  iex(19)> Friendsmysql.Repo.all(query_over_twentysix)

  14:19:08.225 [debug] QUERY OK source="people" db=4.1ms queue=3.9ms idle=1431.1ms
  SELECT p0.`id`, p0.`first_name`, p0.`age` FROM `people` AS p0 WHERE (p0.`age` > 26) ORDER BY p0.`age` DESC []
  [
    %{age: 28, first_name: "Ryan", id: 1},
    %{age: 28, first_name: "im", id: 4},
    %{age: 27, first_name: "John", id: 2}
  ]

ほんの一例ですが、いずれも問題なく抽出実行できました。

この他にも、以下公式リファレンスにEcto.Queryの抽出もろもろについて詳細がありますので、ご覧いただき試してみられると良いかとおもいます。

（補足）Ecto.Query内での変数使用の際は^が必要

変数をクエリ内で展開する際には、^（ピン演算子）が必要になります。

terminal
  iex(20)> first_name_im = "im"
  "im"

  iex(21)> Friendsmysql.Person |> Ecto.Query.where(first_name: ^first_name_im) |> Friendsmysql.Repo.all

  14:24:47.240 [debug] QUERY OK source="people" db=2.0ms idle=1170.0ms
  SELECT p0.`id`, p0.`first_name`, p0.`last_name`, p0.`age` FROM `people` AS p0 WHERE (p0.`first_name` = ?) ["im"]
  [
    %Friendsmysql.Person{
      __meta__: #Ecto.Schema.Metadata<:loaded, "people">,
      age: 28,
      first_name: "im",
      id: 4,
      last_name: "miolab"
    }
  ]

first_name_imで束縛した文字列imをEcto.Queryで展開する際、適用変数を^first_name_imの形にします。

Update（データ更新）

CRUDのつづきで、次はデータの更新を実行します。

  • Repo.update()
terminal
  iex(1)> require Ecto.Query
  Ecto.Query

  iex(2)> ryan = Friendsmysql.Person |> Ecto.Query.where(first_name: "Ryan") |> Friendsmysql.Repo.one

  14:28:40.395 [debug] QUERY OK source="people" db=0.3ms decode=1.0ms queue=2.7ms idle=1582.4ms
  SELECT p0.`id`, p0.`first_name`, p0.`last_name`, p0.`age` FROM `people` AS p0 WHERE (p0.`first_name` = 'Ryan') []
  %Friendsmysql.Person{
    __meta__: #Ecto.Schema.Metadata<:loaded, "people">,
    age: 28,
    first_name: "Ryan",
    id: 1,
    last_name: "Bigg"
  }

上記で準備したRyanさんのデータage: 28を、29に更新してみます。

terminal
  iex(3)> change_age_twentynine = Friendsmysql.Person.changeset(ryan, %{age: 29})
  #Ecto.Changeset<
    action: nil,
    changes: %{age: 29},
    errors: [],
    data: #Friendsmysql.Person<>,
    valid?: true
  >

  iex(4)> Friendsmysql.Repo.update(change_age_twentynine)

  14:31:55.745 [debug] QUERY OK db=7.2ms queue=4.1ms idle=1929.9ms
  UPDATE `people` SET `age` = ? WHERE `id` = ? [29, 1]
  {:ok,
  %Friendsmysql.Person{
    __meta__: #Ecto.Schema.Metadata<:loaded, "people">,
    age: 29,
    first_name: "Ryan",
    id: 1,
    last_name: "Bigg"
  }}

結果を確認します。

terminal
  iex(5)> Friendsmysql.Person |> Friendsmysql.Repo.get_by(first_name: "Ryan")

  14:32:16.250 [debug] QUERY OK source="people" db=0.5ms queue=0.8ms idle=1442.8ms
  SELECT p0.`id`, p0.`first_name`, p0.`last_name`, p0.`age` FROM `people` AS p0 WHERE (p0.`first_name` = ?) ["Ryan"]
  %Friendsmysql.Person{
    __meta__: #Ecto.Schema.Metadata<:loaded, "people">,
    age: 29,
    first_name: "Ryan",
    id: 1,
    last_name: "Bigg"
  }

age: 29に更新がされています。

Delete（データ削除）

CRUDの最後に、データの削除を実行します。

  • Repo.delete()

先ほどのIExで実行したUpdateのつづきに、コードを書いていきます。
Ryanさん レコードを、ごめんなさいですが削除します。

terminal
  iex(6)> Friendsmysql.Repo.delete(ryan)

  14:35:11.201 [debug] QUERY OK db=4.8ms queue=4.8ms idle=1386.5ms
  DELETE FROM `people` WHERE `id` = ? [1]
  {:ok,
  %Friendsmysql.Person{
    __meta__: #Ecto.Schema.Metadata<:deleted, "people">,
    age: 28,
    first_name: "Ryan",
    id: 1,
    last_name: "Bigg"
  }}

削除できているか全件抽出して確認します。

terminal
  iex(7)> Friendsmysql.Person |> Friendsmysql.Repo.all

  14:35:52.982 [debug] QUERY OK source="people" db=0.4ms queue=2.0ms idle=173.7ms
  SELECT p0.`id`, p0.`first_name`, p0.`last_name`, p0.`age` FROM `people` AS p0 []
  [
    %Friendsmysql.Person{
      __meta__: #Ecto.Schema.Metadata<:loaded, "people">,
      age: 27,
      first_name: "John",
      id: 2,
      last_name: "Smith"
    },
    %Friendsmysql.Person{
      __meta__: #Ecto.Schema.Metadata<:loaded, "people">,
      age: 26,
      first_name: "Jane",
      id: 3,
      last_name: "Smith"
    },
    %Friendsmysql.Person{
      __meta__: #Ecto.Schema.Metadata<:loaded, "people">,
      age: 28,
      first_name: "im",
      id: 4,
      last_name: "miolab"
    },
    %Friendsmysql.Person{
      __meta__: #Ecto.Schema.Metadata<:loaded, "people">,
      age: 18,
      first_name: "iiii",
      id: 5,
      last_name: "mimimi"
    }
  ]

Ryanさんのレコードが削除されています。

終わり

IExでElixir（Ecto）によるMySQLのCRUD操作（データ作成・読み出し・更新・削除）を実行しました。

  • Ectoはドキュメントも詳しく、パイプラインでガシガシ書けて良い感じなので、個人的にも色々もっと調べてみようと思ってます。
  • もしかしたら続きを何かしら書くかもしれません。
5
2
1

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
5
2