dynamicPick ts result type from execute

[kir4ik]

Hi,

import {MySqlConnection} from 'ts-sql-query/connections/MySqlConnection';
import {Table} from 'ts-sql-query/Table';
import pick from 'lodash/pick';
import {SelectedValues, UpdatableValues} from 'ts-sql-query/extras/types';
import {extractColumnsFrom} from 'ts-sql-query/extras/utils';
import {dynamicPick} from 'ts-sql-query/dynamicCondition';

class Connection extends MySqlConnection<'Connection'> {
  allowEmptyString = true;
}

class StoreTable extends Table<Connection, 'StoreTable'> {
  id = this.autogeneratedPrimaryKey('id', 'int');
  name = this.column('name', 'string');
  address = this.column('address', 'string');
  foo = this.optionalColumn('foo', 'double');
  bar = this.column('bar', 'boolean');
  someField = this.column('someField', 'string');
  someBool = this.column('someBool', 'boolean');

  constructor() {
    super('store');
  }
}

export const storeTable = new StoreTable();

const EXTRACTED_COLUMNS_FROM_STORE_TABLE = extractColumnsFrom(storeTable);

const AVAILABLE_STORE_FIELD = ['name', 'address', 'foo', 'someBool'] as const;
type AvailableStoreField = typeof AVAILABLE_STORE_FIELD[number];

type SomeStoreClassGetStoreResult<TField extends AvailableStoreField> = Pick<
  SelectedValues<StoreTable>,
  TField
>;

class SomeStoreClass {
  constructor(private getConnection: () => Connection, private storeId: number) {}

  async getStore<TField extends AvailableStoreField>(
    fields: TField[]
  ): Promise<SomeStoreClassGetStoreResult<TField>> {
    const connection = this.getConnection();

    const selectQuery = connection
      .selectFrom(storeTable)
      .select(dynamicPick(EXTRACTED_COLUMNS_FROM_STORE_TABLE, {}, fields))
      // or pick via lodash
      // .select(pick(EXTRACTED_COLUMNS_FROM_STORE_TABLE, fields))
      .where(storeTable.id.equals(this.storeId));

    const store = await selectQuery.executeSelectNoneOrOne();
    if (!store) {
      throw new Error(`store #${this.storeId} not found`);
    }

    // store any field unavailable
    return store; // ts(2322) error
  }
}

const someStoreClass = new SomeStoreClass({} as any, 1);

/**
 * I want to use it like this, so that there are only those fields that I specified
 * and that the type is correct (without undefined for required columns)
 */
someStoreClass.getStore(['address', 'foo']).then((store) => {
  /**
   * store type => SomeStoreClassGetStoreResult<"address" | "foo">
   * store => {address: string; foo?: number | undefined}
   */
})

is it possible to make support for such a sample
Or how can I do the right thing?

[juanluispaz]

Hi,

Wow, this is very complex in TS; I don't know if there is a clean way to implement this, considering the number of transformations the library does internally.

In any case, I found a way for you to do this 😁

First, some points:

• dynamicPick work with an object where the key is the property and the value is a boolean that indicates if you want to get that value, the last parameter if you want to indicate the always required property (it must be const defined when the function is called); if you put there this generic value, the type transformation starts working wrongly due TS not been able to determinate the structure of the resulting object. For this reason, I'm implementing for you a variant of this function that receives the path of the properties you want instead of the mentioned object called dynamicPickPaths

• I'm adding a utility function for you called expandTypeFromDynamicPickPaths that transforms the result of the query with your dynamic pick rules in a way TS can maintain and infer the relation of the generic type in the returning output. It is not the cleanest way to code, but hopefully, it works for you; this solution is an intermedial solution, where I don't make a major change in the library to include this generic relationship at the cost of sacrificing the performance of TS.

• Be aware these functions work with complex projections

• I do not include it in the documentation yet or in the release notes; I will publish it for you as an experimental feature waiting for your feedback.

