Ecto insert does not fill timestamp fields automatically

Question

When I try to insert a row into my database using Ecto:

sla = %MyMetrics.MonthlySLA{metric_name: "x", metric_type: "y", value: 43.33, metric_date: Date.new!(2020,1,1)} 
MyMetrics.Repo.insert(sla)

I get this error:

17:44:26.704 [debug] QUERY ERROR db=134.8ms queue=143.8ms idle=1397.2ms
INSERT INTO `monthly_slas` (`metric_date`,`metric_name`,`metric_type`,`value`) VALUES (?,?,?,?) [~D[2020-01-01], "x", "y", #Decimal<43.33>]
** (MyXQL.Error) (1364) (ER_NO_DEFAULT_FOR_FIELD) Field 'inserted_at' doesn't have a default value
    (ecto_sql 3.5.3) lib/ecto/adapters/myxql.ex:241: Ecto.Adapters.MyXQL.insert/6
    (ecto 3.5.5) lib/ecto/repo/schema.ex:649: Ecto.Repo.Schema.apply/4
    (ecto 3.5.5) lib/ecto/repo/schema.ex:262: anonymous fn/15 in Ecto.Repo.Schema.do_insert/4

My Ecto model .

defmodule MyMetrics.MonthlySLA do

  use Ecto.Schema
  import Ecto.Changeset

  schema "monthly_slas" do
    field :metric_name, :string
    field :metric_type, :string
    field :metric_date, :date
    field :value, :decimal
  end

  def changeset(monthly_sla, params \\ %{}) do
    monthly_sla
    |> Ecto.Changeset.cast(params, [:metric_name, :metric_type, :metric_date, :value])
    |> Ecto.Changeset.validate_required([:metric_name, :metric_type, :metric_date, :value])
  end

end

and my migration definition:

defmodule MyMetrics.Repo.Migrations.CreateMonthlySla do
  use Ecto.Migration

  def change do
    create table(:monthly_slas) do
      add :metric_name, :string, null: false
      add :metric_type, :string, null: false
      add :metric_date, :date, null: false
      add :value, :decimal, precision: 10, scale: 2, null: false

      timestamps()
    end

    create unique_index(:monthly_slas, [:metric_name, :metric_date])
    create unique_index(:monthly_slas, [:metric_date, :metric_name])
    create unique_index(:monthly_slas, [:metric_type, :metric_name, :metric_date])
    create unique_index(:monthly_slas, [:metric_date, :metric_type, :metric_name])
  end
end

How to fix this issue?

Answer 1

You need to add timestamps() to your Ecto model. Your migration defines the schema for the inserted_at and updated_at fields, but your code needs to provide those fields when the model is being inserted into the table. That's what the timestamps() macro is for.

defmodule MyMetrics.MonthlySLA do

  use Ecto.Schema
  import Ecto.Changeset

  schema "monthly_slas" do
    field :metric_name, :string
    field :metric_type, :string
    field :metric_date, :date
    field :value, :decimal
    # Add this
    timestamps()
  end

  def changeset(monthly_sla, params \\ %{}) do
    monthly_sla
    |> Ecto.Changeset.cast(params, [:metric_name, :metric_type, :metric_date, :value])
    |> Ecto.Changeset.validate_required([:metric_name, :metric_type, :metric_date, :value])
  end

end