Ecto 总结

ecto 简介

ecto 相当于 elixir 的 ORM，但是得益于 elixir 语言，和传统的 ORM 相比，更加简洁和强大。
ecto 主要分为 4 部分：

1. Repo: 这是和真正数据库交互的部分
2. Schema： 相当于是数据库中表的定义，但不仅仅是定义
3. Changeset：Schema 到真正数据库之间的转换层
4. Query：elixir 风格的数据查询方式

ecto 连接数据库（以 sqlite 为例）

连接到真实的数据库，需要相应的驱动，下面以 sqlite 为例，配置数据库的连接

创建工程

也就是一般的 elixir 工程

mix new ecto_sample

引入 ecto 和数据库驱动

引入 ecto 和 sqlite 相关的 packages (mix.exs)

defp deps do
  [
    {:sqlite_ecto2, "~> 2.2"},
    {:ecto, "~> 2.1"}
  ]
end

配置好后通过命令行安装：

mix deps.get

创建数据库

在 lib/ecto_sample 下创建文件 repo.ex，文件内容如下：

defmodule EctoSample.Repo do
  use Ecto.Repo,
    otp_app: :ecto_sample,
    adapter: Sqlite.Ecto2
end

配置数据库连接，config/config.esx

config :ecto_sample, ecto_repos: [EctoSample.Repo]  # 配置 repo

config :ecto_sample, EctoSample.Repo,   # 配置驱动和数据库位置，这里用的 sqlite 数据，比较简单
  adapter: Sqlite.Ecto2,
  database: "ecto_sample.db"

sqlite 数据库只要配置数据库文件的位置即可，如果是 postgres 之类的关系数据库，需要配置主机，用户名/密码 等

config :ecto_sample, EctoSample.Repo,
  adapter: Ecto.Adapters.Postgres,
  database: "ecto_sample_repo",
  username: "user",
  password: "pass",
  hostname: "localhost"

配置完成后，创建数据库

mix ecto.create

成功执行的话，能看到在工程根目录下多了个 ecto_sample.db 文件

创建表

创建 schema users, lib/repo.ex 文件中追加
其中 changeset 是在更新数据时用来验证数据有效性或者转换数据用的，不是必须的

defmodule EctoSample.User do
  use Ecto.Schema

  schema "users" do
    field :username, :string
    field :password, :string
    field :email,    :string
    field :age,      :integer
  end

  def changeset(user, params \\ %{}) do
    user
    |> cast(params, [:username, :password, :email, :age])
    |> validate_required([:username, :password])
  end
end

创建建表的 migration

$ mix ecto.gen.migration create_user
Compiling 2 files (.ex)
Generated ecto_sample app
* creating priv/repo/migrations
* creating priv/repo/migrations/20171123012930_create_user.exs

参照 users 的 schema 编辑 priv/repo/migrations/20171123012930_create_user.exs

defmodule EctoSample.Repo.Migrations.CreateUser do
  use Ecto.Migration

  def change do
    create table(:users) do
      add :username, :string
      add :password, :string
      add :email,    :string
      add :age,      :integer
    end
  end
end

创建表

$ mix ecto.migrate

09:33:40.257 [info]  == Running EctoSample.Repo.Migrations.CreateUser.change/0 forward

09:33:40.257 [info]  create table users

09:33:40.259 [info]  == Migrated in 0.0s

登入数据库验证

用 sqlite3 的客户端登入数据库查看情况，下面使用的是命令行方式

$ sqlite3 ecto_sample.db 
SQLite version 3.16.2 2017-01-06 16:32:41
Enter ".help" for usage hints.
sqlite> .fullschema
CREATE TABLE IF NOT EXISTS "schema_migrations" ("version" BIGINT PRIMARY KEY, "inserted_at" NAIVE_DATETIME);
CREATE TABLE IF NOT EXISTS "users" ("id" INTEGER PRIMARY KEY, "username" TEXT, "password" TEXT, "email" TEXT, "age" INTEGER);
/* No STAT tables available */
sqlite> .exit

可以看出：

1. 除了创建了 users 表，ecto 还创建了 schema_migrations 用来管理每次的 migration
2. 默认创建了 id 主键，类型是 INTEGER，如果要改成 uuid 创建时要明确指定

ecto 单表操作

演示示例

通过一个例子来演示对 users 表的 CURD
示例流程： 增加一个记录 -> 查询这条记录 -> 修改这条记录 -> 查询新的记录 -> 删除这条记录 -> 再次查询为空

