Knex cannot run migrations because required configuration option "client" is missing

Question

I'm evaluating Node.js 14.x for a new project. I'm testing Knex.js and Objection.js to interact with databases so I'm building a proof-of-concept project, but I'm running into some issues when trying to run the migrations (using npx knex --esm migrate:latest):

[x08486@uplink:~/Workshop/Development/node-express]$ npx knex --esm migrate:latest 
Error: knex: Required configuration option 'client' is missing.
    at new Client (/home/x08486/Workshop/Development/node-express/node_modules/knex/lib/client.js:54:11)
    at Knex (/home/x08486/Workshop/Development/node-express/node_modules/knex/lib/knex.js:14:28)
    at initKnex (/home/x08486/Workshop/Development/node-express/node_modules/knex/bin/cli.js:56:10)
    at processTicksAndRejections (internal/process/task_queues.js:93:5)
    at async Command.<anonymous> (/home/x08486/Workshop/Development/node-express/node_modules/knex/bin/cli.js:170:26)

I can infer that indeed the knex client might be missing, but I don't really understand that because it's imported in knexfile.js. I suspect this might have something to do with the fact that I'm using ES6, but not entirely sure.

This is (an excerpt of) my package.json file:

{
  "type": "module",
  "scripts": {
    "start": "node src/main.js"
  },
  "dependencies": {
    "dotenv": "8.2.0",
    "express": "4.17.1",
    "knex": "0.21.18",
    "objection": "2.2.14",
    "sqlite3": "5.0.2"
  },
  "engines": {
    "node": "14.x"
  },
}

...then the knexfile.js (at the project's root also):

import dotenv from "dotenv";
import knex from "knex";
import path from "path";

dotenv.config();

const config = {
  // ...there are some more, but I'm using "sandbox" via dotenv

  sandbox: {
    client: "sqlite3",
    connection: {
      filename: "./sandbox.sqlite3", // ...or ":memory:"
    },
    debug: true,
    migrations: {
      directory: path.join(__dirname, "migrations"),
      tableName: "knex_schema_history",
    },
    seeds: {
      directory: path.join(__dirname, "seeds", "sandbox"),
    },
    useNullAsDefault: true,
  },
};

const database = knex(config[process.env.NODE_ENV]);

export { database };

And finally the migrations/ sub-directory (at the project's root) with this content:

// 20210302134908_init.js

export function down(knex) {
  return Promise.all([knex.schema.dropTable("customers")]);
}

export function up(knex) {
  return Promise.all([
    knex.schema.createTable("customers", function (table) {
      // https://knexjs.org/#Schema-Building
      table.uuid("id").notNullable().primary();
      table.string("first_name", 50).notNullable();
      table.string("last_name", 50).notNullable();
      table.string("email", 100).index().notNullable();
      // table.timestamp("created_at").defaultTo(knex.fn.now());
      // table.timestamp("updated_at").defaultTo(knex.fn.now());
      table.timestamps(false, true);
    }),
  ]);
}

Any clues what's going on here?

score 3 · Accepted Answer · answered Mar 03 '21 at 01:13

After a couple of hours going back and forth, it works by changing the knexfile.js in this way:

import dotenv from "dotenv";
import path from "path";

dotenv.config();

const config = {
   // ...configurations as usual for any database/profile
};

export default config; // MUST be the default export!!!

[x80486@uplink:~/Workshop/Development/node-express]$ npx knex --esm migrate:latest 
Using environment: sandbox
[
  {
    sql: "select * from sqlite_master where type = 'table' and name = ?",
    output: [Function: output],
    bindings: [ 'knex_schema_history' ]
  }
]
...
[
  {
    sql: 'create table `customers` (`id` char(36) not null, `first_name` varchar(50) not null, `last_name` varchar(50) not null, `email` varchar(100) not null, `created_at` datetime not null default CURRENT_TIMESTAMP, `updated_at` datetime not null default CURRENT_TIMESTAMP, primary key (`id`))',
    bindings: []
  },
  {
    sql: 'create index `customers_email_index` on `customers` (`email`)',
    bindings: []
  }
]
{
  method: 'insert',
  options: {},
  timeout: false,
  cancelOnTimeout: false,
  bindings: [ 1, 2021-03-03T01:04:00.988Z, '20210302134908_init.js' ],
  __knexQueryUid: 'lO1x3i-L3FBDofzFXN8tB',
  sql: 'insert into `knex_schema_history` (`batch`, `migration_time`, `name`) values (?, ?, ?)'
}
{
  method: 'update',
  options: {},
  timeout: false,
  cancelOnTimeout: false,
  bindings: [ 0 ],
  __knexQueryUid: '1PDbw3WTPPukvhtfjxWYt',
  sql: 'update `knex_schema_history_lock` set `is_locked` = ?'
}
Batch 1 run: 1 migrations

I just spent a whole day trying to migrate a database, it was just a freaking `export default`. Thanks! — Chubby Cows, Apr 21 '23 at 22:00

score 1 · Answer 2 · answered May 31 '22 at 14:38

Just in case you're using typescript and you have similar challenge. I had a challenge of migration file creation: knex migrate:make create_users_table -x ts. A little adjustment to the OP's answer helped me.

//knexfile.ts same directory as .env

import type { Knex } from "knex";
//I didn't import dotenv. it was already imported in src/config/index.
//and my knex instance has been set to use `development` as 
//env
import envVariables from "./src/config/index";
const {dbClient, dbUser, dbPassword, dbDatabase, dbDatabaseTest} = 
envVariables;

const config: { [key: string]: Knex.Config } = {
 //Config as usual...
}

export default config;



//src/config/index
import 'dotenv/config';

export default {
  port: process.env.APP_PORT,
  appName: process.env.APP_NAME,
  appEnv: process.env.APP_ENV || "development",
  appKey: process.env.APP_KEY,
  appUrl: process.env.APP_URL,
  dbClient: process.env.DB_CLIENT,
  dbUser: process.env.DB_USER,
  dbPassword: process.env.DB_PASSWORD,
  dbDatabase: process.env.DB_DATABASE,
  dbDatabaseTest: process.env.DB_DATABASE_TEST,
};

Knex cannot run migrations because required configuration option "client" is missing

2 Answers2