Views

Introduction

A view is a virtual table defined by a saved query. Views behave exactly like tables — you can select from them, join them with other tables, and export their data — but they hold no data of their own. Each time a view is queried the underlying SELECT statement is re-executed by the database.

Because View inherits from Table, a view can be used anywhere a table can: as a query source, in a JOIN, in an export, and so on.

Code Views vs SQL Views

The ViewType enum controls which kind of view is created or loaded.

ViewType.Code

A Code view is defined using Velocity's Query builder. When the schema is built, Velocity translates the Query object into the correct SQL dialect for the target database at runtime, making code views fully portable across all supported databases — PostgreSQL, SQL Server, MySQL, Oracle, SQLite, DB2, and Teradata.

Code views are the recommended approach when building schemas that need to work across different database systems.

ViewType.Sql

A SQL view is defined using a raw SQL SELECT statement. The text is sent to the database unchanged, so you can use any vendor-specific syntax or feature. SQL views are appropriate when you need capabilities that the Velocity query builder does not expose.

When you call manager.LoadSchema(), any views already present in the database are returned as ViewType.Sql views with the raw SQL stored in view.Sql.

	Code View	SQL View
Definition	Velocity Query object	Raw SQL string
Portability	All supported databases	Database-specific
Schema files	C# snippet (compiled at build time)	Inline SQL
Loaded by LoadSchema	No — only programmatic/file schemas	Yes

Defining Views in Schema Files

Views are declared inside the views section of a schema file alongside tables.

Code Views

In schema files, a code view's body is a C# code snippet compiled at schema-load time using Roslyn. Two variables are always available in the snippet:

• schema — the Schema instance; use it to look up tables by name
• query — a pre-created Query instance; configure this object (do not call new Query())

The following namespaces are automatically in scope, so you do not need using directives inside the snippet:

• System, System.Linq, System.Collections.Generic
• YndigoBlue.Velocity.Model
• YndigoBlue.Velocity.Enums
• YndigoBlue.Velocity.Interfaces
• YndigoBlue.Velocity.Functions
• YndigoBlue.Velocity.Generics

Simple filtered view

• XML
• JSON
• YAML
• C#

<schema name="sales">
  <tables>
    <!-- table definitions ... -->
  </tables>
  <views>
    <view name="active_orders" type="code"><![CDATA[
      Table orders = schema["orders"];
      query.Select([
          orders["order_id"],
          orders["customer_id"],
          orders["total"],
          orders["created_at"]
      ]).From(orders)
        .Where(new Criterion<string>(orders["status"], ConditionalType.Equals, "active"));
    ]]></view>
  </views>
</schema>

View with a JOIN

• XML
• JSON
• YAML
• C#

<views>
  <view name="order_details" type="code"><![CDATA[
    Table orders    = schema["orders"];
    Table customers = schema["customers"];

    IFromItem join = new Join(orders, customers, "customer_id");

    query.Select([
        orders["order_id"],
        orders["total"],
        orders["created_at"],
        customers["customer_name"],
        customers["email"]
    ]).From(join);
  ]]></view>
</views>

View with aggregation

• XML
• JSON
• YAML
• C#

<views>
  <view name="customer_totals" type="code"><![CDATA[
    Table orders    = schema["orders"];
    Table customers = schema["customers"];

    var orderCountExpr = new Expression("order_count",  new Aggregate(AggregateType.Count, orders["order_id"]));
    var totalSpentExpr = new Expression("total_spent",  new Aggregate(AggregateType.Sum,   orders["total"]));

    query.Select([
        customers["customer_name"],
        orderCountExpr,
        totalSpentExpr
    ])
    .From(new Join(orders, customers, "customer_id"))
    .GroupBy(customers["customer_name"]);
  ]]></view>
</views>

SQL Views

SQL views store a raw SELECT statement. In schema files the body is the literal SQL.

Note

SQL views defined in schema files are database-specific. If you deploy the same schema file to a different database engine, the raw SQL must be valid on the target database.

• XML
• JSON
• YAML
• C#

<views>
  <view name="recent_orders" type="sql"><![CDATA[
    SELECT order_id, customer_id, total, created_at
    FROM orders
    WHERE created_at >= CURRENT_DATE - INTERVAL '30 days'
  ]]></view>
</views>

Programmatic View Creation

You can create views in C# without a schema file using Schema.CreateView and then deploy them individually with Manager.CreateView.

Creating a Code View

