Common Column Types

One of the key design goals of the Cratis Application Model support for Entity Framework is to make it easy to support different databases for an application.

If you're willing to hand-roll Entity Framework migrations, you can leverage the extension methods that will give you a single migration but support different database types resolved at runtime.

Available Column Extension Methods

The following table shows all the column extension methods available in the Cratis Application Model:

Extension Method	Description	Supported Types	Parameters
`StringColumn()`	Creates a string column with appropriate database-specific type (VARCHAR/NVARCHAR/TEXT)	string	`maxLength` (int?, optional), `nullable` (bool, default: true)
`NumberColumn<T>()`	Creates a numeric column with appropriate database-specific type for any numeric type	char, byte, sbyte, short, ushort, int, uint, long, ulong, float, double, decimal	`nullable` (bool, default: true)
`BoolColumn()`	Creates a boolean column with appropriate database-specific type (BOOLEAN/BIT/INTEGER)	bool	`nullable` (bool, default: true)
`AutoIncrementColumn()`	Creates an auto-incrementing integer column with appropriate database-specific annotations	int	None (always non-nullable)
`GuidColumn()`	Creates a GUID/UUID column with appropriate database-specific type (UUID/UNIQUEIDENTIFIER/TEXT)	Guid	`nullable` (bool, default: true)
`DateTimeOffsetColumn()`	Creates a DateTimeOffset column with appropriate database-specific type (TIMESTAMPTZ/DATETIMEOFFSET/TEXT)	DateTimeOffset	`nullable` (bool, default: true)
`JsonColumn<T>()`	Creates a JSON column with appropriate database-specific type (jsonb/nvarchar(max)/text)	Any type	None (always non-nullable)

Database-Specific Type Mappings

The extension methods automatically select the appropriate SQL type based on the database provider:

String Types

PostgreSQL: VARCHAR(n) for limited length, TEXT for unlimited
SQL Server: NVARCHAR(n) for limited length, NVARCHAR(MAX) for unlimited
SQLite: TEXT

Numeric Types

The NumberColumn<T>() method supports all .NET numeric types and maps them appropriately:

PostgreSQL: SMALLINT, INTEGER, BIGINT, NUMERIC, REAL, DOUBLE PRECISION, DECIMAL
SQL Server: TINYINT, SMALLINT, INT, BIGINT, DECIMAL, REAL, FLOAT
SQLite: INTEGER, REAL (simplified type system)

Boolean Types

PostgreSQL: BOOLEAN
SQL Server: BIT
SQLite: INTEGER

GUID/UUID Types

PostgreSQL: UUID
SQL Server: UNIQUEIDENTIFIER
SQLite: TEXT

DateTime Types

PostgreSQL: TIMESTAMPTZ
SQL Server: DATETIMEOFFSET
SQLite: TEXT

JSON Types

PostgreSQL: jsonb
SQL Server: nvarchar(max)
SQLite: text

Note: Read more about JSON in this article

Usage Examples

Auto Incremental Primary Key

Auto incremental primary keys are very common but implemented in different ways with different annotations for them to actual auto increment. The .AutoIncrementColumn() extension method for the ColumnsBuilder gives you a way to configure it once.

[DbContext(typeof(EventLogDbContext))]
[Migration($"EventLog_{nameof(v1_0_0)}")]
public class v1_0_0 : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.CreateTable(
            name: "EventLog",
            columns: table => new
            {
                SequenceNumber = table.AutoIncrementColumn(migrationBuilder), // Creates an auto increment column
                /*
                Other columns...
                */
            },
            constraints: table => table.PrimaryKey("PK_EventLog", x => x.SequenceNumber));
    }
}

String Columns with Length Limits

columns: table => new
{
    Name = table.StringColumn(migrationBuilder, maxLength: 100), // VARCHAR(100)/NVARCHAR(100)
    Description = table.StringColumn(migrationBuilder), // TEXT/NVARCHAR(MAX)
}

Numeric Columns

columns: table => new
{
    Count = table.NumberColumn<int>(migrationBuilder), // INTEGER/INT
    Price = table.NumberColumn<decimal>(migrationBuilder), // DECIMAL
    Score = table.NumberColumn<double>(migrationBuilder), // DOUBLE PRECISION/FLOAT/REAL
}

Other Column Types

columns: table => new
{
    IsActive = table.BoolColumn(migrationBuilder), // BOOLEAN/BIT/INTEGER
    Id = table.GuidColumn(migrationBuilder, nullable: false), // UUID/UNIQUEIDENTIFIER/TEXT
    CreatedAt = table.DateTimeOffsetColumn(migrationBuilder), // TIMESTAMPTZ/DATETIMEOFFSET/TEXT
    Metadata = table.JsonColumn<Dictionary<string, object>>(migrationBuilder), // jsonb/nvarchar(max)/text
}

Table of Contents