Nullability is not considered

[Mange]

This applies to both MySQL and to Postgres. Examples below will be for MySQL though.

Given I have a table like this:

CREATE TABLE `examples` (
  `id` bigint(20) NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
);

Then a query against it like:

SELECT id, name FROM examples

Yields types like:

export interface IExampleResult {
  id: number;
  name: string;
}

While I expect it to be

export interface IExampleResult {
  id: number;
  name: string | null;
}

This concept also extends to things like JOIN and LEFT JOIN. As an example:

-- Contrived query, but it only requires a single table definition to work.
SELECT
  innerjoin.id AS innerId,
  innerjoin.name as innerName,
  leftJoin.id AS leftId,
  leftJoin.name AS leftName
FROM (SELECT 1 AS id1, 2 AS id2) x
INNER JOIN examples innerJoin ON innerJoin.id = x.id1
LEFT JOIN examples leftJoin ON leftJoin.id = x.id2

Then I would expect the types to be:

export interface IContrivedResult {
  // Inner joins only returns rows where both sides have a match, so these fields
  // match the table definition.
  innerId: number;
  innerName: string | null;

  // Since records could be returned even if the left join couldn't match
  // anything, all these fields need to be nullable.
  leftId: number | null;
  leftId: string | null;
}

You can make left joins required by also filtering for a value from it, but this is probably extremely complicated to implement in the engine:

SELECT
  innerjoin.id AS innerId,
  innerjoin.name as innerName,
  leftJoin.id AS leftId,
  leftJoin.name AS leftName
FROM (SELECT 1 AS id1, 2 AS id2) x
INNER JOIN examples innerJoin ON innerJoin.id = x.id1
LEFT JOIN examples leftJoin ON leftJoin.id = x.id2
-- Make sure the left join found something
WHERE leftJoin.id IS NOT NULL

export interface IContrivedResult {
  // ...
  leftId: number;
  // ...
}