Sequelize Many to Many basics - "no such table"

Question

In my first attempt to define a Many to Many relationship in Sequelize (v6, SQLITE3 v5) I'm already not getting very far. My setup is very simple.

Here is the code in my index.js that causes the error (setPlayers() instead of addPlayer() gives roughly the same error):

const cup = await Cup.findOne({ where: { thread: interaction.message.id }});
const player = await Player.findOne({ where: { id: interaction.user.id } });
await cup.addPlayer(player);

and the error message. As you can see CupPlayers is what I want my junction table to be.

[Error: SQLITE_ERROR: no such table: CupPlayers] {
    errno: 1,
    code: 'SQLITE_ERROR',
    sql: "SELECT `CupId`, `PlayerId`, `createdAt`, `updatedAt`, `cupId`, `playerId` FROM `CupPlayers` AS `CupPlayers` WHERE `CupPlayers`.`cupId` = 
1 AND `CupPlayers`.`playerId` IN ('23485757129835978');"

here are my models:

Player.js

module.exports = (sequelize, DataTypes) => {
    return sequelize.define('player', {
        id:     {type: DataTypes.STRING, primaryKey: true},
    }, {
        timestamps: false,
    });
};

Cup.js

module.exports = (sequelize, DataTypes) => {
    return sequelize.define('cup', {
        id:             {type: DataTypes.INTEGER, primaryKey: true},
        date:           {type: DataTypes.DATE, defaultValue: null},
        thread:         {type: DataTypes.STRING, defaultValue: null},
        list_message:   {type: DataTypes.STRING, defaultValue: null},
        reminder:       {type: DataTypes.STRING, defaultValue: null},
        is_open:        {type: DataTypes.BOOLEAN, defaultValue: true},
    }, {
    });
};

dbObjects.js for relations. As you can see I want Sequelize to automatically create the junction table. (when I try making my own junction table the error is no such table: main.undefined, so not much different)

const Sequelize = require('sequelize');

const sequelize = new Sequelize('a', 'b', 'c', {
    host: 'localhost',
    dialect: 'sqlite',
    logging: false,
    storage: 'cup_database.sqlite',
});

const Player = require('./models/Player.js')(sequelize, Sequelize.DataTypes);
const Cup = require('./models/Cup.js')(sequelize, Sequelize.DataTypes);

Cup.belongsToMany(Player, { through: 'CupPlayers' });
Player.belongsToMany(Cup, { through: 'CupPlayers' });

module.exports = { Player, Cup };

and finally dbInit.js to sync/initialise the database:

const Sequelize = require('sequelize');

const sequelize = new Sequelize('a', 'b', 'c', {
    host: 'localhost',
    dialect: 'sqlite',
    logging: false,
    storage: 'cup_database.sqlite',
});

require('./models/Player.js')(sequelize, Sequelize.DataTypes);
require('./models/Cup.js')(sequelize, Sequelize.DataTypes);

const force = process.argv.includes('--force') || process.argv.includes('-f');

sequelize.sync({ force }).then(async () => {
    
    const init = [
        Player.upsert({ id: '23485757129835978' })
    ];

    await Promise.all(init);
    console.log('Database synced');
    sequelize.close();
    
}).catch(console.error);

Overall a very simple setup, pretty much just copy pasted from the Sequelize and discord.js guides. I haven't had these kinds of problem with the other relationships and I have no idea what the error message means. I'd appreciate any help.

I don't see the code where you initialize models and register associations — Anatoly, Nov 06 '22 at 09:15
Sorry I'm not sure what exactly of relevance is missing so I rewrote the post and included every database related file in its entirety. — Urinstein, Nov 06 '22 at 15:22

Anatoly · Accepted Answer · 2022-11-06T19:47:20.650

0

You registered models twice:

dbObjects.js (model registration along with their associations);
dbInit.js (model registration WITHOUT associations once again).

So all in all while dbInit.js is executed all models were registered once again without their associations and thus sync did not create a junction table.

All you need is to use models from dbObjects in dbInit and remove lines with a direct model registration from models folder.

const Sequelize = require('sequelize');

const sequelize = new Sequelize('a', 'b', 'c', {
    host: 'localhost',
    dialect: 'sqlite',
    logging: false,
    storage: 'cup_database.sqlite',
});

const { registerModels } = require('./dbObjects');

const { Player, Cup } = registerModels(sequelize);

const force = process.argv.includes('--force') || process.argv.includes('-f');

sequelize.sync({ force }).then(async () => {
    const init = [
        Player.upsert({ id: '23485757129835978' })
    ];

    await Promise.all(init);
    console.log('Database synced');
    sequelize.close();
    
}).catch(console.error);

edited Nov 06 '22 at 19:47

answered Nov 06 '22 at 17:06

Anatoly

20,799
3
28
42

Thanks for your answer and your patience. Unfortunately, doing as you suggest doesn't seem to help as now I cannot even create/upsert Cup or Player instances without it telling me that the tables players and cups do not exist. The idea to refer directly back to the model definitions and not `dbObjects.js` comes from the discord.js guide (https://discordjs.guide/sequelize/currency.html#initialize-database) and worked just fine for One-to-Many associations (in another project). – Urinstein Nov 06 '22 at 18:33
Wait a moment. You just need to wrap all model registration and associations into a function and export it from `dbObjects`. You need to pass a Sequelize instance from 'dbInit' into it and do not create one more instance inside `dbObjects` – Anatoly Nov 06 '22 at 19:41
I edited the code example in my answer to show the idea – Anatoly Nov 06 '22 at 19:47
Thank you! This worked indeed. Only thing: I still gotta export the models from the `dbObjects.js` to my index file, so (as far as i can tell) I had to keep the registrations and associations outside the `registerModels()` function. Therefore I basically have everything twice in there and also had to initialize `sequelize` inside `dbObjects` still. – Urinstein Nov 06 '22 at 21:00
You don't need to import models from model files twice as well as create two instances of Sequelize. Either use them in dbObjects, or in dbInit but not in both – Anatoly Nov 07 '22 at 15:57
I recommend to look at my answer here: https://stackoverflow.com/a/61710568/1376618 to get an idea how to register models and associations correctly – Anatoly Nov 07 '22 at 15:58
Thank you Anatoly. I have tried to follow that lead for a few hours today and truly I have not made it anywhere. As I am new to both SQL/Sequelize and Javascript (and programming at large, I guess) I would really need someone to roll out the entire topic for a 5-year-old. I appreciate your tips, but for the time being i will continue with this hydra of a solution. – Urinstein Nov 09 '22 at 00:40

Sequelize Many to Many basics - "no such table"

1 Answers1