A View Entity in TypeOrm is like a virtual table that pulls data from your database view. Creating one is simple! Just define a new class and mark it with @ViewEntity().

Here’s a quick rundown of the options @ViewEntity() accepts:

• name: The name of your view. If you don’t set this, TypeOrm will use your class name.
• database: The name of the database on your server.
• schema: The schema name.
• expression: This is your view definition and it’s a must-have!
• dependsOn: A list of other views your view relies on. This ensures your migrations happen in the right order.

For example, the expression can be a string with properly escaped columns and tables, depending on the database you’re using (like PostgreSQL).

@ViewEntity({
    expression: `
        SELECT "post"."id" AS "id", "post"."name" AS "name", "category"."name" AS "categoryName"
        FROM "post" "post"
        LEFT JOIN "category" "category" ON "post"."categoryId" = "category"."id"
    `
})

or an instance of QueryBuilder

@ViewEntity({
    expression: (dataSource: DataSource) => dataSource
        .createQueryBuilder()
        .select("post.id", "id")
        .addSelect("post.name", "name")
        .addSelect("category.name", "categoryName")
        .from(Post, "post")
        .leftJoin(Category, "category", "category.id = post.categoryId")
})

Heads Up: Parameter binding isn’t supported because of driver limitations. Instead, use literal parameters.

@ViewEntity({
    expression: (dataSource: DataSource) => dataSource
        .createQueryBuilder()
        .select("post.id", "id")
        .addSelect("post.name", "name")
        .addSelect("category.name", "categoryName")
        .from(Post, "post")
        .leftJoin(Category, "category", "category.id = post.categoryId")
        .where("category.name = :name", { name: "Cars" })  // <-- this is wrong
        .where("category.name = 'Cars'")                   // <-- and this is right
})

Every view entity needs to be included in your data source options.

import { DataSource } from "typeorm"
import { UserView } from "./entity/UserView"

const dataSource = new DataSource({
    type: "mysql",
    host: "localhost",
    port: 3306,
    username: "test",
    password: "test",
    database: "test",
    entities: [UserView],
})

View Entity columns

To ensure data from the view maps correctly to entity columns, use the @ViewColumn() decorator on the entity columns and specify them as aliases in your select statement.

Here’s an example using a string expression definition:

import { ViewEntity, ViewColumn } from "typeorm"

@ViewEntity({
    expression: `
        SELECT "post"."id" AS "id", "post"."name" AS "name", "category"."name" AS "categoryName"
        FROM "post" "post"
        LEFT JOIN "category" "category" ON "post"."categoryId" = "category"."id"
    `,
})
export class PostCategory {
    @ViewColumn()
    id: number

    @ViewColumn()
    name: string

    @ViewColumn()
    categoryName: string
}

example using QueryBuilder:

import { ViewEntity, ViewColumn } from "typeorm"

@ViewEntity({
    expression: (dataSource: DataSource) =>
        dataSource
            .createQueryBuilder()
            .select("post.id", "id")
            .addSelect("post.name", "name")
            .addSelect("category.name", "categoryName")
            .from(Post, "post")
            .leftJoin(Category, "category", "category.id = post.categoryId"),
})
export class PostCategory {
    @ViewColumn()
    id: number

    @ViewColumn()
    name: string

    @ViewColumn()
    categoryName: string
}

View Column options

View Column options allow you to set additional settings for your view entity columns, much like you would for regular entity columns.

You can define these options in @ViewColumn:

@ViewColumn({
    name: "postName",
    // ...
})
name: string;

Here are the options available in ViewColumnOptions:

• name: string — The column name in the database view.
• transformer { from(value: DatabaseType): EntityType, to(value: EntityType): DatabaseType } — This is used to convert properties from the database type (DatabaseType) to the entity type (EntityType). You can also use an array of transformers, which are applied in reverse order when reading. Note that since database views are read-only, transformer.to(value) will never be used.

Materialized View Indices

Creating indices for materialized views is supported when using PostgreSQL.

