TypeOrm Single Table Inheritance

The datamodel we want

At MisterGreen, an EV leasing company, we enable customers to configure a Tesla and a lease contract.

Contracts need to be signed. We want users to be able to sign through an electronic signature or by payment signature. The electronic signature is just a written signature, but done digitally. The payment signature is a €0.01 payment to verify you/your bank account.

Either way, a contract needs a signature, no matter the implementation. We want to use inheritance, but also store our data in a relational database.

Inheritance in a relation database

Congratulations, you decided to Program to an interface, not an implementation, but now you need to store your interface implementations in a relational database.

We are going to solve this with Typescript and TypeOrm’s single table inheritance.

Single table inheritance is a method that saves all implementations of an interface to one single table in the relational database. It will look as follows:

id | type           | signedBy | imageUrl   | transactionId
------------------------------------------------------------
1  | ElectronicSi.. | Elon M.. | https://.. | 
2  | PaymentSigna.. | Martij.. |            | aE670Bffw

Single table inheritance combines all fields from ElectronicSignature and PaymentSignature in a single table:

• id , signedBy are from the Signature interface, and thus inherited by ElectronicSignature and PaymentSignature
• imageUrl is only needed for ElectronicSignature , because it needs the image of a human signature.
• transactionId is only needed for PaymentSignature
• type is just an internal column where TypeOrm stores the entity name.

How do we do this in TypeScript land?

We use TypeOrm to do the magic. I left out the contract > signature relation in this example for simplicity.

Implement the Signature interface as abstract class:

@Entity()
@TableInheritance({column: {type: 'varchar', name: 'type'}})
export abstract class Signature {

  @PrimaryGeneratedColumn('uuid')
  id: string;

  @Column()
  signedBy: string;
}

Implement ElectronicSignature . Notice the @ChildEntity annotation and the extending of Signature :

@ChildEntity()
export class ElectronicSignature extends Signature {

  @Column()
  imageUrl: string

}

We do the same for PaymentSignature :

@ChildEntity()
export class PaymentSignature extends Signature {

  @Column()
  transactionId: string

}

Now we can just use TypeOrm’s repository to query the table:

getRepository(ElectronicSignature).findOne({id: 1})

Or, if we don’t care about the type of signature:

getRepository(Signature).findOne({id: 1})

That’s it! Checkout my more detailed example on Github.

Oh, and don’t forget to order a Tesla at MisterGreen.nl…

Alternatives

Alternative options for inheritance in a relational database:

• Concrete Table Inheritance
• Embedded entities