Insert returning api to match select columns

I have the following helpers to select the right columns and ensure I don't leak sensitive data:

export const userSelectPublicColumns = { id: true, firstName: true, lastName: true }
export const userSelectSensitiveColumns = {
  ...userSelectPublicColumns,
  email: true,
  mobileNumber: true,

export const userSelectPublicColumns = { id: true, firstName: true, lastName: true }
export const userSelectSensitiveColumns = {
  ...userSelectPublicColumns,
  email: true,
  mobileNumber: true,

However when I come to call insert().returning() it seems the .returning() method takes a different shape and I'll need to duplicate my objects values above from boolean true to table.columnKey Anyway around this duplication?

6 Replies

Huge Letters•8mo ago

what's the purpose of true in select object? why it doesn't just contain relevant columns like in returning object?

TitanOP•8mo ago

https://orm.drizzle.team/docs/rqb#partial-fields-select

Drizzle ORM - Query

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

TitanOP•8mo ago

the docs say to select columns you use columnName: true

Aaroned•8mo ago

@Titan you could possibly create a conversion function that utilises both getTableColumns(table) (https://orm.drizzle.team/docs/goodies#get-typed-table-columns) and maybe also InferSelectModel<typeof table> (https://orm.drizzle.team/docs/goodies#type-api)

TitanOP•8mo ago

useful thanks, I wonder if that works in a Drizzle relational query "columns" option as shown in the screenshot above. The example only shows it's usage in db.select which makes me doubtful, will have to give it a go thanks

Aaroned•8mo ago

I think you will need to write an automatic mapping function SelectColumnsToReturningColumns, but I think you should be able to get the values needed for returning columns from getTableColumns @Titan Here is a possible conversion function

type TrueKeys<T extends Record<string, boolean>> = {
  [K in keyof T]: T[K] extends true ? K : never
}[keyof T]

function getSelectColumns<
  TQueryColumns extends Record<string, boolean>,
  TKeys extends TrueKeys<TQueryColumns>,
  TTableColumns extends Record<TKeys, unknown>
>(queryColumns: TQueryColumns, tableColumns: TTableColumns) {
  const keys = Object.keys(tableColumns) as TKeys[]
  return keys
    .filter(key => queryColumns[key])
    .reduce(
      (acc, key) => ({ ...acc, [key]: tableColumns[key] }),
      {} as { -readonly [Key in TKeys]: TTableColumns[Key] }
    )
}

const queryColumns = {
  id: true,
} as const

const selectColumns = getSelectColumns(
  queryColumns,
  getTableColumns(tables.organisation)
)

const selectResult = await db
  .select(selectColumns)
  .from(tables.organisation)
const queryResult = await db.query.organisation.findMany({
  columns: queryColumns,
})

type TrueKeys<T extends Record<string, boolean>> = {
  [K in keyof T]: T[K] extends true ? K : never
}[keyof T]

function getSelectColumns<
  TQueryColumns extends Record<string, boolean>,
  TKeys extends TrueKeys<TQueryColumns>,
  TTableColumns extends Record<TKeys, unknown>
>(queryColumns: TQueryColumns, tableColumns: TTableColumns) {
  const keys = Object.keys(tableColumns) as TKeys[]
  return keys
    .filter(key => queryColumns[key])
    .reduce(
      (acc, key) => ({ ...acc, [key]: tableColumns[key] }),
      {} as { -readonly [Key in TKeys]: TTableColumns[Key] }
    )
}

const queryColumns = {
  id: true,
} as const

const selectColumns = getSelectColumns(
  queryColumns,
  getTableColumns(tables.organisation)
)

const selectResult = await db
  .select(selectColumns)
  .from(tables.organisation)
const queryResult = await db.query.organisation.findMany({
  columns: queryColumns,
})

Gaming

Programming

Insert returning api to match select columns