Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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!

like image 985
skwidbreth Avatar asked Jan 09 '17 21:01

skwidbreth


1 Answers

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

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

Boom.

like image 195
skwidbreth Avatar answered Oct 13 '22 18:10

skwidbreth