Back

elixir - phoenix 3. 使用ecto来操作数据库

发布时间: 2022-01-29 07:06:00

时隔好几年，记录一下吧

elixir的增删改查跟rails的几乎一样。唯一不同的是elixir喜欢用postgres

参考：https://hexdocs.pm/ecto/getting-started.html#adding-ecto-to-an-application

前期准备

1. 创建一个mix项目 

mix new test_ecto --sup

$ mix new test_ecto --sup
* creating README.md
* creating .formatter.exs
* creating .gitignore
* creating mix.exs
* creating lib
* creating lib/test_ecto.ex
* creating lib/test_ecto/application.ex
* creating test
* creating test/test_helper.exs
* creating test/test_ecto_test.exs

Your Mix project was created successfully.
You can use "mix" to compile it, test it, and more:

    cd test_ecto
    mix test

Run "mix help" for more commands.

2. 下载依赖

mix deps.get

$ mix deps.get
Resolving Hex dependencies...
Dependency resolution completed:
New:
  connection 1.1.0
  db_connection 2.4.1
  decimal 2.0.0
  ecto 3.7.1
  ecto_sql 3.7.2
  postgrex 0.16.1
  telemetry 1.0.0
* Getting ecto_sql (Hex package)
* Getting postgrex (Hex package)
* Getting connection (Hex package)
* Getting db_connection (Hex package)
* Getting decimal (Hex package)
* Getting telemetry (Hex package)
* Getting ecto (Hex package)

3. 创建配置文件。

mix ecto.gen.repo -r TestEcto.Repo  

==> connection
Compiling 1 file (.ex)
Generated connection app
==> test_ecto
Could not find "rebar3", which is needed to build dependency :telemetry
I can install a local copy which is just used by Mix
Shall I install rebar3? (if running non-interactively, use "mix local.rebar --force") [Yn] 
* creating /home/siwei/.asdf/installs/elixir/1.12.3-otp-24/.mix/rebar
* creating /home/siwei/.asdf/installs/elixir/1.12.3-otp-24/.mix/rebar3
===> Analyzing applications...
===> Compiling telemetry
==> decimal
Compiling 4 files (.ex)
Generated decimal app
==> db_connection
Compiling 14 files (.ex)
Generated db_connection app
==> ecto
Compiling 56 files (.ex)
Generated ecto app
==> postgrex
Compiling 64 files (.ex)
Generated postgrex app
==> ecto_sql
Compiling 26 files (.ex)
Generated ecto_sql app
==> test_ecto
* creating lib/test_ecto
* creating lib/test_ecto/repo.ex
* creating config/config.exs
Don't forget to add your new repo to your supervision tree
(typically in lib/test_ecto/application.ex):

    {TestEcto.Repo, []}

And to add it to the list of Ecto repositories in your
configuration files (so Ecto tasks work as expected):

    config :test_ecto,
      ecto_repos: [TestEcto.Repo]

修改这个 文件的内容：

vim config/config.exs

import Config

config :test_ecto, TestEcto.Repo,
  database: "test_ecto_repo",
  username: "myroot",
  password: "123456",
  hostname: "localhost"

（不用修改，了解即可）

上面的命令会为lib/test_ecto/repo.ex 增加一行代码：

defmodule Friends.Repo do
  use Ecto.Repo,
    otp_app: :friends,     #  就是增加了这一行
    adapter: Ecto.Adapters.Postgres
end

4. 修改 lib/test_ecto/application.ex

def start(_type, _args) do
  children = [
    Friends.Repo,  # 增加这一行，就可以让对应的worker随着application的启动而启动了
  ]

5. 修改 config/config.exs

# 增加这一句
config :test_ecto, ecto_repos: [TestEcto.Repo]

6. 创建数据库：mix ecto.create

Compiling 3 files (.ex)
Generated test_ecto app
The database for TestEcto.Repo has been created

7. 创建一个migration : mix ecto.gen.migration create_books

* creating priv/repo/migrations
* creating priv/repo/migrations/20220129071854_create_books.exs

修改 这个migration的内容如下；

defmodule TestEcto.Repo.Migrations.CreateBooks do
  use Ecto.Migration

  def change do
    create table(:books) do
      add :title, :string
      add :author, :string
      add :year, :integer
    end

  end
end

8. 运行migration : mix ecto.migrate

15:22:08.661 [info]  == Running 20220129071854 TestEcto.Repo.Migrations.CreateBooks.change/0 forward

15:22:08.664 [info]  create table books

15:22:08.751 [info]  == Migrated 20220129071854 in 0.0s

9. 在console中运行试试：  iex -S mix

新增记录

iex(2)> book = %TestEcto.Book{ title: "三体", author: "刘慈欣", year: 2006}
%TestEcto.Book{
  __meta__: #Ecto.Schema.Metadata<:built, "books">,
  author: "刘慈欣",
  id: nil,
  title: "三体",
  year: 2006
}

iex(4)> TestEcto.Repo.insert(book)

15:37:35.758 [debug] QUERY OK db=0.7ms queue=0.7ms idle=1836.2ms
INSERT INTO "books" ("author","title","year") VALUES ($1,$2,$3) RETURNING "id" ["刘慈欣", "三体", 2006]
{:ok,
 %TestEcto.Book{
   __meta__: #Ecto.Schema.Metadata<:loaded, "books">,
   author: "刘慈欣",
   id: 2,
   title: "三体",
   year: 2006
 }}

查询

1 基本查询   Ecto.Query

temp = Ecto.Query.first(TestEcto.Book)
book = TestEcto.Repo.one(temp)

# 上面2句也可以写成：
book = TestEcto.Book |> Ecto.Query.first |> TestEcto.Repo.one

