Nullable Column Groups

[Newbie012]

Currently, SafeQL checks for nullability for each target result (e.g., column) individually. While it's still type-safe, it could be improved.

Sometimes, we fetch a group of columns that are nullable only due to the fact that they are left joined, and checking that one of these columns isn't null should be enough to discriminate the other columns. In TypeScript, it should look like:

type Result = {
  aCol: string;
  group: { // if group.bColA isn't null, then it's safe to assume that bColB isn't null as well
    bColA: string;
    bColB: string;
  } | null
}

In some cases, it could be the same case when querying data from the database, as long as:

• The group of columns are only nullable due to relation.
• The group of columns aren't siblings (in the target list) of other columns.

Valid cases:

SELECT lefty.* FROM tbl LEFT JOIN other AS lefty ON ...
-- ✅type could be typed as `(T | null)` rather than `{ [K in T]: T[K] | null }`

SELECT id, jsonb_agg(lefty.*) as col FROM tbl LEFT JOIN other AS lefty ON ... GROUP BY ...
-- ✅type could be typed as `{ id: ..., col: (T | null)[] | null }`

SELECT id, coalesce(jsonb_agg(lefty.*), '[]') as col FROM tbl LEFT JOIN other AS lefty ON ... GROUP BY ...
-- ✅type could be typed as `{ id: ..., col: (T | null)[] }`

Invalid cases:

SELECT id, lefty.* as col FROM tbl LEFT JOIN other AS lefty ON ...
-- ❌ since the columns of `lefty` exists in the same level of `id`, we would need to
--  translate the type with TypeScript intersection, resulting in unreadable code and expensive type checking.
-- Plus, this can get very complex very fast (multiple groups, intersection of groups, etc.).

SELECT
  jsonb_build_object(
    'Title', episode.title,
    'Number', episode.episode_number
  ) AS "Episode"
FROM season
LEFT JOIN episode ON season.id = episode.season_id
-- ❌ `jsonb_build_object` will build the JSON structure even if all of the referenced columns are null.

---

There are probably more valid/invalid cases. Any feedback would be highly appreciated.

[karlhorky]

Thanks for opening this discussion @Newbie012 👍

Cross-linking my last comment here (LEFT JOIN with json_agg()) for visibility:

• JSON type inference #190 (comment)