Issue with Knex migration

Question

I am not sure what seems to be the issue, but this knex migration is failing. Even though I am new to writing migrations I strongly believe this migration file is correct. The error generated is as follows

migration file "20190321113401_initial.js" failed
migration failed with error: alter table "meraki"."role_permissions" add constraint "role_permissions_role_id_foreign" foreign key ("role_id") references "roles" ("id") - relation "roles" does not exist

The code is as below. Initially those migration functions were in separate files and I assumed it was failing then because the files weren't being executed synchronously, which led me to compose a single file. I am not sure if this is anyhow helpful, but when I remove the code for tables containing foreign key references(UserRoles, RolePermissions, Tokens) rest of it seems to be working.

'use strict';

exports.up = async knex => {
  // Create Schema
  await knex.raw('CREATE SCHEMA IF NOT EXISTS meraki');
  // Load Extensions
  await knex.raw('CREATE EXTENSION IF NOT EXISTS "uuid-ossp"');
  // Roles
  await knex.schema.withSchema('meraki').createTable('roles', table => {
    table
      .string('id')
      .primary()
      .defaultTo(knex.raw('uuid_generate_v4()'));
    table
      .string('name')
      .unique()
      .notNullable();
    table.string('description').notNullable();
    table.timestamps();
  });
  // Permissions
  await knex.schema.withSchema('meraki').createTable('permissions', table => {
    table
      .string('id')
      .primary()
      .defaultTo(knex.raw('uuid_generate_v4()'));
    table
      .string('name')
      .unique()
      .notNullable();
    table.timestamps();
  });
  // Role Permissions
  await knex.schema.withSchema('meraki').createTable('role_permissions', table => {
    table
      .string('role_id')
      .notNullable()
      .references('id')
      .inTable('roles');
    table
      .string('permission_id')
      .notNullable()
      .references('id')
      .inTable('permissions');
    table.timestamps();
  });
  // Users
  await knex.schema.withSchema('meraki').createTable('users', table => {
    table
      .string('id')
      .primary()
      .defaultTo(knex.raw('uuid_generate_v4()'));
    table
      .string('email', 320)
      .unique()
      .notNullable();
    table.string('first_name', 35).notNullable();
    table.string('middle_name', 35).notNullable();
    table.string('last_name', 35).notNullable();
    table.boolean('email_verified');
    table.string('verification_token');
    table.timestamps();
  });
  // User Roles
  await knex.schema.withSchema('meraki').createTable('user_roles', table => {
    table
      .string('user_id')
      .notNullable()
      .references('id')
      .inTable('users');
    table
      .string('role_id')
      .notNullable()
      .references('id')
      .inTable('roles');
    table.timestamps();
  });
  // Tokens
  await knex.schema.withSchema('meraki').createTable('tokens', table => {
    table.string('id').primary();
    table
      .string('user_id')
      .notNullable()
      .references('id')
      .inTable('users');
    table
      .bigInteger('ttl')
      .notNullable()
      .defaultTo(1209600);
    table.timestamps();
  });
};

exports.down = async knex => {
  // Tokens
  await knex.schema.withSchema('meraki').dropTableIfExists('tokens');
  // User Roles
  await knex.schema.withSchema('meraki').dropTableIfExists('user_roles');
  // Users
  await knex.schema.withSchema('meraki').dropTableIfExists('users');
  // Role Permissions
  await knex.schema.withSchema('meraki').dropTableIfExists('role_permissions');
  // Permissions
  await knex.schema.withSchema('meraki').dropTableIfExists('permissions');
  // Roles
  await knex.schema.withSchema('meraki').dropTableIfExists('roles');
  // Drop Extensions
  await knex.raw('DROP EXTENSION IF EXISTS "uuid-ossp"');
  // Delete Schema
  await knex.raw('DROP SCHEMA IF EXISTS meraki');
};

