Table of Contents

Fix a nullable property

Make a nullable property, such as string? or int?, not null, and stop the null reference warning it gives.

Use this when a property is nullable, but its column is never null. Use it too when a property must be nullable, and is not.

Why a property is nullable

SqlSource treats a column as possibly null unless it can show that it never is. See which columns can be null on PostgreSQL and SQL Server. The cost is a null check in your code, never a wrong value. You can overrule it in the .sql file.

The rules you need.

  • A column that can be null gets a property that can be null, such as int?. A column that is never null gets one that cannot, such as int. See Which properties can be null.

Say that a column is never null

If you know that a column is never null, add -- column: <name> not null inside its query:

-- name: ListUserDisplayNames
-- column: display_name not null
SELECT
    u.id,
    initcap(u.name) AS display_name
FROM users AS u
ORDER BY u.id;

Or end the column's alias with !:

The alias is in double quotes: initcap(u.name) AS "display_name!".

What you get. ListUserDisplayNamesDto.DisplayName is a string, not a string?. ListOrderMonthsDto.MonthEnd is a DateTime, not a DateTime?.

The rules you need.

Say that a column can be null

If a method throws "Column 'name' is null." or "Data is Null.", a null reached a property that cannot be null. See How a row is read. Remove your ! or not null, if any. Otherwise, add -- column: <name> null:

-- name: ListUserNames
-- column: name null
SELECT id, name
FROM users;

Or end the alias with ?: AS "name?" on PostgreSQL, AS [name?] on SQL Server.

What you get. ListUserNamesDto.Name is a string?.

The rules you need.

Make a query parameter nullable

If a query parameter can be null, add null to its -- param: line:

-- name: FindUsers
-- param: @name text null
SELECT
    id,
    name,
    email
FROM users
WHERE @name IS NULL OR name = @name
ORDER BY name;

What you get. FindUsersParams.Name is a string?, so new FindUsersParams(null) finds every user.

The rules you need.

On PostgreSQL

On PostgreSQL, a column most often gets a nullable property when:

  • It is on the optional side of a LEFT, RIGHT or FULL join.
  • It comes from a view, whose nullability PostgreSQL does not record.
  • It is a call of a function that the full rules do not list, such as initcap(name).
  • It is sum, max, min or avg with no GROUP BY, which is null over no rows.
  • A WITH query or a subquery in FROM computes it, such as a count(*).

To fix one, say that the column is never null. See Which columns can be null and the limit.

On SQL Server

On SQL Server, a column most often gets a nullable property when:

  • It is on the optional side of an outer join or an OUTER APPLY.
  • A filter keeps it from being null, such as WHERE x IS NOT NULL.
  • It is a function such as EOMONTH, DATEADD or AT TIME ZONE, even of values that are never null.
  • It is CASE WHEN x IS NULL THEN 0 ELSE x END. Write ISNULL(x, 0), which is never null.
  • It is a count or a sum in a UNION, or in an OUTPUT clause.

To fix one, say that the column is never null. See Which columns can be null and the limit.

If it does not work

  • "Column 'name' is null.", or on SQL Server Microsoft.Data.SqlClient's SqlNullValueException, "Data is Null.". A null reached a property that cannot be null: say that the column can be null. See How a row is read. On SQL Server, do not run SET ANSI_WARNINGS OFF on the connection: see the limit.
  • SQLSRC260. The -- column: line names no column of the result, or the alias already ends in ! or ?. Keep the mark or the line's null or not null, not both. See SQLSRC260.
  • SQLSRC227 on PostgreSQL. @name IS NULL does not fix the type of @name. Give the type in its -- param: line. See SQLSRC227.