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!
Aha! A small error has derailed my entire operation. In the migration files, references.model
must be pluralized!
references: {
model: "Users",
key: "id"
}
Boom.