/DB

Defining Columns

How to control column names, SQL types, nullability, and exclusions using [Column], [Ignore], and the full C# to database type mapping in Socigy.OpenSource.DB v0.1.82.

updated 3 May 20264 min readv0.1.82

Overview

Every public property of a [Table]-annotated class automatically becomes a column in the generated DDL and SQL. You can override the column name, the SQL type, nullability, and whether the property participates in SQL at all.

Automatic snake_case Conversion

Property names are converted to snake_case automatically. You do not need a [Column] attribute just to get the standard naming convention.

C# property name Generated column name
CreatedAt created_at
FirstName first_name
EmailVerified email_verified
Id id
PhoneNumber phone_number

The `[Column]` Attribute

Use [Column] to override the column name, the SQL type, or both.

Override Name Only

[Column("email_address")]
public string Email { get; set; }

Generated DDL fragment:

"email_address" TEXT NOT NULL

Override SQL Type Only

[Column(Type = "NUMERIC(10,2)")]
public decimal Price { get; set; }

[Column(Type = "VARCHAR(200)")]
public string Description { get; set; }

[Column(Type = "JSONB")]
public string RawPayload { get; set; }
TIP
Use [Column(Type = "JSONB")] only when you need a raw JSONB column without the managed serialization helpers. For typed or raw JSON columns with proper serialization support, use [JsonColumn] or [RawJsonColumn] instead.

Override Both Name and Type

[Column("unit_price", Type = "NUMERIC(12,4)")]
public decimal UnitPrice { get; set; }

Nullability

Reference Types (`string`, `byte[]`)

Reference-type properties that are declared as nullable (string?, byte[]?) are detected automatically. The generated column will be NULL instead of NOT NULL.

public string? IconUrl { get; set; }   // → "icon_url" TEXT NULL
public string Username { get; set; }   // → "username" TEXT NOT NULL

Value Types (`int`, `bool`, `Guid`, etc.)

Value types are non-nullable by C# semantics and map to NOT NULL columns by default. Use the standard C# nullable syntax (?) to make a value-type column nullable — the generator reads nullable annotations directly:

public int? ParentId { get; set; }      // → "parent_id" INTEGER NULL
public DateTime? BirthDate { get; set; } // → "birth_date" TIMESTAMP WITHOUT TIME ZONE NULL
public Guid? ParentRef { get; set; }    // → "parent_ref" UUID NULL

The `[Ignore]` Attribute

[Ignore] excludes a property entirely from all SQL — DDL, INSERT, UPDATE, SELECT, and WHERE. Use it for computed properties, navigation helpers, or any in-memory state that must not reach the database.

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

    public string FirstName { get; set; }
    public string LastName { get; set; }

    [Ignore]
    public string FullName => $"{FirstName} {LastName}";  // computed, not stored

    [Ignore]
    public bool IsLoggedIn { get; set; }  // transient runtime flag
}

C# to PostgreSQL Type Mapping

The framework maps C# types to PostgreSQL column types automatically. The following table lists all supported mappings:

C# Type PostgreSQL Type
string TEXT
char CHARACTER(1)
bool BOOLEAN
short / Int16 SMALLINT
int / Int32 INTEGER
long / Int64 BIGINT
float / Single REAL
double DOUBLE PRECISION
decimal NUMERIC
Guid UUID
DateTime TIMESTAMP WITHOUT TIME ZONE
DateTimeOffset TIMESTAMP WITH TIME ZONE
DateOnly DATE
TimeOnly TIME WITHOUT TIME ZONE
TimeSpan INTERVAL
byte[] BYTEA

DateOnly and TimeOnly

DateOnly and TimeOnly require Npgsql 6 or later. Earlier Npgsql versions do not include the NpgsqlDbType.Date and NpgsqlDbType.Time enum members needed for parameter binding.

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

    public DateOnly EventDate { get; set; }    // → "event_date" DATE NOT NULL
    public TimeOnly StartTime { get; set; }    // → "start_time" TIME WITHOUT TIME ZONE NOT NULL
}

Enum Columns

A property whose type is an enum (not decorated with [DatabaseEnum] and not used via [FlaggedEnum]) is stored using the enum's underlying integer type:

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

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

    // Stored as SMALLINT (enum underlying type is short)
    public UserVisibility Visibility { get; set; }
}

For a PostgreSQL native ENUM column, apply [DatabaseEnum] to the enum type (see the Tables article). For a flags-based N:M junction table, use [FlaggedEnum] (see the Flagged Enums article).

Combined Example

using Socigy.OpenSource.DB.Attributes;

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

    [Column("product_name"), StringLength(150)]
    public string Name { get; set; }

    [Column(Type = "NUMERIC(12,4)")]
    public decimal UnitPrice { get; set; }

    public int? StockQuantity { get; set; }

    public string? Description { get; set; }

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

    [Ignore]
    public bool IsInStock => StockQuantity > 0;
}