How to handle JSON/JSONB columns?

[thelinuxlich]

How do I enforce a specific type for a JSON column(like a sub-schema)?

[kristiandupont]

You can specify the type using a column comment. So if you have a table user with a column address that is JSON(B), you can run

COMMENT ON COLUMN user.address IS '@type:string ..and the rest of your comment, if any';

and now Kanel should turn it into a string. If you want to specify a type that needs to be imported, you can use @type(MyType, 'my-library', false, true, false)

The parameters are: name, path, isAbsolute, isDefault, importAsType

Yes, I need to document this :-)

[thelinuxlich]

hmmm interesting

[thelinuxlich]

Any reason why you opted for column comment instead of something like typeMap configuration?

[kristiandupont]

"Database as the source of truth", mostly. You can specify a type map but that will just allow you to specify one type for every JSON column, which is probably not what you want.

[karlhorky]

For anyone interested, also check out the similar challenges that SafeQL by @Newbie012 faces:

• ts-safeql/safeql#102
• ts-safeql/safeql#179

[jcyh0120]

Is this still the suggested way to handle JSONB type with custom type definition? I saw some updates in v3 and a preRenderHook looks like a way to tweak custom column JSON types.

You can specify the type using a column comment. So if you have a table user with a column address that is JSON(B), you can run

COMMENT ON COLUMN user.address IS '@type:string ..and the rest of your comment, if any';

and now Kanel should turn it into a string. If you want to specify a type that needs to be imported, you can use @type(MyType, 'my-library', false, true, false)

The parameters are: name, path, isAbsolute, isDefault, importAsType

Yes, I need to document this :-)

[kristiandupont]

@jcyh0120 you could write a hook if you would like to do something else (like have a local configuration that maps table/column to some type. But I personally like to use comments in the database to keep that as the source of truth as much as possible.

[jcyh0120]

@kristiandupont Thanks! Is there a doc for this yet? I couldn’t find it on the official web doc. Would you mind pointing me to the right direction?

[kristiandupont]

Sorry no, I haven't written any documentation about it. The comment above is the closest thing to it. If it doesn't explain enough, try and tell me what type you need and I will write the corresponding comment for you :-)

[setvik]

What would the comment be for a jsonb field containing values of the following type:

{
  property1: string[];
  property2: string[];
  property3: number;
}

[kristiandupont]

Hm, the most obvious way at the moment is to create and export that type from somewhere.

So, if in some folder that isn't deleted by Kanel when generating models, you define:

// src/types/AccountDetails.ts

export type AccountDetails = { name: string; balance: number };

You could add the following comment to your column:
@type(AccountDetails, '../types/AccountDetails', false, false, true)

That is assuming Kanel targets a folder under src like src/models, which will make the relative path correct.

This is all off the top of my head, so I might be getting something wrong. Let me know if it doesn't seem to work!

[dil-dvukman]

I'm trying to use @type but I can't figure it out

Column comment:
@type:(Configuration, 'common-types/configuration', true, false, true)

but all I get is a comment above the field, it doesn't import the package and not change the type:

  /** :(Configuration, 'common-types/configuration', true, false, true) */
  configuration?: unknown;

Is it a bug or I missed something?

[kristiandupont]

The syntax is confusing, I agree. I think the problem there is that the colon is superfluous. Either you use a colon, or you use parenthesis (as if it's a function being applied). And yes, I need to document this.

[ersinakinci]

Does the syntax not support defining complex types inline? I tried

comment on column "scenario"."data" is '@type:{percentageBusinessUsage?:number;homeId?:string;businessEntityId?:string;startDate?:string;}'

with and without spacing, colon vs. parentheses, etc., and all I get is the comment generated similar to @dil-dvukman.

[ersinakinci]

Ah, I see that it doesn't, I will have to import for anything other than string:

kanel/packages/kanel/src/generators/resolveType.ts

Lines 23 to 46 in 0bc8491

	if (typeof tags.type === "string") {
	// If it's just a string, assume system type. No import necessary
	return tags.type;
	} else if (Array.isArray(tags.type)) {
	const [
	name,
	path,
	isAbsoluteString,
	isDefaultString,
	importAsTypeString,
	] = tags.type;
	return {
	name,
	typeImports: [
	{
	name,
	path,
	isAbsolute: isAbsoluteString === "true",
	isDefault: isDefaultString === "true",
	importAsType: importAsTypeString === "true",
	},
	],
	};
	}

[kristiandupont]

Yeah, interestingly I was struggling with this myself yesterday. I have a generic type that requires two imports which is not possible with the current syntax.

The solution I am going with is to move that information out of the database, even though it conflicts with my "Postgres as the source of truth" principle. But basically, I created a new pre-render hook that will replace specific types. So, I am now specifying a few table.column types in the Kanel configuration file instead. It's quite a bit easier to work with. It's scrappy so far, but I might polish it a bit and include it as a built-in hook..

If you are interested, this is what it currently looks like:

/** @type {import("kanel").PreRenderHook} */
const specificTypes = async (outputAcc, instantiatedConfig) => {
  const mappings = {
    "OauthConnection.encrypted_tokens": {
      name: "EncryptedJSON<OauthTokens>",
      typeImports: [
        {
          name: "EncryptedJSON",
          path: "./src/util/EncryptedJSON",
          isDefault: false,
          isAbsolute: false,
          importAsType: true,
        },
        {
          name: "OauthTokens",
          path: "./src/oauth/oauthTokens",
          isDefault: false,
          isAbsolute: false,
          importAsType: true,
        },
      ],
    },
  };

  const output = { ...outputAcc };

  for (const path of Object.keys(output)) {
    const file = outputAcc[path];
    const declarations = file.declarations.map((decl) => {
      const declaration = { ...decl };

      if (declaration.declarationType === "interface") {
        for (const field of declaration.properties) {
          const key = `${declaration.name}.${field.name}`;
          if (mappings[key]) {
            field.typeName = mappings[key].name;
            declaration.typeImports = [
              ...(declaration.typeImports || []),
              ...mappings[key].typeImports,
            ];
          }
        }
      }
      return declaration;
    });

    output[path] = {
      ...file,
      declarations,
    };
  }

  return output;
};

[WMcKibbin]

dropping this here for other internet travelers

-- Type, path, isAbsolute, isDefault, importAsType
COMMENT ON COLUMN locations.address IS E'@type(Address, \'../non-generated/Address\', true, false, true)';

This will output

import type { Address } from '../non-generated/Address';
...

export default interface ManufacturerLocationsTable {
  id: ColumnType<ManufacturerLocationsId, ManufacturerLocationsId, ManufacturerLocationsId>;

  manufacturerId: ColumnType<ManufacturersId | null, ManufacturersId | null, ManufacturersId | null>;

  name: ColumnType<string | null, string | null, string | null>;

  address: ColumnType<Address | null, Address | null, Address | null>;
}

Yet to figure out how to properly populate properly this when I query but Ill get there im sure.