Knex migrate:make fails when using TS and ESM modules

When setting up a Knex project using typescript with knex init -x ts, running knex migrate:make test fails with this error:

Unknown file extension ".ts" for C:\Dev\js\knex-ts\knexfile.ts
TypeError [ERR_UNKNOWN_FILE_EXTENSION]: Unknown file extension ".ts" for C:\Dev\js\knex-ts\knexfile.ts
    at new NodeError (node:internal/errors:405:5)
    at Object.getFileProtocolModuleFormat [as file:] (node:internal/modules/esm/get_format:79:11)
    at defaultGetFormat (node:internal/modules/esm/get_format:124:36)
    at defaultLoad (node:internal/modules/esm/load:89:20)
    at nextLoad (node:internal/modules/esm/loader:163:28)
    at load$1 (file:///C:/Dev/js/knex-ts/.pnp.loader.mjs:1456:12)
    at nextLoad (node:internal/modules/esm/loader:163:28)
    at ESMLoader.load (node:internal/modules/esm/loader:603:26)
    at ESMLoader.moduleProvider (node:internal/modules/esm/loader:457:22)
    at new ModuleJob (node:internal/modules/esm/module_job:64:26)

I set up a stripped down project to reproduce the issue, containing a simple knexfile and tsconfig.

knexfile.ts

import type { Knex } from "knex";

const config: { [key: string]: Knex.Config } = {
  development: {
    client: "pg",
  }
};
export default config

package.json

{
  "name": "knex-ts",
  "type": "module",
  "scripts": {
    "knex": "knex"
  },
  "dependencies": {
    "knex": "^3.0.1",
    "pg": "^8.11.3",
    "ts-node": "^10.9.1",
    "typescript": "^5.2.2"
  },
  "devDependencies": {
    "@types/node": "^20.9.0"
  },
  "packageManager": "yarn@3.6.3"
}

tsconfig.json

{
  "compilerOptions": {
    "module": "ESNext",
    "target": "ESNext",
  },
  "include": ["**/*.ts", "**/*.tsx"],
  "exclude": ["node_modules"]
}

I tried a couple of different configuration options. Adding "esm": true to tsconfig as suggested in other threads did nothing. I tried it under compilerOptions, and under ts-node.

Removing "type": "module" wanted me to add "moduleResolution": "nodenext" to my tsconfig, and then still threw a Syntax Error Unexpected token "export".

The solution that finally worked for me was removing the "type": "module" from my package.json, and then overriding ts-node to compile to commonjs modules by changing my tsconfig.json file to this:

{
  "compilerOptions": {
    "module": "ESNext",
    "target": "ESNext",
  },
  "ts-node": {
    "compilerOptions": {
      "module": "CommonJS"
    }
  },
  "include": ["**/*.ts", "**/*.tsx"],
  "exclude": ["node_modules"]
}

The rest of my app (Next.js) could still use esm modules as normal.