knex.js migrations create tables with relationship between each other fails

Question

I've looked all over the place, there are quite a few examples, but nothing complete or working.

Use case: Simple database structure, a few tables and some relationships. Set up node, knex and pg - in a docker container. All works well. Created the migration file for the first table (table A) - ok. Added the second table (table B) and a 1:n relationship from table B to A. All good. Added a 1:n relationship from table A to table B. And the script errored out.

table info:

exports.up = function(knex) {
  return knex.schema
    .createTable('user', t => {
      t.uuid('user_id').primary()
      t.string('name', 100).notNullable()
      t.string('surname', 100)
      t.string('email').notNullable().unique()
      t.string('password')
      t
        .boolean('email_confirmed')
        .notNullable()
        .defaultTo(false)
      t
        .datetime('last_login', { precision: 6 })
        .defaultTo(knex.fn.now(6))
      t.string('language')
      t.string('newsletter')
      t.timestamps(true, true)
      t
        .uuid('company_id')
        .references('company_id')
        .inTable('company')
  })
  .createTable('company', t => {
    t.uuid('company_id').primary()
    t.string('address_id')
    t.string('name', 100).notNullable()
    t.string('phone')
    t.timestamps(true, true)
    t
      .uuid('owner_user_id')
      .references('user_id')
      .inTable('user')
  })
}

error:

migration failed with error: alter table "user" add constraint "user_company_uuid_foreign" foreign key ("company_uuid") references "company" ("company_id") - relation "company" does not exist

I'd say it tries to create a table and add the foreign key before creating the second table (which the FK references).

Any idea on how to solve this.

1. it is not a m:n relationship really. A company can and should only have 1 owner. A user has to belong to a company, otherwise it can't exist. If this is not solvable I can have the user.company_id field as a simple string w/o any relationship.
2. Having a different table for user_companies would be overkill.

Thanks!

Answer 1

You are correct that it is trying to create the reference before the table it is referencing exists. The easiest way would probably be to simply delay the creation of the foreign key until after the companies table has been created. i.e.

exports.up = async function(knex) {
  await knex.schema.createTable('user', t => {
      t.uuid('user_id').primary()
      t.string('name', 100).notNullable()
      t.string('surname', 100)
      t.string('email').notNullable().unique()
      t.string('password')
      t.boolean('email_confirmed')
        .notNullable()
        .defaultTo(false)
      t.datetime('last_login', { precision: 6 })
        .defaultTo(knex.fn.now(6))
      t.string('language')
      t.string('newsletter')
      t.timestamps(true, true)
      t.uuid('company_id')
  });

  await knex.schema.createTable('company', t => {
    t.uuid('company_id').primary()
    t.string('address_id')
    t.string('name', 100).notNullable()
    t.string('phone')
    t.timestamps(true, true)
    t.uuid('owner_user_id')
      .references('user_id')
      .inTable('user')
  });

  await knex.schema.table('user', t => {
      t.foreign('company_id')
        .references('company_id')
        .inTable('company')
  });
}