Fluent SQL

Constraints

Nullability

Column definitions can be extended with nullability constraints, using Null() and NotNull().

var customer = Table.Create("Customer",
    "ID".Int().NotNull(),
    "Name".NVarChar(100).NotNull(),
    "Description".NVarCharMax().Null());

CREATE TABLE [Customer] ( 
    [ID] INT NOT NULL, 
    [Name] NVARCHAR(100) NOT NULL, 
    [Description] NVARCHAR(MAX) NULL 
);

Duplicate nullability constraints, like "Status".Int().Null().Null(), are harmless and are normalised down to one during SQL generation. Contradictory nullability constraints, like "Status".Int().Null().NotNull(), will cause an error during SQL generation.

Primary Keys

A column can be defined as a primary key with the PrimaryKey() extension method.

var customer = Table.Create("Customer",
    "ID".Int().PrimaryKey(),
    "Name".NVarChar(100).NotNull());

CREATE TABLE [Customer] ( 
    [ID] INT NOT NULL PRIMARY KEY, 
    [Name] NVARCHAR(100) NOT NULL 
);

Note that a primary key column is automatically made non-nullable, so this doesn’t need to be specified. Trying to make a primary key field nullable will cause an error during SQL generation.

Composite (multi-column) primary keys defined on a table are not yet supported by Fluent SQL.

Defaults

Column default constraints can be set using the Default() extension method.

var customer = Table.Create("Customer",
    "ID".Int().PrimaryKey(),
    "Status".Int().NotNull().Default(0));

CREATE TABLE [Customer] ( 
    [ID] INT NOT NULL PRIMARY KEY, 
    [Status] INT NOT NULL DEFAULT (0) 
);

The Default() method takes a SQLExpression as an argument, but this must be a constant expression and cannot refer to other columns. Fluent SQL doesn’t enforce these rules, so be aware that complex expressions can fail at run-time. Use constant values, NULL, or scalar functions.