Managing Schemas

Introduction

Schemas are the foundation of Velocity's database abstraction layer. A Velocity Schema defines the structure of your database—including tables, columns, constraints, indexes, and views—in a vendor-independent format that can be deployed to any supported database system.

Schema Philosophy

Velocity schemas serve as a single source of truth for your database structure. By defining your schema once in a platform-independent format, you can:

Deploy the same schema to multiple database vendors without modification
Version control your database structure alongside your code
Generate consistent database structures across development, testing, and production environments
Document your database design in a human-readable format
Enable automated schema migrations and updates

Schemas vs Databases

While individual schemas are powerful, Velocity provides a higher-level abstraction called Database that allows you to define and manage multiple schemas together as a single unit.

The Database Abstraction

A Database object contains multiple schemas and enables:

Multi-schema applications: Define multiple schemas that work together
Cross-schema relationships: Create foreign key relationships between tables in different schemas
Unified deployment: Deploy all schemas in a single operation
Consistent versioning: Manage related schemas as a cohesive unit

Example Database structure:

Database: "my_application"
├── Schema: "users"
│   ├── Table: "accounts"
│   ├── Table: "profiles"
│   └── Table: "permissions"
├── Schema: "orders"
│   ├── Table: "orders"
│   ├── Table: "order_items"
│   └── Table: "payments"
└── Schema: "inventory"
    ├── Table: "products"
    └── Table: "stock_levels"

Cross-Schema Foreign Keys

Databases support foreign key relationships that span multiple schemas, allowing you to maintain referential integrity across your entire application using CreateForeignKey:

// Foreign key from orders.orders to users.accounts
ordersTable.CreateForeignKey(
    name: "fk_orders_users",
    sourceColumns: [ "user_id" ],
    lookupSchema: "users",      // Different schema
    lookupTable: "accounts",
    lookupColumns: [ "account_id" ]
);

Warning

SQLite Limitation: While SQLite supports the multi-schema abstraction through its folder-based architecture (using ATTACH DATABASE), it does not support cross-schema foreign keys. Foreign key constraints in SQLite can only reference tables within the same schema (database file). Design your SQLite applications accordingly, or implement cross-schema referential integrity at the application level.

When to Use Database vs Schema

Use individual Schemas when:

You have a single, self-contained schema
All tables and relationships exist within one schema
You're working with a legacy database with a single schema
Simplicity is preferred

Use the Database abstraction when:

Your application spans multiple logical schemas
You need cross-schema foreign key relationships
You want to deploy multiple related schemas together
You're building a modular application with separated concerns

Three Approaches to Schema Creation

Velocity supports three complementary approaches to creating and working with schemas:

Declarative - Define schemas in XML, JSON, or YAML files
Programmatic - Build schemas using C# code
Reverse Engineering - Load schemas from existing database connections

Recommended Approach: Declarative First

Best Practice: Define your schema declaratively in a file (XML, JSON, or YAML) before writing application code. This approach provides:

Version control: Track schema changes in source control
Documentation: Human-readable schema definitions
Consistency: Guaranteed same structure across all database vendors
Clarity: Clear separation between schema definition and application logic
Tooling: Easy schema validation and comparison

Declarative Schemas

Declarative schemas are defined in configuration files using XML, JSON, or YAML formats. All three formats provide identical functionality—choose based on your preference and tooling.

Supported Formats

The ConfigType enum defines the supported configuration formats:

Format	Extension	Best For
XML	`.xml`	Schema validation with XSD, tooling support
JSON	`.json`	Easy integration with modern development tools
YAML	`.yml`, `.yaml`	Human readability, minimal syntax

XML Schema Example

<?xml version="1.0" encoding="utf-8"?>
<schema name="my_application" xmlns="http://www.yndigoblue.com/velocity">
    <tables>
        <table name="users">
            <columns>
                <column type="long" notnull="true" autogenerate="true">user_id</column>
                <column type="varchar" size="50" notnull="true">username</column>
                <column type="varchar" size="100">email</column>
                <column type="datetime">created_at</column>
            </columns>
            <constraints>
                <constraint type="primarykey">
                    <primarykey name="pk_users">
                        <keycolumn>user_id</keycolumn>
                    </primarykey>
                </constraint>
                <constraint type="unique">
                    <unique name="uq_users_username">
                        <uniquecolumn>username</uniquecolumn>
                    </unique>
                </constraint>
                <constraint type="default">
                    <default>
                        <defaultcolumn>created_at</defaultcolumn>
                        <defaultvalue type="function">NOW()</defaultvalue>
                    </default>
                </constraint>
            </constraints>
        </table>

        <table name="posts">
            <columns>
                <column type="long" notnull="true" autogenerate="true">post_id</column>
                <column type="long" notnull="true">user_id</column>
                <column type="clob">content</column>
                <column type="datetime">posted_at</column>
            </columns>
            <constraints>
                <constraint type="primarykey">
                    <primarykey name="pk_posts">
                        <keycolumn>post_id</keycolumn>
                    </primarykey>
                </constraint>
                <constraint type="foreignkey">
                    <foreignkey name="fk_posts_users" deleterule="cascade" updaterule="cascade">
                        <sourcecolumns>
                            <sourcecolumn>user_id</sourcecolumn>
                        </sourcecolumns>
                        <lookupschema>my_application</lookupschema>
                        <lookuptable>users</lookuptable>
                        <lookupcolumns>
                            <lookupcolumn>user_id</lookupcolumn>
                        </lookupcolumns>
                    </foreignkey>
                </constraint>
            </constraints>
            <indexes>
                <index name="idx_posts_user_id">
                    <indexcolumn order="ascending">user_id</indexcolumn>
                </index>
            </indexes>
        </table>
    </tables>
    <views />
