Schema and query to get user with list of friends

userFriendsRelations.ts

// Schema

export const users = createTable("users", {
  id: text("id")
    .primaryKey()
    .$defaultFn(() => crypto.randomUUID()),
  username: text("username").unique().notNull(),
  email: text("email").unique().notNull(),
  password: text("password").notNull(),
});

export const usersRelations = relations(users, ({ one, many }) => ({
  friends: many(userFriends, {
    relationName: "user-friends",
  }),
}));

export const friendshipStatusEnum = pgEnum("status", ["pending", "accepted", "canceled"]);
export const userFriends = createTable(
  "user_friends",
  {
    id: serial("id").primaryKey(),
    requestUserId: text("request_user_id").notNull(),
    targetUserId: text("target_user_id").notNull(),
    status: friendshipStatusEnum("status").notNull(),
    lastUpdate: timestamp("last_update")
      .notNull()
      .default(sql`CURRENT_TIMESTAMP`),
  },
  (table) => ({
    friends: index("friends_index").on(table.requestUserId, table.targetUserId),
  }),
);

export const friendsUserRelations = relations(userFriends, ({ one }) => ({
  requestUser: one(users, {
    fields: [userFriends.requestUserId],
    references: [users.id],
    relationName: "requested-friends",
  }),
  targetUser: one(users, {
    fields: [userFriends.targetUserId],
    references: [users.id],
    relationName: "targeted-friends",
  }),
}));

// Query

await db.query.users.findFirst({
  where: (user, { eq }) => eq(user.username, username),
  with: {
    friends: {
      where: and(
        eq(userFriends.requestUserId, "user id"),
        eq(userFriends.targetUserId, "user id"),
        ne(userFriends.status, "canceled"),
      ),
      with: {
        targetUser: {
          with: {
            profile: true,
          },
        },
        requestUser: {
          with: {
            profile: true,
          },
        },
      },
    },
  },
});