ecto 简介
ecto 相当于 elixir 的 ORM，但是得益于 elixir 语言，和传统的 ORM 相比，更加简洁和强大。
ecto 主要分为 4 部分：- Repo: 这是和真正数据库交互的部分
- Schema： 相当于是数据库中表的定义，但不仅仅是定义
- Changeset：Schema 到真正数据库之间的转换层
- 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.Ecto2end
配置数据库连接，config/config.esx
config :ecto_sample, ecto_repos: [EctoSample.Repo] # 配置 repoconfig :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]) endend 创建建表的 migration
$ mix ecto.gen.migration create_userCompiling 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 endend
创建表
$ mix ecto.migrate09:33:40.257 [info] == Running EctoSample.Repo.Migrations.CreateUser.change/0 forward09:33:40.257 [info] create table users09:33:40.259 [info] == Migrated in 0.0s
登入数据库验证
用 sqlite3 的客户端登入数据库查看情况，下面使用的是命令行方式
$ sqlite3 ecto_sample.db SQLite version 3.16.2 2017-01-06 16:32:41Enter ".help" for usage hints.sqlite> .fullschemaCREATE 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 可以看出：
- 除了创建了 users 表，ecto 还创建了 schema_migrations 用来管理每次的 migration
- 默认创建了 id 主键，类型是 INTEGER，如果要改成 uuid 创建时要明确指定
ecto 单表操作
演示示例
通过一个例子来演示对 users 表的 CURD
示例流程： 增加一个记录 -> 查询这条记录 -> 修改这条记录 -> 查询新的记录 -> 删除这条记录 -> 再次查询为空新增记录
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" endend查询记录
import Ecto.Query, only: [from: 2]q = from u in EctoSample.User, where: u.username == ^usernameEctoSample.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)
修改记录
def change_user(id, params \\ %{}) do u = EctoSample.Repo.get!(EctoSample.User, id) changeset = EctoSample.User.changeset(u, params) EctoSample.Repo.update(changeset)end删除记录
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" endend
测试步骤
$ 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 successfullyiex(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 successfullyiex(7)> EctoSample.query_user("change") [] ecto 表关系操作
表关系只有 3 种，1：1，1：N，M：N。 先在现有的表基础上增加 3 张表
增加和 User 关联的表
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]) endend# 1 : 1 computer - userdefmodule 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]) endend# 1 : N company - userdefmodule 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]) endend# M : N friend - userdefmodule 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]) endend原先的 user 表做了一些修改，增加了一些关联属性，另外增加了 3 张表，和 user 表的关系分别是：
- 1:1 user : computer
- 1:N company : user
- M:N friend : user
table migration
创建各个表的 migrationmix ecto.gen.migration create_companymix ecto.gen.migration create_computermix ecto.gen.migration create_friendmix ecto.gen.migration create_users_friends
migration 的代码参见： 整个示例工程的代码都在其中
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 mixErlang/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_one11:00:27.800 [info] ===============================11:00:27.800 [info] id: 111:00:27.800 [info] username: wyb11:00:27.800 [info] password: 12311:00:27.800 [info] computer: *********11:00:27.800 [info] hostname: debian11:00:27.800 [info] ip: 192.168.0.10011:00:27.800 [info] ===============================iex(3)> EctoSample.one_to_many11:01:32.327 [info] ===============================11:01:32.327 [info] id: 111:01:32.327 [info] comp_name:yunbim11:01:32.327 [info] addr : D21611:01:32.327 [info] users: *********11:01:32.327 [info] id: 211:01:32.327 [info] username: wyb00111:01:32.327 [info] password: 12311:01:32.327 [info] id: 311:01:32.327 [info] username: wyb00211:01:32.327 [info] password: 32111:01:32.327 [info] ===============================iex(4)> EctoSample.many_to_many11: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: 12345678911: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: 511:02:22.086 [info] username: wyb00211:02:22.086 [info] password: 32111:02:22.086 [info] ===============================11:02:22.087 [info] ===============================11:02:22.087 [info] id: 211:02:22.087 [info] friend_name:f00211:02:22.087 [info] phone: 98765432111:02:22.087 [info] users: *********11:02:22.087 [info] id: 411:02:22.087 [info] username: wyb00111:02:22.087 [info] password: 12311: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.infoend 插入成功之后，User 和 Computer 表的数据都会增加
其他
除了上述内容之外，Ecto 还有其他的 API 辅助查询和各种数据操作，具体参见