Found a work around here: https://github.com/knex/knex/issues/3849#issuecomment-643411244

in my package.json added this line, then executed whatever command I want:

scripts: {
  ...
  "api-knex": "TS_NODE_COMPILER_OPTIONS='{ \"module\": \"commonjs\" }' knex --knexfile=./path/to/knexfile.ts"
...

and run a command in command-line like:

npm run api-knex migrate:up

Note: My problem was that esnext is not supported for the knex cli

What is the recommended workflow for running Typescript migrations? The documentation still suggests that running knex migrate:latest is sufficient:

Once you have a knexfile.js, you can use the migration tool to create migration files to the specified directory (default migrations). Creating new migration files can be achieved by running:

$ knex migrate:make migration_name 

# or for .ts

$ knex migrate:make migration_name -x ts

...

Once you have finished writing the migrations, you can update the database matching your NODE_ENV by running:

$ knex migrate:latest

See:

root@1e4d151bd246:/srv/app# node --version
v12.18.2
root@1e4d151bd246:/srv/app# npm run knex -- --version

> backend@0.1.0 knex /srv/app
> knex "--version"

Failed to load external module ts-node/register
Failed to load external module typescript-node/register
Failed to load external module typescript-register
Failed to load external module typescript-require
Failed to load external module sucrase/register/ts
Failed to load external module @babel/register
Knex CLI version: 0.19.5
Knex Local version: 0.19.5
root@1e4d151bd246:/srv/app#
root@1e4d151bd246:/srv/app#
root@1e4d151bd246:/srv/app#
root@1e4d151bd246:/srv/app# npm run knex -- migrate:make initial_migration -x ts

> backend@0.1.0 knex /srv/app
> knex "migrate:make" "initial_migration" "-x" "ts"

Failed to load external module ts-node/register
Failed to load external module typescript-node/register
Failed to load external module typescript-register
Failed to load external module typescript-require
Failed to load external module sucrase/register/ts
Failed to load external module @babel/register
Using environment: development
Using environment: development
Using environment: development
Created Migration: /srv/app/migrations/20200722212137_initial_migration.ts
root@1e4d151bd246:/srv/app# npm run knex -- migrate:latest

> backend@0.1.0 knex /srv/app
> knex "migrate:latest"

Failed to load external module ts-node/register
Failed to load external module typescript-node/register
Failed to load external module typescript-register
Failed to load external module typescript-require
Failed to load external module sucrase/register/ts
Failed to load external module @babel/register
Using environment: development
/srv/app/migrations/20200722212137_initial_migration.ts:1
import * as Knex from "knex";
^^^^^^

SyntaxError: Cannot use import statement outside a module
    at wrapSafe (internal/modules/cjs/loader.js:1054:16)
    at Module._compile (internal/modules/cjs/loader.js:1102:27)
    at Object.Module._extensions..js (internal/modules/cjs/loader.js:1158:10)
    at Module.load (internal/modules/cjs/loader.js:986:32)
    at Function.Module._load (internal/modules/cjs/loader.js:879:14)
    at Module.require (internal/modules/cjs/loader.js:1026:19)
    at require (internal/modules/cjs/helpers.js:72:18)
    at FsMigrations.getMigration (/srv/app/node_modules/knex/lib/migrate/sources/fs-migrations.js:84:12)
    at /srv/app/node_modules/knex/lib/migrate/Migrator.js:82:69
    at arrayFilter (/srv/app/node_modules/lodash/lodash.js:582:11)
    at filter (/srv/app/node_modules/lodash/lodash.js:9173:14)
    at /srv/app/node_modules/knex/lib/migrate/Migrator.js:81:13
    at tryCatcher (/srv/app/node_modules/bluebird/js/release/util.js:16:23)
    at Promise._settlePromiseFromHandler (/srv/app/node_modules/bluebird/js/release/promise.js:547:31)
    at Promise._settlePromise (/srv/app/node_modules/bluebird/js/release/promise.js:604:18)
    at Promise._settlePromise0 (/srv/app/node_modules/bluebird/js/release/promise.js:649:10)

Workaround that worked for me using ts-node:

ts-node ./node_modules/.bin/knex migrate:make --knexfile knexfile.ts -x ts <migration_name>

This successfully creates the migration file with a .ts extension.

As for running the migration, I still run them using the transpiled .js files via npm run scripts:

"db:migrate:latest": "cd build && env DOTENV_CONFIG_PATH=../.env node --require dotenv/config ../node_modules/.bin/knex migrate:latest",
"db:migrate:rollback": "cd build && env DOTENV_CONFIG_PATH=../.env node --require dotenv/config ../node_modules/.bin/knex migrate:rollback"

I tried running the migrations as .ts files before but I encountered weird runtime transpilation issues when running on deployed containers (not enough memory?).

If this is truly the desired behavior, I think it is imperative that the docs elaborate on exactly how to get TypeScript working with ts-node. The docs don’t make a single mention of ts-node.

I use env-cmd to load env vars from script "migrate": "env-cmd knex migrate:make -x ts"

I think there are two things to be done here:

1. We need to document what is needed to interpret knexfile (or other files which knex imports) written in typescript - which is basically one of these modules.

2. Currently the utilities for inferring extension for migrations/seeds to be generated are not aware of knexfile extension where as IMHO they should default to knexfile extension in absense of explicit extension specified somewhere in config.

npm install ts-node worked for me

remove “include” from “compilerOptions” in tsconfig.ts

Yea I am not a whiz with npm, guessing the argument is somehow not getting passed. Maybe you can try hardcoding the command in the package.json to see if it works.

https://stackoverflow.com/questions/40910864/cannot-find-module-ts-node-register

I think this solves the issue

No, this does not solve issue. I have ts-node installed locally and the issue still persists.

same error with ts-node:

╰─ npx ts-node ../../../../node_modules/knex/bin/cli  migrate:latest
Using environment: development
/Users/jowparks/Projects/reflect/apps/api/src/app/migrations/20200816085511_add_questions.ts:1
import { __awaiter } from "tslib";
^^^^^^

SyntaxError: Cannot use import statement outside a module
...