</schema>

JSON Schema Example

{
    "schema": {
        "name": "my_application",
        "tables": [
            {
                "name": "users",
                "columns": [
                    {
                        "name": "user_id",
                        "type": "long",
                        "notNull": true,
                        "autogenerate": true
                    },
                    {
                        "name": "username",
                        "type": "varchar",
                        "size": 50,
                        "notNull": true
                    },
                    {
                        "name": "email",
                        "type": "varchar",
                        "size": 100
                    },
                    {
                        "name": "created_at",
                        "type": "datetime"
                    }
                ],
                "constraints": [
                    {
                        "type": "primarykey",
                        "name": "pk_users",
                        "primaryKey": {
                            "keyColumns": ["user_id"]
                        }
                    },
                    {
                        "type": "unique",
                        "name": "uq_users_username",
                        "unique": {
                            "uniqueColumns": ["username"]
                        }
                    },
                    {
                        "type": "default",
                        "default": {
                            "defaultColumn": "created_at",
                            "defaultType": "function",
                            "defaultValue": "NOW()"
                        }
                    }
                ]
            },
            {
                "name": "posts",
                "columns": [
                    {
                        "name": "post_id",
                        "type": "long",
                        "notNull": true,
                        "autogenerate": true
                    },
                    {
                        "name": "user_id",
                        "type": "long",
                        "notNull": true
                    },
                    {
                        "name": "content",
                        "type": "clob"
                    },
                    {
                        "name": "posted_at",
                        "type": "datetime"
                    }
                ],
                "constraints": [
                    {
                        "type": "primarykey",
                        "name": "pk_posts",
                        "primaryKey": {
                            "keyColumns": ["post_id"]
                        }
                    },
                    {
                        "type": "foreignkey",
                        "name": "fk_posts_users",
                        "foreignKey": {
                            "sourceColumns": ["user_id"],
                            "lookupSchema": "my_application",
                            "lookupTable": "users",
                            "lookupColumns": ["user_id"],
                            "deleteRule": "cascade",
                            "updateRule": "cascade"
                        }
                    }
                ],
                "indexes": [
                    {
                        "name": "idx_posts_user_id",
                        "indexColumns": [
                            {
                                "name": "user_id",
                                "order": "ascending"
                            }
                        ]
                    }
                ]
            }
        ],
        "views": []
    }
}

YAML Schema Example

schema:
  name: my_application
  tables:
    - name: users
      columns:
        - name: user_id
          type: long
          notNull: true
          autogenerate: true
        - name: username
          type: varchar
          size: 50
          notNull: true
        - name: email
          type: varchar
          size: 100
        - name: created_at
          type: datetime
      constraints:
        - type: primarykey
          name: pk_users
          primaryKey:
            keyColumns:
              - user_id
        - type: unique
          name: uq_users_username
          unique:
            uniqueColumns:
              - username
        - type: default
          default:
            defaultColumn: created_at
            defaultType: function
            defaultValue: NOW()

    - name: posts
      columns:
        - name: post_id
          type: long
          notNull: true
          autogenerate: true
        - name: user_id
          type: long
          notNull: true
        - name: content
          type: clob
        - name: posted_at
          type: datetime
      constraints:
        - type: primarykey
          name: pk_posts
          primaryKey:
            keyColumns:
              - post_id
        - type: foreignkey
          name: fk_posts_users
          foreignKey:
            sourceColumns:
              - user_id
            lookupSchema: my_application
            lookupTable: users
            lookupColumns:
              - user_id
            deleteRule: cascade
            updateRule: cascade
      indexes:
        - name: idx_posts_user_id
          indexColumns:
            - name: user_id
              order: ascending
  views: []

Schema Validation Files

Velocity provides XSD (XML Schema Definition) and JSON Schema files that you can use to validate your schema and database configuration files. These validation files enable IntelliSense in modern IDEs and help catch errors before deployment.

