sqlite introspector missing generated/computed columns

[zihaolam]

Hi 👋,

I've been using kysely for production at work. We're using kysely-codegen to generate type definitions. One thing that I've noticed is that generated/computed columns are excluded from introspection due to SqliteIntrospector#getTableMetadata method using pragma_table_info, which excludes hidden columns.

    const tableMetadata = await this.#db
      .with('table_list', (qb) => this.#tablesQuery(qb, options))
      .selectFrom([
        'table_list as tl',
        sql<PragmaTableInfo>`pragma_table_info(tl.name)`.as('p'),
      ])
      .select([
        'tl.name as table',
        'p.cid',
        'p.name',
        'p.type',
        'p.notnull',
        'p.dflt_value',
        'p.pk',
      ])
      .orderBy(['tl.name', 'p.cid'])
      .execute()

Instead of pragma_table_info, if pragma_table_xinfo was used, then we can filter by the hidden column where p.hidden <> 1.

pragma_table_xinfo: The output has the same columns as for PRAGMA table_info plus a column, "hidden", whose value signifies a normal column (0), a dynamic or stored generated column (2 or 3), or a hidden column in a virtual table (1).

[petdomaa100]

For those encountering this issue, here is a solution based on @zihaolam's approach.

Create a custom introspector that is the SqliteIntrospector with the following changes:

- // https://www.sqlite.org/pragma.html#pragma_table_info
- interface PragmaTableInfo {
+ // https://www.sqlite.org/pragma.html#pragma_table_xinfo
+ interface PragmaTableXInfo {
  cid: number;
  dflt_value: unknown;
  name: string;
  notnull: 0 | 1;
  pk: number;
  type: string;
+ hidden: number;
}

// ...

const tableMetadata = await this.#db
  .with('table_list', qb => this._tablesQuery(qb, options))
  .selectFrom([
    'table_list as tl',
-   sql<PragmaTableInfo>`pragma_table_info(tl.name)`.as('p'),
+   sql<PragmaTableXInfo>`pragma_table_xinfo(tl.name)`.as('p'),
  ])
  .select([
    'tl.name as table',
    'p.cid',
    'p.name',
    'p.type',
    'p.notnull',
    'p.dflt_value',
    'p.pk',
+   'p.hidden',
  ])
  .orderBy('tl.name')
  .orderBy('p.cid')
  .execute();

// ...

+ const GENERATED_COL_HIDDEN_TYPE = 2;

return {
  name: name,
  isView: type === 'view',
  columns: columns.map(col => ({
    name: col.name,
    dataType: col.type,
-   isNullable: !col.notnull,    
+   isNullable: !col.notnull && col.hidden !== GENERATED_COL_HIDDEN_TYPE,
    isAutoIncrementing: col.name === autoIncrementCol,
    hasDefaultValue: col.dflt_value != null,
    comment: undefined,
  })),
};

To properly type generated columns as read-only, use ColumnType<..., never, never>. If you're using kysely-codegen, this can be done with the overrides config option.

Although it's a small patch, it would be very neat if Kysley would do this out of the box. Generated columns are awesome, first-class support would be great. They might need a new internal representation because isNullable and hasDefaultValue might not apply the same way. A flag like isGenerated would allow type generators like kysely-codegen to generate the proper read-only type. (Just a suggestion, I'm not familiar with the internals of this library.)