Tutorial: Model a Database Schema

Design entities, export DDL and generate a migration

You will build: a small sales schema, export it to PostgreSQL DDL, change it, and generate the migration. Time: about 20 minutes. Prerequisites: Beep.Skia and Beep.Skia.ERD.

Steps

1. Create entities

ERDEntity keeps the table definition as text, so the canvas and the schema tools share one source of truth.

using Beep.Skia;
using Beep.Skia.ERD;

var manager = new DrawingManager();

var customers = new ERDEntity
{
    EntityName = "Customers",
    X = 60, Y = 60, Width = 260, Height = 160
};
var orders = new ERDEntity
{
    EntityName = "Orders",
    X = 420, Y = 60, Width = 260, Height = 160
};

manager.AddComponent(customers);
manager.AddComponent(orders);

2. Describe columns, keys and indexes

Set Columns, Indexes and ForeignKeys. The exporter understands standard SQL fragments, so you can paste existing definitions.

customers.Columns = @"Id INT PRIMARY KEY
Name NVARCHAR(200) NOT NULL
Email NVARCHAR(320) NOT NULL
CreatedAt DATETIME NOT NULL";
customers.Indexes = "IX_Customers_Email UNIQUE (Email)";

orders.Columns = @"Id INT PRIMARY KEY
CustomerId INT NOT NULL
Total DECIMAL(18,2) NOT NULL
Status NVARCHAR(32) NOT NULL
PlacedAt DATETIME NOT NULL";
orders.Indexes = "IX_Orders_CustomerId (CustomerId)";
orders.ForeignKeys = "FK_Orders_Customers FOREIGN KEY (CustomerId) REFERENCES Customers(Id)";

3. Connect the relationship

Draw the line for the diagram, then set the ERD cardinality markers on it.

manager.ConnectComponents(customers, orders);

var fk = manager.GetLines().Last();
fk.Label1 = "places";
fk.StartMultiplicity = ERDMultiplicity.OneOnly;        // one customer
fk.EndMultiplicity = ERDMultiplicity.ZeroOrMany;       // places zero or many orders
fk.RoutingMode = LineRoutingMode.Orthogonal;

4. Export DDL

DDLExporter emits CREATE TABLE statements in six dialects, ordered so referenced tables come first.

var exporter = new DDLExporter(DDLImporter.SQLDialect.PostgreSQL);
var entities = manager.GetComponents().OfType<ERDEntity>();
string script = exporter.ExportEntities(entities);
File.WriteAllText("schema.sql", script);

5. Change the schema and diff it

Parse the exported DDL as the "source" schema, apply a change to the model (a new column), parse that as the "target", and compare. In practice the source usually comes from a live database dump.

var importer = new DDLImporter(DDLImporter.SQLDialect.PostgreSQL);
var source = importer.Parse(File.ReadAllText("schema.sql"));

orders.Columns += "\nShippedAt DATETIME NULL";
var target = importer.Parse(exporter.ExportEntities(entities));

var diff = new SchemaComparer().Compare(source, target);
Console.WriteLine(diff.Summary());
foreach (var change in diff.OfKind(SchemaChangeKind.AddedColumn))
    Console.WriteLine($"  + {change.TableName}.{change.Column?.Name}");

6. Generate the migration

The generator produces both directions; review them before running anything destructive.

var migrations = new MigrationScriptGenerator();
File.WriteAllText("migrate_up.sql", migrations.GenerateForward(diff, DDLImporter.SQLDialect.PostgreSQL));
File.WriteAllText("migrate_down.sql", migrations.GenerateRollback(diff, DDLImporter.SQLDialect.PostgreSQL));

Next steps