XML Schema Definition (XSD) Files

For XML-based schemas and databases, Velocity provides XSD files that define the structure and constraints:

Type Definitions XSD:

File: velocity-schema.xsd
Purpose: Included by schema and database XSD files
Download: velocity.xsd

Schema XSD:

File: velocity-schema.xsd
Purpose: Validates individual schema XML files
Download: velocity-schema.xsd

Database XSD:

File: velocity-database.xsd
Purpose: Validates database XML files (containing multiple schemas)
Download: velocity-database.xsd

Warning

If you upgrade from the community edition to the full subscriber edition then you should change the XSD schema files you are using. The community version excludes features unavailable in that edition. As multiple schema support is not available within the community edition, there is no Database XSD available.

Type Definitions XSD (Community Edition):

File: velocity.xsd
Purpose: Validates individual schema XML files
Download: velocity.xsd

Schema XSD (Community Edition):

File: velocity-schema.xsd
Purpose: Validates individual schema XML files
Download: velocity-schema.xsd

Using XSD in Your XML Files:

Add the XSD reference to your XML schema files for validation and IntelliSense:

<?xml version="1.0" encoding="utf-8"?>
<schema name="my_application"
        xmlns="http://www.yndigoblue.com/velocity"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:noNamespaceSchemaLocation="velocity-schema.xsd">
    <tables>
        <!-- Your tables here -->
    </tables>
</schema>

For database files:

<?xml version="1.0" encoding="utf-8"?>
<database name="my_application"
          xmlns="http://www.yndigoblue.com/velocity"
          xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
          xsi:noNamespaceSchemaLocation="velocity-database.xsd">
    <schemas>
        <!-- Your schemas here -->
    </schemas>
</database>

JSON Schema Files

For JSON-based schemas and databases, Velocity provides JSON Schema files:

Type Definitions JSON Schema:

File: velocity.json
Purpose: Validates individual schema JSON files
Download: velocity.json

Schema JSON Schema:

File: velocity-schema.json
Purpose: Validates individual schema JSON files
Download: velocity-schema.json

Database JSON Schema:

File: velocity-database.json
Purpose: Validates database JSON files (containing multiple schemas)
Download: velocity-database.json

Warning

If you upgrade from the community edition to the full subscriber edition then you should change the JSON schema files you are using. The community version excludes features unavailable in that edition. As multiple schema support is not available within the community edition, there is no Database JSON schema available.

Type Definitions JSON Schema (Community Edition):

File: velocity.json
Purpose: Validates individual schema JSON files
Download: velocity.json

Schema JSON Schema (Community Edition):

File: velocity-schema.json
Purpose: Validates individual schema JSON files
Download: velocity-schema.json

Using JSON Schema in Your JSON Files:

Add the $schema property to your JSON schema files:

{
    "$schema": "velocity-schema.json",
    "schema": {
        "name": "my_application",
        "tables": [
            // Your tables here
        ]
    }
}

For database files:

{
    "$schema": "velocity-database.json",
    "database": {
        "name": "my_application",
        "schemas": [
            // Your schemas here
        ]
    }
}

Benefits of Using Validation Files

IDE IntelliSense - Get auto-completion and suggestions while editing schema files
Early Error Detection - Catch structural errors before attempting to deploy
Documentation - Schema files are self-documenting with inline descriptions
Validation - Ensure your configuration files conform to Velocity's expected format
Type Safety - Validate data types, constraints, and required fields

IDE Support

Visual Studio / Visual Studio Code:

XML files automatically use XSD when the xsi:noNamespaceSchemaLocation attribute is present
JSON files automatically use JSON Schema when the $schema property is present
Both provide IntelliSense, validation, and error highlighting

JetBrains Rider:

Supports both XSD and JSON Schema validation
Enable schema validation in Settings → Languages & Frameworks → Schemas and DTDs

Other IDEs:

Most modern IDEs and text editors support XSD and JSON Schema validation
Refer to your IDE's documentation for schema configuration

Loading Declarative Schemas

Use the ReadSchemaFromFile method to load schemas from configuration files:

using YndigoBlue.Velocity.Engine;
using YndigoBlue.Velocity.Enums;
using YndigoBlue.Velocity.Connections;

// Load from XML
using (var manager = new Manager())
{
    Schema schema = manager.ReadSchemaFromFile("schema.xml", ConfigType.Xml);
}

// Load from JSON
using (var manager = new Manager())
{
    Schema schema = manager.ReadSchemaFromFile("schema.json", ConfigType.Json);
}

// Load from YAML
using (var manager = new Manager())
{
    Schema schema = manager.ReadSchemaFromFile("schema.yaml", ConfigType.Yaml);
}

Saving Schemas to Files

Use the WriteSchema method to save schemas to configuration files:

