Class inheritance (Inherits keyword)

Is there anyway to easily replicate this behaviour in drizzle?

-- Creating the parent table `person`
CREATE TABLE person (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    birth_date DATE
);

-- Creating the child table `student` which inherits from `person`
CREATE TABLE student (
    grade_level VARCHAR(15)
) INHERITS (person);

-- Creating another child table `staff` which inherits from `person`
CREATE TABLE staff (
    position VARCHAR(50)
) INHERITS (person);

-- Creating the parent table `person`
CREATE TABLE person (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    birth_date DATE
);

-- Creating the child table `student` which inherits from `person`
CREATE TABLE student (
    grade_level VARCHAR(15)
) INHERITS (person);

-- Creating another child table `staff` which inherits from `person`
CREATE TABLE staff (
    position VARCHAR(50)
) INHERITS (person);

Inserting Data:

INSERT INTO student (name, birth_date, grade_level) VALUES ('John Doe', '2000-01-01', 'Sophomore');
INSERT INTO staff (name, birth_date, position) VALUES ('Alice Smith', '1980-05-15', 'Manager');

INSERT INTO student (name, birth_date, grade_level) VALUES ('John Doe', '2000-01-01', 'Sophomore');
INSERT INTO staff (name, birth_date, position) VALUES ('Alice Smith', '1980-05-15', 'Manager');

Querying Data:

SELECT * FROM person;

SELECT * FROM person;

This will return all rows from both student and staff tables.

3 Replies

Angelelz•16mo ago

There is no inherits keyword support in drizzle as of now, but you can achieve a similar behavior like this:

const personColumns ={
  id: serial(),
  name: varchar(100),
  birth_date: date()
}

const person = pgTable("person", { ...personColumns });

const student = pgTable("student", {
  ...personColumns,
  grade_level: varchar(15)
});

const staff = pgTable("staff", {
  ...personColumns,
  position: varchar(50)
});

const personColumns ={
  id: serial(),
  name: varchar(100),
  birth_date: date()
}

const person = pgTable("person", { ...personColumns });

const student = pgTable("student", {
  ...personColumns,
  grade_level: varchar(15)
});

const staff = pgTable("staff", {
  ...personColumns,
  position: varchar(50)
});

But could not query from both tables by doing select * from person; If you need that underlying behavior in your database, you would need to create a migration file and add the sql for that inheritance

dionOP•16mo ago

Gotcha, thanks for the insights. Ideally I would not want to edit the migration file since it's not very explicit. Would it then be possible to have a work-around to implement a "Class per table" inheritance as mentioned in this link? https://stackoverflow.com/questions/9174200/how-to-create-multiple-one-to-ones/9178524#9178524

Stack Overflow

How to create multiple one to one's

I have a database set up with many tables and it all looks good apart from one bit... Inventory Table <*-----1> Storage Table <1-----1> Van Table ^ ...

Angelelz•16mo ago

My database knowledge doesn't get that far But I don't see any problem with creating an empty migration, and adding the inherits stuff that you need

Gaming

Programming

Class inheritance (Inherits keyword)