I'm using sequelize to define my tables and their relations, and after that inserting some entries to the tables.

I've got three tables :

• BankAccounts : pk - accountNumber
• Businesses : pk - sequence id
• BusinessBankAccounts : pk- bankAccountAccountNumber & businessId

Relations :

BankAccount.belongsToMany(Business, {through: 'BusinessBankAccounts'});

Creating entries for BankAccount and Business:

Connection.models.bankAccount.create({accountNumber: 12345678, ...});
Connection.models.business.create({...});

These two entries are inserted correctly to the tables (using pgAdmin to inspect my DB), but as soon as im trying to insert an entry to connect these two :

Connection.models.BusinessBankAccounts.create({bankAccountAccountNumber: 12345678,
                                               businessId: 1});

An error pops up:

Unhandled rejection SequelizeForeignKeyConstraintError: insert or update on table 
"BusinessBankAccounts" violates foreign key constraint
"BusinessBankAccounts_bankAccountAccountNumber_fkey"

NOTE: Manually inserting the exact same entry using the pgAdmin tool no error pops up, and it is inserted!

Any help would be much appreciated!

Thanks in advance.

I'm using sequelize to define my tables and their relations, and after that inserting some entries to the tables.

I've got three tables :

• BankAccounts : pk - accountNumber
• Businesses : pk - sequence id
• BusinessBankAccounts : pk- bankAccountAccountNumber & businessId

Relations :

BankAccount.belongsToMany(Business, {through: 'BusinessBankAccounts'});

Creating entries for BankAccount and Business:

Connection.models.bankAccount.create({accountNumber: 12345678, ...});
Connection.models.business.create({...});

These two entries are inserted correctly to the tables (using pgAdmin to inspect my DB), but as soon as im trying to insert an entry to connect these two :

Connection.models.BusinessBankAccounts.create({bankAccountAccountNumber: 12345678,
                                               businessId: 1});

An error pops up:

Unhandled rejection SequelizeForeignKeyConstraintError: insert or update on table 
"BusinessBankAccounts" violates foreign key constraint
"BusinessBankAccounts_bankAccountAccountNumber_fkey"

NOTE: Manually inserting the exact same entry using the pgAdmin tool no error pops up, and it is inserted!

Any help would be much appreciated!

Thanks in advance.

• javascript
• postgresql
• foreign-keys
• relational-database
• sequelize.js

• Commit between the two inserts. (pgadmin does an invisible automit for you) – wildplasser Commented Sep 18, 2016 at 11:27
• @wildplasser i see, any ideas how to mit in sequelize? – Kesem David Commented Sep 18, 2016 at 11:33
• @wildplasser Please do take a look at the answer i posted and upvote it if you think its well explained, im unable to accept my own answer for the next two days, Thanks a lot for your help. – Kesem David Commented Sep 18, 2016 at 12:12

2 Answers 2

Thanks to @wildplasser ment, I found on sequelize transaction docs how to force mit of some inserts.

Ended up doing as follows:

Connection.transaction((t)=> {
     return Promise.all([
       Connection.models.bankAccount.create({accountNumber: 12345678, ...}, 
                                            {transaction: t}),
       Connection.models.business.create({...},
                                         {transaction: t})
     ]).then((result)=> {
         Connection.models.BusinessBankAccounts.create({
             bankAccountAccountNumber: 12345678,
             businessId: 1
         });
     })
}

I was having the same issue myself with using Sequelize and Postgres together.

After doing some research, I found the 'deferrable' feature with Postgres that seemed to be the solution to my issue.

What you have will work, Kesem. My solution adds an option to Sequelize's transaction to enable Postgres's deferrable feature to allow me to insert as many rows as I want and deferring the foreign key constraints check until the transaction is about to mit rather than Postgres checking the constraint immediately after each insert statement. This allows me to run all of my create statements at one time rather than have to create a Promise chain as you have done.

var objectsToCreate = []
// all 3 of the objects you want to insert. 
objectsToCreate.push({accountNumber: 12345678, ...}) 
objectsToCreate.push({...})
objectsToCreate.push({bankAccountAccountNumber: 12345678, businessId: 1})

await sequelize.transaction({
  deferrable: sequelize.Deferrable.SET_DEFERRED // magic sauce.
}, (transaction) => {
  var promises = []
  objectsToCreate.forEach((objectToCreate) => {
    promises.push(Connection.models.BusinessBankAccounts.create(objectToCreate, {transaction:transaction}))
  })
  // Here, we are able to run all the queries at one time. No having to deal with a Promise().then().then().then() chain. 
  return Promise.all(promises)
})