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

marick

I’m wondering if I understand the Ecto.insert :returning option. Here’s how I think it works when inserting a changeset (and possibly a schema struct - I haven’t checked):

Regardless of :returning, the input :data is returned, including virtual fields.
Regardless of :returning, the primary key is always added to that result.

Is that right?

Here’s some code:

      Enum.at(@changesets, 0)
      |> Crit.Repo.insert(prefix: "demo", returning: false)
      |> IO.inspect

The return value is this:

{:ok,
 %Crit.Usables.ServiceGap{
   __meta__: #Ecto.Schema.Metadata<:loaded, "demo", "service_gaps">,
   end_date: nil,
   gap: %Ecto.Datespan{
     first: :unbound,
     last: ~D[2012-12-12],
     lower_inclusive: false,
     upper_inclusive: false
   },
   id: 1236,
   reason: "before animal was put in service",
   start_date: ~D[2012-12-12],
   timezone: nil
 }}

This is the same result as happens with returning: true or returning: [:id].

(I picked a schema without timestamps, because it was the most convenient. I’m betting I could affect whether timestamps were returned with :returning. I’m nearly out of power. I’ll check if my understanding above is wrong.)

6 comments

#ecto

0 10683 6

2019-09-22 18:28:21 UTC

Showing Posts 1 to 6

Show Best Posts
Show All (oldest first)
Show All (newest first)

peerreynders

: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]
 }}

Post #1

marick

The raw SQL looks like this:

INSERT INTO "demo"."service_gaps" ("gap","reason") VALUES ($1,$2) RETURNING "id"...
                                                                                                                   ^^^^^^^^^^^^^

… for all of these cases:

... |> Crit.Repo.insert(prefix: "demo")
... |> Crit.Repo.insert(prefix: "demo", returning: true)
... |> Crit.Repo.insert(prefix: "demo", returning: [:id])
... |> Crit.Repo.insert(prefix: "demo", returning: false)

I tried it with a nonsense value and did get an error:

... |> Crit.Repo.insert(prefix: "demo", returning: 5)


** (CaseClauseError) no case clause matching: 5
code: |> Crit.Repo.insert(prefix: "demo", returning: 5)
stacktrace:
  (ecto) lib/ecto/repo/schema.ex:474: Ecto.Repo.Schema.returning/2
  (ecto) lib/ecto/repo/schema.ex:224: Ecto.Repo.Schema.do_insert/4

This to look at the source.

Post #2

LostKobrakai

Changesets already hold all the data needed to return you the inserted value. :returning similar to :read_after_writes is only useful if your db has autogenerated values, defaults, triggers or such means to modify the value you’re persisting and you want to read it back, because what the changeset holds won’t match what the db stores.

Post #3

marick

The following appears to be true, and I’ll make a pull request against the documentation (better written than the below).

By default, insert receives only the new primary key (typically, :id) from the database. That id is merged with the data given to insert to produce what is usually a copy of the data in the database. The only exception is if the database itself changes or creates fields (with, for example, triggers). Here are returning’s options:

true: asks the database to provide all the fields in the record (including ones whose value is already known. (This only works when the data comes from a schema struct.)

a list of fields: specify fields to be provided (always in addition to the primary key).

false: the same as giving no returning keyword: only the new primary key is provided.

Post #4

peerreynders

And any fields that have :read_after_writes set to true - they are essentially treated the same as the autogenerated id.

:read_after_writes - When true, the field is always read back from the database after insert and updates.

For relational databases, this means the RETURNING option of those statements is used. For this reason, MySQL does not support this option and will raise an error if a schema is inserted/updated with read after writes fields.

What the documentation isn’t explicit about (likely assumed by implication) is that the returned values will replace the original values in the changeset :data.

Post #5