Sequelize in Node/Express - 'no such table: main.User` error

Question

I'm trying to build a simple Node/Express app with Sequelize, but when I try to create a new record in my relational database, I am getting the error Unhandled rejection SequelizeDatabaseError: SQLITE_ERROR: no such table: main.User. Basically, I create a user in the Users table and then try to create a related address in the Addresses table - the user is successfully created but it fails with this error when creating the address... where is it getting the main prefix from in the table name? (full error readout below)...

First off, here's a rundown of my program...

• My Sequelize version is Sequelize [Node: 6.8.1, CLI: 2.4.0, ORM: 3.29.0], and I used the Sequelize CLI command sequelize init to set up this portion of my project.

• I am using SQLite3 for local development, and in config/config.json I have the development db defined as

  "development": {
      "storage": "dev.sqlite",
      "dialect": "sqlite"
  }
  

• My user migration:

  'use strict';
      module.exports = {
          up: function(queryInterface, Sequelize) {
              return queryInterface.createTable('Users', {
                  id: {
                      allowNull: false,
                      autoIncrement: true,
                      primaryKey: true,
                      type: Sequelize.INTEGER
                  },
                  first_name: {
                      type: Sequelize.STRING
                  },
                  last_name: {
                      type: Sequelize.STRING
                  },
                  createdAt: {
                      allowNull: false,
                      type: Sequelize.DATE
                  },
                  updatedAt: {
                      allowNull: false,
                      type: Sequelize.DATE
                  }
              });
          },
          down: function(queryInterface, Sequelize) {
              return queryInterface.dropTable('Users');
          }
      };
  

• and the address migration (abbreviated):

  module.exports = {
      up: function(queryInterface, Sequelize) {
          return queryInterface.createTable('Addresses', {
              id: {
                  allowNull: false,
                  autoIncrement: true,
                  primaryKey: true,
                  type: Sequelize.INTEGER
              },
              address_line_one: {
                  type: Sequelize.STRING
              },
              UserId: {
                  type: Sequelize.INTEGER,
                  allowNull: false,
                  references: {
                      model: "User",
                      key: "id"
                  }
              }
          })
      }
  

• The user model:

  'use strict';
  module.exports = function(sequelize, DataTypes) {
      var User = sequelize.define('User', {
          first_name: DataTypes.STRING,
          last_name: DataTypes.STRING
      }, {
     classMethods: {
         associate: function(models) {
             models.User.hasOne(models.Address);
         }
      }
   });
  return User;
  };
  

• and the address model:

  'use strict';
  module.exports = function(sequelize, DataTypes) {
      var Address = sequelize.define('Address', {
          address_line_one: DataTypes.STRING,
          UserId: DataTypes.INTEGER
      }, {
          classMethods: {
              associate: function(models) {
                  models.Address.hasOne(models.Geometry);
                  models.Address.belongsTo(models.User, {
                      onDelete: "CASCADE",
                      foreignKey: {
                          allowNull: false
                      }
                  });
              }
            }
        });
   return Address;
   };
  

• finally, my route index.js:

  router.post('/createUser', function(req, res){
      var firstName = req.body.first_name;
      var lastName = req.body.last_name;
      var addressLineOne = req.body.address_line_one;
  
      models.User.create({
          'first_name': newUser.firstName,
          'last_name': newUser.lastName
      }).then(function(user){         
          return user.createAddress({
              'address_line_one': newUser.addressLineOne
      })
  })
  

So when I try to post to /createUser, the User will successfully be created and the console will say that a new Address has been created (INSERT INTO 'Addresses'...), but the address is NOT created and the following error is logged:

Unhandled rejection SequelizeDatabaseError: SQLITE_ERROR: no such table: main.User at Query.formatError (/Users/darrenklein/Desktop/Darren/NYCDA/WDI/projects/world_table/wt_test_app_1/node_modules/sequelize/lib/dialects/sqlite/query.js:348:14) at afterExecute (/Users/darrenklein/Desktop/Darren/NYCDA/WDI/projects/world_table/wt_test_app_1/node_modules/sequelize/lib/dialects/sqlite/query.js:112:29) at Statement.errBack (/Users/darrenklein/Desktop/Darren/NYCDA/WDI/projects/world_table/wt_test_app_1/node_modules/sqlite3/lib/sqlite3.js:16:21)

I've done this sort of thing with Sequelize once before a few months ago and it was successful, I cannot for the life of me figure out what I'm missing here. Why is the app looking for main.User, and how can I get it to look for the correct table? Thank you!

Palikar

Answer 1

Aha! A small error has derailed my entire operation. In the migration files, references.model must be pluralized!

references: {
    model: "Users",
    key: "id"
}

Boom.

Palikar

if you dont want to worry about pluralization, you can add the option: 'freezeTableName: true' to your table – Dank

@R.Gulbrandsen That is awesome! – Palikar

Answer 2

The same thing happens to me.

In my development and production database, I use MySQL; it seems to be that for MySQL you can use references.model and references.id the name of the model and the field in the model.

Locally I started to use SQLite for testing, and that doesn't work. It requires for you to indicate the table name and field exactly as it is in the referenced table.

Davie

Answer 3

For me, it worked when I assigned "the table name" to that model, instead of what was defined in my actual model/class.

So I had this:

      inAppNotificationId: {
        allowNull: false,
        type: DataTypes.UUID,
        references: {
          model: 'InAppNotifications',
          key: 'id',
        },
        onUpdate: 'CASCADE',
        onDelete: 'CASCADE',
      }

And I changed it to the table's name, and it worked.

      inAppNotificationId: {
        allowNull: false,
        type: DataTypes.UUID,
        references: {
          model: 'in_app_notifications',
          key: 'id',
        },
        onUpdate: 'CASCADE',
        onDelete: 'CASCADE',
      }

I assumed this could have been the issue in the question as well, since their table was also called Users, and changing to that, fixed it. Might have been nothing to do with "plural/singular".

Organize