I’m building an abstract base repository class using Drizzle ORM for a PostgreSQL database. The repository is generic and works with any table extending PgTable, and the primary key is passed dynamically. Here’s the relevant part of my implementation:

async findOne(id: T['$inferSelect'][ID]): Promise<InferSelectModel<T>> {
  if (!id) {
    throw new Error('ID is required');
  }

  const result = await this.db
    .select()
    .from(this.table)
    .where(eq(sql.raw(`${this.primaryKey.toString()}`), id));

  if (!result.length) {
    throw new Error(`No record found for ID: ${id}`);
  }

  return result[0];
}

The Problem:

The sql.raw(${this.primaryKey.toString()}) usage is not type-safe, which defeats the purpose of using TypeScript for strict typing. If the primaryKey string doesn’t match the actual column name in the table, it will only throw runtime errors.

I want a solution that ensures the primary key is type-safe and can be correctly validated at compile-time.

Additional Context:

• The primaryKey parameter in the constructor is of type ID extends keyof T[‘$inferSelect’].
• The repository is used to perform common CRUD operations (findOne, create, update, delete) for various tables.
• Drizzle ORM does not directly support operations like .where() using dynamic keys without sql.raw.

Here’s the constructor for additional reference:

constructor(
  protected readonly db: DrizzleDB,
  protected readonly table: T,
  protected readonly primaryKey: ID,
) {}

1. How can I make the where() clause for the primary key type-safe in this context?
2. Is there a better approach or pattern for handling dynamic primary keys in a repository class with Drizzle ORM?