Search code examples
elixirphoenix-frameworkecto

insert_all does not match type :utc_datetime

So I have an array of objects that i want to save using insert_all. Im getting an error that one of my fields does not match.

edtr.json

{
"edtrs":
[
    {
        "dtr_date": "2018-12-29T16:00:00.000Z",
        "user_id": "8189f04b-e3d7-4d17-8df2-fedbeb0399b1"
    },
    {
        "dtr_date": "2018-12-30T16:00:00.000Z",
        "user_id": "8189f04b-e3d7-4d17-8df2-fedbeb0399b1"
    }
]}

post_controller

 def create_edtr(edtrs) do
maps =
  Enum.map(edtrs["edtrs"], fn(item) ->
    %{dtr_date: item["dtr_date"], user_id: item["user_id"]}
  end)

IO.inspect maps, label: "MAPS"
Repo.insert_all(Edtr,maps) end

ERROR

HrisApp.Attendance.Edtr.dtr_date in insert_all does not match type :utc_datetime

Solution

  • You have a string for your dtr_date field, but in your schema for your database table you must have specified that the dtr_date field is a :utc_datetime type, so ecto requires that you pass it a DateTime struct.

    iex(1)> date_string = "2018-12-30T16:00:00.000Z"             
"2018-12-30T16:00:00.000Z"

