Comments (15)
Available from [email protected]
from drizzle-orm.
@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... 🥳 🍰
from drizzle-orm.
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)
})
);
from drizzle-orm.
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" ]
}
from drizzle-orm.
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.
from drizzle-orm.
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.
from drizzle-orm.
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
from drizzle-orm.
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?
from drizzle-orm.
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
from drizzle-orm.
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)
from drizzle-orm.
(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
}
from drizzle-orm.
@luxaritas you will end up with a null
column on every row in the Investor
table
from drizzle-orm.
@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.
from drizzle-orm.
The Investments
and Company
tables don't make sense to me
from drizzle-orm.
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
from drizzle-orm.
Related Issues (20)
- [BUG]: BLOB json values are not being stringified before insert. HOT 2
- [BUG]: Drizzle migration does not rollback if it fails
- [BUG]: Execute Alter Sequence HOT 1
- [FEATURE]: Subquery select column HOT 3
- [BUG]: drizzle-kit generate creates broken SQL for postgres when creating compound primary key
- [FEATURE]: Manual control migrations
- [BUG]: `drizzle-typebox` fails to map geometry column to type-box schema HOT 1
- [BUG]: drizzle-valibot does not provide types for returned schemas HOT 1
- [BUG]: The function of installation of neon database is was not working HOT 5
- [BUG]: Drizzle-typebox types SQLite real field to string
- [BUG]: Missing `index names` when running `introspect` command [MYSQL]
- [BUG]: Postgis `geometry` query select fails when using `with`
- [BUG]: Schema generation with custom schema not working for mysql
- [BUG]: Issues with nested conditions & placeholders in SQLite query HOT 1
- [BUG]: `drizzle-kit introspect` produces diffs without underlying schema changes
- [BUG]: Cannot rename table with composite primary keys HOT 4
- [FEATURE]: Track constraints in the type system
- [FEATURE]: explicitly mark a models' column definition as NULL with a ".null()" chain function
- [FEATURE]: SQLITE - Introspect `BOOLEAN` as `integer("...", { mode : "boolean" })`
- [BUG]: Drizzle studio wont run with current versions of orm and kit HOT 9
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from drizzle-orm.