using (var manager = new Manager())
{
    // Save as XML
    manager.WriteSchema(schema, "output.xml", ConfigType.Xml);

    // Save as JSON
    manager.WriteSchema(schema, "output.json", ConfigType.Json);

    // Save as YAML
    manager.WriteSchema(schema, "output.yaml", ConfigType.Yaml);
}

Programmatic Schemas

Programmatic schemas are built using C# code. This approach is useful for:

Dynamic schema generation
Programmatically creating schemas based on business logic
Testing and prototyping
Migrating from other ORMs or frameworks

Creating a Schema Programmatically

Use the Schema, Table, and Column classes to build schemas programmatically:

using YndigoBlue.Velocity.Model;
using YndigoBlue.Velocity.Enums;

// Create a new schema
Schema schema = Schema.NewSchema("my_application");

// Create a table
Table usersTable = schema.CreateTable("users");

// Add columns
usersTable.AddColumn("user_id", DataType.Long, autoGenerate: true, notNull: true);
usersTable.AddColumn("username", DataType.VarChar, size: 50, notNull: true);
usersTable.AddColumn("email", DataType.VarChar, size: 100);
usersTable.AddColumn("created_at", DataType.DateTime);

// Add primary key
usersTable.CreatePrimaryKey("pk_users", "user_id");

// Add unique constraint
usersTable.CreateUniqueConstraint("uq_users_username", "username");

// Add default constraint
usersTable.CreateDefaultConstraint("created_at", "NOW()", DefaultType.Function);

// Create posts table with foreign key
Table postsTable = schema.CreateTable("posts");
postsTable.AddColumn("post_id", DataType.Long, autoGenerate: true, notNull: true);
postsTable.AddColumn("user_id", DataType.Long, notNull: true);
postsTable.AddColumn("content", DataType.Clob);
postsTable.AddColumn("posted_at", DataType.DateTime);

postsTable.CreatePrimaryKey("pk_posts", "post_id");

// Create foreign key
postsTable.CreateForeignKey(
    name: "fk_posts_users",
    sourceColumns: new[] { "user_id" },
    lookupSchema: "my_application",
    lookupTable: "users",
    lookupColumns: new[] { "user_id" },
    deleteRule: DeleteRule.Cascade,
    updateRule: UpdateRule.Cascade
);

// Create index
postsTable.CreateIndex("idx_posts_user_id", new[] { "user_id" });

Complete Example with Deployment

This example demonstrates creating a schema programmatically using CreateTable, AddColumn, CreatePrimaryKey, and CreateIndex:

using YndigoBlue.Velocity.Model;
using YndigoBlue.Velocity.Engine;
using YndigoBlue.Velocity.Connections;
using YndigoBlue.Velocity.Enums;

// Create schema programmatically
Schema schema = Schema.NewSchema("sensor_data");
Table sensorTable = schema.CreateTable("sensor_readings");

sensorTable.AddColumn("reading_id", DataType.Long, autoGenerate: true, notNull: true);
sensorTable.AddColumn("reading_guid", DataType.VarChar, size: 36, notNull: true);
sensorTable.AddColumn("sensor_id", DataType.Long, notNull: true);
sensorTable.AddColumn("reading_timestamp", DataType.Timestamp, notNull: true);
sensorTable.AddColumn("reading_location", DataType.Geography, notNull: true);
sensorTable.AddColumn("temperature", DataType.Decimal, precision: 5, scale: 2);

sensorTable.CreatePrimaryKey("pk_sensor_readings", "reading_id");
sensorTable.CreateSpatialIndex("spi_reading_location", "reading_location");

// Deploy to database
var connection = new SqlServerDatasourceConnection
{
    Hostname = "localhost",
    Port = 1433,
    Username = "myuser",
    Password = "mypassword",
    Database = "sensor_db"
};

using (var manager = new Manager(connection))
{
    // Build schema in database using BuildSchema
    manager.BuildSchema(schema, overwrite: true);

    // Optionally save the programmatic schema to a file for version control using WriteSchema
    manager.WriteSchema(schema, "sensor_schema.json", ConfigType.Json);
}

Loading Schemas from Existing Databases

Velocity can reverse-engineer schemas from existing database connections using LoadSchema. This is useful for:

Migrating existing databases to Velocity
Generating schema definitions from legacy systems
Creating initial schema files from production databases

Loading a Schema

using YndigoBlue.Velocity.Engine;
using YndigoBlue.Velocity.Connections;

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

using (var manager = new Manager(connection))
{
    // Load existing schema from database
    Schema schema = manager.LoadSchema("public");

    // Save to file for version control
    manager.WriteSchema(schema, "production_schema.json", ConfigType.Json);
}

Important Considerations with LoadSchema

⚠️ Warning: While LoadSchema is powerful, it has important limitations you must understand:

