Back

Technologies:

javascriptjavascript
node.jsnode.js
avatar
Tolerim
a month ago

NodeJS with Sequelize throws error 1146 for non-existent tables.

Whilst I am learning Sequelize for NodeJS, I encountered an error when setting db.sequelize.sync({ force: true }) to db.sequelize.sync({ force: false }). The error message is: code: 'ERNOSUCH_TABLE', errno: 1146, sqlState: '42S02', sqlMessage: "Table 'test.users' doesn't exist", sql: 'SHOW INDEX FROM `users`',. Below is my code: 
const { Sequelize, DataTypes } = require('sequelize');

const sequelize = new Sequelize('test', 'root', 'root', {
  host: 'localhost',
  dialect: 'mysql'
});

async function checkConnection() {
  try {
    await sequelize.authenticate();
    console.log('Connection has been established successfully.');
  } catch (error) {
    console.error('Unable to connect to the database:', error);
  }
}

const db = {};
db.Sequelize = Sequelize;
db.sequelize = sequelize;
db.user = require('./user')(sequelize, Sequelize);
db.contact = require('./contact')(sequelize, Sequelize);

db.sequelize.sync({ force: false }).then(() => {
  console.log('The database schema is now synchronized.');
});
module.exports = db;
module.exports = { sequelize, checkConnection };
Furthermore, you can find my code for the User model in the following file: 
const { Sequelize } = require('sequelize');
module.exports = (sequelize, Sequelize) => {
  const User = sequelize.define('user', {
    firstName: {
      type: Sequelize.STRING,
      allowNull: false,
    },
    lastName: {
      type: Sequelize.STRING,
    },
    userID: {
      type: Sequelize.INTEGER,
      primaryKey: true,
      autoIncrement: true,
    }
  },{
    tableName: 'users',
    timestamps: false,
  });

  return User; 
};
Answers(2)
avatar
Vogi
a month ago
Verified Answer
The error message you are getting is because the table 'users' does not exist in your database. You can create the table by running the following command in your terminal: 
npx sequelize-cli db:migrate
This will create the table 'users' in your database. You can also check if the table exists by running the following command: 
npx sequelize-cli db:seed:all
This will seed your database with data. If you still encounter issues, you can try dropping the table and then re-creating it by running: 
npx sequelize-cli db:migrate:undo:all
and then 
npx sequelize-cli db:migrate
I hope this helps! Let me know if you have any other questions.

Sources

"Error: ER_NO_SUCH_TABLE" when connecting ACE to DatabaseER_NO_SUCH_TABLE:" while showing on mysql - GithubImpossible [SequelizeDatabaseError: ER_NO_SUCH_TABLE: Table ... - GithubSequelize for NodeJS, error:1146 ER_NO_SUCH_TABLE
avatar
Tolerim
a month ago
The error message suggests that the table users does not exist in the database and the query SHOW INDEX FROM users could not be executed. When you use {force: true} with sequelize.sync(), it drops the tables if they already exist and creates new ones as per the defined models. When you use {force: false}, it does not try to recreate the tables if they already exist, it just creates the tables that are not yet created. So, you need to make sure that the table users exists in your database. You can create the table by running the migration for your user model with the command npx sequelize-cli db:migrate. Also, in your index.js file, you are exporting db and sequelize objects individually, but you are overwriting the export for db object with just testConnection() function. To fix this, you can change the last line of index.js to module.exports = {db, sequelize, testConnection};. This will export all three objects together.
;