Can't execute CLI migrations using ESM typescript #4793

rubenferreira97 · 2021-11-02T14:10:11Z

Environment

Knex version: ^0.95.12
Database + version: Mysql 8.0.26
OS: Windows 10

Bug

Explain what kind of behaviour you are getting and how you think it should do
I am trying to do typescript migrations using the ESM. Using "type": "module" on package.json with module ESNext (project requirements).
I would expect a successful migration without errors.
Error message

TypeError [ERR_UNKNOWN_FILE_EXTENSION]: Unknown file extension ".ts" for C:\Users\User\Desktop\project\src\database\knexfile.ts
    at new NodeError (node:internal/errors:371:5)
    at Object.file: (node:internal/modules/esm/get_format:72:15)
    at defaultGetFormat (node:internal/modules/esm/get_format:85:38)
    at defaultLoad (node:internal/modules/esm/load:13:42)
    at ESMLoader.load (node:internal/modules/esm/loader:303:26)
    at ESMLoader.moduleProvider (node:internal/modules/esm/loader:230:58)
    at new ModuleJob (node:internal/modules/esm/module_job:63:26)
    at ESMLoader.getModuleJob (node:internal/modules/esm/loader:244:11)
    at async Promise.all (index 0)
    at async ESMLoader.import (node:internal/modules/esm/loader:281:24) {
  code: 'ERR_UNKNOWN_FILE_EXTENSION'

Reduced test code

package.json

{
  "main": "index.js",
  "type": "module",
  "scripts": {
    "build": "tsc -p tsconfig.json",
    "start": "node index.js",
    "start-dev": "node -r dotenv/config build/index.js",
    "knex:migrate:make": "knex --knexfile src/database/knexfile.ts migrate:make -x ts",
    "knex:migrate:latest": "knex --knexfile src/database/knexfile.ts migrate:latest",
    "knex:migrate:rollback": "knex --knexfile src/database/knexfile.ts migrate:rollback",
  },
  "dependencies": {
    "fastify": "^3.22.1",
    "knex": "^0.95.12",
    "mysql2": "^2.3.2",
    "ts-node": "^10.4.0"
  },
  "devDependencies": {
    "@types/node": "^16.11.6",
    "@typescript-eslint/eslint-plugin": "^4.29.3",
    "@typescript-eslint/parser": "^4.29.3",
    "dotenv": "^10.0.0",
    "eslint": "^7.32.0",
    "eslint-config-airbnb-base": "^14.2.1",
    "eslint-config-airbnb-typescript": "^14.0.1",
    "eslint-plugin-import": "^2.25.2",
    "typescript": "^4.4.4"
  }
}

tsconfig.json

{
  "compilerOptions": {
    "lib": ["esnext"],
    "module": "esnext",
    "target": "esnext",
    "moduleResolution": "node",
    "strict": true,
    "esModuleInterop": true,
    "skipLibCheck": true,
    "resolveJsonModule": true,
    "forceConsistentCasingInFileNames": true,
    "outDir": "build",
  },
  "include": [
    "src/**/*.ts",
    "package.json"
  ],
  "exclude": [
    "node_modules",
    "build"
  ],
}

knexfile.ts

import { Knex } from 'knex';

const config: Knex.Config = {
  client: 'mysql2',
  connection: {
    host: '127.0.0.1',
    port: 3306,
    user: 'root',
    password: '',
    database: 'databasename',
  },
  pool: { min: 2, max: 10 },
  migrations: {
    tableName: 'knex_migrations',
  },
};

export default config;

The text was updated successfully, but these errors were encountered:

tamtamchik · 2022-02-01T13:23:21Z

Same here. Tried to hunt it down and got to this issue gulpjs/rechoir#43

nmsobri · 2022-05-17T07:42:19Z

so is there any solution to this?

schneefux · 2022-05-24T16:14:13Z

Run node with the ts-node/esm loader, either via exporting NODE_OPTIONS="--loader ts-node/esm" or by passing the argument directly to the node executable:

node --loader ts-node/esm ./node_modules/.bin/knex -x ts migrate:make example_migration

sliterok · 2022-10-25T11:47:16Z

I'm getting this error when I try to run node --loader ts-node/esm ./node_modules/.bin/knex migrate:latest from package.json

jmsunseri · 2023-02-14T09:10:56Z

any library with .js is trash for typescript support apparently

Naddiseo · 2023-02-14T15:55:54Z

I managed to get typescript migrations working under esm:

Excerpt from package.json:

{
   "type": "module",
   "scripts": {
      "migrate:latest": "NODE_OPTIONS='--loader ts-node/esm' yarn run knex migrate:latest",
   },
   "devDependencies": {
      "ts-node": "^10.9.1",
     "typescript": "^4.9.5"
   }
}

excerpt from tsconfig.json:

{
   "compilerOptions": {
       "module": "esnext",
    "moduleResolution": "node",
    "exclude": ["node_modules"],
  "ts-node": {
    "experimentalSpecifierResolution": "node",
    "require": ["dotenv/config"],
    "typeCheck": false,
    "files": true,
    "esm": true,
    "swc": true,
    "ignore": [
      "(?:^|/)node_modules/",
      "(?:^|/)\\.next/",
      ".yarn/*",
    ]
  }
}

excerpt from knexfile.js

const commonConf = {
  client: "pg",
    migrations: {
    tableName: "knex_migrations",
    directory: "./migrations",
    loadExtensions: ['.js', '.ts']
  },
  pool: {
    min: 1,
    max: 1,
  },
};
export default commonConf;

$ cat migrations/20230214154135_hello.ts

import { Knex } from "knex";


export async function up(knex: Knex): Promise<void> {
  console.log("HELLO");
  throw new Error("HELLO");
}


export async function down(knex: Knex): Promise<void> {
}

$ node --version
v16.18.1
$ yarn --version
3.4.1
$ yarn migrate:latest
(node:810828) ExperimentalWarning: Custom ESM Loaders is an experimental feature. This feature could change at any time
(Use `node --trace-warnings ...` to show where the warning was created)
(node:810856) ExperimentalWarning: Custom ESM Loaders is an experimental feature. This feature could change at any time
(Use `node --trace-warnings ...` to show where the warning was created)
HELLO
migration file "20230214154135_hello.ts" failed
migration failed with error: HELLO
HELLO
Error: HELLO
    at Module.up (file:///migrations/20230214154135_hello.ts:6:9)
    at /node_modules/knex/lib/migrations/migrate/Migrator.js:519:40
    at processTicksAndRejections (node:internal/process/task_queues:96:5)
    at async Migrator._runBatch (/node_modules/knex/lib/migrations/migrate/Migrator.js:381:19)

beenotung · 2024-01-04T03:26:43Z

My workaround to use knex from cli is to use custom knex cli wrapper with self-defined knex instance.

This custom cli can be invoked using tsx, e.g. npx tsx knex-cli.ts migrate:status

You can also setup a knex script in package.json:

{
  "scripts": {
    "knex": "tsx knex-cli.ts"
  }
}

Then you replace typical call to knex from npx to npm run, e.g. npm run knex migrate:status

(In my dev machine, I setup nr as alias to npm run so it's even shorter than calling knex via npx)

The knex-cli.ts:

import { knex } from './db.js'

async function main() {
  let args = process.argv.slice(2)
  if (args.length == 0) {
    console.error('Error: missing knex command in argument')
    return
  }
  type MigrationResult = [batch: number, migrations: string[]]
  function showMigrationResult(
    label: string,
    [batch, migrations]: MigrationResult,
  ) {
    if (migrations.length === 0) {
      console.log('No migrations affected.')
      return
    }
    console.log(label, 'batch:', batch)
    console.log('migrations:')
    console.log(migrations.map(s => '- ' + s).join('\n'))
  }
  switch (args[0]) {
    case 'migrate:up': {
      showMigrationResult('migrate:up', await knex.migrate.up())
      break
    }
    case 'migrate:down': {
      showMigrationResult('migrate:down', await knex.migrate.down())
      break
    }
    case 'migrate:latest': {
      showMigrationResult('migrate:latest', await knex.migrate.latest())
      break
    }
    case 'migrate:rollback': {
      let config = undefined
      let all = args[1] === '--all'
      showMigrationResult('rollback', await knex.migrate.rollback(config, all))
      break
    }
    case 'migrate:status': {
      type Result = [{ name: string }[], { file: string }[]]
      let [done, pending] = (await knex.migrate.list()) as Result

      console.log(done.length, 'applied migrations')
      for (let each of done) {
        console.log('- ' + each.name)
      }

      console.log(pending.length, 'pending migrations')
      for (let each of pending) {
        console.log('- ' + each.file)
      }
      break
    }
    default: {
      console.error('Error: unknown arguments:', args)
    }
  }
}
main()
  .catch(e => console.error(e))
  .then(() => knex.destroy())

kevinforrestconnors · 2024-03-14T20:30:12Z

My workaround to use knex from cli is to use custom knex cli wrapper with self-defined knex instance.

This custom cli can be invoked using tsx, e.g. npx tsx knex-cli.ts migrate:status

You can also setup a knex script in package.json:

{
  "scripts": {
    "knex": "tsx knex-cli.ts"
  }
}

Then you replace typical call to knex from npx to npm run, e.g. npm run knex migrate:status

(In my dev machine, I setup nr as alias to npm run so it's even shorter than calling knex via npx)

The knex-cli.ts:

import { knex } from './db.js'

async function main() {
  let args = process.argv.slice(2)
  if (args.length == 0) {
    console.error('Error: missing knex command in argument')
    return
  }
  type MigrationResult = [batch: number, migrations: string[]]
  function showMigrationResult(
    label: string,
    [batch, migrations]: MigrationResult,
  ) {
    if (migrations.length === 0) {
      console.log('No migrations affected.')
      return
    }
    console.log(label, 'batch:', batch)
    console.log('migrations:')
    console.log(migrations.map(s => '- ' + s).join('\n'))
  }
  switch (args[0]) {
    case 'migrate:up': {
      showMigrationResult('migrate:up', await knex.migrate.up())
      break
    }
    case 'migrate:down': {
      showMigrationResult('migrate:down', await knex.migrate.down())
      break
    }
    case 'migrate:latest': {
      showMigrationResult('migrate:latest', await knex.migrate.latest())
      break
    }
    case 'migrate:rollback': {
      let config = undefined
      let all = args[1] === '--all'
      showMigrationResult('rollback', await knex.migrate.rollback(config, all))
      break
    }
    case 'migrate:status': {
      type Result = [{ name: string }[], { file: string }[]]
      let [done, pending] = (await knex.migrate.list()) as Result

      console.log(done.length, 'applied migrations')
      for (let each of done) {
        console.log('- ' + each.name)
      }

      console.log(pending.length, 'pending migrations')
      for (let each of pending) {
        console.log('- ' + each.file)
      }
      break
    }
    default: {
      console.error('Error: unknown arguments:', args)
    }
  }
}
main()
  .catch(e => console.error(e))
  .then(() => knex.destroy())

What is './db.js'? I am getting these errors

TypeError: Cannot read properties of undefined (reading 'up')

TypeError: knex.destroy is not a function

joshkay · 2024-03-15T13:43:35Z

@beenotung awesome work!

Here's the same script with added support for migrate:make

import knex from '@server/db/knex';

type MigrationResult = [batch: number, migrations: string[]];
function showMigrationResult(
  label: string,
  [batch, migrations]: MigrationResult,
) {
  if (migrations.length === 0) {
    console.log('No migrations affected.');
    return;
  }
  console.log(label, 'batch:', batch);
  console.log('migrations:');
  console.log(migrations.map((s) => '- ' + s).join('\n'));
}

async function main() {
  const args = process.argv.slice(2);
  if (args.length == 0) {
    console.error('Error: missing knex command in argument');
    return;
  }

  switch (args[0]) {
    case 'migrate:up': {
      showMigrationResult('migrate:up', await knex.migrate.up());
      break;
    }
    case 'migrate:down': {
      showMigrationResult('migrate:down', await knex.migrate.down());
      break;
    }
    case 'migrate:latest': {
      showMigrationResult('migrate:latest', await knex.migrate.latest());
      break;
    }
    case 'migrate:rollback': {
      const config = undefined;
      const all = args[1] === '--all';
      showMigrationResult('rollback', await knex.migrate.rollback(config, all));
      break;
    }
    case 'migrate:status': {
      type Result = [{ name: string }[], { file: string }[]];
      const [done, pending] = (await knex.migrate.list()) as Result;

      console.log(done.length, 'applied migrations');
      for (const each of done) {
        console.log('- ' + each.name);
      }

      console.log(pending.length, 'pending migrations');
      for (const each of pending) {
        console.log('- ' + each.file);
      }
      break;
    }
    case 'migrate:make': {
      const result = await knex.migrate.make(args[1]);
      console.log(result);
      break;
    }
    default: {
      console.error('Error: unknown arguments:', args);
    }
  }
}
main()
  .catch((e) => console.error(e))
  .then(() => knex.destroy());

joshkay · 2024-03-15T13:45:35Z

My workaround to use knex from cli is to use custom knex cli wrapper with self-defined knex instance.
This custom cli can be invoked using tsx, e.g. npx tsx knex-cli.ts migrate:status
You can also setup a knex script in package.json:

{
  "scripts": {
    "knex": "tsx knex-cli.ts"
  }
}

Then you replace typical call to knex from npx to npm run, e.g. npm run knex migrate:status
(In my dev machine, I setup nr as alias to npm run so it's even shorter than calling knex via npx)
The knex-cli.ts:

import { knex } from './db.js'

async function main() {
  let args = process.argv.slice(2)
  if (args.length == 0) {
    console.error('Error: missing knex command in argument')
    return
  }
  type MigrationResult = [batch: number, migrations: string[]]
  function showMigrationResult(
    label: string,
    [batch, migrations]: MigrationResult,
  ) {
    if (migrations.length === 0) {
      console.log('No migrations affected.')
      return
    }
    console.log(label, 'batch:', batch)
    console.log('migrations:')
    console.log(migrations.map(s => '- ' + s).join('\n'))
  }
  switch (args[0]) {
    case 'migrate:up': {
      showMigrationResult('migrate:up', await knex.migrate.up())
      break
    }
    case 'migrate:down': {
      showMigrationResult('migrate:down', await knex.migrate.down())
      break
    }
    case 'migrate:latest': {
      showMigrationResult('migrate:latest', await knex.migrate.latest())
      break
    }
    case 'migrate:rollback': {
      let config = undefined
      let all = args[1] === '--all'
      showMigrationResult('rollback', await knex.migrate.rollback(config, all))
      break
    }
    case 'migrate:status': {
      type Result = [{ name: string }[], { file: string }[]]
      let [done, pending] = (await knex.migrate.list()) as Result

      console.log(done.length, 'applied migrations')
      for (let each of done) {
        console.log('- ' + each.name)
      }

      console.log(pending.length, 'pending migrations')
      for (let each of pending) {
        console.log('- ' + each.file)
      }
      break
    }
    default: {
      console.error('Error: unknown arguments:', args)
    }
  }
}
main()
  .catch(e => console.error(e))
  .then(() => knex.destroy())

What is './db.js'? I am getting these errors

TypeError: Cannot read properties of undefined (reading 'up')

TypeError: knex.destroy is not a function

The db.js file is just a file that exports the knex config.

Here's mine for example:

import { sqlConfig } from '@server/config/env';
import knex from 'knex';

export default knex({
  client: 'mssql',
  connection: {
    server: sqlConfig.server,
    database: sqlConfig.database,
    user: sqlConfig.user,
    password: sqlConfig.password,
    port: sqlConfig.port,
    options: {
      encrypt: false,
      enableArithAbort: true,
    },
    pool: {
      max: 100,
      min: 5,
      idleTimeoutMillis: 30000,
    },
    requestTimeout: 6000000,
  },
  pool: { min: 0, max: 100 },
});

kevinforrestconnors · 2024-03-15T18:37:31Z

My workaround to use knex from cli is to use custom knex cli wrapper with self-defined knex instance.
This custom cli can be invoked using tsx, e.g. npx tsx knex-cli.ts migrate:status
You can also setup a knex script in package.json:

{
  "scripts": {
    "knex": "tsx knex-cli.ts"
  }
}

Then you replace typical call to knex from npx to npm run, e.g. npm run knex migrate:status
(In my dev machine, I setup nr as alias to npm run so it's even shorter than calling knex via npx)
The knex-cli.ts:

import { knex } from './db.js'

async function main() {
  let args = process.argv.slice(2)
  if (args.length == 0) {
    console.error('Error: missing knex command in argument')
    return
  }
  type MigrationResult = [batch: number, migrations: string[]]
  function showMigrationResult(
    label: string,
    [batch, migrations]: MigrationResult,
  ) {
    if (migrations.length === 0) {
      console.log('No migrations affected.')
      return
    }
    console.log(label, 'batch:', batch)
    console.log('migrations:')
    console.log(migrations.map(s => '- ' + s).join('\n'))
  }
  switch (args[0]) {
    case 'migrate:up': {
      showMigrationResult('migrate:up', await knex.migrate.up())
      break
    }
    case 'migrate:down': {
      showMigrationResult('migrate:down', await knex.migrate.down())
      break
    }
    case 'migrate:latest': {
      showMigrationResult('migrate:latest', await knex.migrate.latest())
      break
    }
    case 'migrate:rollback': {
      let config = undefined
      let all = args[1] === '--all'
      showMigrationResult('rollback', await knex.migrate.rollback(config, all))
      break
    }
    case 'migrate:status': {
      type Result = [{ name: string }[], { file: string }[]]
      let [done, pending] = (await knex.migrate.list()) as Result

      console.log(done.length, 'applied migrations')
      for (let each of done) {
        console.log('- ' + each.name)
      }

      console.log(pending.length, 'pending migrations')
      for (let each of pending) {
        console.log('- ' + each.file)
      }
      break
    }
    default: {
      console.error('Error: unknown arguments:', args)
    }
  }
}
main()
  .catch(e => console.error(e))
  .then(() => knex.destroy())

What is './db.js'? I am getting these errors

TypeError: Cannot read properties of undefined (reading 'up')

TypeError: knex.destroy is not a function

The db.js file is just a file that exports the knex config.

Here's mine for example:

import { sqlConfig } from '@server/config/env';
import knex from 'knex';

export default knex({
  client: 'mssql',
  connection: {
    server: sqlConfig.server,
    database: sqlConfig.database,
    user: sqlConfig.user,
    password: sqlConfig.password,
    port: sqlConfig.port,
    options: {
      encrypt: false,
      enableArithAbort: true,
    },
    pool: {
      max: 100,
      min: 5,
      idleTimeoutMillis: 30000,
    },
    requestTimeout: 6000000,
  },
  pool: { min: 0, max: 100 },
});

Thanks, I got it working finally - I had to dynamically import the db file since I'm using dotenv to get a different database file depending on environment - was missing a .default after the import. Thanks to you and @beenotung ! This has been plaguing me.

feedmypixel · 2024-04-02T18:24:47Z

Ended up making an npm script based on answers above:

  "db:migrate": "node --import tsx/esm ./node_modules/.bin/knex migrate:make -x ts",

Naddiseo mentioned this issue Mar 29, 2023

Migrations that use ESM format can't run via the migrations API #5323

Open

er-lim mentioned this issue Jun 21, 2023

[FEATURE] Créer une table pour la journalisation (PIX-8185) 1024pix/pix-audit-logger#1

Merged

mattdean-digicatapult mentioned this issue Jan 5, 2024

Chore/upgrade chai digicatapult/sqnc-hyproof-api#65

Closed

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Can't execute CLI migrations using ESM typescript #4793

Can't execute CLI migrations using ESM typescript #4793

rubenferreira97 commented Nov 2, 2021

tamtamchik commented Feb 1, 2022

nmsobri commented May 17, 2022

schneefux commented May 24, 2022

sliterok commented Oct 25, 2022

jmsunseri commented Feb 14, 2023

Naddiseo commented Feb 14, 2023

beenotung commented Jan 4, 2024 •

edited

kevinforrestconnors commented Mar 14, 2024

joshkay commented Mar 15, 2024

joshkay commented Mar 15, 2024

kevinforrestconnors commented Mar 15, 2024

feedmypixel commented Apr 2, 2024

Can't execute CLI migrations using ESM typescript #4793

Can't execute CLI migrations using ESM typescript #4793

Comments

rubenferreira97 commented Nov 2, 2021

Environment

Bug

tamtamchik commented Feb 1, 2022

nmsobri commented May 17, 2022

schneefux commented May 24, 2022

sliterok commented Oct 25, 2022

jmsunseri commented Feb 14, 2023

Naddiseo commented Feb 14, 2023

beenotung commented Jan 4, 2024 • edited

kevinforrestconnors commented Mar 14, 2024

joshkay commented Mar 15, 2024

joshkay commented Mar 15, 2024

kevinforrestconnors commented Mar 15, 2024

feedmypixel commented Apr 2, 2024

beenotung commented Jan 4, 2024 •

edited