I am using postgres 10.5. Do you think there's a compatibility issue at play here? — PrivateOmega, Mar 22 '19 at 02:51
I think the problem is that PG needs to autogenerate the name of the constraint with the two column names, however you are issuing one statement in the create and the column name for the FK is not available (in the create sql knex is building) to properly name the constraint. Could you try to add the foreign key with an alter table command below your awaited creates ? So the issue is not from knex, but from PG. If it works, I will post it as an answer — Gonzalo.-, Mar 22 '19 at 02:57
But why would it not be available? I am executing these commands serially and by that the column should be available for reference right? I will try your recommendation. But you don't find any issue with my code right? — PrivateOmega, Mar 22 '19 at 03:00
See for instance [this](https://dba.stackexchange.com/questions/132029/how-to-add-a-column-with-a-foreign-key-constraint-to-a-table-that-already-exists) and [this](https://stackoverflow.com/questions/35676149/adding-a-column-as-a-foreign-key-gives-error-column-referenced-in-foreign-key-co/50681996#50681996) how the constraints are added with name, or in two separated statements (Although with the comma as syntactic sugar, run as a transaction probably) — Gonzalo.-, Mar 22 '19 at 03:00
it is available in your code, but knex, when generating SQL is not using it to name the constraint. Therefore PG will rely on autonaming, but that is not possible because the table is under creation yet. But is my guess after reading a bit a couple of answers. Might fail :^ ) — Gonzalo.-, Mar 22 '19 at 03:02
Yes, I understand but by principle, `table.string('role_id').notNullable().references('id').inTable('roles');` since these are chained functions, column role_id is to be created first and references to be created after it right? — PrivateOmega, Mar 22 '19 at 03:04
I would believe so, but not quite sure. Reviewing their [unit tests for PG](https://github.com/tgriesser/knex/blob/0115f933ca09b20bf457d1e47e3e06a9e26b0ae1/test/unit/schema/postgres.js) I see that if they use inTable, they later use `onUpdate` or `onDelete`. Very weird. Could you try the other methods, such as `table.foreign('role_id').references('id').on('roles')` ? — Gonzalo.-, Mar 22 '19 at 03:25
@Gonzalo.-Yup even I noticed that `refrences('id').on('roles')` kind of syntax. — PrivateOmega, Mar 22 '19 at 03:28
after reading the docs, I think that `references` is to be called after `foreign`. I have to go now. Also thinking the same looking at the [source code](https://github.com/tgriesser/knex/blob/232fe9f1517dba927f6a3a1fb1b8842d7c0a4007/src/schema/tablebuilder.js#L198) — Gonzalo.-, Mar 22 '19 at 03:29
@Gonzalo.-Appreciate the help, but references — column.references(column) is specified, that means it can be chained, there's no where in references it says it has to be used after foreign(), but might be a documentation mistake also. — PrivateOmega, Mar 22 '19 at 03:32
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/190473/discussion-between-kiran-mathew-mohan-and-gonzalo). — PrivateOmega, Mar 22 '19 at 03:46

Fazal Rasel · Accepted Answer · 2019-03-22T06:17:09.823

5

Do something like this-

await knex.schema.withSchema('meraki').createTable('role_permissions', table => {
    table
      .string('role_id')
      .notNullable()
      .references('id')
      .inTable('meraki.roles'); // scmema attached.
    table
      .string('permission_id')
      .notNullable()
      .references('id')
      .inTable('meraki.permissions');  // scmema attached.
    table.timestamps();
  })

edited Mar 22 '19 at 06:17

answered Mar 22 '19 at 06:10

Fazal Rasel

4,446
2
20
31

Thank yo so much, you are a life saver. It worked and extremely sorry for late reply. But shouldn't it work without explicitly specifying the schema name? – PrivateOmega Mar 28 '19 at 06:49
Just get to generated SQL by calling toString(), you will know why it will now work without schema part. – Fazal Rasel Mar 28 '19 at 06:52

Issue with Knex migration

1 Answers1