Not_null_violation on seeding my database: timestamps() macro failing?

Questions / Help
,
1

I’m able to create and migrate my database tables Ok. But when I try to seed it, I’m getting errors as soon as I encounter a table that uses the timestamps() macro in its migration definition:

** (Postgrex.Error) ERROR 23502 (not_null_violation) null value in column "inserted_at" violates not-null constraint

    table: tenants
    column: inserted_at

My migration looks like this:

defmodule Auth.Repo.Migrations.Tenants do
  use Ecto.Migration

  def change do
    create table(:tenants, primary_key: false) do
      add :id, :string, size: 16, null: false, primary_key: true # Slug
      add :status_id, references("tenants_statuses", type: :string), size: 10, null: false
      add :name, :string, size: 32, null: false, default: ""
      add :description, :text, null: false, default: ""
      timestamps()
    end
  end
end

And my seeds.exs like this:

alias Auth.Repo

Repo.insert_all(
  "tenants_statuses",
  [
    %{id: "on", name: "On", description: "Signups and logins for this tenant are enabled"},
    %{id: "off", name: "Off", description: "Signups and logins for this are disabled"},
  ]
)
Repo.insert_all(
  Auth.Schemas.Tenant,
  [
    %{
      id: "wholesale",
      name: "Wholesale",
      status_id: "on",
      description: "Deny Designs and the Wholesale channel",
    }
  ]
)

I’ve tried using both the string table name as the 1st argument or the schema module, but the error is the same. When I inspect the table inside PostGreSQL, I see that it has not defined a default value:

Column    |              Type              | Collation | Nullable |        Default        | Storage  | Stats target | Description 
-------------+--------------------------------+-----------+----------+-----------------------+----------+--------------+-------------
id          | character varying(16)          |           | not null |                       | extended |              | 
status_id   | character varying(10)          |           | not null |                       | extended |              | 
name        | character varying(32)          |           | not null | ''::character varying | extended |              | 
description | text                           |           | not null | ''::text              | extended |              | 
inserted_at | timestamp(0) without time zone |           | not null |                       | plain    |              | 
updated_at  | timestamp(0) without time zone |           | not null |                       | plain    |              |

I thought that using timestamps() would set up a default value for both of those columns, but clearly I’m missing something. Can anyone point out what I’m missing? Thanks!

1 Like
2

:wave:

The defaults come from your ecto schemas (if you use another timestamps() macro there), they are not generated in the database.

So since *_all functions like Repo.insert_all don’t use ecto schemas, there are no default timestamps being sent to the database thus violating the constraint.

3 Likes
3

THANK YOU! I did not think of that. Ok, that makes sense: you can set some database-level constraints in PostGreSQL, setting up an “on update CURRENT TIMESTAMP” (or whatever the MySQL equivalent is) is more complicated in PostGreSQL and it requires a trigger. So instead, that functionality is handled by code in the Schema. That makes sense. As soon as I adjusted my seeds to use Repo.insert/1 it worked.

Repo.insert(
    %Auth.Schemas.Tenant{
      id: "wholesale",
      name: "Wholesale",
      status_id: "on",
      description: "Deny Designs and the Wholesale channel",
    }
)
4

Minor clarification, Repo.insert_all does use ecto schemas but does not autogenerate default values. From the docs for Repo.insert_all/3:

If the schema contains an autogenerated ID field, it will be handled either at the adapter or the storage layer. However any other autogenerated value, like timestamps, won’t be autogenerated when using insert_all/3 . This is by design as this function aims to be a more direct way to insert data into the database without the conveniences of insert/2 . This is also consistent with update_all/3 that does not handle timestamps as well.

5 Likes
5

Further clarification: Repo.insert_all MAY use Ecto Schemas (but it is not required to do so: you can simply pass the name of the table as a string). At various points I have tried removing my schema files altogether to avoid having my seeding operation dependent on them – it works fine (excepting the timestamps as discussed)

3 Likes
6

True! And also true for Repo.insert :slight_smile:

1 Like
7

In order to freely use both schema and schemaless queries and don’t bother yourself manually updating timestamps, you must move autoupdating logic from timestamps macro to the database layer. In PostgreSQL you can use the built-in moddatetime extension and its function with a trigger. Take a look at my examples.

2 Likes