How Ecto.insert(..., returning: ...) works

Questions / Help
2

https://hexdocs.pm/ecto/Ecto.Repo.html#c:insert/2-options

:returning - selects which fields to return. It accepts a list of fields to be returned from the database. When true , returns all fields. When false , no extra fields are returned. It will always include all fields in read_after_writes as well as any autogenerated id. Not all databases support this option.

PostgreSQL does support RETURNING on a detailed level (MySQL doesn’t seem to support it).

It could be informative to see the generated SQL through an iex session (example).

$ iex -S mix
Erlang/OTP 22 [erts-10.4.4] [source] [64-bit] [smp:8:8] [ds:8:8:10] [async-threads:1] [hipe] [dtrace]

Interactive Elixir (1.9.1) - press Ctrl+C to exit (type h() ENTER for help)
iex(1)> alias MusicDB.{Repo}
[MusicDB.Repo]
iex(2)> import Ecto.Query
Ecto.Query
iex(3)> Repo.insert(%Artist{name: "Test 001"}, returning: false)

20:12:19.999 [debug] QUERY OK db=9.0ms decode=1.2ms queue=1.0ms
INSERT INTO "artists" ("name","inserted_at","updated_at") VALUES ($1,$2,$3) RETURNING "id" ["Test 001", ~N[2019-09-19 00:12:19], ~N[2019-09-19 00:12:19]]
{:ok,
 %MusicDB.Artist{
   __meta__: #Ecto.Schema.Metadata<:loaded, "artists">,
   albums: #Ecto.Association.NotLoaded<association :albums is not loaded>,
   birth_date: nil,
   death_date: nil,
   id: 5,
   inserted_at: ~N[2019-09-19 00:12:19],
   name: "Test 001",
   tracks: #Ecto.Association.NotLoaded<association :tracks is not loaded>,
   updated_at: ~N[2019-09-19 00:12:19]
 }}
iex(4)> Repo.insert(%Artist{name: "Test 002"}, returning: true)

20:12:20.008 [debug] QUERY OK db=0.9ms queue=0.6ms
INSERT INTO "artists" ("name","inserted_at","updated_at") VALUES ($1,$2,$3) RETURNING "id", "updated_at", "inserted_at", "death_date", "birth_date", "name" ["Test 002", ~N[2019-09-19 00:12:20], ~N[2019-09-19 00:12:20]]
{:ok,
 %MusicDB.Artist{
   __meta__: #Ecto.Schema.Metadata<:loaded, "artists">,
   albums: #Ecto.Association.NotLoaded<association :albums is not loaded>,
   birth_date: nil,
   death_date: nil,
   id: 6,
   inserted_at: ~N[2019-09-19 00:12:20],
   name: "Test 002",
   tracks: #Ecto.Association.NotLoaded<association :tracks is not loaded>,
   updated_at: ~N[2019-09-19 00:12:20]
 }}
iex(5)> Repo.insert(%Artist{name: "Test 003"}, returning: [:name, :updated_at])  
20:12:41.065 [debug] QUERY OK db=6.8ms queue=1.5ms
INSERT INTO "artists" ("name","inserted_at","updated_at") VALUES ($1,$2,$3) RETURNING "id", "updated_at", "name" ["Test 003", ~N[2019-09-19 00:12:41], ~N[2019-09-19 00:12:41]]
{:ok,
 %MusicDB.Artist{
   __meta__: #Ecto.Schema.Metadata<:loaded, "artists">,
   albums: #Ecto.Association.NotLoaded<association :albums is not loaded>,
   birth_date: nil,
   death_date: nil,
   id: 7,
   inserted_at: ~N[2019-09-19 00:12:41],
   name: "Test 003",
   tracks: #Ecto.Association.NotLoaded<association :tracks is not loaded>,
   updated_at: ~N[2019-09-19 00:12:41]
 }}
iex(6)>

So RETURNING works - but look at insert/2’s return type: {:ok, Ecto.Schema.t()} | {:error, Ecto.Changeset.t()} i.e. it’s obliged to return the full schema struct anyway.

Presumably the returned values are used while the remaining ones are the ones that were inserted in the first place (which seems to happen here).

delete/2 doesn’t mention supporting it.

update/2 doesn’t seem to work despite the documentation mentioning it.

iex(8)> Repo.get(Artist,7) |> Artist.changeset(%{name: "Test C"}) |> Repo.update()

20:43:17.638 [debug] QUERY OK source="artists" db=1.4ms
SELECT a0."id", a0."name", a0."birth_date", a0."death_date", a0."inserted_at", a0."updated_at" FROM "artists" AS a0 WHERE (a0."id" = $1) [7]
 
