How to create a TypeORM View Entity?

Database Views are a handy tool to extract of a subset of the database. Database views can simplify the complexity of run-time queries by extracting the data into a separate into a sort of virtual table. Using TypeORM View Entity, we can create views in TypeORM.

To create a TypeORM View Entity, we can use the special @ViewEntity decorator. Using this decorator, we can specify a query to generate the view and link it to a special class.

In this post, we will create a TypeORM View Entity and see it in action.

1 – What is TypeORM View Entity?

TypeORM View Entity is a special class. This class maps to a database view instead of an actual table. To create a new View Entity, we need to use the @ViewEntity() decorator.

We can provide a few options to the @ViewEntity() decorator.

• name – This is basically the view name.
• database – Name of the database. If not specified, it is taken from the TypeORM Datasource configuration.
• schema – Schema name.
• expression – This is the definition of view. It is a mandatory option as the view will be created based on this expression.
• dependsOn – Other views on which the current view depends on.

Consider the below TypeORM Entity for storing flight information.

import { BaseEntity, BeforeInsert, Column, CreateDateColumn, Entity, PrimaryGeneratedColumn, UpdateDateColumn } from "typeorm";

export enum FlightType {
    DOMESTIC = "domestic",
    INTERNATIONAL = "international",
}

@Entity()
export class Flight {

    @PrimaryGeneratedColumn()
    id: number;

    @Column()
    source: string;

    @Column()
    destination: string;

    @Column({
        generatedType: 'STORED',
        asExpression: `source || destination`
    })
    sourceDestinationCode: string;

    @Column()
    category: string;

    @Column()
    flightCode: string;

    @Column({
        type: "enum",
        enum: FlightType,
        default: FlightType.DOMESTIC
    })
    flightType: FlightType;

    @Column({type: "float"})
    durationInHours: number;

    @Column()
    availableSeats: number;

    @CreateDateColumn()
    createdDate: Date;

    @UpdateDateColumn()
    lastUpdatedDate: Date;

    @BeforeInsert()
    updateFlightCode() {
        const randomFlightNumber = Math.floor(Math.random() * 1000);

        this.flightCode = this.category.concat(randomFlightNumber.toString());
    }
}

While there are a bunch of fields within this entity (some of them even computed), we are interested in only a few. Basically, we want to create a view for a set of more important fields.

We can do so by creating a separate class for the view. See below:

import { DataSource, ViewColumn, ViewEntity } from "typeorm";
import { Flight } from "./Flight";

@ViewEntity({
    expression: (dataSource: DataSource) => dataSource
        .createQueryBuilder()
        .select("flight.id", "id")
        .addSelect("flight.source", "source")
        .addSelect("flight.destination", "destination")
        .from(Flight, "flight")
})
export class FlightView {

    @ViewColumn()
    id: number;

    @ViewColumn()
    source: string;

    @ViewColumn()
    destination: string;
}

The @ViewEntity decorator takes an expression as input. Within the expression, we use the createQueryBuilder() function to create a query for the view entity. As you can see, we included only three fields – the id, source and destination of the flight.

We also need to register the view in the TypeORM Datasource.

import "reflect-metadata"
import { DataSource } from "typeorm"
import { Flight } from "./entity/Flight"
import { FlightView } from "./entity/FlightView"
import { Passenger } from "./entity/Passenger"
import { Person } from "./entity/Person"

export const AppDataSource = new DataSource({
    type: "postgres",
    host: "localhost",
    port: 5432,
    username: "postgres",
    password: "password",
    database: "flight-booking",
    synchronize: true,
    dropSchema: true,
    logging: false,
    entities: [Flight, Passenger, FlightView, Person],
    migrations: [],
    subscribers: [],
})

2 – TypeORM View Column Options

We map the expression for the view to an underlying entity. The view entity has columns decorated with @ViewColumn decorator.

export class FlightView {

    @ViewColumn({ name: 'flightId })
    id: number;

    @ViewColumn()
    source: string;

    @ViewColumn()
    destination: string;
}

The @ViewColumn() decorator can also take some options such as name.

3 – Using a TypeORM View Entity

We can now fetch data from the FlightView as easily as fetching data from the actual table.

See below example:

import { AppDataSource } from "./data-source"
import { Flight } from "./entity/Flight"
import { FlightView } from "./entity/FlightView"
import { MealPreference, Passenger } from "./entity/Passenger"
import { Person } from "./entity/Person"
import { Seat, SeatColumn } from "./entity/Seat"

AppDataSource.initialize().then(async () => {

    console.log("Inserting a new flight into the database...")

    const flight = new Flight()

    flight.source = "LAX";
    flight.destination = "NYC";
    flight.category = "7G";
    flight.durationInHours = 4.5;
    flight.availableSeats = 150;

    const flightRepository = AppDataSource.getRepository(Flight);

    await flightRepository.save(flight);

    console.log("Fetching Flight View...")
    const flightView = await AppDataSource.manager.find(FlightView);
    console.log("Flight view details: ", flightView)

}).catch(error => console.log(error))

Basically, after inserting a record into the Flight table, we can use the AppDataSource to find records in the FlightView.

Fetching Flight View...
Flight view details:  [ FlightView { id: 1, source: 'LAX', destination: 'NYC' } ]

Conclusion

TypeORM View Entity is a great tool to create a database view using TypeORM. We get the option to use a string expression or Query Builder to specify the underlying query for the view.

Want to learn more features of TypeORM? Also, Check out this post on TypeORM Entity Inheritance.

If you have any comments or queries about this post, please feel free to mention them in the comments section below.