/DB

Defining Tables

How to map C# classes and enums to PostgreSQL tables using [Table], [FlagTable], [DatabaseEnum], and related attributes in Socigy.OpenSource.DB v0.1.82.

updated 3 May 20264 min readv0.1.82

Overview

Every entity that gets its own SQL table must carry a [Table] attribute. The source generator reads this attribute 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 will fail because 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 enum 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 will hold 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. This 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] when you want a PostgreSQL native ENUM type; use [Table] when you want a reference table seeded with integer–name pairs.

The `[Renamed]` Attribute on a Class

[Renamed("old_table_name")] placed on a class tells the migration CLI that the table was previously named old_table_name. The CLI will emit an ALTER TABLE ... RENAME TO ... statement instead of a DROP + 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 will cause the migration CLI to emit the RENAME statement again on the next run.

Complete Example

The following class demonstrates several table-level attributes together:

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; }
}