Sequelize foreing key is not creating

Question

I try to create a relation between 2 tables with Sequelize in NodeJS for MariaDB.

I have 2 tables order and local, the table order needs one of the information of the table local.

The order table contains information about an order (id: 1, type: Jacket, color: blue, tracking_number: TR123)

The table local contains information about the place where the order is stored (address: 20 rue madeline, city: Paris)

I tried to link the two tables but it does not work, the foreing key is not created

models/order.js

module.exports = (sequelize, DataTypes) => {
    const Order = sequelize.define('order', {
        id: {
            type: DataTypes.INTEGER,
            primaryKey: true,
            autoIncrement: true
        },
        trackingNumber: {
            type: DataTypes.STRING,
            allowNull: false
        },
        type: {
            type: DataTypes.STRING(50),
            allowNull: false
        },
        color: {
            type: DataTypes.STRING(50),
            allowNull: false
        },
        email: {
            type: DataTypes.STRING,
            allowNull: false
        },
        tel: {
            type: DataTypes.STRING(10),
            allowNull: false
        }
    }, {
        timestamps: true,
        createdAt: true,
        updatedAt: 'updateTimestamp'
    })

    Order.associate = function (models) {
        Order.hasOne(models.local);
    }

    return Order;
}

models/local.js


module.exports = (sequelize, DataTypes) => {
    const Local = sequelize.define('local', {
        id: {
            type: DataTypes.INTEGER,
            primaryKey: true,
            autoIncrement: true
        },
        adress: {
            type: DataTypes.STRING,
            allowNull: false
        },
        informations_about: {
            type: DataTypes.STRING,
            allowNull: false
        },
        contact: {
            type: DataTypes.STRING,
            allowNull: false
        },
        city: {
            type: DataTypes.STRING,
            allowNull: false
        },
        zip: {
            type: DataTypes.STRING(5),
            allowNull: false
        },

    }, {
        timestamps: true,
        createdAt: true,
        updatedAt: 'updateTimestamp'
    })
    return Local;
}

app.js

// Imports
const express = require('express')
const morgan = require('morgan')
const db = require('./database')
const sequelize = require('./database').sequelize;

var apiRouter = require('./apiRouter.js').router;
var helmet = require('helmet');

const app = express()
const port = process.env.PORT || 3000;

// Init server
app.use(morgan('combined'))

// Parser config
app.use(express.urlencoded({ extended: false }));
app.use(express.json());

// Security API
app.use(helmet());
app.disable('x-powered-by');


app.use(({ res }) => {
    res.status(404).json({ message: "404 Not Found" })
})

db.sequelize.authenticate()
    .then(_ => console.log("La connexion à bien été établie."))
    .catch(error => console.log(`error ${error}`))

db.sequelize.sync({ force: true })
    .then(_ => {
        console.log("Base de donnée synchronisée.")
        app.use('/api/', apiRouter);
    })


app.listen(port, () => {
    console.log("Server is up and listening in " + port)
})

database.js

const fs = require('fs')
const path = require('path')
const { Sequelize } = require('sequelize')
const db = {}
const models = path.join(__dirname, 'models') // correct it to path where your model files are

const sequelize = new Sequelize(
    '',
    '',
    '',
    {
        host: 'localhost',
        dialect: 'mariadb',
        dialectOptions: {
            useUTC: false, // for reading from database
        },
        pool: {
            max: 5,
            min: 0,
            acquire: 30000,
            idle: 10000
        },
        logging: false
    }
)

var basename = path.basename(module.filename)

fs
    .readdirSync(models)
    .filter(function (file) {
        return (file.indexOf('.') !== 0) && (file !== basename) && (file.slice(-3) === '.js')
    })
    .forEach(function (file) {
        const model = require(path.join(__dirname + '/models', file))(sequelize, Sequelize.DataTypes)
        db[model.name] = model
    })

Object.keys(db).forEach(function (modelName) {
    if (db[modelName].associate) {
        db[modelName].associate(db)
    }
})

db.Sequelize = Sequelize // for accessing static props and functions like Op.or
db.sequelize = sequelize // for accessing connection props and functions like 'query' or 'transaction'

module.exports = db

Despite the associate function in the model/order.js it does not work, I have no key in my order table

score 0 · Answer 1 · answered Nov 08 '21 at 21:05

0

You have to manually call all associate functions in order to register associations between models and only after all models are already registered in the Sequelize instance. You can look at my other answer to see how you can do it.
And please show the content of database module and then I probably correct my answer or append more useful tips.

answered Nov 08 '21 at 21:05

Anatoly

20,799
3
28
42

I updated my code in the post with your answer but I still don't have a "localId" key in my orders table :/ – luck99 Nov 09 '21 at 18:31
Try to add an opposite association `Local.belongsTo(models.order)` – Anatoly Nov 09 '21 at 18:46
Still no result, I tried to move the association all over the code but I never get any association I start to get discouraged lol. My version of Sequelize is 6.8.0 – luck99 Nov 09 '21 at 18:51
Wait, wait, wait! If you wish that an order should have a link to a local then you need `Order.belongsTo(models.local)` and not `hasOne`. You should call `belongsTo` of a model that has a link field to a model that is indicated as the first argument in `belongsTo`. – Anatoly Nov 09 '21 at 18:59
Alright, i tried : ```Order.associate = function (models){Order.belongsTo(models.local)};``` but still nothing on the horizon, I also tried to opposite the association – luck99 Nov 09 '21 at 19:08
And no luck again? – Anatoly Nov 09 '21 at 19:09
I'm about to curl up under my desk – luck99 Nov 09 '21 at 19:11
The last thing I can propose is to indicate the field `localId` on `Order` model explicitly and don't forget to indicate `references` option in this field description. – Anatoly Nov 09 '21 at 19:11
Ok I'm going to do that now, it's just that when I retrieve the data with order.findAll how can I add the data from the local table to each order object with the associated local_id? – luck99 Nov 09 '21 at 19:22
That's another question ). Please create a new one here and post a link to it here. In short you need to add `include` option – Anatoly Nov 10 '21 at 17:33

Sequelize foreing key is not creating

1 Answers1