1. 新增记录

   def add_user(username, password, email \\ "", age \\ 0) do
     user = EctoSample.User.changeset(%EctoSample.User{}, %{:username => username, :password => password,
                                                            :email => email, :age => age})
   
     case EctoSample.Repo.insert(user) do
       {:ok, _} -> Logger.info "insert successfully"
       {:error, _} -> Logger.error "insert failed"
     end
   end
   

2. 查询记录

   import Ecto.Query, only: [from: 2]
   
   q = from u in EctoSample.User,
     where: u.username == ^username
   
   EctoSample.Repo.all(q) |> Enum.map(fn (u) ->
     Logger.info "==============================="
     Logger.info "username: " <> u.username
     Logger.info "password: " <> u.password
     if u.email do
       Logger.info "email:    " <> u.email
     end
     Logger.info "age:      " <> Integer.to_string u.age
     Logger.info "==============================="
   end)
   

3. 修改记录

   def change_user(id, params \\ %{}) do
     u = EctoSample.Repo.get!(EctoSample.User, id)
     changeset = EctoSample.User.changeset(u, params)
   
     EctoSample.Repo.update(changeset)
   end
   

4. 删除记录

   def delete_user(id) do
     u = EctoSample.Repo.get!(EctoSample.User, id)
     EctoSample.Repo.delete(u)
     |> case  do
          {:ok, _} -> Logger.info "delete successfully"
          {:error, _} -> Logger.error "delete failed"
        end
   end
   

测试步骤

$ iex -S mix
  Erlang/OTP 20 [erts-9.1] [source] [64-bit] [smp:2:2] [ds:2:2:10] [async-threads:10] [hipe] [kernel-poll:false]

  Interactive Elixir (1.5.2) - press Ctrl+C to exit (type h() ENTER for help)
