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:
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],
})
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 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:
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.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
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"