Knex/MySQL snake_case to camelCase, typecasting tinyint to boolean

[markuslerner]

Discussed in #3516

I think it would be useful, if this was added to the documentation. I'm creating an issue, since my discussion post didn't create any feedback.

^{Originally posted by markuslerner July 11, 2024}
I was struggling for a long time to find a way to automatically convert snake_case MySQL database fields to camelCase and typecasting tinyint to boolean.

I finally found this solution, which works very well for me. I thought this might be useful to be included in the docs. I guess, it's quite a common case, but unfortunately I didn't find anything there yet. What you do you'll think?

Here's my custom src/mysql.ts:

// For more information about this file see https://dove.feathersjs.com/guides/cli/databases.html
import knex from 'knex'
import type { Knex } from 'knex'
import type { Application } from './declarations'
import { knexSnakeCaseMappers } from 'objection'

declare module './declarations' {
  interface Configuration {
    mysqlClient: Knex
  }
}

const typeCast = (field: any, next: any) => {
  if (field.type == 'TINY' && field.length == 1) {
    // Convert tinyint to boolean
    return field.string() === '1' // 1 = true, 0 = false
  }
  return next()
}

export const mysql = (app: Application) => {
  const config = app.get('mysql')

  const db = knex({
    ...config!,
    ...knexSnakeCaseMappers(),
    connection: {
      ...(config!.connection as {
        host?: string
        port?: number
        user?: string
        password?: string
        database?: string
      }),
      typeCast
    }
  })

  app.set('mysqlClient', db)
}
```</div>