iex(1)> EctoSample.start
  {:ok, #PID<0.182.0>}
iex(2)> EctoSample.add_user("a", "b", "c", 10)

  22:45:22.570 [info]  insert successfully
iex(3)> EctoSample.query_user("a")

  22:45:29.370 [info]  ===============================
  [:ok]

  22:45:29.370 [info]  username: a

  22:45:29.370 [info]  password: b

  22:45:29.370 [info]  email:    c

  22:45:29.370 [info]  age:      10

  22:45:29.370 [info]  ===============================

iex(4)> EctoSample.change_user(1, %{:username => "change", :age => 20})
  {:ok,
   %EctoSample.User{__meta__: #Ecto.Schema.Metadata<:loaded, "users">, age: 20,
    email: "c", id: 1, password: "b", username: "change"}}
iex(5)> EctoSample.query_user("change")

  22:48:47.644 [info]  ===============================

  22:48:47.644 [info]  username: change

  22:48:47.644 [info]  password: b

  22:48:47.644 [info]  email:    c

  22:48:47.644 [info]  age:      20

  22:48:47.644 [info]  ===============================
iex(6)> EctoSample.delete_user(1)

  22:50:17.848 [info]  delete successfully
iex(7)> EctoSample.query_user("change")

  []

ecto 表关系操作

表关系只有 3 种，1：1，1：N，M：N。 先在现有的表基础上增加 3 张表

增加和 User 关联的表

1. table 定义

   defmodule EctoSample.Schema.User do
     use Ecto.Schema
     import Ecto.Changeset
   
     schema "users" do
       field :username, :string
       field :password, :string
       field :email,    :string
       field :age,      :integer
   
       has_one :computer, EctoSample.Schema.Computer
       belongs_to :company, EctoSample.Schema.Company
       many_to_many :friends, EctoSample.Schema.Friend, join_through: "users_friends"
     end
   
     def changeset(user, params \\ %{}) do
       user
       |> cast(params, [:username, :password, :email, :age])
       |> validate_required([:username, :password])
     end
   end
   
   # 1 : 1  computer - user
   defmodule EctoSample.Schema.Computer do
     use Ecto.Schema
     import Ecto.Changeset
   
     schema "computers" do
       field :hostname, :string
       field :ip,       :string
   
       belongs_to :user, EctoSample.Schema.User
     end
   
     def changeset(computer, params \\ %{}) do
       computer
       |> cast(params, [:hostname, :ip])
       |> validate_required([:hostname, :ip])
     end
   end
   
   # 1 : N  company - user
   defmodule EctoSample.Schema.Company do
     use Ecto.Schema
     import Ecto.Changeset
   
     schema "companys" do
       field :comp_name, :string
       field :addr,      :string
   
       has_many :users, EctoSample.Schema.User
     end
   
     def changeset(company, params \\ %{}) do
       company
       |> cast(params, [:comp_name, :addr])
       |> validate_required([:comp_name, :addr])
     end
   end
   
   # M : N  friend - user
   defmodule EctoSample.Schema.Friend do
     use Ecto.Schema
     import Ecto.Changeset
   
     schema "friends" do
       field :frient_name, :string
       field :phone,       :string
   
       many_to_many :users, EctoSample.Schema.User, join_through: "users_friends"
     end
   
     def changeset(friend, params \\ %{}) do
       friend
       |> cast(params, [:friend_name, :phone])
       |> validate_required([:friend_name, :phone])
     end
   end
   

   原先的 user 表做了一些修改，增加了一些关联属性，另外增加了 3 张表，和 user 表的关系分别是：

   • 1:1 user : computer
   • 1:N company : user
   • M:N friend : user

2. table migration
   创建各个表的 migration

   mix ecto.gen.migration create_company
   mix ecto.gen.migration create_computer
   mix ecto.gen.migration create_friend
   mix ecto.gen.migration create_users_friends
   

   migration 的代码参见：https://gitee.com/wangyubin/ecto_sample.git 整个示例工程的代码都在其中

1：1 示例

def one_to_one() do
  import Ecto.Changeset
  alias EctoSample.Schema.User
  alias EctoSample.Schema.Computer

  # insert
  computer = %Computer{}
  |> Computer.changeset(%{:hostname => "debian", :ip => "192.168.0.100"})
  |> EctoSample.Repo.insert!

  user = %User{}
  |> User.changeset(%{:username => "wyb", :password => "123"})
  |> put_assoc(:computer, computer)
  |> EctoSample.Repo.insert!

  # query
  u = EctoSample.Repo.get!(User, user.id) |> EctoSample.Repo.preload(:computer)
  Logger.info "==============================="
  Logger.info "id:       " <> Integer.to_string(u.id)
  Logger.info "username: " <> u.username
  Logger.info "password: " <> u.password
  Logger.info "computer: *********"
  Logger.info "hostname: " <> u.computer.hostname
  Logger.info "ip:       " <> u.computer.ip
  Logger.info "==============================="
end

1：N 示例

def one_to_many() do
  alias EctoSample.Schema.User
  alias EctoSample.Schema.Company

  # insert
  user1 = EctoSample.Repo.insert!(%User{:username => "wyb001", :password => "123"})
  user2 = EctoSample.Repo.insert!(%User{:username => "wyb002", :password => "321"})
  company = EctoSample.Repo.insert!(%Company{:comp_name => "yunbim", :addr => "D216", :users => [user1, user2]})

  # TODO 这里是根据 user 来新建 company，也可以 根据已有的 company 来创建 user

  # query
  c = EctoSample.Repo.get!(Company, company.id) |> EctoSample.Repo.preload(:users)
  Logger.info "==============================="
  Logger.info "id:       " <> Integer.to_string(c.id)
  Logger.info "comp_name:" <> c.comp_name
  Logger.info "addr    : " <> c.addr
  Logger.info "users: *********"
  c.users |> Enum.map(fn (u) ->
    Logger.info "id:       " <> Integer.to_string(u.id)
    Logger.info "username: " <> u.username
    Logger.info "password: " <> u.password
  end)
  Logger.info "==============================="
end

M：N 示例

def many_to_many() do
  alias EctoSample.Schema.User
  alias EctoSample.Schema.Friend
  import Ecto.Changeset

  # insert
  user1 = EctoSample.Repo.insert!(%User{:username => "wyb001", :password => "123"})
  user2 = EctoSample.Repo.insert!(%User{:username => "wyb002", :password => "321"})
  friend1 = EctoSample.Repo.insert!(%Friend{:friend_name => "f001", :phone => "123456789"})
  friend2 = EctoSample.Repo.insert!(%Friend{:friend_name => "f002", :phone => "987654321"})

  EctoSample.Repo.get!(User, user1.id)
  |> EctoSample.Repo.preload(:friends)
  |> change
  |> put_assoc(:friends, [friend1, friend2])
  |> EctoSample.Repo.update!()

  EctoSample.Repo.get!(User, user2.id)
  |> EctoSample.Repo.preload(:friends)
  |> change
  |> put_assoc(:friends, [friend1])
  |> EctoSample.Repo.update!()

  # query
  f1 = EctoSample.Repo.get!(Friend, friend1.id) |> EctoSample.Repo.preload(:users)
  Logger.info "==============================="
  Logger.info "id:         " <> Integer.to_string(f1.id)
  Logger.info "friend_name:" <> f1.friend_name
  Logger.info "phone:      " <> f1.phone
  Logger.info "users: *********"
  f1.users |> Enum.map(fn (u) ->
    Logger.info "id:       " <> Integer.to_string(u.id)
    Logger.info "username: " <> u.username
    Logger.info "password: " <> u.password
  end)
  Logger.info "==============================="

  f2 = EctoSample.Repo.get!(Friend, friend2.id) |> EctoSample.Repo.preload(:users)
  Logger.info "==============================="
  Logger.info "id:         " <> Integer.to_string(f2.id)
  Logger.info "friend_name:" <> f2.friend_name
  Logger.info "phone:      " <> f2.phone
  Logger.info "users: *********"
  f2.users |> Enum.map(fn (u) ->
    Logger.info "id:       " <> Integer.to_string(u.id)
    Logger.info "username: " <> u.username
    Logger.info "password: " <> u.password
  end)
  Logger.info "==============================="
end

运行测试

$ mix ecto.drop
$ mix ecto.create
$ mix ecto.migrate

$ iex -S mix
Erlang/OTP 20 [erts-9.1] [source] [64-bit] [smp:2:2] [ds:2:2:10] [async-threads:10] [hipe] [kernel-poll:false]

Interactive Elixir (1.5.2) - press Ctrl+C to exit (type h() ENTER for help)
iex(1)> EctoSample.start
{:ok, #PID<0.182.0>}

iex(2)> EctoSample.one_to_one
11:00:27.800 [info]  ===============================

11:00:27.800 [info]  id:       1

11:00:27.800 [info]  username: wyb

11:00:27.800 [info]  password: 123

11:00:27.800 [info]  computer: *********

11:00:27.800 [info]  hostname: debian

11:00:27.800 [info]  ip:       192.168.0.100

11:00:27.800 [info]  ===============================

iex(3)> EctoSample.one_to_many
11:01:32.327 [info]  ===============================

11:01:32.327 [info]  id:       1

11:01:32.327 [info]  comp_name:yunbim

11:01:32.327 [info]  addr    : D216

11:01:32.327 [info]  users: *********

11:01:32.327 [info]  id:       2

11:01:32.327 [info]  username: wyb001

11:01:32.327 [info]  password: 123

11:01:32.327 [info]  id:       3

11:01:32.327 [info]  username: wyb002

11:01:32.327 [info]  password: 321

11:01:32.327 [info]  ===============================

iex(4)> EctoSample.many_to_many
11:02:22.086 [info]  ===============================

11:02:22.086 [info]  id:         1   

11:02:22.086 [info]  friend_name:f001     

11:02:22.086 [info]  phone:      123456789

11:02:22.086 [info]  users: *********                                                     

11:02:22.086 [info]  id:       4                                 

11:02:22.086 [info]  username: wyb001                                                            

11:02:22.086 [info]  password: 123                  

11:02:22.086 [info]  id:       5

11:02:22.086 [info]  username: wyb002

11:02:22.086 [info]  password: 321

11:02:22.086 [info]  ===============================

11:02:22.087 [info]  ===============================

11:02:22.087 [info]  id:         2

11:02:22.087 [info]  friend_name:f002

11:02:22.087 [info]  phone:      987654321

11:02:22.087 [info]  users: *********

11:02:22.087 [info]  id:       4

11:02:22.087 [info]  username: wyb001

11:02:22.087 [info]  password: 123

11:02:22.087 [info]  ===============================

ecto 中的事务

ecto 中的事务，首先通过 Multi 来组装需要进行的数据库操作，然后通过 Repo.transaction 来执行

def trans() do
  alias EctoSample.Schema.User
  alias EctoSample.Schema.Computer
  import Ecto.Query, only: [from: 2]
  alias Ecto.Multi

  # insert user and computer in one transaction, insert all success
  Logger.info "=========== before transaction==============="
  EctoSample.Repo.one(from u in User, select: count(u.id)) |> Logger.info
  EctoSample.Repo.one(from c in Computer, select: count(c.id)) |> Logger.info

  Multi.new()
  |> Multi.insert(:user, %User{username: "m-user", password: "m-password"})
  |> Multi.insert(:computer, %Computer{hostname: "host-name", ip: "0.0.0.0"})
  |> EctoSample.Repo.transaction
  |> case do
       {:ok, _} -> Logger.info "multi success"
       {:error, _} -> Logger.error "multi error"
     end


  Logger.info "=========== after  transaction==============="
  EctoSample.Repo.one(from u in User, select: count(u.id)) |> Logger.info
  EctoSample.Repo.one(from c in Computer, select: count(c.id)) |> Logger.info

end

插入成功之后，User 和 Computer 表的数据都会增加

其他

除了上述内容之外，Ecto 还有其他的 API 辅助查询和各种数据操作，具体参见 Ecto 文档