DT
Drizzle Team•3mo ago
DemonWaves

Select statement with dynamic fields

Hello I'm writing out this because I don't get how I'm supposed to pass a subset of a given table's fields to a select() statement. I would like to do something like this: 
getUserById(id: number, fields: ??? = ['*'])  => {
  const [user] = await drizzle.select(fields).from(usersTable).where(eq(usersTable.id, id));
}
getUserById(id: number, fields: ??? = ['*'])  => {
  const [user] = await drizzle.select(fields).from(usersTable).where(eq(usersTable.id, id));
}
I would like to call that function like this: 
getUserById(1, ['id', 'name']);
getUserById(1, ['id', 'name']);
If second parameter is ommited then i get all columns (with the default ['*'] or something like that) I tried several approches, and it seems like i need to use SelectedFields but I don't understand how to do so. I would assume that the secont type argument is typeof userTable but cannot find what to put as first one.
1 Reply
DemonWaves
DemonWavesOP•3mo ago
Alright, I solved half of the issue, i was importing SelectedFields from the wrong package (drizzle-orm, instead of the one from my driver). Now it looks like this: 
import { eq, getTableColumns } from 'drizzle-orm';
import type { SelectedFields } from 'drizzle-orm/pg-core';
import type { SelectUser } from '#src/schemas/users/usersTable.js';
import { usersTable } from '#src/schemas/users/usersTable.js';

export const getUserById = async (
  id: number,
  fields: Array<keyof SelectUser> = [],
) => {
  const selectedFields: SelectedFields =
    fields.length === 0 ? getTableColumns(usersTable) : {};

  for (const field of fields) {
    selectedFields[field] = usersTable[field];
  }

  const result = await drizzle
    .select(selectedFields)
    .from(usersTable)
    .where(eq(usersTable.id, id));

  return result.at(0);
};
import { eq, getTableColumns } from 'drizzle-orm';
import type { SelectedFields } from 'drizzle-orm/pg-core';
import type { SelectUser } from '#src/schemas/users/usersTable.js';
import { usersTable } from '#src/schemas/users/usersTable.js';

export const getUserById = async (
  id: number,
  fields: Array<keyof SelectUser> = [],
) => {
  const selectedFields: SelectedFields =
    fields.length === 0 ? getTableColumns(usersTable) : {};

  for (const field of fields) {
    selectedFields[field] = usersTable[field];
  }

  const result = await drizzle
    .select(selectedFields)
    .from(usersTable)
    .where(eq(usersTable.id, id));

  return result.at(0);
};
I need to figure out how to correctly type the output now because i'm getting the following for the query result array: 
{
  [x: string]: unknown;
}[]
{
  [x: string]: unknown;
}[]
For some reason, I understood where the typing was lost, apparently, giving an object to select instead of declaring it directly in the function call makes typescript behaving differently. I'm not expert enough in this language to figure out a better solution than casting the output as what it should be but anyways... 
import { eq, getTableColumns } from 'drizzle-orm';
import type { SelectedFields } from 'drizzle-orm/pg-core';

import type { SelectUser } from '#src/schemas/users/usersTable.js';
import { usersTable } from '#src/schemas/users/usersTable.js';

export const getUserById = async <
  AskedFields extends ReadonlyArray<keyof SelectUser> = [],
>(
  id: number,
  fields?: AskedFields,
) => {
  const fieldsToSelect = (fields ?? []) as AskedFields;
  const selectedFields: SelectedFields =
    fieldsToSelect.length === 0 ? getTableColumns(usersTable) : {};

  for (const field of fieldsToSelect) {
    selectedFields[field] = usersTable[field];
  }

  const [user] = await database
    .select(selectedFields)
    .from(usersTable)
    .where(eq(usersTable.gameUserId, gameUserId));

  if (!user) {
    return null;
  }

  return user as AskedFields extends []
    ? SelectUser | null
    : Pick<SelectUser, AskedFields[number]> | null;
};
import { eq, getTableColumns } from 'drizzle-orm';
import type { SelectedFields } from 'drizzle-orm/pg-core';

import type { SelectUser } from '#src/schemas/users/usersTable.js';
import { usersTable } from '#src/schemas/users/usersTable.js';

export const getUserById = async <
  AskedFields extends ReadonlyArray<keyof SelectUser> = [],
>(
  id: number,
  fields?: AskedFields,
) => {
  const fieldsToSelect = (fields ?? []) as AskedFields;
  const selectedFields: SelectedFields =
    fieldsToSelect.length === 0 ? getTableColumns(usersTable) : {};

  for (const field of fieldsToSelect) {
    selectedFields[field] = usersTable[field];
  }

  const [user] = await database
    .select(selectedFields)
    .from(usersTable)
    .where(eq(usersTable.gameUserId, gameUserId));

  if (!user) {
    return null;
  }

  return user as AskedFields extends []
    ? SelectUser | null
    : Pick<SelectUser, AskedFields[number]> | null;
};
Now i can use this like: 
  const userWithEverything = await getUserById(id);
  const userWithOnlyNameAndId = await getUserById(gameUser.id, [
    'id',
    'name',
  ]);
  const userWithEverything = await getUserById(id);
  const userWithOnlyNameAndId = await getUserById(gameUser.id, [
    'id',
    'name',
  ]);
And getting both "type-safety" on the result and auto-completion for column names to get

Did you find this page helpful?