Tuxedo.Bowtie Migrations Documentation

Key Features

Multi-Database DDL

Generate correct SQL for SQL Server, PostgreSQL, MySQL, and SQLite from the same model classes.

Advanced Indexing

B-Tree, Hash, GIN, GiST, BRIN, Clustered, NonClustered, ColumnStore, FullText, and Spatial indexes.

CLI Tool

Command-line interface for sync, generate, and validate operations with CI/CD support.

Data Loss Analysis

Automatic risk detection with severity levels (High, Medium, Low) for schema changes.

Database Introspection

Read existing schema for intelligent diff-based migrations.

Dry Run & Script Output

Preview changes without applying or output DDL scripts for manual review.

Quick Start

1. Define Models with Attributes

using Noundry.Tuxedo.Contrib;
using Noundry.Tuxedo.Bowtie.Attributes;

[Table("Products")]
public class Product
{
 [Key]
 public int Id { get; set; }

 [Column(MaxLength = 200)]
 [Index("IX_Products_Name")]
 public string Name { get; set; } = string.Empty;

 [Column(Precision = 18, Scale = 2)]
 [CheckConstraint("Price > 0")]
 public decimal Price { get; set; }

 [ForeignKey("Categories", ReferencedColumn = "Id",
 OnDelete = ReferentialAction.Cascade)]
 public int CategoryId { get; set; }

 [DefaultValue("GETDATE()", IsRawSql = true)]
 public DateTime CreatedAt { get; set; }
}

2. Register in ASP.NET Core

using Noundry.Tuxedo.Bowtie;

// Simplified API (v0.5.0+)
services.AddBowtie(options =>
{
 options.ConnectionString = "your-connection-string";
 options.Provider = DatabaseProvider.SqlServer;
 options.AutoMigrate = true;
});

// Or provider-specific shorthand
services.AddBowtieForProvider(DatabaseProvider.Postgres, "connection-string");

Bowtie Attributes

`[Index("name", IsUnique, IndexType)]`

Create an index on the column. Supports named, unique, and type-specific indexes. Use the Group and Order properties for composite indexes.

`[ForeignKey("Table", ReferencedColumn, OnDelete, OnUpdate)]`

Define a foreign key constraint with referential actions (Cascade, NoAction, SetNull, SetDefault, Restrict).

`[Unique("name")]`

Add a unique constraint on the column with an optional constraint name.

`[CheckConstraint("expression", Name)]`

Add a CHECK constraint with a SQL expression.

`[DefaultValue("value", IsRawSql)]`

Set a default value. Use IsRawSql = true for SQL expressions like GETDATE() or NOW().

`[TableOrder(n)]`

Control table creation order. Lower values are created first. Use tiers: 1 for base tables, 2 for dependents, 3 for deeply nested.

Index Types by Database

IndexType	SQL Server	PostgreSQL	MySQL	SQLite
BTree	Yes	Yes	Yes	Yes
Hash	No	Yes	Yes	No
GIN	No	Yes	No	No
GiST	No	Yes	No	No
Clustered	Yes	No	No	No
ColumnStore	Yes	No	No	No
FullText	Yes	Yes	Yes	No
Spatial	Yes	Yes	Yes	No

Composite Indexes

[Table("OrderItems")]
public class OrderItem
{
 [Key]
 public int Id { get; set; }

 [Index(Group = "IX_OrderItems_OrderProduct", Order = 1)]
 public int OrderId { get; set; }

 [Index(Group = "IX_OrderItems_OrderProduct", Order = 2)]
 public int ProductId { get; set; }
}

Base Entity Inheritance

Bowtie (v0.4.0+) correctly handles inherited properties from base classes:

public abstract class BaseEntity
{
 [ExplicitKey]
 [Column("id")]
 public Guid Id { get; set; }

 [Column("created_at")]
 [DefaultValue("NOW()", IsRawSql = true)]
 public DateTime CreatedAt { get; set; }

 [Column("is_deleted")]
 public bool IsDeleted { get; set; }
}

[Table("tickets")]
[TableOrder(2)]
public class Ticket : BaseEntity
{
 [Column("title")]
 public string Title { get; set; } = string.Empty;

 [Column("requester_id")]
 [ForeignKey("users")]
 public Guid RequesterId { get; set; }
}
// Bowtie creates: id, created_at, is_deleted, title, requester_id columns

Foreign Key Ordering with [TableOrder]

Use the [TableOrder] attribute to control table creation order and avoid foreign key errors:

[Table("users")]
[TableOrder(1)] // Tier 1: no dependencies
public class User : BaseEntity { }

