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

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!



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source