using (var manager = new Manager(connection))
{
    Schema schema = manager.LoadSchema("sales");
    Table ordersTable    = schema["orders"];
    Table customersTable = schema["customers"];

    // Build the query that defines the view
    var query = new Query()
        .Select([
            ordersTable["order_id"],
            ordersTable["total"],
            ordersTable["created_at"],
            customersTable["customer_name"]
        ])
        .From(new Join(ordersTable, customersTable, "customer_id"))
        .Where(new Criterion<string>(ordersTable["status"], ConditionalType.Equals, "shipped"));

    // Register the view in the schema object and deploy it
    View shippedOrders = schema.CreateView("shipped_orders", ViewType.Code, query);
    manager.CreateView(shippedOrders);
}

Creating a Code View (two-step)

When you need to inspect or modify the view object before deploying:

using (var manager = new Manager(connection))
{
    Schema schema = manager.LoadSchema("sales");
    Table productsTable = schema["products"];
    Table ordersTable   = schema["orders"];

    // Create the view entry in the schema
    View topProducts = schema.CreateView("top_products", ViewType.Code);

    // Assign the query separately
    var revenueExpr = new Expression("revenue", new Aggregate(AggregateType.Sum, ordersTable["total"]));

    topProducts.Query = new Query()
        .Select([
            productsTable["product_name"],
            revenueExpr
        ])
        .From(new Join(ordersTable, productsTable, "product_id"))
        .GroupBy(productsTable["product_name"])
        .OrderBy(revenueExpr, OrderClauseType.Descending);

    // Deploy — overwrites if it already exists
    manager.CreateView(topProducts, overwrite: true);
}

Creating a SQL View

using (var manager = new Manager(connection))
{
    Schema schema = manager.LoadSchema("reporting");

    // Create a SQL view using database-specific syntax
    View monthlySales = schema.CreateView("monthly_sales", ViewType.Sql,
        "SELECT DATE_TRUNC('month', created_at) AS month, " +
        "       SUM(total) AS monthly_total, " +
        "       COUNT(*) AS order_count " +
        "FROM orders " +
        "GROUP BY DATE_TRUNC('month', created_at)");

    manager.CreateView(monthlySales);
}

Overwriting an Existing View

Pass overwrite: true to drop and recreate the view if it already exists. Without this flag, attempting to create a view that already exists throws an error.

manager.CreateView(view, overwrite: true);

Dropping a View

using (var manager = new Manager(connection))
{
    Schema schema = manager.LoadSchema("sales");
    View view = schema.GetView("shipped_orders");

    manager.DropView(view);
}

Building Views as Part of a Schema

When you call Manager.BuildSchema, Velocity automatically creates all views defined in the schema — tables first, then views in declaration order.

Schema schema = Schema.NewSchema("sales");

// Define tables
Table customersTable = schema.CreateTable("customers");
customersTable.AddColumn("customer_id", DataType.Long, autoGenerate: true, notNull: true);
customersTable.AddColumn("customer_name", DataType.VarChar, size: 100, notNull: true);
customersTable.AddColumn("email", DataType.VarChar, size: 200);
customersTable.CreatePrimaryKey("pk_customers", "customer_id");

Table ordersTable = schema.CreateTable("orders");
ordersTable.AddColumn("order_id", DataType.Long, autoGenerate: true, notNull: true);
ordersTable.AddColumn("customer_id", DataType.Long, notNull: true);
ordersTable.AddColumn("total", DataType.Decimal, precision: 12, scale: 2);
ordersTable.AddColumn("status", DataType.VarChar, size: 50);
ordersTable.AddColumn("created_at", DataType.DateTime);
ordersTable.CreatePrimaryKey("pk_orders", "order_id");
ordersTable.CreateForeignKey("fk_orders_customer", "customer_id", schema.Name, "customers", "customer_id",
    ForeignKeyRuleType.Restrict, ForeignKeyRuleType.Cascade);

// Define views that reference those tables
View activeOrders = schema.CreateView("active_orders", ViewType.Code);
activeOrders.Query = new Query()
    .Select([
        ordersTable["order_id"],
        ordersTable["total"],
        ordersTable["created_at"],
        customersTable["customer_name"]
    ])
    .From(new Join(ordersTable, customersTable, "customer_id"))
    .Where(new Criterion<string>(ordersTable["status"], ConditionalType.Equals, "active"));

using (var manager = new Manager(connection))
{
    // Creates the schema, all tables, and all views in one call
    manager.BuildSchema(schema, overwrite: true);
}

Using Views in Queries

