[FEATURE]: Single Table Inheritance

[MarkusWendorf]

Describe what you want

Hello,

I would really like to see support for single table inheritance in drizzle.

Single table inheritance is a way to save inheritance structures to a single database table. Imagine a accommodation class in OOP, which can be either a hotel or a motel. Both sub-classes inherit a set of properties from the parent (only id in this case) but each class also has additional fields (hotelName or motelName) that are unique to their specific class.

You could save it in the database like this:

type	id	hotelName	motelName
hotel	1	Hotel-Name-1	NULL
motel	2	NULL	Motel1
hotel	3	Hotel-Name-2	NULL

Prior work in other ORMs: TypeORM Node.js CycleORM PHP Hibernate Java

Possible API:

pgTable({
  id: serial("id"),
  type: text("type"),
})
.addChildEntity({
  type: 'hotel',
  hotelName: text("hotelName"),
})
.addChildEntity({
  type: 'motel',
  motelName: text("motelName"),
});

The output type should be a discriminated union for: type Table = Hotel | Motel based on the value of type. Not sure how well this API works out in practice (type inference, relations?).

Thanks

[dankochetov]

Thanks for the suggestion, this looks useful. We'll discuss it internally.

[SebastianGarces]

+1 to INHERITS

PostgreSQL Docs

[dcchrispen]

+1 to Inherits

[bu3alwa]

Would like this feature as well

[Aymericr]

This would be great. Like @SebastianGarces mentioned: For Postgres specifically, it would be great if we had the native table inheritance: https://www.postgresql.org/docs/current/tutorial-inheritance.html

CREATE TABLE cities (
  name       text,
  population real,
  elevation  int     -- (in ft)
);

CREATE TABLE capitals (
  state      char(2) UNIQUE NOT NULL
) INHERITS (cities);

ALTER TABLE cities
ADD test_id varchar(255); -- Both table would contains test col
DROP TABLE cities; -- Cannot drop because capitals depends on it

ALTER TABLE cities
ADD CONSTRAINT fk_test FOREIGN KEY (test_id) REFERENCES sometable (id);

• When you add/delete/update fields -> the inheritance table will also be affected.
• Cannot drop the parent table.
• Foreign keys would NOT be inherited.

It could be implement via pgTable().inherits(), example:

// Parent table (cities)
export const cities = pgTable('cities', {
  name: text('name').notNull(),
  population: real('population'),
  elevation: integer('elevation'),
})

// Child table (capitals) inheriting from cities
export const capitals = pgTable('capitals', {
  state: char('state', { length: 2 }).notNull().unique(),
}).inherits(cities)

[slimshreydy]

+1 for inherits. Even simple single table inheritance would be super useful

[curiosbasant]

+1 to this one

[t8g]

+1