/DB

Defining Tables

Map C# classes and enums to PostgreSQL tables using [Table], [FlagTable], and [DatabaseEnum] in Socigy.OpenSource.DB v0.3.2.

updated 5 Jun 20264 min readv0.3.2View as Markdown

Overview

Every entity that gets its own SQL table must carry a [Table] attribute. The source generator reads it to produce all DDL, query builders, insert builders, and migration helpers for that entity. Without it, the class is invisible to the framework.

The `[Table]` Attribute

[Table("sql_name")] maps a C# class or enum to a PostgreSQL table. The string argument becomes the literal table name used in every generated SQL statement.

using Socigy.OpenSource.DB.Attributes;

[Table("users")]
public partial class User
{
    [PrimaryKey, Default(DbDefaults.Guid.Random)]
    public Guid Id { get; set; }

    public string Username { get; set; }
    public string? Email { get; set; }
}

Classes Must Be `partial`

The source generator augments your class with generated methods. If the class is not declared partial, the build fails: the generator cannot emit the second part of the class.

// CORRECT
[Table("users")]
public partial class User { ... }

// WRONG. Build error: cannot add members to a non-partial type.
[Table("users")]
public class User { ... }

Enum as Reference Table

Applying [Table] to a plain (non-flags) enum tells the migration CLI to create a reference table with three columns: id (the underlying integer value), value (the enum member name as a string), and description (populated from [Description("...")] attributes on the members, or NULL if absent). The CLI seeds the rows automatically.

[Table("user_visibility")]
public enum UserVisibility : short
{
    [Description("Visible to everyone")]
    Public,
    CirclesOnly,
    CustomCircles
}

The generated DDL produces a table equivalent to:

CREATE TABLE "user_visibility" (
    "id"          SMALLINT NOT NULL,
    "value"       TEXT     NOT NULL,
    "description" TEXT,
    PRIMARY KEY ("id")
);

INSERT INTO "user_visibility" ("id", "value", "description") VALUES
    (0, 'Public',        'Visible to everyone'),
    (1, 'CirclesOnly',   NULL),
    (2, 'CustomCircles', NULL);

A class that stores a UserVisibility column holds the id value (a SMALLINT) and can join to the reference table when needed.

The `[FlagTable]` Attribute

[FlagTable("junction_table_name")] marks an explicit junction class for a flags-enum relationship. It is the alternative to the auto-generated junction table that [FlaggedEnum] creates when no explicit class is provided.

Use [FlagTable] when you need extra columns on the junction table, for example an AssignedAt timestamp:

[Flags]
[Table("user_role")]
public enum UserRole : short
{
    User      = 1,
    Admin     = 2,
    Developer = 4,
    Reviewer  = 8
}

[Table("users")]
public partial class User
{
    [PrimaryKey, Default(DbDefaults.Guid.Random)]
    public Guid Id { get; set; }

    public string Username { get; set; }

    // Points to the explicit junction class.
    [FlaggedEnumTable(typeof(UserParentRole))]
    public UserRole ParentRole { get; set; }
}

// Explicit junction. Carries an extra column.
[FlagTable("users_parent_role")]
public partial class UserParentRole
{
    [PrimaryKey, ForeignKey(typeof(User), OnDelete = DbValues.ForeignKey.Cascade)]
    public Guid UserId { get; set; }

    [PrimaryKey, ForeignKey(typeof(UserRole), OnDelete = DbValues.ForeignKey.Cascade)]
    public short UserRoleId { get; set; }

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

[FlagTable] classes must also be partial.

The `[DatabaseEnum]` Attribute

[DatabaseEnum] on an enum instructs the migration CLI to create a PostgreSQL ENUM type instead of a reference table. A column of that type stores the enum member name directly as a PostgreSQL enumeration value.

[DatabaseEnum]
public enum OrderStatus
{
    Pending,
    Processing,
    Shipped,
    Delivered,
    Cancelled
}

[Table("orders")]
public partial class Order
{
    [PrimaryKey, Default(DbDefaults.Guid.Random)]
    public Guid Id { get; set; }

    public OrderStatus Status { get; set; }
}

The migration CLI emits:

CREATE TYPE "order_status" AS ENUM (
    'Pending', 'Processing', 'Shipped', 'Delivered', 'Cancelled'
);

CREATE TABLE "orders" (
    "id"     UUID         NOT NULL DEFAULT gen_random_uuid(),
    "status" order_status NOT NULL,
    PRIMARY KEY ("id")
);
NOTE
[DatabaseEnum] and [Table] on an enum are mutually exclusive. Use [DatabaseEnum] for a PostgreSQL native ENUM type. Use [Table] for a reference table seeded with integer-to-name pairs.

The `[Renamed]` Attribute on a Class

[Renamed("old_table_name")] on a class tells the migration CLI that the table was previously named old_table_name. The CLI emits ALTER TABLE ... RENAME TO ... instead of DROP plus CREATE, preserving all existing rows.

// Previously the table was called "app_users".
[Renamed("app_users")]
[Table("users")]
public partial class User
{
    [PrimaryKey, Default(DbDefaults.Guid.Random)]
    public Guid Id { get; set; }

    public string Username { get; set; }
}

Generated migration SQL:

ALTER TABLE "app_users" RENAME TO "users";
WARNING
Remove [Renamed] after the migration has been applied and committed. Leaving it in place causes the migration CLI to emit the RENAME statement again on the next run.

Complete Example

The following class combines several table-level attributes:

using Socigy.OpenSource.DB.Attributes;

[Table("blog_posts")]
[Check("LENGTH(slug) > 0")]
[Unique(nameof(AuthorId), nameof(Slug), Name = "uq_author_slug")]
public partial class BlogPost
{
    [PrimaryKey, Default(DbDefaults.Guid.Random)]
    public Guid Id { get; set; }

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

    [StringLength(200)]
    public string Title { get; set; }

    [StringLength(200)]
    public string Slug { get; set; }

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

    public bool IsPublished { get; set; }
}