Ecto will not insert a :date field into the database - why?

Question

I am facing an issue while learning Elixir & Ecto. The idea is to build a standard posts/comments page to understand how the basics work. I am at a point where I have schemas defined, a migration written and encounter an error when trying to insert data into the database (PostgreSQL) via the Repo. I have done a fair deal of web searching and documentation reading, which leads me to believe it's a scenario that should just work and I am making a stupid mistake somewhere, which I just can't see.

They are defined as follows:

lib/hello/schemas.ex

defmodule Hello.PostAuthor do
    use Ecto.Schema

    schema "post_authors" do
        field :name, :string
    end
end

defmodule Hello.CommentAuthor do
    use Ecto.Schema

    schema "comment_authors" do
        field :name, :string
    end
end

defmodule Hello.Comment do
    use Ecto.Schema

    schema "comments" do
        has_one :author, Hello.CommentAuthor
        field :date, :date
        field :body, :string
    end
end

defmodule Hello.Post do
    use Ecto.Schema

    schema "posts" do
        has_one :author, Hello.PostAuthor
        field :date, :date
        field :body, :string
        has_many :comments, Hello.Comment
    end
end

as you can see, I have two fields with :date type - on post and comment schemas. The corresponding migration is as follows:

defmodule Hello.Repo.Migrations.CreatePosts do
  use Ecto.Migration

  def change do
    create table(:post_authors) do
      add :name, :string
    end

    create table(:comment_authors) do
      add :name, :string
    end

    create table(:comments) do
      add :author, references(:comment_authors)
      add :date, :date
      add :body, :string
    end

    create table(:posts) do
      add :author, references(:post_authors), null: false
      add :date, :date
      add :body, :string
      add :comments, references(:comments)

      timestamps()
    end
  end
end

Now, when I start iex -S mix I can successfully create all structs:

iex(1)> post_author = %Hello.PostAuthor{name: "John"} 
%Hello.PostAuthor{
  __meta__: #Ecto.Schema.Metadata<:built, "post_authors">,
  id: nil,
  name: "John"
}

iex(2)> comment_author = %Hello.PostAuthor{name: "Adam"}
%Hello.PostAuthor{
  __meta__: #Ecto.Schema.Metadata<:built, "post_authors">,
  id: nil,
  name: "Adam"
}


iex(3)> comment = %Hello.Comment{author: comment_author, date: ~D[2019-01-01], body: "this is a comment"}
%Hello.Comment{
  __meta__: #Ecto.Schema.Metadata<:built, "comments">,
  author: %Hello.PostAuthor{
    __meta__: #Ecto.Schema.Metadata<:built, "post_authors">,
    id: nil,
    name: "Adam"
  },
  body: "this is a comment",
  date: ~D[2019-01-01],
  id: nil
}

iex(4)> post = %Hello.Post{author: post_author, date: ~D[2019-01-01], body: "this is a post", comments: [comment]}
%Hello.Post{
  __meta__: #Ecto.Schema.Metadata<:built, "posts">,
  author: %Hello.PostAuthor{
    __meta__: #Ecto.Schema.Metadata<:built, "post_authors">,
    id: nil,
    name: "John"
  },
  body: "this is a post",
  comments: [%Hello.Comment{
    __meta__: #Ecto.Schema.Metadata<:built, "comments">,
    author: %Hello.PostAuthor{
      __meta__: #Ecto.Schema.Metadata<:built, "post_authors">,
      id: nil,
      name: "Adam"
    },
    body: "this is a comment",
    date: ~D[2019-01-01],
    id: nil
  }],
  date: ~D[2019-01-01],
  id: nil
}

The problem arises when I call Hello.Repo.insert(post) (where post is the struct representing the Hello.Post schema). I receive what looks like serialization error:

iex(8)> Hello.Repo.insert(post)                                                                     [debug] QUERY OK db=0.1ms
begin []
[debug] QUERY ERROR db=1.6ms
INSERT INTO "posts" ("body","date") VALUES ($1,$2) RETURNING "id" ["this is a post", ~D[2019-01-01]]
[debug] QUERY OK db=0.1ms
rollback []
** (DBConnection.EncodeError) Postgrex expected a binary, got ~D[2019-01-01]. Please make sure the value you are passing matches the definition in your table or in your query or convert the value accordingly.
    (postgrex) lib/postgrex/type_module.ex:897: Postgrex.DefaultTypes.encode_params/3
    (postgrex) lib/postgrex/query.ex:75: DBConnection.Query.Postgrex.Query.encode/3
    (db_connection) lib/db_connection.ex:1148: DBConnection.encode/5
    (db_connection) lib/db_connection.ex:1246: DBConnection.run_prepare_execute/5
    (db_connection) lib/db_connection.ex:540: DBConnection.parsed_prepare_execute/5
    (db_connection) lib/db_connection.ex:533: DBConnection.prepare_execute/4
    (postgrex) lib/postgrex.ex:198: Postgrex.query/4
    (ecto_sql) lib/ecto/adapters/sql.ex:666: Ecto.Adapters.SQL.struct/10
    (ecto) lib/ecto/repo/schema.ex:651: Ecto.Repo.Schema.apply/4
    (ecto) lib/ecto/repo/schema.ex:262: anonymous fn/15 in Ecto.Repo.Schema.do_insert/4
    (ecto) lib/ecto/repo/schema.ex:916: anonymous fn/3 in Ecto.Repo.Schema.wrap_in_transaction/6
    (ecto_sql) lib/ecto/adapters/sql.ex:898: anonymous fn/3 in Ecto.Adapters.SQL.checkout_or_transaction/4
    (db_connection) lib/db_connection.ex:1415: DBConnection.run_transaction/4

This is where I am lost. Both the schema and the migration are expecting a :date . I believe that ~D[2019-01-01] is a date. PostgreSQL defines date as a 4 byte binary value. I am expecting Ecto.Adapters.Postgres to translate elixir date struct into the Postgres binary value. This is not happening. Why?

Aleksei Matiushkin Aleksei Matiushkin · Accepted Answer · 2019-11-28T13:45:21

Struct itself is just raw data. You should go through Ecto.Changeset as shown in the documentation, specifically to all types to be cast to the respective DB types with Ecto.Changeset.cast/4.

The conversion will be done automagically, but you need to explicitly call cast/4 (hence the Changeset,) otherwise the adapter has no idea of how to convert your ecto types.

Ecto will not insert a :date field into the database - why?

1 Answers