20:43:17.644 [debug] QUERY OK db=3.3ms queue=2.6ms
UPDATE "artists" SET "name" = $1, "updated_at" = $2 WHERE "id" = $3 ["Test C", ~N[2019-09-19 00:43:17], 7]
{:ok,
 %MusicDB.Artist{
   __meta__: #Ecto.Schema.Metadata<:loaded, "artists">,
   albums: #Ecto.Association.NotLoaded<association :albums is not loaded>,
   birth_date: nil,
   death_date: nil,
   id: 7,
   inserted_at: ~N[2019-09-19 00:12:41],
   name: "Test C",
   tracks: #Ecto.Association.NotLoaded<association :tracks is not loaded>,
   updated_at: ~N[2019-09-19 00:43:17]
 }}
iex(9)> Repo.get(Artist,7) |> Artist.changeset(%{name: "Test D"}) |> Repo.update(returning: false)

20:44:14.572 [debug] QUERY OK source="artists" db=0.3ms
SELECT a0."id", a0."name", a0."birth_date", a0."death_date", a0."inserted_at", a0."updated_at" FROM "artists" AS a0 WHERE (a0."id" = $1) [7]
 
20:44:14.578 [debug] QUERY OK db=6.4ms
UPDATE "artists" SET "name" = $1, "updated_at" = $2 WHERE "id" = $3 ["Test D", ~N[2019-09-19 00:44:14], 7]
{:ok,
 %MusicDB.Artist{
   __meta__: #Ecto.Schema.Metadata<:loaded, "artists">,
   albums: #Ecto.Association.NotLoaded<association :albums is not loaded>,
   birth_date: nil,
   death_date: nil,
   id: 7,
   inserted_at: ~N[2019-09-19 00:12:41],
   name: "Test D",
   tracks: #Ecto.Association.NotLoaded<association :tracks is not loaded>,
   updated_at: ~N[2019-09-19 00:44:14]
 }}
iex(10)> Repo.get(Artist,7) |> Artist.changeset(%{name: "Test F"}) |> Repo.update(returning: true)

20:45:23.991 [debug] QUERY OK source="artists" db=0.4ms
SELECT a0."id", a0."name", a0."birth_date", a0."death_date", a0."inserted_at", a0."updated_at" FROM "artists" AS a0 WHERE (a0."id" = $1) [7]
 
20:45:23.995 [debug] QUERY OK db=3.2ms queue=1.3ms
UPDATE "artists" SET "name" = $1, "updated_at" = $2 WHERE "id" = $3 ["Test F", ~N[2019-09-19 00:45:23], 7]
{:ok,
 %MusicDB.Artist{
   __meta__: #Ecto.Schema.Metadata<:loaded, "artists">,
   albums: #Ecto.Association.NotLoaded<association :albums is not loaded>,
   birth_date: nil,
   death_date: nil,
   id: 7,
   inserted_at: ~N[2019-09-19 00:12:41],
   name: "Test F",
   tracks: #Ecto.Association.NotLoaded<association :tracks is not loaded>,
   updated_at: ~N[2019-09-19 00:45:23]
 }}
iex(11)> Repo.get(Artist,7) |> Artist.changeset(%{name: "Test F"}) |> Repo.update(returning: [:name, :updated_at])

20:49:57.043 [debug] QUERY OK source="artists" db=0.4ms
SELECT a0."id", a0."name", a0."birth_date", a0."death_date", a0."inserted_at", a0."updated_at" FROM "artists" AS a0 WHERE (a0."id" = $1) [7]
{:ok,
 %MusicDB.Artist{
   __meta__: #Ecto.Schema.Metadata<:loaded, "artists">,
   albums: #Ecto.Association.NotLoaded<association :albums is not loaded>,
   birth_date: nil,
   death_date: nil,
   id: 7,
   inserted_at: ~N[2019-09-19 00:12:41],
   name: "Test F",
   tracks: #Ecto.Association.NotLoaded<association :tracks is not loaded>,
   updated_at: ~N[2019-09-19 00:45:23]
 }}
iex(12)> Repo.get(Artist,7) |> Artist.changeset(%{name: "Test G"}) |> Repo.update(returning: [:name, :updated_at])

20:50:37.031 [debug] QUERY OK source="artists" db=0.8ms
SELECT a0."id", a0."name", a0."birth_date", a0."death_date", a0."inserted_at", a0."updated_at" FROM "artists" AS a0 WHERE (a0."id" = $1) [7]
 
20:50:37.038 [debug] QUERY OK db=6.7ms queue=0.3ms
UPDATE "artists" SET "name" = $1, "updated_at" = $2 WHERE "id" = $3 ["Test G", ~N[2019-09-19 00:50:37], 7]
{:ok,
 %MusicDB.Artist{
   __meta__: #Ecto.Schema.Metadata<:loaded, "artists">,
   albums: #Ecto.Association.NotLoaded<association :albums is not loaded>,
   birth_date: nil,
   death_date: nil,
   id: 7,
   inserted_at: ~N[2019-09-19 00:12:41],
   name: "Test G",
   tracks: #Ecto.Association.NotLoaded<association :tracks is not loaded>,
   updated_at: ~N[2019-09-19 00:50:37]
 }}
3 Likes