ObjectionJS model used in knex migration reports "relation does not exist" when running batch of migrations

Question

When running batch of knex migrations, either through the API or via the CLI, the migrations might fail if they use ObjectionJS models. This can happen particularly in the case where the knexfile itself is resolved as an asynchronous function.

Setup

To explain this better, here is an example:

database.js

// This file stores logic responsible for providing credentials.

async function getKnexfile() {
  // Some asynchronous behaviour that returns valid configuration.
  // A good use case for this can be retrieving a secret stored in AWS Secrets Manager
  // and passing it to the connection string part of the config.
  // 
  // For this example, let's assume the following is returned:
  return {
    client: 'pg',
    connectionString: 'pg://user:password@host:5432/database'
  };
}

module.exports = { getKnexfile };

knexfile.js

module.exports = require('./database').getKnexfile();

Now let's consider two migration files that will be ran concurrently.

001_build_schema.js

exports.up = async (knex) => {
  await knex.schema.createTable('mytable', (table) => {
    table.string('id').unique().notNullable().primary();
    table.string('text', 45);
  });
}

exports.down = async (knex) => {
  await knex.schema.dropTable('mytable');
}

And in the second migration file, we begin by importing one of the models. I'm not providing the complete source for that model because ultimately, the way it is defined does not really matter for this example. The important part however, is that (in my case) this model was making use of several plugins, such as knexSnakeCaseMappers(), which together with the fact that my configuration was fetched asynchronously required some creative coding. The partial source for that model will be defined at the end.

002_insert_data.js

const MyModel = require('./MyModel');

exports.up = async (knex) => {
  await MyModel.query().insert({text: 'My Text'});
}

exports.down = async (knex) => {
  // Do nothing, this part is irrelevant...
}

The problem

What does not work, is running the two migrations as a batch. This means that triggering the batch of migrations (i.e. via CLI), causes them to fail like so:

# We are currently at the base migration (i.e. migrations were not ran yet).

knex migrate:latest

The above will result in the following error:

migration file "002_insert_data.js" failed

migration failed with error: insert into "mytable" ("text") values ($1) returning "id" - relation "mytable" does not exist

DBError: insert into "mytable" ("text") values ($1) returning "id" - relation "mytable" does not exist

This seemed like the migrations were not being awaited (i.e. migration 002 was running before migration 001 has finished), but experimenting with it has shown that this was not the case. Or at least, the problem was not as simple as the migrations not running one after another, since using simple console.log statements have shown that these files were in fact executed concurrently.

Moreover, running the migrations one by one (i.e. not in a batch) using script similar to the following would result in successful migrations and the data would be populated in the database appropriately:

knex migrate:up && knex migrate:up

Having made sure that the schema used was identical across the board (setting .withSchema('schema_name')), I figured out that the issue must have been related to the migrations being run in transactions, but using flag disableTransactions: true has proven to be a poor solution, since in case of a crash, the database would be left in an unknown state.

Here is the partial source for MyModel.js

const { Model, knexSnakeCaseMappers, snakeCaseMappers } = require('objection');

// The below line imports an async function that returns the connection string. This is
// needed as knex() expects the provided argument to be an object, and accepts async function
// only for the connection field (which is why previously defined getKnexfile cannot be used).
const getConnectionStringAsync = require('./database');

const db = knex({
  client: 'pg',
  connection: knexfile.getConnectionString,
  ...knexSnakeCaseMappers(),
});

Model.knex(db);


module.exports = class MyModel extends Model {
  // The implementation of the model goes here...
  // The table name of this model is set to `mytable`.
}

Answer 1

I have managed to solve the problem by realising two things:

1. The migrations are ran in transactions, which suggests that the actual knex object used to communicate with the database is shared across migrations and is the same. It therefore matters which knex object is used.
2. My setup with asynchronous configuration fetching resulted in multiple connections when running migrations that make use of the models, because models would initialise their own connections.

From there, the solution was pretty obvious: use the same knex object across all the models and migration commands. This could be achieved in a relatively easy manner, by tweaking the migration files that use models in a following way:

002_insert_data.js

// Import the model as previously (name can be changed for clarity).
const MyModelUnbound = require('./MyModel');


exports.up = async (knex) => {
  // Bind the existing knex connection to the model.
  const MyModel = MyModelUnbound.bindKnex(knex);

  await MyModel.query().insert({text: 'My Text'});
}

// ...

It's important to note, that the above code sets the knexfile configuration in the model, adding the knexSnakeCaseMapper plugin, which will not be applied to the knex configuration generated by the getKnexfile() function. This could be fixed by moving that configuration to the getKnexfile() method (or in case where the API is used, duplicating that definition in the knexfile configuration in that place).

This has fixed my issue completely and now running the migrations in batches works fine. One thing I am still not entirely sure about is why the initial behaviour actually takes place. The way I imagined the transactions working was on a migration basis (i.e. 1 migration = 1 transaction), which would suggest that things should work one way or another.

My current theory is that there might be some race condition for when the first migration's transaction is completed, and when the next connection is established for the models in second migration. Either way, binding the original knex object (built during the call to migrations API or CLI) solves the problem.

Answer 2

Taking in consideration Marceli's reply, you can as well bind transaction directly in the query like:

exports.up = async (knex) => {
  await MyModel.query(knex).insert({text: 'My Text'});
}

this way works better if you have joins in your model