@ViewEntity({
    materialized: true,
    expression: (dataSource: DataSource) =>
        dataSource
            .createQueryBuilder()
            .select("post.id", "id")
            .addSelect("post.name", "name")
            .addSelect("category.name", "categoryName")
            .from(Post, "post")
            .leftJoin(Category, "category", "category.id = post.categoryId"),
})
export class PostCategory {
    @ViewColumn()
    id: number

    @Index()
    @ViewColumn()
    name: string

    @Index("catname-idx")
    @ViewColumn()
    categoryName: string
}

Currently, only the unique option is supported for indices in materialized views. All other index options will be ignored.

@Index("name-idx", { unique: true })
@ViewColumn()
name: string

Complete example

Let’s create two entities and a view that contains aggregated data from these entities:

import { Entity, PrimaryGeneratedColumn, Column } from "typeorm"

@Entity()
export class Category {
    @PrimaryGeneratedColumn()
    id: number

    @Column()
    name: string
}

import {
    Entity,
    PrimaryGeneratedColumn,
    Column,
    ManyToOne,
    JoinColumn,
} from "typeorm"
import { Category } from "./Category"

@Entity()
export class Post {
    @PrimaryGeneratedColumn()
    id: number

    @Column()
    name: string

    @Column()
    categoryId: number

    @ManyToOne(() => Category)
    @JoinColumn({ name: "categoryId" })
    category: Category
}

import { ViewEntity, ViewColumn, DataSource } from "typeorm"

@ViewEntity({
    expression: (dataSource: DataSource) =>
        dataSource
            .createQueryBuilder()
            .select("post.id", "id")
            .addSelect("post.name", "name")
            .addSelect("category.name", "categoryName")
            .from(Post, "post")
            .leftJoin(Category, "category", "category.id = post.categoryId"),
})
export class PostCategory {
    @ViewColumn()
    id: number

    @ViewColumn()
    name: string

    @ViewColumn()
    categoryName: string
}

Next, populate these tables with data and retrieve all records from the PostCategory view.

import { Category } from "./entity/Category"
import { Post } from "./entity/Post"
import { PostCategory } from "./entity/PostCategory"

const category1 = new Category()
category1.name = "Cars"
await dataSource.manager.save(category1)

const category2 = new Category()
category2.name = "Airplanes"
await dataSource.manager.save(category2)

const post1 = new Post()
post1.name = "About BMW"
post1.categoryId = category1.id
await dataSource.manager.save(post1)

const post2 = new Post()
post2.name = "About Boeing"
post2.categoryId = category2.id
await dataSource.manager.save(post2)

const postCategories = await dataSource.manager.find(PostCategory)
const postCategory = await dataSource.manager.findOneBy(PostCategory, { id: 1 })

The output in postCategories will be:

[ PostCategory { id: 1, name: 'About BMW', categoryName: 'Cars' },
  PostCategory { id: 2, name: 'About Boeing', categoryName: 'Airplanes' } ]

and in postCategory:

PostCategory { id: 1, name: 'About BMW', categoryName: 'Cars' }

If you liked this content I’d appreciate an upvote or a comment. That helps me improve the quality of my posts as well as getting to know more about you, my dear reader.

Muchas gracias!

Follow me for more content like this.

X | PeakD | Rumble | YouTube | Linked In | GitHub | PayPal.me | Medium

Down below you can find other ways to tip my work.

BankTransfer: "710969000019398639", // CLABE
BAT: "0x33CD7770d3235F97e5A8a96D5F21766DbB08c875",
ETH: "0x33CD7770d3235F97e5A8a96D5F21766DbB08c875",
BTC: "33xxUWU5kjcPk1Kr9ucn9tQXd2DbQ1b9tE",
ADA: "addr1q9l3y73e82hhwfr49eu0fkjw34w9s406wnln7rk9m4ky5fag8akgnwf3y4r2uzqf00rw0pvsucql0pqkzag5n450facq8vwr5e",
DOT: "1rRDzfMLPi88RixTeVc2beA5h2Q3z1K1Uk3kqqyej7nWPNf",
DOGE: "DRph8GEwGccvBWCe4wEQsWsTvQvsEH4QKH",
DAI: "0x33CD7770d3235F97e5A8a96D5F21766DbB08c875"