'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.
- 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_idfield as a simple string w/o any relationship. - 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 |
|---|
