Why does Ecto expect primary keys in every table?

Questions / Help
1

Why does Ecto expect primary key in every table? Or maybe I have not understood something right?

Here is the rooms schema.

 @primary_key {:id, :binary_id, autogenerate: true}
  schema "rooms" do
    field :name, :string
   
    belongs_to :created_by_user, KayaanChat.Accounts.User
    has_many :room_members, RoomMember

    timestamps(type: :utc_datetime)
  end

Here is the room_members schema

@primary_key false
  schema "room_members" do
   ...

    belongs_to :room, KayaanChat.Rooms.Room, type: :binary_id
    belongs_to :user, User, type: :id

    timestamps(type: :utc_datetime)
  end
  1. The below query returns a blank array
from(r in Room,
      join: rm in KayaanChat.Rooms.RoomMember, on: rm.room_id == r.id,
      where: rm.user_id == ^user_id,
      preload: [room_members: rm]
    )
    |> Repo.all()

  1. Also join: rm in assoc(r, :room_members), doesnt work.

  2. Also using # has_many :rooms_joined, through: [:room_members, :room] in the user table doesnt work.

Do I have to give a primary-key for every table I create to use Ecto?

2

No, you don’t have to. It’s just the default. You could just as easily make the primary key of a many-to-many table to be the two foreign keys to the other tables if you like.

5 Likes
3

Be warned though: there are parts of Ecto that do not work with composite foreign keys.

4 Likes
4

Preloading requires PKs but I don’t think many other features of Ecto require them. This works for me:

    room = Repo.insert!(%Room{})
    user = Repo.insert!(%User{})
    Repo.insert!(%RoomMember{room_id: room.id, user_id: user.id})

    from(r in Room,
      join: rm in assoc(r, :room_members),
      where: rm.user_id == ^user.id
    )
    |> Repo.all()
    |> IO.inspect()

Full snippet here: ecto_sql.exs · GitHub

But yeah, if you want to use preloads, add a PK.

4 Likes
5

Hi!

Thanks for explanation & code. That was really helpful. The issue was in my create query :sweat_smile:

I am having trouble selecting the field & getting the values. I tried |> select_merge([_r, rm], %{room_members: rm}) but this doesnot give me an array of all room_members. I tried some aggreegate (array_agg) & grouping logic but couldnt make it work.

Some help would be great. Thanks :slight_smile:

6

/me pours a glass of Lagavulin, lights a cigar/

To answer your question on why Ecto needs primary keys in every database table (almost) you have to go back to 2005, when a framework Ruby on Rails was first published.

What Ruby on Rails uses as their database access layer is something called ActiveRecord which implements Martin Fowler’s pattern of DAO mapping database tables to Objects (as in Object-Oriented Programming Objects).

Jose Valim used to be a Ruby on Rails developer, and a prominent one in Open Source Ruby community.

Both Phoenix and Ecto inherited a lot from Ruby on Rails, and Ecto as well.

You do not have to use primary keys in Ecto these days in every table, as suggested above. But it is still the default. And you’re probably better off doing so, as some features of Ecto will depend on it (like mentioned preloads). Ecto is now much more flexible than that, but if you’re just starting, follow ActiveRecord pattern and you’ll avoid many traps.

2 Likes
7

Hi! Thanks for that bit of history… Sadly I dont come from RoR, I come from JS/TS, where the things are a lot more malleable.

I am finding Ecto to be not straight forward & really confusing compared to the tools available in TS ecosystem. I’ll make it through & am hoping I can see the light that gets other developers to love Ecto so much :slight_smile:

8

Interesting insights.
I tried so many JS solutions and none of them were reliable and made any sense, until Ecto.
I strongly recommend the Ecto book:

1 Like
9

I read somewhere that the first language (coding) one learn influences their thought patterns & ofcourse familiarity to higher concepts based on that… I am sure we both are victims to same… Also I am not really fan of JS but absolutely love the TS ecosystem…

You may like to try OrchidORM, I found it to be the most complete solution for PostgreSQL, it ofcourse has its own challenges though…

I will try to list my understanding of differences so far with the intention to learn from this discussion… Also since Ecto is not an ORM, its not an apples-to-apples comparision…

  1. This arbitary condition from this thread of having primary-key in all tables. I know its not mandatory but seeing the workarounds, I am not going to miss it now.

  2. The workaround of managing seperate Structs, Schemas & Changesets is such a pain!!! In OrchidORM you define the schema once & get everything out of the box. You get seperate types & validations for each CRUD operation.

  3. Also most JS/TS frameworks give migrations out-of-the-box. Very rarely I have had to work on the migration files directly. Also changing the schema mostly creates migrations files automatically, here all of that is manual but then Ecto is not an ORM.

  4. Ecto makes me give the whole record in update queries. I like to do atomic updates. I think I have mostly not understood this part yet.

  5. Also the examples I have seen so far first make you query the record & then send an update. I like to do atomic updates with just the primary key or specific queries.

I will definitely read Programming Ecto, it was in my to-read but still start over today itself. Looking forward to start thinking in Elixir, Phoenix & Ecto.

1 Like