Then, here is how your code looks like with the changes I made:

import {MySqlConnection} from 'ts-sql-query/connections/MySqlConnection';
import {Table} from 'ts-sql-query/Table';
import {SelectedValues} from 'ts-sql-query/extras/types';
import {extractColumnsFrom} from 'ts-sql-query/extras/utils';
import {dynamicPickPaths, expandTypeFromDynamicPickPaths} from 'ts-sql-query/dynamicCondition';

class Connection extends MySqlConnection<'Connection'> {
  allowEmptyString = true;
}

class StoreTable extends Table<Connection, 'StoreTable'> {
  id = this.autogeneratedPrimaryKey('id', 'int');
  name = this.column('name', 'string');
  address = this.column('address', 'string');
  foo = this.optionalColumn('foo', 'double');
  bar = this.column('bar', 'boolean');
  someField = this.column('someField', 'string');
  someBool = this.column('someBool', 'boolean');

  constructor() {
    super('store');
  }
}

export const storeTable = new StoreTable();

const EXTRACTED_COLUMNS_FROM_STORE_TABLE = extractColumnsFrom(storeTable);

const AVAILABLE_STORE_FIELD = ['name', 'address', 'foo', 'someBool'] as const;
type AvailableStoreField = typeof AVAILABLE_STORE_FIELD[number];

type SomeStoreClassGetStoreResult<TField extends AvailableStoreField> = Pick<
  SelectedValues<StoreTable>,
  TField
>;

class SomeStoreClass {
  constructor(private getConnection: () => Connection, private storeId: number) {}

  async getStore<TField extends AvailableStoreField>(
    fields: TField[]
  ): Promise<SomeStoreClassGetStoreResult<TField>> {
    const connection = this.getConnection();

    const pick = dynamicPickPaths(EXTRACTED_COLUMNS_FROM_STORE_TABLE, fields)
    const store = await connection
      .selectFrom(storeTable)
      .select(pick)
      .where(storeTable.id.equals(this.storeId))
      .executeSelectNoneOrOne();

    if (!store) {
      throw new Error(`store #${this.storeId} not found`);
    }

    return expandTypeFromDynamicPickPaths(EXTRACTED_COLUMNS_FROM_STORE_TABLE, fields, store);
  }
}

const someStoreClass = new SomeStoreClass({} as any, 1);

/**
 * I want to use it like this, so that there are only those fields that I specified
 * and that the type is correct (without undefined for required columns)
 */
someStoreClass.getStore(['address', 'foo']).then((store) => {
  /**
   * store type => SomeStoreClassGetStoreResult<"address" | "foo">
   * store => {address: string; foo?: number | undefined}
   */
})

Please, test this solution and let me know if that satisfies and works for you.

I'm also interested in understanding why you want to use this picking feature; several people asked me about this feature, but only some of them had already needed it.

I will be waiting for your feedback.

[juanluispaz]

Released ts-sql-query 1.49.0 with the changes commented here.

[kir4ik]

Hi

Nice try)) but there are problems.
For example, if you change the query, then the result no longer matches, but no errors appear

const store = await connection
      .selectFrom(storeTable)
      .select(pick)
      .where(storeTable.id.equals(this.storeId))
      .executeSelectNoneOrOne();

to

const store = await connection
      .selectFrom(storeTable)
      .select({
          lastRawOrderTime: storeTable.lastOrderTime
      })
      .where(storeTable.id.equals(this.storeId))
      .executeSelectNoneOrOne();

Also we should always do this check

if (!store) {
  throw ...
}

And need the same for SelectMany support

---

I think we can put it off for now, I'll think about it again
While I can write "as any"

return store as any;

[juanluispaz]

Released ts-sql-query 1.52.0 making public and documenting this feature. The function expandTypeFromDynamicPickPaths had been modified to deal with all kind of output coming from a query. Let me know if the changes avoid you the cast to any.

