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.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
可以看出：
- 除了创建了 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" end end -
查询记录
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) -
修改记录
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" 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 关联的表
-
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
-
table migration
创建各个表的 migrationmix ecto.gen.migration create_company mix ecto.gen.migration create_computer mix ecto.gen.migration create_friend mix ecto.gen.migration create_users_friendsmigration 的代码参见：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 文档