Database-Specific Variations

Different databases handle schema metadata differently, which can lead to inconsistencies:

Data Type Mapping:

What Velocity represents as DataType.Integer may be loaded as NUMBER(10) from Oracle, INT from SQL Server, or INTEGER from PostgreSQL
Numeric precision and scale may vary between databases
Character types (VARCHAR, CHAR, CLOB) may have different size limits

Constraint Representation:

Some databases expose constraint names differently
Default value expressions may use database-specific syntax
Check constraints may be reformatted or simplified

Index Metadata:

Index types and options vary significantly between vendors
Some databases don't expose all index properties through metadata queries

Why Declarative-First is Preferred

Scenario: You load a schema from MySQL and save it to a file:

// Load from MySQL
var mysqlConnection = new MySqlDatasourceConnection { /* ... */ };
using (var manager = new Manager(mysqlConnection))
{
    Schema schema = manager.LoadSchema("myapp");
    manager.WriteSchema(schema, "schema.json", ConfigType.Json);
}

Problem: The generated schema.json now contains MySQL-specific interpretations:

Data types might not map cleanly to other vendors
Constraints may use MySQL-specific expressions
Indexes may include MySQL-specific options

When you deploy to PostgreSQL:

var pgConnection = new PostgreSqlDatasourceConnection { /* ... */ };
using (var manager = new Manager(pgConnection))
{
    Schema schema = manager.ReadSchemaFromFile("schema.json", ConfigType.Json);
    manager.BuildSchema(schema); // May produce unexpected results
}

You might encounter:

Type conversion issues
Constraint validation failures
Index creation errors
Subtle behavioral differences

Best Practice: Treat LoadSchema as a Starting Point

Recommended workflow:

Load the schema from your database using LoadSchema:

Schema loadedSchema = manager.LoadSchema("existing_db");
manager.WriteSchema(loadedSchema, "initial_schema.xml", ConfigType.Xml);

Review and clean up the generated file:
- Verify data types are appropriate for all target databases
- Ensure constraints use vendor-independent syntax
- Remove database-specific extensions
- Standardize naming conventions
Test across all target databases:

// Test on SQL Server
using (var sqlManager = new Manager(sqlServerConnection))
{
    Schema schema = manager.ReadSchemaFromFile("schema.xml", ConfigType.Xml);
    sqlManager.BuildSchema(schema);
}

// Test on PostgreSQL
using (var pgManager = new Manager(postgresConnection))
{
    Schema schema = manager.ReadSchemaFromFile("schema.xml", ConfigType.Xml);
    pgManager.BuildSchema(schema);
}

Commit the cleaned schema to version control as your source of truth

Summary: Use LoadSchema to bootstrap your schema definition, but always review, test, and refine the result before treating it as your canonical schema.

Working with Database Objects

The Database object provides a higher-level abstraction for managing multiple schemas together. This is particularly useful for complex applications with multiple logical domains.

Creating a Database Programmatically

using YndigoBlue.Velocity.Model;
using YndigoBlue.Velocity.Engine;
using YndigoBlue.Velocity.Connections;
using YndigoBlue.Velocity.Enums;

// Create a new database with multiple schemas
Database database = new Database("my_application");

// Create first schema: users
Schema usersSchema = database.CreateSchema("users");
Table accountsTable = usersSchema.CreateTable("accounts");
accountsTable.AddColumn("account_id", DataType.Long, autoGenerate: true, notNull: true);
accountsTable.AddColumn("username", DataType.VarChar, size: 50, notNull: true);
accountsTable.AddColumn("email", DataType.VarChar, size: 100);
accountsTable.CreatePrimaryKey("pk_accounts", "account_id");

// Create second schema: orders
Schema ordersSchema = database.CreateSchema("orders");
Table ordersTable = ordersSchema.CreateTable("orders");
ordersTable.AddColumn("order_id", DataType.Long, autoGenerate: true, notNull: true);
ordersTable.AddColumn("account_id", DataType.Long, notNull: true);
ordersTable.AddColumn("order_date", DataType.DateTime, notNull: true);
ordersTable.AddColumn("total_amount", DataType.Decimal, precision: 10, scale: 2);
ordersTable.CreatePrimaryKey("pk_orders", "order_id");

// Create cross-schema foreign key
ordersTable.CreateForeignKey(
    name: "fk_orders_accounts",
    sourceColumns: new[] { "account_id" },
    lookupSchema: "users",           // Different schema!
    lookupTable: "accounts",
    lookupColumns: new[] { "account_id" },
    deleteRule: DeleteRule.Cascade,
    updateRule: UpdateRule.Cascade
);

// Deploy the entire database
var connection = new PostgreSqlDatasourceConnection
{
    Hostname = "localhost",
    Port = 5432,
    Username = "postgres",
    Password = "password",
    Database = "myapp"
};