[kir4ik]

no, unfortunately this only works when ts knows exactly which fields are requested, but then the type is already inferred without any extra effort.

It doesn't work for dynamic fields:

async getStore<TField extends AvailableStoreField>(
    fields: TField[]
  ): Promise<SomeStoreClassGetStoreResult<TField>> {
    const connection = this.getConnection();
    
    const pickedFields = dynamicPickPaths(EXTRACTED_COLUMNS_FROM_STORE_TABLE, fields);
    const selectQuery = connection
      .selectFrom(storeTable)
      .select(pickedFields)
      .where(storeTable.id.equals(this.storeId));

    const store = await selectQuery.executeSelectNoneOrOne();
    if (!store) {
      throw new Error(`store #${this.storeId} not found`);
    }

    const res = expandTypeFromDynamicPickPaths(EXTRACTED_COLUMNS_FROM_STORE_TABLE, fields, store);

    /**
     * Type 'Expand<{ id?: number | undefined; name?: string | undefined; address?: string | undefined; foo?: number | undefined; bar?: boolean | undefined; someField?: string | undefined; someBool?: boolean | undefined; } & ResultObjectValues<...> & Pick<...>>' is not assignable to type 'SomeStoreClassGetStoreResult<TField>'.
  Type '{}' is not assignable to type 'SomeStoreClassGetStoreResult<TField>'.ts(2322)
     */
    return res;
  }

---

At the moment i am doing this:

async getStore<TField extends AvailableStoreField>(
    fields: TField[]
  ): Promise<SomeStoreClassGetStoreResult<TField>> {
    const connection = this.getConnection();
    
    const selectQuery = connection
      .selectFrom(storeTable)
      .select(dynamicPick(EXTRACTED_COLUMNS_FROM_STORE_TABLE, {}, fields))
      .where(storeTable.id.equals(this.storeId));

    const store = (await selectQuery.executeSelectNoneOrOne()) as SomeStoreClassGetStoreResult<TField> | null;
    if (!store) {
      throw new Error(`store #${this.storeId} not found`);
    }

    return store;
  }

I would like without "as", but so far so))

[juanluispaz]

mmmm, I already have some usage of that, even in the documentation I placed an example. Can you include here your tsconfig y typescript version?

[juanluispaz]

Can you include as well your AvailableStoreField definition, as you have in your code?

I see you are not placing any mandatory field; I will verify that case as well

[juanluispaz]

Include as well SomeStoreClassGetStoreResult type definition

[kir4ik]

all difinitions from first comment, need only replace method async getStore

[kir4ik]

tsconfig.json

{
  "compilerOptions": {
    "target": "es2019",
    "module": "commonjs",
    "jsx": "preserve",
    "lib": [
      "dom",
      "es2018",
      "esnext"
    ],
    "baseUrl": ".",
    "moduleResolution": "Node",
    "strict": true,
    "allowJs": true,
    "esModuleInterop": true,
    "skipLibCheck": true,
    "sourceMap": true,
    "forceConsistentCasingInFileNames": true,
    "resolveJsonModule": true,
    "isolatedModules": true,
    "noEmit": false,
    "removeComments": true,
    "experimentalDecorators": true,
    "emitDecoratorMetadata": true,
    "incremental": true,
    "downlevelIteration": true,
    "allowSyntheticDefaultImports": true,
    "plugins": [{"name": "typescript-plugin-css-modules"}],
    "declaration": true,
    "noUnusedLocals": true,
    "noUnusedParameters": true,
    "noImplicitAny": true,
    "outDir": "./lib"
  },
  "include": [
    "next-env.d.ts",
    "src/**/*.ts",
    "src/**/*.tsx",
    "src/**/*.js",
    "src/**/*.jsx"
  ],
  "exclude": [
    "node_modules",
    ".yarn"
  ]
}