Define types for JSON columns

[daffron]

Hey!, First thanks for creating this tool - saves time for sure!

This is probably similar to https://github.com/RobinBlomberg/kysely-codegen/issues/30

However that seemed focused on deriving the types from ENUMs and headed down a different path.

I was wondering if theres any way to achieve the following:

export interface IMarkertingData {
 someValue: string;
}

export interface User {
  created_at: Generated<Timestamp | null>;
  email: string;
  first_name: string | null;
  id: Generated<string>;
  last_name: string | null;
  marketing_data: IMarkertingData | null;
  updated_at: Generated<Timestamp | null>;
}

Currently I had to use the codegen as a once off, save the file in my source code and modify / keep updated manually. Which I assume isnt the intended use?

Thanks!

Upvote & Fund

• We're using Polar.sh so you can upvote and help fund this issue.
• We receive the funding once the issue is completed & confirmed by you.
• Thank you in advance for helping prioritize & fund our backlog.

[maccman]

Same question :)

[igalklebanov]

Hey 👋

If this is possible, it could be very helpful for type-safe JSON traversal https://github.com/kysely-org/kysely/pull/440 that was released in kysely v0.26.

[RobinClowers]

@koskimas suggested that json types should be any or unknown. Currently the Json type that is generated requires us to drop down to sql template string to actually do anything useful with a json column.

[jjdonov]

From the outside looking in, I'd think that would be really hard to generate. Any json/b column could have completely different contents.

I wonder if this becomes a simpler problem if something like https://github.com/supabase/pg_jsonschema were used to constrain the column

[noppa]

One way to do this could be to generate another file for the json column - if one doesn't exist already - and not automatically modify it after that, so it'd be safe to edit it to contain the column type.

For example, it could generate something like

// jsonb-MarketingData.d.ts
export type MarketingData = unknown

// database.d.ts
import type {MarketingData} from './jsonb-MarketingData'

export interface User {
  marketing_data: MarkertingData | null;
}

Then the developer would be free to modify that unknown type to something else, without fearing that it will be overridden later.

[bombillazo]

I agree this is needed to extend or override types with developer known types. @noppa 's idea would be great since you isolate manual types from auto-generated types.

[mb21]

Plus one. Came here from https://kysely.dev/docs/recipes/extending-kysely where they access the address JSON field by declaring the type:

interface DB {
  person: {
    address: {
      postalCode: string
      street: string
    }
  }
}

But if you're using kysely-codegen this seems to be currently impossible to do? TypeScript declaration merging doesn't allow you to change the type of a field. Only way I see is you post-process the generated file with a manual string search-and-replace with sed or similar...

[Exellin]

I ended up solving this with a script locally by adding the type to a comment in the database through a migration file, using the functionality in Kanel as an inspiration: https://github.com/kristiandupont/kanel/issues/429.

`await sql`COMMENT ON COLUMN habits.amount_thresholds_by_frequency IS '@type:{ [key: number]: {min: number, max: number} }'`.execute(db);`

I created a script file to run right after a migration, grabbing everything after @type if it exists and replacing any json type in a column.

// based on https://github.com/RobinBlomberg/kysely-codegen/issues/90#issuecomment-1825032368

import db from '../';
import { readFileSync, writeFileSync } from 'fs';

const transformFile = async () => {
  const filePath = process.argv[2];

  if (!filePath) {
    console.error('No file path provided!');
    return;
  }

  const content = readFileSync(filePath, 'utf-8');
  const lines = content.split('\n');
  let dbInterfaceLineIndex = lines.findIndex((line) => {
    return line === 'export interface DB {';
  });

  if (dbInterfaceLineIndex === -1) {
    console.warn("Couldn't find `export interface DB");
  }

  const tables = await db.introspection.getTables();
  db.destroy();

  let tableName: string;

  const transformedLines = lines.flatMap((line) => {
    if (line.includes('export interface')) {
      tableName = line.split('export interface')[1].split('{')[0].trim().toLowerCase();
    }
    if (line.includes(': Json;')) {
      const columnName = line.split(': Json')[0].trim();
      if (tableName && columnName) {
        const table = tables.find((table) => table.name == tableName);
        const column = table?.columns.find((column) => column.name == columnName);
        const type = column?.comment?.split('@type:')[1];

        if (type) return line.replace('Json', type);
      }

      return line;
    }

    return line;
  });

  writeFileSync(filePath, transformedLines.join('\n'), 'utf-8');
};

transformFile();

and added these scripts to my package.json:

    "replace-json": "ts-node db/scripts/insertJsonTypes db/schema.d.ts",
    "migrate": "ts-node db/scripts/migrateToLatest && kysely-codegen --out-file db/schema.d.ts && npm run replace-json"

and I get the following line in my schema.d.ts:

 amount_thresholds_by_frequency: { [key: number]: {min: number, max: number} };

If there is interest I could work on a PR to implement this functionality into kysely-codegen.

[probablykabari]

Coming here from working on another typescript postgres library. The way that library handles this is the type for JSON columns is generated separately as a default value that you can update in a non-generated file, similar to what @noppa has suggested. That is very flexible and convenient.

[tarushnagpal]

@Exellin This worked beautifully for me! I added a few more features, basically had a use case where we wanted to use defined interfaces and also allow multiple types of json for the same column (Bad design but at times it is what it is)

So ended up adding this

if (type.includes(' | ')) {
    type.split(' | ').forEach((t) => addedImportTypes.add(t);
    return line.replace('Json', type);
}

Also catch the addedImportTypes which is basically let addedImportTypes: Set<string> = new Set();, and you can just end up adding a

`import {\n\t${Array.from(addedImportTypes).join(',\n\t')}\n} from "./json-types";\n`

to the final file

[sfratini]

I am currently doing this:

import {DB, Question} from './db.types'
import {QuestionScoreConfig, QuestionType, QuestionValidation} from "../types/graphql";

interface EnhancedQuestionType extends Omit<Question, 'options' | 'score' | 'type' | 'validation'> {
    options: string[],
    score: QuestionScoreConfig,
    type: QuestionType,
    validation: QuestionValidation
}

export interface EnhancedDB extends Omit<DB, 'question'> {
    question: EnhancedQuestionType
}

First import is the results from kysely-codegen. Second import is the auto generated types from graphql-codegen. I am just overriding the types for the JSON columns. The JSON types are generated directly into Graphql schema but it would not matter . QuestionType is an enum so it also works for inserts:

    const singleSelect = await kysely.updateTable('question')
        .where('type', 'is', null).where('idDdQuestionType', '=', 1)
        .set('type', QuestionType.SingleSelect)
        .executeTakeFirst();

Then I just do Kysely<EnhancedDB> and that is it.

[RobinBlomberg]

Should be released in kysely-codegen@0.16.0! 🚀

[bombillazo]

Hey, just saw this, is there any documentation on how this new feature works?

We have a post-processing script that updates all our imports and wanted to see if now we can do it directly with this codegen

[RobinClowers]

@bombillazo Looks like this comment describes it: https://github.com/RobinBlomberg/kysely-codegen/pull/148#issuecomment-2047907063, but it would be awesome to include an example in the readme.