[Table("campuses")]
[TableOrder(1)] // Tier 1: no dependencies
public class Campus : BaseEntity { }

[Table("tickets")]
[TableOrder(2)] // Tier 2: depends on users and campuses
public class Ticket : BaseEntity
{
 [ForeignKey("users")]
 public Guid RequesterId { get; set; }
}

[Table("ticket_comments")]
[TableOrder(3)] // Tier 3: depends on tickets and users
public class TicketComment : BaseEntity
{
 [ForeignKey("tickets")]
 public Guid TicketId { get; set; }
}

CLI Tool

# Install globally
dotnet tool install -g Noundry.Tuxedo.Bowtie.CLI

# Or install locally (recommended for CI/CD)
dotnet new tool-manifest
dotnet tool install Noundry.Tuxedo.Bowtie.CLI

Commands

Command	Purpose
bowtie sync	Synchronize database schema with model classes
bowtie generate	Generate DDL scripts without database connection
bowtie validate	Validate model compatibility with target database

Usage Examples

# Sync schema (SQL Server)
bowtie sync \
 --assembly ./bin/Release/net8.0/MyApp.dll \
 --connection-string "Server=.;Database=MyDb;Integrated Security=true" \
 --provider SqlServer

# Dry run to preview changes (PostgreSQL)
bowtie sync \
 --assembly ./MyApp.dll \
 --connection-string "Host=localhost;Database=mydb;Username=postgres;Password=pass" \
 --provider PostgreSQL \
 --dry-run

# Generate DDL script for multiple databases
bowtie generate -a ./MyApp.dll -p SqlServer -o sqlserver.sql
bowtie generate -a ./MyApp.dll -p PostgreSQL -o postgresql.sql
bowtie generate -a ./MyApp.dll -p MySQL -o mysql.sql

# Validate models against a provider
bowtie validate --assembly ./MyApp.dll --provider PostgreSQL --verbose

Data Loss Analysis

Bowtie (v0.5.0+) includes automatic data loss detection and prevention:

Severity	Description	Examples
High	Data loss likely	TableDrop, ColumnDrop
Medium	Potential truncation	LengthReduction, PrecisionReduction
Low	Compatibility issues	DataTypeChange, NullabilityChange
None	Safe operation	AddColumn, AddIndex

var analyzer = new DataLossAnalyzer();
var risks = analyzer.Analyze(currentSchema, proposedSchema);

foreach (var risk in risks.Where(r => r.Severity >= RiskSeverity.Medium))
{
 Console.WriteLine($"[{risk.Severity}] {risk.Table}.{risk.Column}: {risk.Description}");
}

Programmatic API

using Noundry.Tuxedo.Bowtie;

// Sync all entity types from assembly
var sync = new DatabaseSynchronizer(connection, DatabaseProvider.SqlServer);
await sync.SynchronizeAsync(typeof(Product).Assembly);

// Sync specific types
await sync.SynchronizeAsync(new[] { typeof(Product), typeof(Category) });

// Generate DDL without executing
var analyzer = new ModelAnalyzer();
var table = analyzer.Analyze<Product>();

var generator = new SqlServerDdlGenerator();
string createSql = generator.GenerateCreateTable(table);
string indexSql = generator.GenerateIndexes(table);

CI/CD Integration

GitHub Actions

- name: Install Bowtie CLI
 run: dotnet tool install -g Noundry.Tuxedo.Bowtie.CLI

- name: Generate Migration Script
 run: |
 bowtie generate \
 --assembly ./bin/Release/net8.0/MyApp.dll \
 --provider SqlServer \
 --output migration.sql

- name: Apply Migration (Staging)
 if: github.ref == 'refs/heads/develop'
 run: |
 bowtie sync \
 --assembly ./bin/Release/net8.0/MyApp.dll \
 --connection-string "${{ secrets.STAGING_DB }}" \
 --provider SqlServer

MSBuild Integration

<Target Name="GenerateSchema" AfterTargets="Build"
 Condition="'$(Configuration)' == 'Debug'">
 <Exec Command="bowtie generate -a $(OutputPath)$(AssemblyName).dll -p SQLite -o schema.sql" />
</Target>

Related Packages

Noundry.Tuxedo

Core ORM with CRUD, query builder, bulk operations, and more.

Tuxedo.Cufflink

Fake data generation with Bogus for testing and database seeding.

Noundry.Connector

HTTP API client library with authentication, caching, and rate limiting.

Noundry.Slurp

High-performance CSV-to-database ingestion tool.