Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How can we configure a TypeORM ViewEntity's ViewColumn to be of JSON type?

Tags:

typeorm

I am creating a @ViewEntity() with TypeORM on MySQL in which I directly select a JSON column. The view is correct and just a normal SQL view.

The column definition for the view class looks as such:

@ViewColumn() document: Estimate;

where Estimate is an interface specifying the data shape of the JSON, although I have tried Estimate | Object as well. The entities retrieved by the Repository are always having the document property as a string, evidently the ORM is not parsing the JSON. Therefore I am having to do some annoying JSON.parse() and mutating the retrieved record before responding to requests.

ViewColumnOptions only takes a property of name, so I cannot specify { type: 'json' } as I would on a conventional @Entity()'s @Column(). Are JSON columns in TypeORM views even implemented? I have been unable to find results in the docs nor on the github issues.

like image 305
Andrew Krueger Avatar asked Mar 03 '23 00:03

Andrew Krueger


1 Answers

This is not well-covered in the documentation.

Turns out you can just use a @Column() decorator within a view, so my solution for the above is to specify my view's column definition as

@Column({ type: 'json' }) document: Estimate;

instead of as

@ViewColumn() document: Estimate; in the original question.

Furthermore, for anyone who may stumble upon this later, if you want to do relations against your view, you must decorate at least one column in your view as @PrimaryColumn() in order for TypeORM to formulate the SQL properly. Again, this is not covered in the docs; you could be forgiven after having read them for believing that only @ViewColumn() is valid within a @ViewEntity().

Your relations inside the @ViewEntity() will need to look as such for an example Customer relation:

@ManyToOne(() => Customer)
@JoinColumn({ name: 'customerId' })
customer: Customer;
@ViewColumn() customerId: string;

In the above I have selected in my view's SQL customerId alias and you can see I must specify that both as the name of the @JoinColumn() as well as a @ViewColumn() after the property declaration of the related entity.

like image 90
Andrew Krueger Avatar answered May 18 '23 22:05

Andrew Krueger