drizzle-orm with multiple many-to-one relations - Please specify relation name at normalizeRelation

Question

I have two tables and two relations that I have defined as follows:

export const games = pgTable('games', {
    id: serial('id').primaryKey(),
    player1Id: integer('player1_id'),
    player1Scores: json('player1_scores'),

    player2Id: integer('player2_id'),
    player2Scores: json('player2_scores'),

    winnerId: integer('winner_id'),
});

export const players = pgTable('players', {
    id: serial('id').primaryKey(),
    email: varchar('email'),
    name: varchar('name', { length: 256 }),
});

export const playersRelations = relations(players, ({ many }) => ({
    games: many(games),
}));

export const gamesRelations = relations(games, ({ one }) => ({
    player1: one(players, { fields: [games.player1Id], references: [players.id] }),
    player2: one(players, { fields: [games.player2Id], references: [players.id] }),
    winner: one(players, { fields: [games.winnerId], references: [players.id] }),
}));

However, I get the error message with the gamesRelations that there are "multiple relations between 'games' and 'players'). If I remove the entry with 'player2' and 'winner' from the gamesRelation, it works. But then the information is missing.

Probably the error is relatively simple, but I have no more ideas and am grateful for any help.

Error message:

There are multiple relations between "games" and "players". Please specify relation name at normalizeRelation

Hi Sebastian. Did you find another solution to this problem? — Dewaun Ayers, Jul 31 '23 at 11:30

Dewaun Ayers · Answer 1 · 2023-07-25T03:30:16.687

This error is just telling you that it has found many relations that share a reference to players, and it won't know which field is the winner, player1 or player2 when you query your gamesRelations

All you have to do is give the player1, player2 and winner relations names, as the error suggests.

The one function has a relationName property that can be passed to with the config.

one(
    your_table, 
    { 
      fields: [...], 
      references: [...], 
      // Optional `relation name` field used for disambiguating relations
      relationName: '...' 
    }
)

Make the following changes and I think this will fix your issue.

export const gamesRelations = relations(games, ({ one }) => ({
  player1: one(players, { fields: [games.player1Id], references: [players.id], relationName: 'game_player1' } ),
  player2: one(players, { fields: [games.player2Id], references: [players.id], relationName: 'game_player2' }),
  winner: one(players, { fields: [games.winnerId], references: [players.id], relationName:  'game_winner' }),
}));

This isn't mentioned in the docs but you can find the code for this in this spots in their GH repo:

RelationConfig Interface -- the type for the config you're passing into one: https://github.com/drizzle-team/drizzle-orm/blob/main/drizzle-orm/src/relations.ts#L290

createOne Function -- the one function you're using above: https://github.com/drizzle-team/drizzle-orm/blob/main/drizzle-orm/src/relations.ts#L384

createTableRelationsHelpers Function -- the relations callback function that returns the one function: https://github.com/drizzle-team/drizzle-orm/blob/main/drizzle-orm/src/relations.ts#L488

drizzle-orm with multiple many-to-one relations - Please specify relation name at normalizeRelation

1 Answers1