Non-nullable rows are typed as optional

[khromov]

Describe the bug 👋 Perhaps I am misunderstanding something basic, consider this table:

CREATE SEQUENCE users_id_seq INCREMENT 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1;
CREATE TABLE "public"."users" (
    "id" integer DEFAULT nextval('users_id_seq') NOT NULL,
    "name" text NOT NULL,
    "avatar" json NOT NULL,
    "profile" json NOT NULL,
    "created" timestamptz DEFAULT CURRENT_TIMESTAMP NOT NULL,
    "updated" timestamptz DEFAULT CURRENT_TIMESTAMP NOT NULL,
    "phone" text NOT NULL,
    "email" text NOT NULL,
    CONSTRAINT "users_pkey" PRIMARY KEY ("id")
) WITH (oids = false);

& then we write this query:

/* @name CreateUser */
INSERT INTO users (name, avatar, profile, phone, email) VALUES (:name, :avatar, :profile, :phone, :email) RETURNING *;

Because all of the columns are NOT NULL, I would expect all the parameters to be required, however the generated type is:

export interface ICreateUserParams {
  avatar?: Json | null | void;
  email?: string | null | void;
  name?: string | null | void;
  phone?: string | null | void;
  profile?: Json | null | void;
}

This in turns allows us to easily create a broken query:

		const newUser = await createUser.run(
			{
				name,
				avatar,
				phone
			},
			pool
		);

Getting the error:

error: null value in column "profile" of relation "users" violates not-null constraint
    at /Users/k/Documents/GitHub/belong/node_modules/pg-pool/index.js:45:11
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5) {
  length: 291,
  severity: 'ERROR',
  code: '23502',

Expected behavior The parameters should be required because it should be possible to infer that the columns are not nullable!

You can work around this by manually marking the columns as non-nullable, eg VALUES (:name!, :avatar!, :profile!, :phone!, :email!), this generated the correct type:

export interface ICreateUserParams {
  avatar: Json;
  email: string;
  name: string;
  phone: string;
  profile: Json;
}

...but why does it not infer it correctly out of the box?

Test case

N/A

[khromov]

After some sleuthing I found https://github.com/adelsz/pgtyped/issues/118 which seems related. It would be great if simple INSERT statements like these could be supported without having to manually enforcing non-nullability for parameters.

https://pgtyped.dev/docs/sql-file#enforcing-non-nullability-for-parameters