Because View extends Table, a view can appear anywhere a table can in the query API.

Querying a View Directly

using (var manager = new Manager(connection))
{
    Schema schema = manager.LoadSchema("sales");
    View activeOrders = schema.GetView("active_orders");

    // Select all columns from the view
    var query = new Query().SelectAll(activeOrders);
    var results = manager.Retrieve(query);
}

Filtering and Aggregating on a View

using (var manager = new Manager(connection))
{
    Schema schema = manager.LoadSchema("sales");
    View customerTotals = schema.GetView("customer_totals");

    var query = new Query()
        .Select([
            customerTotals["customer_name"],
            customerTotals["total_spent"]
        ])
        .From(customerTotals)
        .Where(new Criterion<decimal>(customerTotals["total_spent"], ConditionalType.GreaterThan, 1000m))
        .OrderBy(customerTotals["total_spent"], OrderClauseType.Descending);

    var results = manager.Retrieve(query);
}

Joining a View with a Table

Views can be joined with other tables or views exactly like regular tables:

using (var manager = new Manager(connection))
{
    Schema schema = manager.LoadSchema("sales");
    View   activeOrders  = schema.GetView("active_orders");
    Table  productsTable = schema["products"];

    var query = new Query()
        .Select([
            activeOrders["order_id"],
            activeOrders["created_at"],
            productsTable["product_name"]
        ])
        .From(new Join(activeOrders, productsTable, "product_id", JoinType.Left));

    var results = manager.Retrieve(query);
}

Exporting View Data

using (var manager = new Manager(connection))
{
    Schema schema = manager.LoadSchema("sales");
    View customerTotals = schema.GetView("customer_totals");

    // Export view data directly to CSV
    manager.ExportData(customerTotals, "customer_totals.csv");
}

Loading Views from an Existing Database

When you call LoadSchema, Velocity introspects the database and populates schema.Views with the views it finds. These are always returned as ViewType.Sql with the view's definition stored in view.Sql.

using (var manager = new Manager(connection))
{
    Schema schema = manager.LoadSchema("sales");

    foreach (View view in schema.Views)
    {
        Console.WriteLine($"Name: {view.Name}");
        Console.WriteLine($"Type: {view.ViewType}");   // ViewType.Sql for loaded views
        Console.WriteLine($"SQL:  {view.Sql}");
        Console.WriteLine();
    }
}

Code views defined in a schema file and deployed with BuildSchema will also appear here as SQL views once loaded back, because the database stores only the compiled SQL — not the original Velocity query.

Exporting Schemas with Views

By default, ExportSchema and ExportDatabase omit view definitions. Pass includeViews: true to include them:

using (var manager = new Manager(connection))
{
    Schema schema = manager.LoadSchema("sales");

    // Export schema structure and data, including view definitions
    manager.ExportSchema(schema, ConfigType.Json, "./export/sales", includeViews: true);
}

Exporting with views produces one CSV per view (its query results) alongside the table CSVs, and the view definitions are written into the schema file.

Updating Views

Like tables, views support the SchemaUpdate flag for use with Manager.UpdateSchema. Set it in the schema file or programmatically to indicate that a view should be recreated during an update run.

In a schema file:

<view name="active_orders" type="code" schemaupdate="true"><![CDATA[
  Table orders = schema["orders"];
  query.Select([
      orders["order_id"],
      orders["total"]
  ]).From(orders)
    .Where(new Criterion<string>(orders["status"], ConditionalType.Equals, "active"));
]]></view>

Programmatically:

view.SchemaUpdate = true;

using (var manager = new Manager(connection))
{
    manager.UpdateSchema(schema);   // recreates only views (and tables) flagged with SchemaUpdate = true
}

Complete Example

The following example builds a schema with two tables and three views — a filtered view, a joined view, and an aggregated view — then queries each one.

using YndigoBlue.Velocity.Engine;
using YndigoBlue.Velocity.Model;
using YndigoBlue.Velocity.Enums;
using YndigoBlue.Velocity.Data.PostgreSql;

var connection = new PostgreSqlDatasourceConnection
{
    Hostname = "localhost",
    Port     = 5432,
    Username = "postgres",
    Password = "password",
    Database = "ecommerce"
};

// ── Build schema ────────────────────────────────────────────────────────────

Schema schema = Schema.NewSchema("public");

