Add UNION support

[dankochetov]

[cr101]

Union Types / Polymorphic Associations are a common use case. It's one of the most requested Prisma features which was first requested 3 years ago and it still hasn't been impletemented.

I'm currently building a web app using PostgreSQL where shareholders of a company could be either or both an Organization or an Individual Person and I'm having trouble deciding which approach to follow in order to avoid null columns in the Shareholders table

const shareholders = pgTable("shareholders", {
    id: serial("id").primaryKey(),
    investorId: integer('investor_id').references(() => people.id OR organizations.id),
    createdAt: timestamp('created_at').defaultNow().notNull()
  }, (table) => ({
    investorIdx: index("investor_idx", table.investorId),
  })
);

const organizations = pgTable("organizations", {
    id: serial("id").primaryKey(),
    name: text('name').notNull(),
    description: text('name').notNull(),
    status: operatingStatusEnum('active'),
    foundedOn: date('founded_on')
    websiteUrl: text('name').notNull(),
    createdAt: timestamp('created_at').defaultNow().notNull(),
  }, (table) => ({
    nameIdx: index("name_idx", table.name),
  })
);

const people = pgTable("people", {
    id: serial("id").primaryKey(),
    firstName: text('first_name').notNull(),
    middleName: text('middle_name').notNull(),
    lastName: text('last_name').notNull(),
    gender: genderEnum('female'),
    createdAt: timestamp('created_at').defaultNow().notNull(),
  }, (table) => ({
    firstLastNameIdx: index("first_last_name_idx").on(people.firstName, people.lastName)
  })
);

[dankochetov]

One solution would be a many:many table between shareholders and people/organizations, so that shareholders will always reference a row in that table. But then the many:many table will have null columns.

[cr101]

Yes, the many:many table will have null columns which I'd like to avoid. The same Prisma feature is discussed at length with all the use cases here and here

[dankochetov]

OK, so if I understand correctly: with unions, you could select shareholders + inner join organizations, and then union select shareholders + inner join people. In that case, the inverstorId won't be a foreign key. Is that what you want to do?

[cr101]

investorId needs to be a foreign key and also add a new column investorType to the shareholders table. The problem is that I can't set investorId to be a foreign key to both tables organizations and shareholders in the Drizzle schema

[luxaritas]

As far as schema modeling, could this be handled by adding an extra table? That is, you create an investor table, each entry of which is pointed to by either a person or an organization, resolving the polymorphism. You then create an investments table which has a company and investor FK, performing the many:many join. The main limitation (aside from the extra join) is that you could theoretically have an investor that isn't pointing to an organization or person (but that seems better than pointing to something invalid)

[luxaritas]

(Excuse the abuse of fields on an ER diagram, having example rows seems helpful to me)

erDiagram
  Organization 1--0+ Investor : is
  Person 1--0+ Investor : is
  Investor 1--0+ Investments : has
  Investments 0+--1 Company: for
  
  Organization {
    name investor
    MyOrg InvMyOrg 
  }
  
  Person {
    name investor
    SomePerson InvSomePerson
  }
  
  Investor {
      id x
      InvMyOrg x
      InvMyPerson x      
  }
  
  Investments {
    investor company
    InvMyOrg MyCorp
    InvSomePerson MyCorp
  }
  
  Company {
    id x
    MyCorp x
  }

[cr101]

@luxaritas you will end up with a null column on every row in the Investor table

[luxaritas]

@cr101 The way I have this designed, it only has one column, its ID in the Investor table (which may as well be an autogenerated number or UUID). Instead of it having a foreign key out to organization and person, organization and person have a foreign key to it.

[cr101]

The Investments and Company tables don't make sense to me

[luxaritas]

My assumption is that your end goal was just to tie a person/organization to the company they invest in. The investments table is the many:many join table (pretend you just had individuals investing in companies - in that case, each row would have an fk to the individual and to the company being invested in; in this case we add the investor table to aggregate both organization and individual investors).

I guess in your original example, these aren't represented, and I was taking this a step further - the key point is that a person or organization points to a shareholder, not the other way around

[ninjrdevelop]

I've previously had Polymorphic relationships operate like this:

Where there's a FK column and a Type column to determine which foreign table it's referencing.

This allows for two columns to determine the whole relationship (as long as the ORM can accommodate), and there ends up being no null values like in the many:many join table mentioned above.

[Hamsterzs]

I was able to get a simple example of a Polymorphic Association working with the conditional operators.

// Query
db
  .select()
  .from(schema.users)
  .leftJoin(
    schema.doctors,
    and(
      eq(schema.users.profileId, schema.doctors.id),
      eq(schema.users.profileType, "Doctor")
    )
  )
  .leftJoin(
    schema.patients,
    and(
      eq(schema.users.profileId, schema.patients.id),
      eq(schema.users.profileType, "Patient")
    )
  )

The schema loos like this

export const profileTypeEnum = pgEnum("profile_type", ["Doctor", "Patient"]);

export const users = pgTable("users", {
  id: serial("id").primaryKey(),
  name: varchar("name", { length: 256 }),
  profileType: profileTypeEnum("profile_type"),
  profileId: integer("profile_id"),
});

export const doctors = pgTable("doctors", {
  id: serial("id").primaryKey(),
  specialty: varchar("specialty", { length: 256 }),
});

export const patients = pgTable("patients", {
  id: serial("id").primaryKey(),
  condition: varchar("condition", { length: 256 }),
});

And the result will look like this, Where in the response JSON if you are a doctor you will have a null as a patient value (and vice vera), But the DB will not contain nulls.

[
  {
    users: {
      id: 1,
      name: "House",
      profileType: "Doctor",
      profileId: 1
    },
    doctors: {
      id: 1,
      specialty: "Everything"
    },
    patients: null
  }, {
    users: {
      id: 2,
      name: "0",
      profileType: "Patient",
      profileId: 1
    },
    doctors: null,
    patients: {
      id: 1,
      condition: "Unkown"
    }
  }
]

And this is what the sql looks like when i run .toSql()

{
  sql: "select \"users\".\"id\", \"users\".\"name\", \"users\".\"profile_type\", \"users\".\"profile_id\", \"doctors\".\"id\", \"doctors\".\"specialty\", \"patients\".\"id\", \"patients\".\"condition\" from \"users\" left join \"doctors\" on (\"users\".\"profile_id\" = \"doctors\".\"id\" and \"users\".\"profile_type\" = $1) left join \"patients\" on (\"users\".\"profile_id\" = \"patients\".\"id\" and \"users\".\"profile_type\" = $2)",
  params: [ "Doctor", "Patient" ]
}

[AndriiSherman]

Available from [email protected]

[torchsmith]

@AndriiSherman @dankochetov and team/contributors on this ... honestly guys - congrats on completing this in less than a year compared to prisma who haven't even considered adding this yet... since 2020... 🥳 🍰