ts-node/register not working for Knex Config

Question

I have Knex set up with Typescript and would like to be able to run "knex migrate:latest" or any of my normal Knex scripts.

Currently I am using tsx to make my life easier and can run migrations via scripts like this: "NODE_OPTIONS='--import=tsx' knex migrate:latest" with directories pointed towards my dist JS compiled folder.

I would love to use the ts-node/register in my knexconfig file to just have this resolved for me. But I cannot get Knex to recognize this import. How do you get the register function to work in your knexconfig file?

By the way, I am using "type" : "module" in my package.json.

Reasoning: My current approach requires me to run tests (and migrations/seeds for those tests) from a /dist folder compiled to JS. While I can run migrations using tsx or ts-node in the CLI, I can't use those commands in a beforeEach, afterEach() during testing. I have tried unsuccessfully in the past to get ts-node/register to "register" my knexconfig file. I would like to avoid my workaround and include ts-node/register, but I am open to other solutions (if there are any).

In the below script, I show a "directory" of server/db/migrations which is what I want to be able to do. Currently, to get this working with the CLI, I run my directory through the dist folder: dist/server/db/migrations.

import { knexCamelCaseResponse } from "./server/utils/utils.js";
const dbName = "app_db";

require("ts-node").register();

export default {
  development: {
    client: "pg",
    connection: {
      host: "127.0.0.1",
      port: 5432,
      database: dbName,
    },
    postProcessResponse: (result, queryContext) =>
      knexCamelCaseResponse(result),
    asyncStackTraces: true,
    migrations: {
      extension: "ts",
      directory: `${process.cwd()}/server/db/migrations`,
    },
    seeds: {
      extension: "ts",
      directory: `${process.cwd()}/server/db/seeds`,
    },
  },
  production: {
    client: "pg",
    connection: {
      host: process.env.PRODUCTION_DB_HOST,
      database: dbName,
      port: 5432,
      user: process.env.PRODUCTION_DB_USER,
      password: process.env.PRODUCTION_DB_PASSWORD,
      ssl: true,
    },
    postProcessResponse: (result, queryContext) =>
      knexCamelCaseResponse(result),
    migrations: {
      extension: "ts",
      directory: `${process.cwd()}/server/db/migrations`,
    },
  },
  test: {
    client: "pg",
    connection: {
      host: "127.0.0.1",
      port: 5432,
      database: `${dbName}_test`,
    },
    postProcessResponse: (result, queryContext) =>
      knexCamelCaseResponse(result),
    asyncStackTraces: true,
    migrations: {
      extension: "ts",
      directory: `${process.cwd()}/server/db/migrations`,
    },
    seeds: {
      extension: "ts",
      directory: `${process.cwd()}/server/db/seeds`,
    },
  },
};

Running the require("ts-node").register() returns ReferenceError: require is not defined in ES module scope, you can use import instead.

I have also created a register.cjs file like this:

module.exports = require("ts-node");

Where I then import it into my knexconfig like this:

import tsNode from "./register.cjs";
tsNode.register();

This doesn't return an error now when I run my server, but with either approach I still get the following error:

Requiring external module ts-node/register
Unknown file extension ".ts"