Many to Many - create a new item on the other end - I get inserted at missing null value

roganjoshua

I have a many_to_many relationship ( I guess there really isn’t such a thing) but a junction table nevertheless.

erDiagram
    USER_GAMES {
        int user_id
        int game_id
    }
    USER {
        int id
        string name
    }
    GAME {
        int id
        string name
    }

  schema "users" do
    field :email, :string
    many_to_many :games, Game, join_through: "users_games"
    timestamps(type: :utc_datetime)
  end

 schema "games" do
    field :name, :string
    field :state, :map
    field :started_at, :utc_datetime
    many_to_many :users, User, join_through: "users_games"
    timestamps(type: :utc_datetime)
  end

No schema for users_games but in the migration

  create table("users_games", primary_key: false) do
      add :user_id,
          references(:users, on_delete: :delete_all),
          null: false

      add :game_id,
          references(:games, on_delete: :delete_all),
          null: false

      timestamps()
    end

    create unique_index(:users_games, [:user_id, :game_id])
  end
end

The action happens here

    new_game = %Game{
      name: attrs.name,
      started_at: attrs.started_at
    }

    user =
      Accounts.get_user_with_games(user_id)
      |> Ecto.Changeset.change()
      |> Ecto.Changeset.put_assoc(:games, [new_game])
      |> Repo.update!()

where get_user_with_games is

    get_user!(user_id)
    |> Repo.preload(:games)

I realise right now I need to include any existing related games but I can get to that later.

I get this

[debug] QUERY OK db=1.0ms idle=895.3ms
begin []
↳ ScrabbleWeb.GameLive.Form.save_game/3, at: lib/scrabble_web/live/game_live/form.ex:87
[debug] QUERY OK source="games" db=1.6ms
INSERT INTO "games" ("name","started_at","inserted_at","updated_at") VALUES ($1,$2,$3,$4) RETURNING "id" ["ABC", ~U[2026-02-12 19:12:43Z], ~U[2026-02-12 19:12:43Z], ~U[2026-02-12 19:12:43Z]]
↳ ScrabbleWeb.GameLive.Form.save_game/3, at: lib/scrabble_web/live/game_live/form.ex:87
[debug] QUERY ERROR source="users_games" db=0.5ms
INSERT INTO "users_games" ("user_id","game_id") VALUES ($1,$2) [1, 6]
↳ ScrabbleWeb.GameLive.Form.save_game/3, at: lib/scrabble_web/live/game_live/form.ex:87
[debug] QUERY OK db=0.2ms
rollback []
↳ ScrabbleWeb.GameLive.Form.save_game/3, at: lib/scrabble_web/live/game_live/form.ex:87
[error] GenServer #PID<0.1178.0> terminating
** (Postgrex.Error) ERROR 23502 (not_null_violation) null value in column "inserted_at" of relation "users_games" violates not-null constraint

    table: users_games
    column: inserted_at

Which looks so close…it creates a transaction creates the game but when it is inserting into users_games it omits the timestamps.

What am I missing?

It looks like building the query

INSERT INTO "users_games" ("user_id","game_id") VALUES ($1,$2) [1, 6]

should know there are timestamp fields that could be populated for this.

Should I sack the many to many and create an intermediate entity and and have two one_to_many relationships and do it manually?

9 comments

#ecto #many_to_many

1 315 9

2026-02-15 01:57:00 UTC

Marked As Solved

Show Best Posts
Show All Posts (oldest first)
Show All Posts (newest first)

roganjoshua

I have update the schema and migration according to your suggestions

   @primary_key false
  schema "users_games" do
    field :user_id, :id, primary_key: true
    belongs_to :user, User, define_field: false

    field :game_id, :id, primary_key: true
    belongs_to :game, Game, define_field: false
    timestamps()
  end

and

    create table(:users_games, primary_key: false) do
      add :user_id,
          references(:users, on_delete: :delete_all),
          null: false,
          primary_key: true

      add :game_id,
          references(:games, on_delete: :delete_all),
          null: false,
          primary_key: true

      timestamps()
    end

And this now works without the id column

Post #8

Also Liked

linusdm

Since you’re using the :join_through option with a string value, there is nothing more that Ecto knows about this join table. It knows the two columns with the foreign keys (using the _id suffix). But it knows nothing about your timestamp columns.

Read the documentation on Ecto.Schema.many_to_many again, and you’ll see they make a distinction between passing in just a string, and passing in a schema.

From the docs:

However, if your :join_through is a schema, like MyApp.PostTag, your join table may be structured as any other table in your codebase, including timestamps

and:

Because :join_through contains a schema, in such cases, autogenerated values and primary keys will be automatically handled by Ecto.

There are many other ways to model many-to-many relations in Ecto. But if the missing timestamp is your only issue right now, I’d define the schema, and you’re all set.

You could also define a default value (NOW()) for a inserted_at column in your migration. Then the database handles setting this value for you, instead of Ecto. But you would still need to model a schema for the join table if you need the timestamp in your application somewhere.

Post #2

jdiago

Do you need the timestamp on the join table? If not, delete it.

If you do need it, I think doing the many-to-many through a join schema should work. Associations — Ecto v3.14.0

edit: also, this section of the guide talks about the differences between having a join schema and not. Polymorphic associations with many to many — Ecto v3.14.0

Post #1

LostKobrakai

You don’t need that if you configure the schema to not use an :id column. Once you’re using a schema you can use it to provide all manner of additional information to ecto.

Where exactly do you read it like that. Imo the documentation has always been very explicit that no-schema means no additional columns – especially given in the past there was no option for providing a schema at all, so this really needed to be explicit.

Post #5