using (var manager = new Manager(connection))
{
    manager.BuildDatabase(database, overwrite: true);
}

Loading and Saving Database Definitions

Database definitions can be saved to and loaded from files just like schemas:

Save Database to file:

using (var manager = new Manager())
{
    // Save as JSON
    manager.WriteDatabase(database, "database.json", ConfigType.Json);

    // Save as XML
    manager.WriteDatabase(database, "database.xml", ConfigType.Xml);

    // Save as YAML
    manager.WriteDatabase(database, "database.yaml", ConfigType.Yaml);
}

Load Database from file:

using (var manager = new Manager())
{
    Database database = manager.ReadDatabaseFromFile("database.json", ConfigType.Json);
}

Loading Database from Connection

You can reverse-engineer an entire database with all its schemas:

var connection = new SqlServerDatasourceConnection
{
    Hostname = "localhost",
    Port = 1433,
    Username = "sa",
    Password = "password",
    Database = "myapp"
};

using (var manager = new Manager(connection))
{
    // Load all schemas in the database
    Database database = manager.LoadDatabase();

    // Or load specific schemas only
    Database database = manager.LoadDatabase(new[] { "users", "orders", "inventory" });

    // Save to file for version control
    manager.WriteDatabase(database, "production_db.json", ConfigType.Json);
}

Deploying Databases

Deploy an entire database with all schemas in one operation:

Database database = manager.ReadDatabaseFromFile("database.json", ConfigType.Json);

using (var manager = new Manager(connection))
{
    // Deploy all schemas in the database
    manager.BuildDatabase(database, overwrite: true);
}

Accessing Schemas within a Database

Database database = manager.ReadDatabaseFromFile("database.json", ConfigType.Json);

// Access schema by name
Schema usersSchema = database["users"];
Schema ordersSchema = database["orders"];

// Iterate through all schemas
foreach (Schema schema in database.Schemas)
{
    Console.WriteLine($"Schema: {schema.Name}");
    foreach (Table table in schema.Tables)
    {
        Console.WriteLine($"  Table: {table.Name}");
    }
}

Cross-Schema Relationships in Practice

When working with databases that span multiple schemas, you can establish relationships between them:

Database database = new Database("ecommerce");

// Users schema
Schema usersSchema = database.CreateSchema("users");
Table customersTable = usersSchema.CreateTable("customers");
customersTable.AddColumn("customer_id", DataType.Long, autoGenerate: true, notNull: true);
customersTable.AddColumn("name", DataType.VarChar, size: 100, notNull: true);
customersTable.CreatePrimaryKey("pk_customers", "customer_id");

// Products schema
Schema productsSchema = database.CreateSchema("products");
Table productsTable = productsSchema.CreateTable("products");
productsTable.AddColumn("product_id", DataType.Long, autoGenerate: true, notNull: true);
productsTable.AddColumn("product_name", DataType.VarChar, size: 200, notNull: true);
productsTable.AddColumn("price", DataType.Decimal, precision: 10, scale: 2);
productsTable.CreatePrimaryKey("pk_products", "product_id");

// Orders schema with cross-schema foreign keys
Schema ordersSchema = database.CreateSchema("orders");
Table ordersTable = ordersSchema.CreateTable("orders");
ordersTable.AddColumn("order_id", DataType.Long, autoGenerate: true, notNull: true);
ordersTable.AddColumn("customer_id", DataType.Long, notNull: true);
ordersTable.AddColumn("product_id", DataType.Long, notNull: true);
ordersTable.AddColumn("quantity", DataType.Integer, notNull: true);
ordersTable.CreatePrimaryKey("pk_orders", "order_id");

// Foreign key to users.customers
ordersTable.CreateForeignKey(
    name: "fk_orders_customers",
    sourceColumns: new[] { "customer_id" },
    lookupSchema: "users",
    lookupTable: "customers",
    lookupColumns: new[] { "customer_id" }
);

// Foreign key to products.products
ordersTable.CreateForeignKey(
    name: "fk_orders_products",
    sourceColumns: new[] { "product_id" },
    lookupSchema: "products",
    lookupTable: "products",
    lookupColumns: new[] { "product_id" }
);

Note

Remember that SQLite does not support cross-schema foreign keys. If you're targeting SQLite, keep all related tables within the same schema or handle referential integrity at the application level.

Deploying Schemas to Databases

Once you have a schema (from any source), you can deploy it to a database using BuildSchema.

Building a Schema

using YndigoBlue.Velocity.Engine;
using YndigoBlue.Velocity.Connections;
using YndigoBlue.Velocity.Enums;

// Load schema from file
Schema schema;
using (var manager = new Manager())
{
    schema = manager.ReadSchemaFromFile("myapp_schema.json", ConfigType.Json);
}

