# Defining Columns

Control column names, SQL types, nullability, and exclusions with [Column] and [Ignore], plus the full C# to PostgreSQL type mapping in Socigy.OpenSource.DB v0.3.2.

## Overview

Every public property of a `[Table]`-annotated class 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 convert 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

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

Generated DDL fragment:

```sql
"email_address" TEXT NOT NULL
```

### Override SQL Type Only

```csharp
[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

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

## Nullability

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

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

```csharp
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:

```csharp
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.

```csharp
[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. 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 versions do not include the `NpgsqlDbType.Date` and `NpgsqlDbType.Time` enum members needed for parameter binding.

```csharp
[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:

```csharp
[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

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