iex(2)> {:ok, dt_struct, utc_offset} = DateTime.from_iso8601(date_string)
{:ok, #DateTime<2018-12-30 16:00:00.000Z>, 0}

iex(3)> dt_struct
#DateTime<2018-12-30 16:00:00.000Z>

    See here:

    Phoenix/Ecto - converting ISO string into utc_datetime primitive type

    Note that if you use ecto changesets, then cast() will transform the data into the proper types for you. Here's an example:

    Directory structure:

    lib/
    myapp/
         edtr_api/
                 edtr.ex
                 edtr_api.ex
    myapp_web/

    edtr.ex:

    defmodule Myapp.EdtrApi.Edtr do

  use Ecto.Schema
  import Ecto.Changeset
  alias Myapp.EdtrApi.Edtr

  schema "edtrs" do
    field :dtr_date, :utc_datetime, null: false
    field :user_id, :string

    timestamps()
  end

  def create_changesets(edtr_data) do
    Enum.map(edtr_data, fn data ->
      %Edtr{}
      |> cast(data, [:dtr_date, :user_id])
      |> validate_required([:dtr_date, :user_id])
      |> validate_length(:user_id, min: 10, max: 100)
    end)
  end

end

    edtr_api.ex:

    defmodule Myapp.EdtrApi do
  alias Myapp.EdtrApi.Edtr
  alias Myapp.Repo
  
  def get_data() do 
    [
      %{
          "dtr_date" => "2018-12-29T16:00:00.000Z",
          "user_id" => "8189f04b-e3d7-4d17-8df2-fedbeb0399b1"
      },
      %{
          "dtr_date" => "2018-12-30T16:00:00.000Z",
      },
    ]
  end

  def insert_edtrs() do
    changesets = Edtr.create_changesets(get_data())
    Enum.map(changesets, fn changeset -> Repo.insert(changeset) end) 
  end

  def all() do
    Repo.all(Edtr)
  end

end

    Then you need to create a migration file:

    ~/phoenix_apps/myapp$ mix ecto.gen.migration create_edtrs

    Then change the migration file to mimic your Edtr schema:

    myapp/priv/repo/migrations/20181211070748_create_edtrs.exs:

    defmodule Myapp.Repo.Migrations.CreateEdtrs do
  use Ecto.Migration

  def change do
    create table(:edtrs) do
      add :dtr_date, :utc_datetime, null: false
      add :user_id, :string

      timestamps()
    end

  end
end

    Then perform the migration to create the table in the database:

     ~/phoenix_apps/myapp$ mix ecto.migrate

    Now try it out in iex:

    $ iex -S mix
Erlang/OTP 20 [erts-9.3] [source] [64-bit] [smp:4:4] [ds:4:4:10] [async-threads:10] [hipe] [kernel-poll:false]
Interactive Elixir (1.6.6) - press Ctrl+C to exit (type h() ENTER for help)

iex(1)> alias Myapp.EdtrApi        
Myapp.EdtrApi

iex(2)> alias Myapp.EdtrApi.Edtr   
Myapp.EdtrApi.Edtr

iex(3)> Myapp.Repo.delete_all(Edtr)
[debug] QUERY OK source="edtrs" db=1.0ms decode=1.7ms queue=0.9ms
DELETE FROM "edtrs" AS e0 []
{0, nil}

iex(4)> EdtrApi.insert_edtrs()                  
[debug] QUERY OK db=7.4ms queue=2.1ms
INSERT INTO "edtrs" ("dtr_date","user_id","inserted_at","updated_at") VALUES ($1,$2,$3,$4) RETURNING "id" [#DateTime<2018-12-29 16:00:00Z>, "8189f04b-e3d7-4d17-8df2-fedbeb0399b1", ~N[2018-12-11 09:11:18], ~N[2018-12-11 09:11:18]]
[debug] QUERY OK db=2.3ms queue=1.0ms
INSERT INTO "edtrs" ("dtr_date","user_id","inserted_at","updated_at") VALUES ($1,$2,$3,$4) RETURNING "id" [#DateTime<2018-12-30 16:00:00Z>, "8189f04b-e3d7-4d17-8df2-fedbeb0399b1", ~N[2018-12-11 09:11:18], ~N[2018-12-11 09:11:18]]
[
  ok: %Myapp.EdtrApi.Edtr{
    __meta__: #Ecto.Schema.Metadata<:loaded, "edtrs">,
    dtr_date: #DateTime<2018-12-29 16:00:00Z>,
    id: 1,
    inserted_at: ~N[2018-12-11 09:11:18],
    updated_at: ~N[2018-12-11 09:11:18],
    user_id: "8189f04b-e3d7-4d17-8df2-fedbeb0399b1"
  },
  ok: %Myapp.EdtrApi.Edtr{
    __meta__: #Ecto.Schema.Metadata<:loaded, "edtrs">,
    dtr_date: #DateTime<2018-12-30 16:00:00Z>,
    id: 2,
    inserted_at: ~N[2018-12-11 09:11:18],
    updated_at: ~N[2018-12-11 09:11:18],
    user_id: "8189f04b-e3d7-4d17-8df2-fedbeb0399b1"
  }
]

iex(5)>

    In the output, you can see that inside the Edtr structs that were returned the date string got converted to a DateTime struct. Calling cast() is what did that.

    Okay, now lets delete a user_id from the data:

      def get_edtrs() do 
    [
      %{
          "dtr_date" => "2018-12-29T16:00:00.000Z",
          "user_id" => "8189f04b-e3d7-4d17-8df2-fedbeb0399b1"
      },
      %{
          "dtr_date" => "2018-12-30T16:00:00.000Z",
      },
    ]
  end

    and try it again:

    ~/phoenix_apps/myapp$ iex -S mix
Erlang/OTP 20 [erts-9.3] [source] [64-bit] [smp:4:4] [ds:4:4:10] [async-threads:10] [hipe] [kernel-poll:false]
Compiling 1 file (.ex)
Interactive Elixir (1.6.6) - press Ctrl+C to exit (type h() ENTER for help)

iex(1)> alias Myapp.EdtrApi.Edtr   
Myapp.EdtrApi.Edtr

iex(2)> EdtrApi.insert_edtrs()     
[debug] QUERY OK db=4.4ms decode=2.9ms queue=1.5ms
INSERT INTO "edtrs" ("dtr_date","user_id","inserted_at","updated_at") VALUES ($1,$2,$3,$4) RETURNING "id" [#DateTime<2018-12-29 16:00:00Z>, "8189f04b-e3d7-4d17-8df2-fedbeb0399b1", ~N[2018-12-11 09:12:17], ~N[2018-12-11 09:12:17]]
[
  ok: %Myapp.EdtrApi.Edtr{
    __meta__: #Ecto.Schema.Metadata<:loaded, "edtrs">,
    dtr_date: #DateTime<2018-12-29 16:00:00Z>,
    id: 3,
    inserted_at: ~N[2018-12-11 09:12:17],
    updated_at: ~N[2018-12-11 09:12:17],
    user_id: "8189f04b-e3d7-4d17-8df2-fedbeb0399b1"
  },
  error: #Ecto.Changeset<
    action: :insert,
    changes: %{dtr_date: #DateTime<2018-12-30 16:00:00Z>},
    errors: [user_id: {"can't be blank", [validation: :required]}],
    data: #Myapp.EdtrApi.Edtr<>,
    valid?: false
  >
]

iex(3)>

    If you examine the last portion of output, notice:

    1. The second insert returned a Changeset struct rather than an Edtr struct like the first insert.

    2. The second insert() returned error: ... rather than ok: ..., and the Changeset contains:

      errors: [user_id: {"can't be blank", [validation: :required]}],

    Then, if you list everything in the database table:

    iex(4)> EdtrApi.all             
[debug] QUERY OK source="edtrs" db=2.8ms queue=2.0ms
SELECT e0."id", e0."dtr_date", e0."user_id", e0."inserted_at", e0."updated_at" FROM "edtrs" AS e0 []
[
  %Myapp.EdtrApi.Edtr{
    __meta__: #Ecto.Schema.Metadata<:loaded, "edtrs">,
    dtr_date: #DateTime<2018-12-29 16:00:00Z>,
    id: 1,
    inserted_at: ~N[2018-12-11 09:11:18],
    updated_at: ~N[2018-12-11 09:11:18],
    user_id: "8189f04b-e3d7-4d17-8df2-fedbeb0399b1"
  },
  %Myapp.EdtrApi.Edtr{
    __meta__: #Ecto.Schema.Metadata<:loaded, "edtrs">,
    dtr_date: #DateTime<2018-12-30 16:00:00Z>,
    id: 2,
    inserted_at: ~N[2018-12-11 09:11:18],
    updated_at: ~N[2018-12-11 09:11:18],
    user_id: "8189f04b-e3d7-4d17-8df2-fedbeb0399b1"
  },
  %Myapp.EdtrApi.Edtr{
    __meta__: #Ecto.Schema.Metadata<:loaded, "edtrs">,
    dtr_date: #DateTime<2018-12-29 16:00:00Z>,
    id: 3,
    inserted_at: ~N[2018-12-11 09:12:17],
    updated_at: ~N[2018-12-11 09:12:17],
    user_id: "8189f04b-e3d7-4d17-8df2-fedbeb0399b1"
  }
]

iex(5)>

    ...you can see that the changeset with the error did not insert anything in the database because there are only three entries.