// Deploy to SQL Server
var sqlServerConn = new SqlServerDatasourceConnection
{
    Hostname = "sqlserver.example.com",
    Port = 1433,
    Username = "sa",
    Password = "password",
    Database = "myapp_db"
};

using (var manager = new Manager(sqlServerConn))
{
    // Create schema in database (drop and recreate if exists)
    manager.BuildSchema(schema, overwrite: true);
}

// Deploy the SAME schema to PostgreSQL
var postgresConn = new PostgreSqlDatasourceConnection
{
    Hostname = "postgres.example.com",
    Port = 5432,
    Username = "postgres",
    Password = "password",
    Database = "myapp_db"
};

using (var manager = new Manager(postgresConn))
{
    // Create schema in database (drop and recreate if exists)
    manager.BuildSchema(schema, overwrite: true);
}

BuildSchema Options

// Create or recreate schema (drops existing objects)
manager.BuildSchema(schema, overwrite: true);

// Create only if doesn't exist (fails if exists)
manager.BuildSchema(schema, overwrite: false);

// Ignore warnings about unsupported operations
manager.BuildSchema(schema, overwrite: true, ignoreWarnings: true);

Updating Existing Schemas

For incremental changes to existing schemas, use UpdateSchema:

// Load current schema from database
Schema currentSchema = manager.LoadSchema("myapp");

// Load target schema from file
Schema targetSchema = manager.ReadSchemaFromFile("myapp_v2.json", ConfigType.Json);

// Apply changes
manager.UpdateSchema(targetSchema);

Important: UpdateSchema performs a diff and applies only the changes. This is safer for production environments where you can't drop and recreate tables.

Schema Validation

Validate that a database schema matches a defined schema using ValidateSchema:

using (var manager = new Manager(connection))
{
    Schema definedSchema = manager.ReadSchemaFromFile("schema.json", ConfigType.Json);

    bool isValid = manager.ValidateSchema(definedSchema);

    if (!isValid)
    {
        Console.WriteLine("Database schema does not match definition!");
    }
}

Working with Schema Objects

Accessing Tables

Use the Table class to work with table definitions:

Schema schema = manager.ReadSchemaFromFile("schema.json", ConfigType.Json);

// Get table by name
Table usersTable = schema["users"];

// Iterate through all tables
foreach (Table table in schema.Tables)
{
    Console.WriteLine($"Table: {table.Name}");
}

Accessing Columns

Use the Column class to work with column definitions:

Table table = schema["users"];

// Get column by name
Column column = table["username"];

// Iterate through all columns
foreach (Column col in table.Columns)
{
    Console.WriteLine($"{col.Name}: {col.DataType}");
}

Working with Constraints

Tables support various constraint types including PrimaryKey, ForeignKey, and Unique:

Table table = schema["users"];

// Access primary key
PrimaryKey pk = table.PrimaryKey;

// Access foreign keys
foreach (ForeignKey fk in table.ForeignKeys)
{
    Console.WriteLine($"FK: {fk.Name} -> {fk.LookupTable}");
}

// Access unique constraints
foreach (Unique unique in table.UniqueConstraints)
{
    Console.WriteLine($"Unique: {unique.Name}");
}

Advanced Schema Features

Spatial Indexes

Use CreatePrimaryKey and CreateIndex to create spatial indexes for geospatial data:

Table locationTable = schema.CreateTable("locations");
locationTable.AddColumn("location_id", DataType.Long, autoGenerate: true, notNull: true);
locationTable.AddColumn("coordinates", DataType.Geography, notNull: true);

locationTable.CreatePrimaryKey("pk_locations", "location_id");
locationTable.CreateSpatialIndex("spi_coordinates", "coordinates");

Full-Text Indexes

Table articlesTable = schema.CreateTable("articles");
articlesTable.AddColumn("article_id", DataType.Long, autoGenerate: true, notNull: true);
articlesTable.AddColumn("title", DataType.VarChar, size: 200);
articlesTable.AddColumn("content", DataType.Clob);

articlesTable.CreatePrimaryKey("pk_articles", "article_id");

// Create full-text index on multiple columns
articlesTable.CreateFullTextIndex(
    name: "fti_articles",
    primaryKeyIndexName: "pk_articles",
    columns: new[] { "title", "content" }
);

Check Constraints

Use CreateDefaultConstraint and CreateUniqueConstraint to add constraints to tables:

Table productsTable = schema.CreateTable("products");
productsTable.AddColumn("product_id", DataType.Long, autoGenerate: true, notNull: true);
productsTable.AddColumn("price", DataType.Decimal, precision: 10, scale: 2);
productsTable.AddColumn("quantity", DataType.Integer);

productsTable.CreatePrimaryKey("pk_products", "product_id");