Table customersTable = schema.CreateTable("customers");
customersTable.AddColumn("customer_id",   DataType.Long,    autoGenerate: true, notNull: true);
customersTable.AddColumn("customer_name", DataType.VarChar, size: 100, notNull: true);
customersTable.AddColumn("email",         DataType.VarChar, size: 200);
customersTable.AddColumn("region",        DataType.VarChar, size: 50);
customersTable.CreatePrimaryKey("pk_customers", "customer_id");

Table ordersTable = schema.CreateTable("orders");
ordersTable.AddColumn("order_id",    DataType.Long,    autoGenerate: true, notNull: true);
ordersTable.AddColumn("customer_id", DataType.Long,    notNull: true);
ordersTable.AddColumn("total",       DataType.Decimal, precision: 12, scale: 2);
ordersTable.AddColumn("status",      DataType.VarChar, size: 50);
ordersTable.AddColumn("created_at",  DataType.DateTime);
ordersTable.CreatePrimaryKey("pk_orders", "order_id");
ordersTable.CreateForeignKey("fk_orders_customer", "customer_id",
    schema.Name, "customers", "customer_id",
    ForeignKeyRuleType.Restrict, ForeignKeyRuleType.Cascade);

// ── View 1: filtered — active orders with customer name ──────────────────

View activeOrdersView = schema.CreateView("active_orders", ViewType.Code);
activeOrdersView.Query = new Query()
    .Select([
        ordersTable["order_id"],
        ordersTable["total"],
        ordersTable["created_at"],
        customersTable["customer_name"],
        customersTable["region"]
    ])
    .From(new Join(ordersTable, customersTable, "customer_id"))
    .Where(new Criterion<string>(ordersTable["status"], ConditionalType.Equals, "active"));

// ── View 2: aggregated — spend per customer ──────────────────────────────

var orderCountExpr = new Expression("order_count", new Aggregate(AggregateType.Count, ordersTable["order_id"]));
var totalSpentExpr = new Expression("total_spent", new Aggregate(AggregateType.Sum,   ordersTable["total"]));

View customerSpendView = schema.CreateView("customer_spend", ViewType.Code);
customerSpendView.Query = new Query()
    .Select([
        customersTable["customer_name"],
        customersTable["region"],
        orderCountExpr,
        totalSpentExpr
    ])
    .From(new Join(ordersTable, customersTable, "customer_id"))
    .GroupBy([customersTable["customer_name"], customersTable["region"]]);

// ── View 3: SQL — database-specific syntax example ───────────────────────

View recentOrdersView = schema.CreateView("recent_orders", ViewType.Sql,
    "SELECT o.order_id, o.total, o.created_at, c.customer_name " +
    "FROM orders o " +
    "JOIN customers c ON o.customer_id = c.customer_id " +
    "WHERE o.created_at >= NOW() - INTERVAL '30 days'");

// ── Deploy and query ─────────────────────────────────────────────────────

using (var manager = new Manager(connection))
{
    // Build tables and all three views
    manager.BuildSchema(schema, overwrite: true);

    // Query the filtered view
    var activeQuery = new Query()
        .SelectAll(activeOrdersView)
        .OrderBy(activeOrdersView["created_at"], OrderClauseType.Descending);

    var activeResults = manager.Retrieve(activeQuery);
    Console.WriteLine($"Active orders: {activeResults.Count}");

    // Query the aggregated view — top spenders in a given region
    var topSpendersQuery = new Query()
        .Select([
            customerSpendView["customer_name"],
            customerSpendView["total_spent"]
        ])
        .From(customerSpendView)
        .Where(new Criterion<string>(customerSpendView["region"], ConditionalType.Equals, "West"))
        .OrderBy(customerSpendView["total_spent"], OrderClauseType.Descending);

    var topSpenders = manager.Retrieve(topSpendersQuery);
    Console.WriteLine($"Top spenders in West: {topSpenders.Count}");

    // Export the aggregated view to CSV
    manager.ExportData(customerSpendView, "customer_spend.csv");
}

Summary

• Code views (ViewType.Code) are defined with Velocity's Query builder and are portable across all supported databases. They are translated to the target SQL dialect at build time.
• SQL views (ViewType.Sql) hold raw SQL and are database-specific. They are the form returned by LoadSchema for views already present in the database.
• In schema files, code view bodies are C# snippets compiled by Roslyn; the schema and query variables are injected automatically.
• Schema.CreateView registers a view in the schema object. Manager.CreateView deploys it to the database. Manager.BuildSchema deploys all tables and views together.
• Views extend Table and can be used anywhere a table can: as a query source, in joins, in exports, and so on.