Skip to main content

Database Views

Database views are virtual tables that are based on the result set of a SQL query. They provide a way to encapsulate complex queries and present them as simple tables. Hysteria ORM supports creating and working with database views through the @view decorator.

Overview​

Views in Hysteria ORM allow you to:

  • Create virtual tables based on complex SQL queries
  • Encapsulate business logic at the database level
  • Provide simplified interfaces for complex data relationships
  • Improve query performance through materialized views (when supported by the database)

Creating Views​

Basic View Definition​

To create a view, use the @view decorator on a model class. The decorator takes a function that receives a query builder and returns the view definition.

import { Model, column } from '@hysteria/orm';
import { view } from '@hysteria/orm/decorators';

@view((query) => {
  // It is advised to only use selectRaw in views, as it is not type safe and will not be validated by the ORM.
  query.selectRaw("COUNT(*) as total").from("users");
})
export class UserView extends Model {
  @column({ primaryKey: true })
  declare id: number;

  @column()
  declare total: number;
}

View with Complex Queries​

Views can contain complex SQL queries with joins, aggregations, and subqueries:

@view((query) => {
  query
    .selectRaw('users.id as userId')
    .selectRaw('users.name as userName')
    .selectRaw('COUNT(posts.id) as postCount')
    .selectRaw('AVG(posts.rating) as avgRating')
    .from('users')
    .leftJoin('posts', 'users.id', 'posts.user_id')
    .groupBy('users.id', 'users.name')
    .having('post_count', '>', 0);
})
export class UserStatsView extends Model {
  @column({ primaryKey: true })
  declare userId: number;

  @column()
  declare userName: string;

  @column()
  declare postCount: number;

  @column()
  declare avgRating: number;
}