Sequelize or MySql creates extra tables, table connections missing in EER diagram
Update 01/02
Please see screenshot. I reduced app to 2 tables: Addresses and Users.
Running sequelize db:migrate with migrations/models below creates an extra 'users' table. See Catalog section. Clicking on 'users' pops up message "The table you are trying to edit is a model-only stub, created to represent missing external tables referenced by foreign keys."
Diagram does not have the extra 'users' table. A connection is not being drawn for Addresses.userId to Users.id like it should be.
Anybody have any thoughts on why or how to fix?
- Running on Macbook OS X 10.14.2
- Sequelize version: Sequelize CLI [Node: 10.7.0, CLI: 5.4.0, ORM:
4.42.0] - MySql Server version: 8.0.13
- MySql Workbench version is also: 8.0.13
Update 12/30
In MySql Workbench, Database > Reverse Engineer renders a diagram of tables but foreign/primary key lines are not drawn. DB engine is InnoDB.
Am using Sequelize migrations/models to create the DB and relationships. For example, Addresses table has a 'userId' foreign key to Users table.
In the EER diagram's Catalog section of tables, somehow there is a 'Users' table and 'users' table. The foreign key Addresses.userId points to lowercased 'users' table, a table that should not exist. The lowercased table doesn't appear in the diagram, nor in the DB table list in Schema section, it only appears in Catalog section of tables.
There are two other pairs of tables as well: Property/property, PropertyClass/propertyclass. No other tables have duplicates. Users and PropertyClasses have a join table UserPropertyClasses.
Any idea why this might be happening or how to prevent it?
Sequelize version: Sequelize CLI [Node: 10.7.0, CLI: 5.4.0, ORM: 4.42.0]
MySql Server version: 8.0.13
MySql Workbench version is also: 8.0.13
Here is Address model:
'use strict';
module.exports = (sequelize, DataTypes) => {
const Address = sequelize.define('Address', {
userId: {
type: DataTypes.INTEGER,
validate: {
notNull: true
}
},
street1: {
type: DataTypes.STRING,
validate: {
notEmpty: true
}
}
}, {
paranoid: true
});
Address.associate = function(models) {
Address.belongsTo(models.User, {
as: 'user',
foreignKey: 'userId'
})
}
return Address;
};
Here is Addresses migration:
'use strict';
module.exports = {
up: (queryInterface, Sequelize) => {
return queryInterface.createTable('Addresses', {
id: {
allowNull: false,
autoIncrement: true,
primaryKey: true,
type: Sequelize.INTEGER
},
userId: {
allowNull: false,
type: Sequelize.INTEGER,
references: {
model: 'Users',
key: 'id'
}
},
street1: {
allowNull: false,
type: Sequelize.STRING(50)
},
createdAt: {
allowNull: false,
type: Sequelize.DATE
},
updatedAt: {
allowNull: false,
type: Sequelize.DATE
},
deletedAt: {
allowNull: true,
defaultValue: null,
type: Sequelize.DATE
}
})
.then(async () => {
return await Promise.all([
queryInterface.addIndex('Addresses', [ 'userId' ]),
])
});
}
User model:
'use strict';
module.exports = (sequelize, DataTypes) => {
const User = sequelize.define('User', {
firstname: {
type: DataTypes.STRING,
validate: {
notEmpty: true
}
}
}, {
paranoid: true
});
User.associate = function(models) {
User.hasOne(models.Address, {
as: 'address',
foreignKey: 'userId'
})
};
return User;
};
Here is the User migration:
'use strict';
module.exports = {
up: (queryInterface, Sequelize) => {
return queryInterface.createTable('Users', {
id: {
allowNull: false,
autoIncrement: true,
primaryKey: true,
type: Sequelize.INTEGER
},
firstname: {
allowNull: false,
type: Sequelize.STRING(50)
},
createdAt: {
allowNull: false,
type: Sequelize.DATE
},
updatedAt: {
allowNull: false,
type: Sequelize.DATE
},
deletedAt: {
allowNull: true,
defaultValue: null,
type: Sequelize.DATE
}
})
.then(async () => {
return await Promise.all([
queryInterface.addIndex('Users', [ 'firstname' ]),
]);
})
}
mysql node.js sequelize.js mysql-workbench
asked Dec 29 '18 at 18:02
user1192805user1192805
365617
add a comment |
Update 01/02
Please see screenshot. I reduced app to 2 tables: Addresses and Users.
Running sequelize db:migrate with migrations/models below creates an extra 'users' table. See Catalog section. Clicking on 'users' pops up message "The table you are trying to edit is a model-only stub, created to represent missing external tables referenced by foreign keys."
Diagram does not have the extra 'users' table. A connection is not being drawn for Addresses.userId to Users.id like it should be.
Anybody have any thoughts on why or how to fix?
- Running on Macbook OS X 10.14.2
- Sequelize version: Sequelize CLI [Node: 10.7.0, CLI: 5.4.0, ORM:
4.42.0] - MySql Server version: 8.0.13
- MySql Workbench version is also: 8.0.13
Update 12/30
In MySql Workbench, Database > Reverse Engineer renders a diagram of tables but foreign/primary key lines are not drawn. DB engine is InnoDB.
Am using Sequelize migrations/models to create the DB and relationships. For example, Addresses table has a 'userId' foreign key to Users table.
In the EER diagram's Catalog section of tables, somehow there is a 'Users' table and 'users' table. The foreign key Addresses.userId points to lowercased 'users' table, a table that should not exist. The lowercased table doesn't appear in the diagram, nor in the DB table list in Schema section, it only appears in Catalog section of tables.
There are two other pairs of tables as well: Property/property, PropertyClass/propertyclass. No other tables have duplicates. Users and PropertyClasses have a join table UserPropertyClasses.
Any idea why this might be happening or how to prevent it?
Sequelize version: Sequelize CLI [Node: 10.7.0, CLI: 5.4.0, ORM: 4.42.0]
MySql Server version: 8.0.13
MySql Workbench version is also: 8.0.13
Here is Address model:
'use strict';
module.exports = (sequelize, DataTypes) => {
const Address = sequelize.define('Address', {
userId: {
type: DataTypes.INTEGER,
validate: {
notNull: true
}
},
street1: {
type: DataTypes.STRING,
validate: {
notEmpty: true
}
}
}, {
paranoid: true
});
Address.associate = function(models) {
Address.belongsTo(models.User, {
as: 'user',
foreignKey: 'userId'
})
}
return Address;
};
Here is Addresses migration:
'use strict';
module.exports = {
up: (queryInterface, Sequelize) => {
return queryInterface.createTable('Addresses', {
id: {
allowNull: false,
autoIncrement: true,
primaryKey: true,
type: Sequelize.INTEGER
},
userId: {
allowNull: false,
type: Sequelize.INTEGER,
references: {
model: 'Users',
key: 'id'
}
},
street1: {
allowNull: false,
type: Sequelize.STRING(50)
},
createdAt: {
allowNull: false,
type: Sequelize.DATE
},
updatedAt: {
allowNull: false,
type: Sequelize.DATE
},
deletedAt: {
allowNull: true,
defaultValue: null,
type: Sequelize.DATE
}
})
.then(async () => {
return await Promise.all([
queryInterface.addIndex('Addresses', [ 'userId' ]),
])
});
}
User model:
'use strict';
module.exports = (sequelize, DataTypes) => {
const User = sequelize.define('User', {
firstname: {
type: DataTypes.STRING,
validate: {
notEmpty: true
}
}
}, {
paranoid: true
});
User.associate = function(models) {
User.hasOne(models.Address, {
as: 'address',
foreignKey: 'userId'
})
};
return User;
};
Here is the User migration:
'use strict';
module.exports = {
up: (queryInterface, Sequelize) => {
return queryInterface.createTable('Users', {
id: {
allowNull: false,
autoIncrement: true,
primaryKey: true,
type: Sequelize.INTEGER
},
firstname: {
allowNull: false,
type: Sequelize.STRING(50)
},
createdAt: {
allowNull: false,
type: Sequelize.DATE
},
updatedAt: {
allowNull: false,
type: Sequelize.DATE
},
deletedAt: {
allowNull: true,
defaultValue: null,
type: Sequelize.DATE
}
})
.then(async () => {
return await Promise.all([
queryInterface.addIndex('Users', [ 'firstname' ]),
]);
})
}
mysql node.js sequelize.js mysql-workbench
asked Dec 29 '18 at 18:02
user1192805user1192805
365617
I think you need to define the references (constraints) in the migration as well if you are using migrations, are you using migrations ?
– Mohdule
Dec 29 '18 at 18:19
Reference is in migration. I'll update question
– user1192805
Dec 29 '18 at 18:48
Awesome, you see creating the table and the constraint is handled by your migration the model is just the interface to get that data, this is weird though, your migration andreferencesclause is correct, maybe you forgot to define an id on the Users table or something ? Can you maybe post the fullUsers migrationand fullAddresses migrationhere
– Mohdule
Dec 29 '18 at 19:25
Updated to include full models and migrations
– user1192805
Dec 29 '18 at 20:50
add a comment |
Update 01/02
Please see screenshot. I reduced app to 2 tables: Addresses and Users.
Running sequelize db:migrate with migrations/models below creates an extra 'users' table. See Catalog section. Clicking on 'users' pops up message "The table you are trying to edit is a model-only stub, created to represent missing external tables referenced by foreign keys."
Diagram does not have the extra 'users' table. A connection is not being drawn for Addresses.userId to Users.id like it should be.
Anybody have any thoughts on why or how to fix?
- Running on Macbook OS X 10.14.2
- Sequelize version: Sequelize CLI [Node: 10.7.0, CLI: 5.4.0, ORM:
4.42.0] - MySql Server version: 8.0.13
- MySql Workbench version is also: 8.0.13
Update 12/30
In MySql Workbench, Database > Reverse Engineer renders a diagram of tables but foreign/primary key lines are not drawn. DB engine is InnoDB.
Am using Sequelize migrations/models to create the DB and relationships. For example, Addresses table has a 'userId' foreign key to Users table.
In the EER diagram's Catalog section of tables, somehow there is a 'Users' table and 'users' table. The foreign key Addresses.userId points to lowercased 'users' table, a table that should not exist. The lowercased table doesn't appear in the diagram, nor in the DB table list in Schema section, it only appears in Catalog section of tables.
There are two other pairs of tables as well: Property/property, PropertyClass/propertyclass. No other tables have duplicates. Users and PropertyClasses have a join table UserPropertyClasses.
Any idea why this might be happening or how to prevent it?
Sequelize version: Sequelize CLI [Node: 10.7.0, CLI: 5.4.0, ORM: 4.42.0]
MySql Server version: 8.0.13
MySql Workbench version is also: 8.0.13
Here is Address model:
'use strict';
module.exports = (sequelize, DataTypes) => {
const Address = sequelize.define('Address', {
userId: {
type: DataTypes.INTEGER,
validate: {
notNull: true
}
},
street1: {
type: DataTypes.STRING,
validate: {
notEmpty: true
}
}
}, {
paranoid: true
});
Address.associate = function(models) {
Address.belongsTo(models.User, {
as: 'user',
foreignKey: 'userId'
})
}
return Address;
};
Here is Addresses migration:
'use strict';
module.exports = {
up: (queryInterface, Sequelize) => {
return queryInterface.createTable('Addresses', {
id: {
allowNull: false,
autoIncrement: true,
primaryKey: true,
type: Sequelize.INTEGER
},
userId: {
allowNull: false,
type: Sequelize.INTEGER,
references: {
model: 'Users',
key: 'id'
}
},
street1: {
allowNull: false,
type: Sequelize.STRING(50)
},
createdAt: {
allowNull: false,
type: Sequelize.DATE
},
updatedAt: {
allowNull: false,
type: Sequelize.DATE
},
deletedAt: {
allowNull: true,
defaultValue: null,
type: Sequelize.DATE
}
})
.then(async () => {
return await Promise.all([
queryInterface.addIndex('Addresses', [ 'userId' ]),
])
});
}
User model:
'use strict';
module.exports = (sequelize, DataTypes) => {
const User = sequelize.define('User', {
firstname: {
type: DataTypes.STRING,
validate: {
notEmpty: true
}
}
}, {
paranoid: true
});
User.associate = function(models) {
User.hasOne(models.Address, {
as: 'address',
foreignKey: 'userId'
})
};
return User;
};
Here is the User migration:
'use strict';
module.exports = {
up: (queryInterface, Sequelize) => {
return queryInterface.createTable('Users', {
id: {
allowNull: false,
autoIncrement: true,
primaryKey: true,
type: Sequelize.INTEGER
},
firstname: {
allowNull: false,
type: Sequelize.STRING(50)
},
createdAt: {
allowNull: false,
type: Sequelize.DATE
},
updatedAt: {
allowNull: false,
type: Sequelize.DATE
},
deletedAt: {
allowNull: true,
defaultValue: null,
type: Sequelize.DATE
}
})
.then(async () => {
return await Promise.all([
queryInterface.addIndex('Users', [ 'firstname' ]),
]);
})
}
mysql node.js sequelize.js mysql-workbench
asked Dec 29 '18 at 18:02
user1192805user1192805
365617
Update 01/02
Please see screenshot. I reduced app to 2 tables: Addresses and Users.
Running sequelize db:migrate with migrations/models below creates an extra 'users' table. See Catalog section. Clicking on 'users' pops up message "The table you are trying to edit is a model-only stub, created to represent missing external tables referenced by foreign keys."
Diagram does not have the extra 'users' table. A connection is not being drawn for Addresses.userId to Users.id like it should be.
Anybody have any thoughts on why or how to fix?
- Running on Macbook OS X 10.14.2
- Sequelize version: Sequelize CLI [Node: 10.7.0, CLI: 5.4.0, ORM:
4.42.0] - MySql Server version: 8.0.13
- MySql Workbench version is also: 8.0.13
Update 12/30
In MySql Workbench, Database > Reverse Engineer renders a diagram of tables but foreign/primary key lines are not drawn. DB engine is InnoDB.
Am using Sequelize migrations/models to create the DB and relationships. For example, Addresses table has a 'userId' foreign key to Users table.
In the EER diagram's Catalog section of tables, somehow there is a 'Users' table and 'users' table. The foreign key Addresses.userId points to lowercased 'users' table, a table that should not exist. The lowercased table doesn't appear in the diagram, nor in the DB table list in Schema section, it only appears in Catalog section of tables.
There are two other pairs of tables as well: Property/property, PropertyClass/propertyclass. No other tables have duplicates. Users and PropertyClasses have a join table UserPropertyClasses.
Any idea why this might be happening or how to prevent it?
Sequelize version: Sequelize CLI [Node: 10.7.0, CLI: 5.4.0, ORM: 4.42.0]
MySql Server version: 8.0.13
MySql Workbench version is also: 8.0.13
Here is Address model:
'use strict';
module.exports = (sequelize, DataTypes) => {
const Address = sequelize.define('Address', {
userId: {
type: DataTypes.INTEGER,
validate: {
notNull: true
}
},
street1: {
type: DataTypes.STRING,
validate: {
notEmpty: true
}
}
}, {
paranoid: true
});
Address.associate = function(models) {
Address.belongsTo(models.User, {
as: 'user',
foreignKey: 'userId'
})
}
return Address;
};
Here is Addresses migration:
'use strict';
module.exports = {
up: (queryInterface, Sequelize) => {
return queryInterface.createTable('Addresses', {
id: {
allowNull: false,
autoIncrement: true,
primaryKey: true,
type: Sequelize.INTEGER
},
userId: {
allowNull: false,
type: Sequelize.INTEGER,
references: {
model: 'Users',
key: 'id'
}
},
street1: {
allowNull: false,
type: Sequelize.STRING(50)
},
createdAt: {
allowNull: false,
type: Sequelize.DATE
},
updatedAt: {
allowNull: false,
type: Sequelize.DATE
},
deletedAt: {
allowNull: true,
defaultValue: null,
type: Sequelize.DATE
}
})
.then(async () => {
return await Promise.all([
queryInterface.addIndex('Addresses', [ 'userId' ]),
])
});
}
User model:
'use strict';
module.exports = (sequelize, DataTypes) => {
const User = sequelize.define('User', {
firstname: {
type: DataTypes.STRING,
validate: {
notEmpty: true
}
}
}, {
paranoid: true
});
User.associate = function(models) {
User.hasOne(models.Address, {
as: 'address',
foreignKey: 'userId'
})
};
return User;
};
Here is the User migration:
'use strict';
module.exports = {
up: (queryInterface, Sequelize) => {
return queryInterface.createTable('Users', {
id: {
allowNull: false,
autoIncrement: true,
primaryKey: true,
type: Sequelize.INTEGER
},
firstname: {
allowNull: false,
type: Sequelize.STRING(50)
},
createdAt: {
allowNull: false,
type: Sequelize.DATE
},
updatedAt: {
allowNull: false,
type: Sequelize.DATE
},
deletedAt: {
allowNull: true,
defaultValue: null,
type: Sequelize.DATE
}
})
.then(async () => {
return await Promise.all([
queryInterface.addIndex('Users', [ 'firstname' ]),
]);
})
}
mysql node.js sequelize.js mysql-workbench
mysql node.js sequelize.js mysql-workbench
asked Dec 29 '18 at 18:02
user1192805user1192805
365617
asked Dec 29 '18 at 18:02
user1192805user1192805
365617
edited Jan 3 at 3:31
user1192805
asked Dec 29 '18 at 18:02
user1192805user1192805
365617
asked Dec 29 '18 at 18:02
user1192805user1192805
365617
asked Dec 29 '18 at 18:02
user1192805user1192805
365617
365617
I think you need to define the references (constraints) in the migration as well if you are using migrations, are you using migrations ?
– Mohdule
Dec 29 '18 at 18:19
Reference is in migration. I'll update question
– user1192805
Dec 29 '18 at 18:48
Awesome, you see creating the table and the constraint is handled by your migration the model is just the interface to get that data, this is weird though, your migration andreferencesclause is correct, maybe you forgot to define an id on the Users table or something ? Can you maybe post the fullUsers migrationand fullAddresses migrationhere
– Mohdule
Dec 29 '18 at 19:25
Updated to include full models and migrations
– user1192805
Dec 29 '18 at 20:50
add a comment |
I think you need to define the references (constraints) in the migration as well if you are using migrations, are you using migrations ?
– Mohdule
Dec 29 '18 at 18:19
Reference is in migration. I'll update question
– user1192805
Dec 29 '18 at 18:48
Awesome, you see creating the table and the constraint is handled by your migration the model is just the interface to get that data, this is weird though, your migration andreferencesclause is correct, maybe you forgot to define an id on the Users table or something ? Can you maybe post the fullUsers migrationand fullAddresses migrationhere
– Mohdule
Dec 29 '18 at 19:25
Updated to include full models and migrations
– user1192805
Dec 29 '18 at 20:50
I think you need to define the references (constraints) in the migration as well if you are using migrations, are you using migrations ?
– Mohdule
Dec 29 '18 at 18:19
I think you need to define the references (constraints) in the migration as well if you are using migrations, are you using migrations ?
– Mohdule
Dec 29 '18 at 18:19
Reference is in migration. I'll update question
– user1192805
Dec 29 '18 at 18:48
Reference is in migration. I'll update question
– user1192805
Dec 29 '18 at 18:48
Awesome, you see creating the table and the constraint is handled by your migration the model is just the interface to get that data, this is weird though, your migration and
references clause is correct, maybe you forgot to define an id on the Users table or something ? Can you maybe post the full Users migration and full Addresses migration here– Mohdule
Dec 29 '18 at 19:25
Awesome, you see creating the table and the constraint is handled by your migration the model is just the interface to get that data, this is weird though, your migration and
references clause is correct, maybe you forgot to define an id on the Users table or something ? Can you maybe post the full Users migration and full Addresses migration here– Mohdule
Dec 29 '18 at 19:25
Updated to include full models and migrations
– user1192805
Dec 29 '18 at 20:50
Updated to include full models and migrations
– user1192805
Dec 29 '18 at 20:50
add a comment |
1 Answer
1
active
oldest
votes
Posting here what I discovered if it helps others.
Mysql docs state that table names should be lowercase when using InnoDB.
If you are using InnoDB tables, you should set this variable to 1 on all platforms to force names to be converted to lowercase.
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_lower_case_table_names
Here are changes made to eliminate extra table creation and render the diagram with relationship connections:
- Table names snake case plural
- Table column names and their references snake case singular
- Model and name PascalCase singular
- Model attribute names camelCase with 'field' snake case singular
- Model created with option 'tableName' snake case plural
- Model created with option 'underscored' true
- Model association with 'as' alias and 'foreignKey' snake case
Migration example:
'use strict';
module.exports = {
up: (queryInterface, Sequelize) => {
return queryInterface.createTable('addresses', {
id: {
allowNull: false,
autoIncrement: true,
primaryKey: true,
type: Sequelize.INTEGER
},
user_id: {
allowNull: false,
type: Sequelize.INTEGER,
references: {
model: 'users',
key: 'id'
}
},
street1: {
allowNull: false,
type: Sequelize.STRING(50)
}
created_at: {
allowNull: false,
type: Sequelize.DATE
},
updated_at: {
allowNull: false,
type: Sequelize.DATE
},
deleted_at: {
allowNull: true,
defaultValue: null,
type: Sequelize.DATE
}
})
},
down: (queryInterface, Sequelize) => {
return queryInterface.dropTable('addresses');
}
};
Model example:
'use strict';
module.exports = (sequelize, DataTypes) => {
const Address = sequelize.define('Address', {
userId: {
type: DataTypes.INTEGER,
field: 'user_id',
allowNull: false,
validate: {
notNull(value) {
if (value == null) {
throw new Error('Missing user id')
}
}
}
},
street1: {
type: DataTypes.STRING,
field: 'street1',
validate: {
notEmpty: true
}
}
}, {
tableName: 'addresses',
paranoid: true,
underscored: true
});
Address.associate = function(models) {
Address.belongsTo(models.User, {
as: 'user',
foreignKey: 'user_id'
})
}
return Address;
};
answered Jan 6 at 3:31
user1192805user1192805
365617
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53972064%2fsequelize-or-mysql-creates-extra-tables-table-connections-missing-in-eer-diagra%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Posting here what I discovered if it helps others.
Mysql docs state that table names should be lowercase when using InnoDB.
If you are using InnoDB tables, you should set this variable to 1 on all platforms to force names to be converted to lowercase.
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_lower_case_table_names
Here are changes made to eliminate extra table creation and render the diagram with relationship connections:
- Table names snake case plural
- Table column names and their references snake case singular
- Model and name PascalCase singular
- Model attribute names camelCase with 'field' snake case singular
- Model created with option 'tableName' snake case plural
- Model created with option 'underscored' true
- Model association with 'as' alias and 'foreignKey' snake case
Migration example:
'use strict';
module.exports = {
up: (queryInterface, Sequelize) => {
return queryInterface.createTable('addresses', {
id: {
allowNull: false,
autoIncrement: true,
primaryKey: true,
type: Sequelize.INTEGER
},
user_id: {
allowNull: false,
type: Sequelize.INTEGER,
references: {
model: 'users',
key: 'id'
}
},
street1: {
allowNull: false,
type: Sequelize.STRING(50)
}
created_at: {
allowNull: false,
type: Sequelize.DATE
},
updated_at: {
allowNull: false,
type: Sequelize.DATE
},
deleted_at: {
allowNull: true,
defaultValue: null,
type: Sequelize.DATE
}
})
},
down: (queryInterface, Sequelize) => {
return queryInterface.dropTable('addresses');
}
};
Model example:
'use strict';
module.exports = (sequelize, DataTypes) => {
const Address = sequelize.define('Address', {
userId: {
type: DataTypes.INTEGER,
field: 'user_id',
allowNull: false,
validate: {
notNull(value) {
if (value == null) {
throw new Error('Missing user id')
}
}
}
},
street1: {
type: DataTypes.STRING,
field: 'street1',
validate: {
notEmpty: true
}
}
}, {
tableName: 'addresses',
paranoid: true,
underscored: true
});
Address.associate = function(models) {
Address.belongsTo(models.User, {
as: 'user',
foreignKey: 'user_id'
})
}
return Address;
};
answered Jan 6 at 3:31
user1192805user1192805
365617
add a comment |
Posting here what I discovered if it helps others.
Mysql docs state that table names should be lowercase when using InnoDB.
If you are using InnoDB tables, you should set this variable to 1 on all platforms to force names to be converted to lowercase.
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_lower_case_table_names
Here are changes made to eliminate extra table creation and render the diagram with relationship connections:
- Table names snake case plural
- Table column names and their references snake case singular
- Model and name PascalCase singular
- Model attribute names camelCase with 'field' snake case singular
- Model created with option 'tableName' snake case plural
- Model created with option 'underscored' true
- Model association with 'as' alias and 'foreignKey' snake case
Migration example:
'use strict';
module.exports = {
up: (queryInterface, Sequelize) => {
return queryInterface.createTable('addresses', {
id: {
allowNull: false,
autoIncrement: true,
primaryKey: true,
type: Sequelize.INTEGER
},
user_id: {
allowNull: false,
type: Sequelize.INTEGER,
references: {
model: 'users',
key: 'id'
}
},
street1: {
allowNull: false,
type: Sequelize.STRING(50)
}
created_at: {
allowNull: false,
type: Sequelize.DATE
},
updated_at: {
allowNull: false,
type: Sequelize.DATE
},
deleted_at: {
allowNull: true,
defaultValue: null,
type: Sequelize.DATE
}
})
},
down: (queryInterface, Sequelize) => {
return queryInterface.dropTable('addresses');
}
};
Model example:
'use strict';
module.exports = (sequelize, DataTypes) => {
const Address = sequelize.define('Address', {
userId: {
type: DataTypes.INTEGER,
field: 'user_id',
allowNull: false,
validate: {
notNull(value) {
if (value == null) {
throw new Error('Missing user id')
}
}
}
},
street1: {
type: DataTypes.STRING,
field: 'street1',
validate: {
notEmpty: true
}
}
}, {
tableName: 'addresses',
paranoid: true,
underscored: true
});
Address.associate = function(models) {
Address.belongsTo(models.User, {
as: 'user',
foreignKey: 'user_id'
})
}
return Address;
};
answered Jan 6 at 3:31
user1192805user1192805
365617
add a comment |
Posting here what I discovered if it helps others.
Mysql docs state that table names should be lowercase when using InnoDB.
If you are using InnoDB tables, you should set this variable to 1 on all platforms to force names to be converted to lowercase.
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_lower_case_table_names
Here are changes made to eliminate extra table creation and render the diagram with relationship connections:
- Table names snake case plural
- Table column names and their references snake case singular
- Model and name PascalCase singular
- Model attribute names camelCase with 'field' snake case singular
- Model created with option 'tableName' snake case plural
- Model created with option 'underscored' true
- Model association with 'as' alias and 'foreignKey' snake case
Migration example:
'use strict';
module.exports = {
up: (queryInterface, Sequelize) => {
return queryInterface.createTable('addresses', {
id: {
allowNull: false,
autoIncrement: true,
primaryKey: true,
type: Sequelize.INTEGER
},
user_id: {
allowNull: false,
type: Sequelize.INTEGER,
references: {
model: 'users',
key: 'id'
}
},
street1: {
allowNull: false,
type: Sequelize.STRING(50)
}
created_at: {
allowNull: false,
type: Sequelize.DATE
},
updated_at: {
allowNull: false,
type: Sequelize.DATE
},
deleted_at: {
allowNull: true,
defaultValue: null,
type: Sequelize.DATE
}
})
},
down: (queryInterface, Sequelize) => {
return queryInterface.dropTable('addresses');
}
};
Model example:
'use strict';
module.exports = (sequelize, DataTypes) => {
const Address = sequelize.define('Address', {
userId: {
type: DataTypes.INTEGER,
field: 'user_id',
allowNull: false,
validate: {
notNull(value) {
if (value == null) {
throw new Error('Missing user id')
}
}
}
},
street1: {
type: DataTypes.STRING,
field: 'street1',
validate: {
notEmpty: true
}
}
}, {
tableName: 'addresses',
paranoid: true,
underscored: true
});
Address.associate = function(models) {
Address.belongsTo(models.User, {
as: 'user',
foreignKey: 'user_id'
})
}
return Address;
};
answered Jan 6 at 3:31
user1192805user1192805
365617
Posting here what I discovered if it helps others.
Mysql docs state that table names should be lowercase when using InnoDB.
If you are using InnoDB tables, you should set this variable to 1 on all platforms to force names to be converted to lowercase.
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_lower_case_table_names
Here are changes made to eliminate extra table creation and render the diagram with relationship connections:
- Table names snake case plural
- Table column names and their references snake case singular
- Model and name PascalCase singular
- Model attribute names camelCase with 'field' snake case singular
- Model created with option 'tableName' snake case plural
- Model created with option 'underscored' true
- Model association with 'as' alias and 'foreignKey' snake case
Migration example:
'use strict';
module.exports = {
up: (queryInterface, Sequelize) => {
return queryInterface.createTable('addresses', {
id: {
allowNull: false,
autoIncrement: true,
primaryKey: true,
type: Sequelize.INTEGER
},
user_id: {
allowNull: false,
type: Sequelize.INTEGER,
references: {
model: 'users',
key: 'id'
}
},
street1: {
allowNull: false,
type: Sequelize.STRING(50)
}
created_at: {
allowNull: false,
type: Sequelize.DATE
},
updated_at: {
allowNull: false,
type: Sequelize.DATE
},
deleted_at: {
allowNull: true,
defaultValue: null,
type: Sequelize.DATE
}
})
},
down: (queryInterface, Sequelize) => {
return queryInterface.dropTable('addresses');
}
};
Model example:
'use strict';
module.exports = (sequelize, DataTypes) => {
const Address = sequelize.define('Address', {
userId: {
type: DataTypes.INTEGER,
field: 'user_id',
allowNull: false,
validate: {
notNull(value) {
if (value == null) {
throw new Error('Missing user id')
}
}
}
},
street1: {
type: DataTypes.STRING,
field: 'street1',
validate: {
notEmpty: true
}
}
}, {
tableName: 'addresses',
paranoid: true,
underscored: true
});
Address.associate = function(models) {
Address.belongsTo(models.User, {
as: 'user',
foreignKey: 'user_id'
})
}
return Address;
};
answered Jan 6 at 3:31
user1192805user1192805
365617
answered Jan 6 at 3:31
user1192805user1192805
365617
answered Jan 6 at 3:31
user1192805user1192805
365617
answered Jan 6 at 3:31
user1192805user1192805
365617
365617
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53972064%2fsequelize-or-mysql-creates-extra-tables-table-connections-missing-in-eer-diagra%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
I think you need to define the references (constraints) in the migration as well if you are using migrations, are you using migrations ?
– Mohdule
Dec 29 '18 at 18:19
Reference is in migration. I'll update question
– user1192805
Dec 29 '18 at 18:48
Awesome, you see creating the table and the constraint is handled by your migration the model is just the interface to get that data, this is weird though, your migration and
referencesclause is correct, maybe you forgot to define an id on the Users table or something ? Can you maybe post the fullUsers migrationand fullAddresses migrationhere– Mohdule
Dec 29 '18 at 19:25
Updated to include full models and migrations
– user1192805
Dec 29 '18 at 20:50