// Create check constraint
productsTable.CreateCheckConstraint(
    name: "chk_positive_price",
    expression: "price > 0"
);

productsTable.CreateCheckConstraint(
    name: "chk_valid_quantity",
    expression: "quantity >= 0 AND quantity <= 10000"
);

Best Practices

1. Use Declarative Schemas as Source of Truth

Always maintain your schemas in XML, JSON, or YAML files under version control. Treat these files as the authoritative definition of your database structure.

Use Manager to load and deploy schemas:

Good:

// Load from versioned schema file
Schema schema = manager.ReadSchemaFromFile("schemas/v1.2.3/app_schema.json", ConfigType.Json);
manager.BuildSchema(schema);

Avoid:

// Building schemas programmatically without saving to file
Schema schema = Schema.NewSchema("myapp");
// ... lots of code to build schema ...
manager.BuildSchema(schema); // No record of what was deployed!

2. Version Your Schemas

Include version information in your schema files or file names:

schemas/
  v1.0.0/
    app_schema.json
  v1.1.0/
    app_schema.json
  v2.0.0/
    app_schema.json

3. Test Schema Deployments

Always test schema changes on all target database platforms:

Schema schema = manager.ReadSchemaFromFile("schema.json", ConfigType.Json);

// Test on each supported database
foreach (var connection in testConnections)
{
    using (var manager = new Manager(connection))
    {
        try
        {
            manager.BuildSchema(schema, overwrite: true);
            Console.WriteLine($"✓ Schema deployed successfully to {connection.DatasourceType}");
        }
        catch (Exception ex)
        {
            Console.WriteLine($"✗ Schema deployment failed on {connection.DatasourceType}: {ex.Message}");
        }
    }
}

4. Use Consistent Naming Conventions

Choose a naming convention (snake_case, PascalCase, etc.) and stick to it throughout your schema:

// Good: Consistent snake_case
Schema schema = Schema.NewSchema("my_application");
Table usersTable = schema.CreateTable("users");
usersTable.AddColumn("user_id", DataType.Long);
usersTable.AddColumn("first_name", DataType.VarChar, size: 50);

// Avoid: Mixed conventions
Schema schema = Schema.NewSchema("MyApplication");
Table usersTable = schema.CreateTable("Users");
usersTable.AddColumn("UserID", DataType.Long);
usersTable.AddColumn("first_name", DataType.VarChar, size: 50);

5. Document Complex Schemas

Add comments in your schema files to explain design decisions:

<!-- User authentication and profile management -->
<table name="users">
    <columns>
        <!-- Using LONG instead of INTEGER to support large-scale deployments -->
        <column type="long" notnull="true" autogenerate="true">user_id</column>
        <!-- Username must be unique across the platform -->
        <column type="varchar" size="50" notnull="true">username</column>
    </columns>
    ...
</table>

Summary

Velocity schemas provide a powerful, vendor-independent way to define and manage database structures. By following these guidelines:

Define schemas declaratively in XML, JSON, or YAML files
Version control your schema definitions
Test thoroughly across all target database platforms
Use LoadSchema sparingly and always review the results
Treat schema files as the single source of truth for your database structure

You'll build maintainable, portable database applications that work consistently across all supported database vendors.

Table of Contents

Managing Schemas

Introduction

Schema Philosophy

Schemas vs Databases

The Database Abstraction

Cross-Schema Foreign Keys

Warning

When to Use Database vs Schema

Three Approaches to Schema Creation

Recommended Approach: Declarative First

Declarative Schemas

Supported Formats

XML Schema Example

JSON Schema Example

YAML Schema Example

Schema Validation Files

XML Schema Definition (XSD) Files

Warning

JSON Schema Files

Warning

Benefits of Using Validation Files

IDE Support

Loading Declarative Schemas

Saving Schemas to Files

Programmatic Schemas

Creating a Schema Programmatically

Complete Example with Deployment

Loading Schemas from Existing Databases

Loading a Schema

Important Considerations with LoadSchema

Database-Specific Variations

Why Declarative-First is Preferred

Best Practice: Treat LoadSchema as a Starting Point

Working with Database Objects

Creating a Database Programmatically

Loading and Saving Database Definitions

Loading Database from Connection

Deploying Databases

Accessing Schemas within a Database

Cross-Schema Relationships in Practice

Note

Deploying Schemas to Databases

Building a Schema

BuildSchema Options

Updating Existing Schemas

Schema Validation

Working with Schema Objects

Accessing Tables

Accessing Columns

Working with Constraints

Advanced Schema Features

Spatial Indexes

Full-Text Indexes

Check Constraints

Best Practices

1. Use Declarative Schemas as Source of Truth

2. Version Your Schemas

3. Test Schema Deployments

4. Use Consistent Naming Conventions

5. Document Complex Schemas

Summary