[FEATURE]: Ability to specify selected fields dynamically

[moshest]

Describe what you want

I have a model on my project that fetching users from the database. Each time I need different set of fields and I would like to define a function with generics so only the exact fields I asked for will be return:

const findByEmail = async <P>(email: string, projection: P) => {
  const rows = await db
    .select(projection)
    .from(users)
    .where(eq(users.email, email))
    .limit(1);
  
  if (!rows.length) {
    return;
  }
  
  return rows[0];
}

And then I can ask only for the fields I want:

const userWithIdOnly = await findByEmail(email, { id: users.id });
      // ^ { id: number }
           
const userWithName =  = await findByEmail(email, { id: users.id, name: users.name });
      // ^ { id: number; name: string }

I encounter 2 problems when trying to implement this:

1. There is no type I can use to extend P. I'm looking for something like P extends SelectedFields<typeof users>
2. The current implementation breaks and produce invalid types in response. It seems that Drizzle expecting only static projection types and can't handle generic properly.

Any help will be appreciated.

[Angelelz]

The selection passed to select is too wide, you could select whatever you want from the database. like count, max, all kinds of functions, etc.
I guess you just need to manually type the generic parameter and the help the compiler by type casting.
Check this example implementation.

[Angelelz]

The trick is actually the "partial" generic parameter. That needs to exists somewhere for the inference to work. That's why type casting is needed. I think we can close this issue and if you think we could expose a type helper for something like this, please create a new one with your idea. I even went ahead and extended my last example to accept extra properties with proper types. See here

[FredericLatour]

Hi,
Does not seem to work with MySql.
I tried to convert the provided example implementation but it seems to be missing a parameter for MySqlSelectBase.
Would you mind providing the missing parameter. I tried to follow the never ending suite of extends but could not get it.
Thanks in advance.

example implementation converted to MySql

[Angelelz]

The types changed a little bit in the last release. You actually need to use 'single' instead of 'partial' and mysql has an extra type parameter. See here

[FredericLatour]

Thanks a lot...
I just had to make a slight change because I'm using this in an effect-ts context. I had first to cast the result to unknown. I made some tests and it works correctly.

Here is a link for anyone who would be looking for the same thing:
mysql implementation in an effect-ts context

I had also published a post in the help section of Discord. I will answer myself by pointing to your solution and my slight change.

[mjbergman92]

Dynamic selection is "impossible" once you try to do joins. I have tried for hours, even with type casting.

EDIT: I did find a way that is not fully dynamic by creating a fixed set of preset options and type casting... but that is not good for the long term.

[cyrilchapon]

@mjbergman92 , found a way. See #3560 (comment)