DT
Drizzle Team•13mo ago
Titan

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?
No description
6 Replies
Huge Letters
Huge Letters•13mo ago
what's the purpose of true in select object? why it doesn't just contain relevant columns like in returning object?
Titan
TitanOP•13mo 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.
Titan
TitanOP•13mo ago
the docs say to select columns you use columnName: true
No description
Aaroned
Aaroned•13mo 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)
Titan
TitanOP•13mo 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
Aaroned•12mo 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,
})

Did you find this page helpful?