15:47:31.089 [debug] QUERY OK source="books" db=0.9ms idle=1401.0ms
SELECT b0."id", b0."title", b0."author", b0."year" FROM "books" AS b0 ORDER BY b0."id" LIMIT 1 []
%TestEcto.Book{
  __meta__: #Ecto.Schema.Metadata<:loaded, "books">,
  author: "刘慈欣",
  id: 2,
  title: "三体", 
  year: 2006
}

2. 查询第一条

Ecto.Query.first(TestEcto.Book) |> TestEcto.Repo.one

16:15:17.868 [debug] QUERY OK source="books" db=1.2ms idle=1587.8ms
SELECT b0."id", b0."title", b0."author", b0."year" FROM "books" AS b0 ORDER BY b0."id" LIMIT 1 []
nil

3. 查询所有

TestEcto.Repo.all(TestEcto.Book)

16:17:18.707 [debug] QUERY OK source="books" db=0.3ms queue=0.5ms idle=1970.0ms
SELECT b0."id", b0."title", b0."author", b0."year" FROM "books" AS b0 []
[]

4. 根据条件查询

 TestEcto.Repo.all TestEcto.Book

16:43:17.966 [debug] QUERY OK source="books" db=0.6ms idle=1762.1ms
SELECT b0."id", b0."title", b0."author", b0."year" FROM "books" AS b0 []
[
  %TestEcto.Book{
    __meta__: #Ecto.Schema.Metadata<:loaded, "books">,
    author: "刘慈欣",
    id: 3,
    title: "三体",
    year: 2004
  },
  %TestEcto.Book{
    __meta__: #Ecto.Schema.Metadata<:loaded, "books">,
    author: "刘慈欣",
    id: 4,
    title: "三体2",
    year: 2006
  },
  %TestEcto.Book{
    __meta__: #Ecto.Schema.Metadata<:loaded, "books">, 
    author: "刘慈欣",
    id: 5,
    title: "三体3",
    year: 2008
  }
]

5. 根据ID查询

TestEcto.Repo.get(TestEcto.Book, 5)

16:47:15.855 [debug] QUERY OK source="books" db=0.7ms idle=1884.6ms
SELECT b0."id", b0."title", b0."author", b0."year" FROM "books" AS b0 WHERE (b0."id" = $1) [5]
%TestEcto.Book{
  __meta__: #Ecto.Schema.Metadata<:loaded, "books">,
  author: "刘慈欣",
  id: 5,
  title: "三体3",
  year: 2008
}

6. where查询

# 1. 传入字符串
TestEcto.Book |> Ecto.Query.where(title: "三体") |> TestEcto.Repo.all

17:54:15.356 [debug] QUERY OK source="books" db=0.3ms queue=0.7ms idle=1598.7ms
SELECT b0."id", b0."title", b0."author", b0."year" FROM "books" AS b0 WHERE (b0."title" = '三体') []
[
  %TestEcto.Book{
    __meta__: #Ecto.Schema.Metadata<:loaded, "books">,
    author: "刘慈欣",
    id: 3,
    title: "三体",
    year: 2004
  }
]

# 2. 使用变量
TestEcto.Book |> Ecto.Query.where(title: ^book_title) |> TestEcto.Repo.all  

17:56:10.968 [debug] QUERY OK source="books" db=0.3ms queue=0.7ms idle=1166.4ms
SELECT b0."id", b0."title", b0."author", b0."year" FROM "books" AS b0 WHERE (b0."title" = $1) ["三体2"]
[
  %TestEcto.Book{
    __meta__: #Ecto.Schema.Metadata<:loaded, "books">,
    author: "刘慈欣",
    id: 4,
    title: "三体2",
    year: 2006
  }
]

修改

修改需要使用changeset, 这里需要import一个新的内容。

(changeset API ： https://hexdocs.pm/ecto/Ecto.Changeset.html ）

修改model文件如下：

defmodule TestEcto.Book do

  use Ecto.Schema

  # 增加这一行， 引入ChangeSet
  import Ecto.Changeset   

  schema "books" do
    field :title, :string
    field :author, :string
    field :year, :integer
  end 

  # 实现这个方法。 
  def changeset book, params \\ %{} do     
    book
    |> cast(params, [:title, :author, :year])
    |> unique_constraint(:title)
  end 
end

然后就可以更新了

iex> changeset = TestEcto.Book.changeset(book, %{ year: 2007, title: "三体x"})
#Ecto.Changeset<
  action: nil,
  changes: %{title: "三体x", year: 2007},
  errors: [],
  data: #TestEcto.Book<>,
  valid?: true
>

iex> TestEcto.Repo.update(changeset)

16:01:12.238 [debug] QUERY OK db=0.7ms queue=0.6ms idle=497.9ms
UPDATE "books" SET "title" = $1, "year" = $2 WHERE "id" = $3 ["三体x", 2007, 2]
{:ok,
 %TestEcto.Book{
   __meta__: #Ecto.Schema.Metadata<:loaded, "books">,
   author: "刘慈欣",
   id: 2,
   title: "三体x",
   year: 2007
 }}

每次修改之前都需要查询它。。

删除记录 Repo.delete

iex > TestEcto.Repo.delete(book)

16:04:53.865 [debug] QUERY OK db=0.8ms queue=0.5ms idle=1074.6ms
DELETE FROM "books" WHERE "id" = $1 [2]
{:ok,
 %TestEcto.Book{
   __meta__: #Ecto.Schema.Metadata<:deleted, "books">,
   author: "刘慈欣",
   id: 2,
   title: "三体x",
   year: 2007
 }}

Back