/DB

Schema generation

How the migration tool derives DDL from annotated C# models and what each attribute produces.

updated 3 May 20263 min readv0.1.82

Generation pipeline

When the CLI tool runs it follows these steps:

  1. Load the assembly — reflects over the compiled DLL and finds all types that implement IDbTable
  2. Read column metadata — calls GetTableName(), GetColumns(), and reads attribute annotations via AssemblyAnalyzer
  3. Map C# types to SQL types — uses the built-in type mapping (see Column types)
  4. Emit DDL — writes CREATE TABLE, CREATE SEQUENCE, constraint clauses, and seed INSERT statements
  5. Diff against baseline — if a previous migration exists, computes the delta and emits ALTER TABLE statements

Column DDL rules

Condition DDL emitted
Property with no attributes "col_name" TYPE NOT NULL (no DEFAULT)
T? nullable annotation ... NULL instead of NOT NULL
[Default(DbDefaults.Guid.Random)] DEFAULT gen_random_uuid()
[Default(DbDefaults.Time.Now)] DEFAULT timezone('utc', now())
[Default("expr")] DEFAULT expr
[Default] (no argument) No DEFAULT clause; ExcludeAutoFields() still skips it
C# initializer = "value" (non-zero/non-empty) DEFAULT 'value'
[AutoIncrement] DEFAULT nextval('"table_col_seq"')

Primary key DDL

PRIMARY KEY ("id")

-- Composite
PRIMARY KEY ("user_id", "course_id")

Foreign key DDL

FOREIGN KEY ("user_id") REFERENCES "users"("id")

-- With referential action
FOREIGN KEY ("user_id") REFERENCES "users"("id") ON DELETE CASCADE

Constraint DDL

-- [Unique]
UNIQUE ("email")

-- [Unique(nameof(Col1), nameof(Col2), Name = ...)]
CONSTRAINT "uq_post_slug" UNIQUE ("author_id", "slug")

-- [Check("expr")]
CHECK (LENGTH("email") < 100)

-- [StringLength(200, MinLength = 1)]
CHECK (LENGTH("title") <= 200)
CHECK (LENGTH("title") >= 1)

-- [Min(0)]
CHECK ("quantity" >= 0)

Enum reference table DDL

For a [Table]-annotated enum the CLI creates a three-column table: id (the underlying integer), value (the member name), and description (from [Description("...")] attributes, or NULL):

CREATE TABLE IF NOT EXISTS "roles" (
    "id"          INTEGER NOT NULL,
    "value"       TEXT    NOT NULL,
    "description" TEXT,
    PRIMARY KEY ("id")
);
INSERT INTO "roles" ("id", "value", "description") VALUES
    (1, 'Reader', NULL), (2, 'Writer', NULL), (4, 'Moderator', NULL), (8, 'Admin', NULL)
ON CONFLICT ("id") DO UPDATE SET "value" = EXCLUDED."value", "description" = EXCLUDED."description";

FlaggedEnum junction table DDL

For a [FlaggedEnum] property on a model:

CREATE TABLE IF NOT EXISTS "users_roles" (
    "users_id"  UUID    NOT NULL,
    "roles_id"  INTEGER NOT NULL,
    PRIMARY KEY ("users_id", "roles_id"),
    FOREIGN KEY ("users_id") REFERENCES "users"("id") ON DELETE CASCADE,
    FOREIGN KEY ("roles_id") REFERENCES "roles"("id")
);

Sequence DDL

For an [AutoIncrement] column:

CREATE SEQUENCE IF NOT EXISTS "table_col_seq" AS INTEGER START 1;

The sequence is always created before the table that references it.

Full example

Given this model:

[Table("posts")]
[Unique(nameof(AuthorId), nameof(Slug), Name = "uq_post_slug")]
public partial class Post
{
    [PrimaryKey, Default(DbDefaults.Guid.Random)]
    public Guid Id { get; set; }

    [ForeignKey(typeof(User), OnDelete = DbValues.ForeignKey.Cascade)]
    public Guid AuthorId { get; set; }

    [StringLength(200, MinLength = 1)]
    public string Title { get; set; }

    [Column("slug"), Unique]
    public string Slug { get; set; }

    [Default(DbDefaults.Time.Now)]
    public DateTime PublishedAt { get; set; }

    public DateTime? DeletedAt { get; set; }
}

The tool emits:

CREATE TABLE IF NOT EXISTS "posts" (
    "id"           UUID      NOT NULL DEFAULT gen_random_uuid(),
    "author_id"    UUID      NOT NULL,
    "title"        VARCHAR(200) NOT NULL,
    "slug"         VARCHAR(200) NOT NULL,
    "published_at" TIMESTAMP NOT NULL DEFAULT timezone('utc', now()),
    "deleted_at"   TIMESTAMP,
    PRIMARY KEY ("id"),
    FOREIGN KEY ("author_id") REFERENCES "users"("id") ON DELETE CASCADE,
    UNIQUE ("slug"),
    CONSTRAINT "uq_post_slug" UNIQUE ("author_id", "slug"),
    CONSTRAINT "chk_posts_title" CHECK (LENGTH("title") <= 200),
    CONSTRAINT "chk_posts_title_1" CHECK (LENGTH("title") >= 1)
);