Query data from another table referenced by a foreign key with Sequelize and Postgres

Question

I'm building a NextJs NodeJs Express app with Postrges as a database, I'm also running two servers and using axios for get and post requests to and from my api endpoints.

I have two tables: Company and Bank, The primary key from the Bank table is referenced in the Company table as a foreign key. Company can have only one bank.

If I have a Bank table:
BankId    Bank Name    Account Number
1         New Bank     123456789

Company table:
CompanyId   Company Name    BankId
1           Newer Company   1

I want to show data for the company:

Company Name: Newer Company
Bank Name: New Bank
Account Number: 123456789 

Here are my files:

Company model: models/companyData.js

const Sequelize = require ('sequelize');
const db = require('../config/database');
const bank = require('./bank');

const companyData = db.define('companyData', {
    companyId: {
        type: Sequelize.INTEGER,
        primaryKey: true,
        autoIncrement: true
    },
    company_name: {
        type: Sequelize.STRING
    },
    bankId: {
        type: Sequelize.INTEGER,
        references: {
           model: 'bank', 
           key: 'bankId',
        }
    }
}, {
    freezeTableName: true
})

bank.hasOne(companyData);

module.exports = companyData;

Bank model: models/bank.js

const Sequelize = require('sequelize');
const db = require('../config/database');

const Bank = db.define('bank', {
    bankId: {
        type: Sequelize.INTEGER,
        primaryKey: true,
        autoIncrement: true
    },
    bank_name: {
        type: Sequelize.STRING
    },
    account_number: {
        type: Sequelize.BIGINT
    }
}, {
    freezeTableName: true
})

module.exports = Bank;

Here is a Component containing state and input fields for creating a new Company: components/newCompanyData.js

import React from 'react';
import axios from 'axios';

class newCompanyData extends React.Component {

        state = {
            company_name: '',
            bankId: '',
        }

    onChangeCompanyName = (e) => {
        this.setState({
            company_name: e.target.value
        })
    }

    onChangeBankId = (e) => {
        this.setState({
            bankId: e.target.value
        })
    }

    onSubmit = (e) => {
        e.preventDefault()

        axios.post('http://localhost:9000/api/company', {
            company_name: this.state.company_name,
            bankId: this.state.bankId,
        })
        .then(res => {
            console.log(res);
        }).catch(err => console.log(err))
    }

    onReset = (e) => {
        this.setState({
            company_name: '',
            bankId: '',
        })
    }

    render() {
        return (
            <div>
                <form>
                    Company name:<br/>
                    <input type="text" name="companyname" onChange={this.onChangeCompanyName} value={this.state.company_name}></input><br/>
                    Bank Id:<br/>
                    <input type="number" name="bankid" onChange={this.onChangeBankId} value={this.state.bankId}></input><br/>
                    <br/>
                    <button type="submit" onClick={this.onSubmit}>Submit</button><br/>
                    <input type="button" value="Reset Form" onClick={this.onReset} />
                </form>
            </div>
        )
    }
}

export default newCompanyData;

I then send request to this api endpoint to create a table if it doesn't exist or just fill in the new row: routes/api/company.js

const express = require('express');
const router = express.Router();
const DB = require('../../config/database');
const companyData = require('../../models/companyData');

router.post('/', (req, res) => {
    const data = req.body;

    DB.sync().then(function() {
        return companyData.create({
            company_name: data.company_name,
            bankId: data.bankId,
        });
      }).then(function () { 
          res.send(req.body);
          console.log('Success!')
      });
    })

module.exports = router;

So it does create a table and fills in all the data, however the issue is when I want to print all data from the company table including the data from the bank table that is referenced by the bankId: pages/viewCompany.js

import React from 'react';
import axios from 'axios';

class listCompany extends React.Component {

    state = {
        data: []
    }

    componentDidMount = () =>  {
        axios.get('http://localhost:9000/api/listcompany')
            .then((response) => {
                console.log(response.data);
                this.setState({data: response.data.response})
            }).catch(err => {
                console.log(err);
              });  
    }

    render() {
        return (
            <div>
                <h1>Company List</h1>
            </div>
        )
    }
}

export default listCompany;

I sent a get request to this API endpoint but it doesnt return it properly: routes/api/listcompany.js

const express = require('express');
const router = express.Router();
const Bank = require('../../models/bank');
const companyData = require('../../models/companyData');

router.get('/', (req, res) => {
        companyData.findAll({
            include: [{
            model: Bank
        }]}).then(function(response) {
            console.log(response);
            res.send({response});
          }).catch(function(err){
            console.log('Oops! something went wrong, : ', err);
          });
    })

module.exports = router;

I have a feeling I don't connect it well enough in the model stage, and also when sending a query. I appreciate all help I can get. Thanks.

Answer 1

I've finally managed to solve this.

I've made changes to both models and the API endpoint where the query runs to fetch the data. First I have made changes to the foreign key in the companyData model renaming it for more clarifications and declaring the model without brackets. I've also removed any hasOne or belongsTo from this model.

const Sequelize = require ('sequelize');
const db = require('../config/database');
const Bank = require('./bank');

const companyData = db.define('companyData', {
    companyId: {
        type: Sequelize.INTEGER,
        primaryKey: true,
        autoIncrement: true
    },
    company_name: {
        type: Sequelize.STRING
    },
    bankingId: {
        type: Sequelize.INTEGER,
        references: {
           model: Bank, 
           key: 'bankId',
        }
    }
}, {
    freezeTableName: true
})

module.exports = companyData;

The magic is within bank model. If you try to separate hasOne and belongsTo to two tables it throws errors however if you get them in one file it runs smoothly. Note it didn't work without the foreign key constraints.

const Sequelize = require('sequelize');
const db = require('../config/database');
const companyData = require('./companyData');

const Bank = db.define('bank', {
    bankId: {
        type: Sequelize.INTEGER,
        primaryKey: true,
        autoIncrement: true
    },
    bank_name: {
        type: Sequelize.STRING
    },
    account_number: {
        type: Sequelize.BIGINT
    }
}, {
    freezeTableName: true
})

Bank.belongsTo(companyData, {foreignKey: 'bankingId'});
companyData.hasOne(Bank, {foreignKey: 'bankId'});

The last change was to the API endpoint where I included bank with all attributes:

const express = require('express');
const router = express.Router();
const Bank = require('../../models/bank');
const companyData = require('../../models/companyData');

router.get('/', (req, res) => {
        companyData.findAll({
          include: [{
            model: Bank,
            attributes: ['bankId', 'bank_name', 'account_number']  
          }],
        }).then(function(response) {
            console.log(response);
            res.send({response});
          }).catch(function(err){
            console.log('Oops! something went wrong, : ', err);
          });
    })