How to use generic with Knex.raw?

Question

I am trying to type the following code. Instead of using type assertion, how can I type it with generic?

const result = await knex.raw<string>('select NOW()');

console.log(result?.rows[0]) //Print the date

Right now typescript will infer result as string, but it should have a shape like this:

Result {
  command: 'SELECT',
  rowCount: 1,
  oid: null,
  rows: [ { now: 2022-05-10T19:28:43.624Z } ],
  fields: [
    Field {
      name: 'now',
      tableID: 0,
      columnID: 0,
      dataTypeID: 1184,
      dataTypeSize: 8,
      dataTypeModifier: -1,
      format: 'text'
    }
  ],
  _parsers: [ [Function: parseDate] ],
  _types: TypeOverrides {
    _types: {
      getTypeParser: [Function: getTypeParser],
      setTypeParser: [Function: setTypeParser],
      arrayParser: [Object],
      builtins: [Object]
    },
    text: {},
    binary: {}
  },
  RowCtor: null,
  rowAsArray: false
}

I tried to look for the type for Result, but seems like Knex does not provide one by default?

https://github.com/knex/knex/blob/master/types/result.d.ts

Answer 1

When using knex.raw() the return type comes from the driver's response itself.

If your using Postgres for instance, you're most likely using pg as driver, in which case it has a generic interface QueryResult that can be use for typing your expected response.

Take a look at the following example:

import { knex } from 'knex'
import { QueryResult } from 'pg'

const db = knex({
  client: 'pg',
  connection: {
    host: 'localhost',
    port: 5432
  }
})

interface Student {
  id: number
  name: string
  last_name: string
}

const fetchStudents = async (): Promise<Student[]> => {
  const response = await db.raw<QueryResult<Student>>(
    'select id, name, last_name from students'
  )
  return response.rows // rows type is successfully inferred as Student[]
}

Same approach can be followed for other DB clients, look at the underlying driver and try to find their generic response type.