Response of insert doesn't contain default values

Questions / Help
1

After insert via Ecto, the response doesn’t contain default values.
ex)

# Migration
create table(:models) do
  add :date, :naive_datetime, null: false, default: fragment("now()")
end

# Model
schema "models" do
  field(:date, :naive_datetime)
end

Repo.insert(Model, %{})
=>
{:ok,
 %Model{
   __meta__: #Ecto.Schema.Metadata<:loaded, "models">,
   date: nil
 }}

But when I load that model, it contains date.

%Model{
   __meta__: #Ecto.Schema.Metadata<:loaded, "models">,
   date: ~N[2018-02-06 11:47:41.517655]
 }

What should I do to get response from Ecto insert that contains default values?

2 Likes
2

Probably not the answer you are looking for, but you might add it to the ecto schema.

schema "model" do
  field(:datetime, :naive_datetime, default: NaiveDateTime.utc_now())
end

Actually, not sure if that would work. :default might be “compilte-time” and then all your structs would have the datetime of compilation.

I think the reason that you don’t get the default value from your database back, is that it just sets it, and doesn’t actually send it. I might be wrong.

3

It is returning the structs it sent to the database, not the values the database inserted. This is obviously more performant than always fetching data back from the database when that isn’t needed. (And not all databases support returning inserted values, which means the worse case is actually having to issue a second query, not just parsing the response to an insert!) For large numbers of insertions (or small numbers of large insertions :wink: ) this makes a big difference.

You can, however, get Ecto to comply. From the documentation:

If you need to guarantee the data in the returned struct mirrors the database, you have three options:

Use on_conflict: :replace_all, although that will replace all fields in the database with current ones:

MyRepo.insert(%Post{title: "this is unique"},
              on_conflict: :replace_all, conflict_target: :title)

Specify read_after_writes: true in your schema for choosing fields that are read from the database after every operation. Or pass returning: true to insert to read all fields back:

MyRepo.insert(%Post{title: "this is unique"}, returning: true,
              on_conflict: on_conflict, conflict_target: :title)

Alternatively, read the data again from the database in a separate query. This option requires the primary key to be generated by the database:

{:ok, updated} = MyRepo.insert(%Post{title: "this is unique"}, on_conflict: on_conflict)
Repo.get(Post, updated.id)
7 Likes
4

This is the reason, why default values are better kept in the schema in my opinion. Simply make the db column NOT NULL if it’s required to be filled. This way it’s also easier to have multiple defaults if you’d have multiple ecto schemas writing to the same table.

2 Likes
5

Agreed, with some exceptions (there are always exceptions! :wink: ), such as when the default is not a simple built-in data type but a function that relies on some database state. In such cases, the most sensible solution may be to resort to load-on-insert. But yeah, for simple defaults, in the schema and just rely on the database to enforce things like no null values, must be a valid date, etc.

6

Thanks!