SequelizeJS allows UNIQUE field to be duplicated
0
in my web app (NodeJS + ExpressJS + SequelizeJS) i have defined the model User:
let User = connection.define('user', {
username: {
type: Sequelize.STRING(40),
unique: true,
allowNull: false,
validate: {
notEmpty: true,
min: 4,
}
},
email: {
type: Sequelize.STRING,
unique: true,
allowNull: false,
notEmpty: true,
validate: {
isEmail: true
}
},
password: {
type: Sequelize.STRING,
allowNull: false,
notEmpty: true,
validate: {
min: 8,
}
},
name: {
type: Sequelize.STRING(50),
validate: {
min: 2
}
},
surname: {
type: Sequelize.STRING(50),
validate: {
min: 3
}
},
bio: Sequelize.TEXT(140),
lastLogin: Sequelize.DATE,
dateJoined: Sequelize.DATE,
active: {
type: Sequelize.BOOLEAN,
defaultValue: false
}
});
I want both username and email to be unique so that no one can reuse an existing email or an existing username.
So i tested it and i noticed that the query built is the following:
Executing (default):
CREATE TABLE IF NOT EXISTS `user`(
`id` INTEGER PRIMARY KEY AUTOINCREMENT,
`username` VARCHAR(40) NOT NULL UNIQUE,
`email` VARCHAR(255) NOT NULL UNIQUE,
`password` VARCHAR(255) NOT NULL,
`nome` VARCHAR(50),
`cognome` VARCHAR(50),
`bio` TEXT,
`lastLogin` DATETIME,
`dateJoined` DATETIME,
`active` TINYINT(1) DEFAULT0,
`createdAt` DATETIME NOT NULL,
`updatedAt` DATETIME NOT NULL);
And the query looks fine.
Then i decided to make the same insert query (whit the same username and email) every time my node server gets on, to test if the NOT NULL constraint is preserved.
User.create({
username: 'MYUSERNAME',
email: 'MYEMAIL@GMAIL.COM',
password: 'MYPASSWORD'
})
.then(newUser => {
console.log('Insertion OK, username:', newUser.username);
})
.catch(error => {
console.log(error);
});
The console output every time i restart my server:
Insertion OK, username: MYUSERNAME
And if i check the database records, in the user table i see multiple rows with the same data in the same fields.
-----------------------------------------------
| Username | Email | Password |
-----------------------------------------------
| MYUSERNAME | MYEMAIL@GMAIL.COM | MYPASSWORD |
-----------------------------------------------
| MYUSERNAME | MYEMAIL@GMAIL.COM | MYPASSWORD |
-----------------------------------------------
| MYUSERNAME | MYEMAIL@GMAIL.COM | MYPASSWORD |
-----------------------------------------------
Why doesn't it preserve the UNIQUE constraint?
I'm using sqlite3 file for the database.
node.js database sqlite3 constraints sequelize.js
asked Nov 23 '18 at 19:35
DamienzOnlyDamienzOnly
367
add a comment |
0
in my web app (NodeJS + ExpressJS + SequelizeJS) i have defined the model User:
let User = connection.define('user', {
username: {
type: Sequelize.STRING(40),
unique: true,
allowNull: false,
validate: {
notEmpty: true,
min: 4,
}
},
email: {
type: Sequelize.STRING,
unique: true,
allowNull: false,
notEmpty: true,
validate: {
isEmail: true
}
},
password: {
type: Sequelize.STRING,
allowNull: false,
notEmpty: true,
validate: {
min: 8,
}
},
name: {
type: Sequelize.STRING(50),
validate: {
min: 2
}
},
surname: {
type: Sequelize.STRING(50),
validate: {
min: 3
}
},
bio: Sequelize.TEXT(140),
lastLogin: Sequelize.DATE,
dateJoined: Sequelize.DATE,
active: {
type: Sequelize.BOOLEAN,
defaultValue: false
}
});
I want both username and email to be unique so that no one can reuse an existing email or an existing username.
So i tested it and i noticed that the query built is the following:
Executing (default):
CREATE TABLE IF NOT EXISTS `user`(
`id` INTEGER PRIMARY KEY AUTOINCREMENT,
`username` VARCHAR(40) NOT NULL UNIQUE,
`email` VARCHAR(255) NOT NULL UNIQUE,
`password` VARCHAR(255) NOT NULL,
`nome` VARCHAR(50),
`cognome` VARCHAR(50),
`bio` TEXT,
`lastLogin` DATETIME,
`dateJoined` DATETIME,
`active` TINYINT(1) DEFAULT0,
`createdAt` DATETIME NOT NULL,
`updatedAt` DATETIME NOT NULL);
And the query looks fine.
Then i decided to make the same insert query (whit the same username and email) every time my node server gets on, to test if the NOT NULL constraint is preserved.
User.create({
username: 'MYUSERNAME',
email: 'MYEMAIL@GMAIL.COM',
password: 'MYPASSWORD'
})
.then(newUser => {
console.log('Insertion OK, username:', newUser.username);
})
.catch(error => {
console.log(error);
});
The console output every time i restart my server:
Insertion OK, username: MYUSERNAME
And if i check the database records, in the user table i see multiple rows with the same data in the same fields.
-----------------------------------------------
| Username | Email | Password |
-----------------------------------------------
| MYUSERNAME | MYEMAIL@GMAIL.COM | MYPASSWORD |
-----------------------------------------------
| MYUSERNAME | MYEMAIL@GMAIL.COM | MYPASSWORD |
-----------------------------------------------
| MYUSERNAME | MYEMAIL@GMAIL.COM | MYPASSWORD |
-----------------------------------------------
Why doesn't it preserve the UNIQUE constraint?
I'm using sqlite3 file for the database.
node.js database sqlite3 constraints sequelize.js
asked Nov 23 '18 at 19:35
DamienzOnlyDamienzOnly
367
If you look at the definition of the table actually being used (open the database in the sqlite3 shell and run.schema user) does it have the unique constraints? You might be using an older version from before you added those (assuming you didn't have them from the start).
– Shawn
Nov 23 '18 at 20:02
Yes i noticed using DBrowser for SQLite that username field was not set to unique. i manually set it to unique and now everything works but how can i solve this? Once the query is correctly built i'm not supposed to touch the database anymore and change things manually...
– DamienzOnly
Nov 23 '18 at 20:12
Sqlite has a way to store a version number in a database and many ORMs will check this against the expected version and call an upgrade function to give you a chance to create or modify tables as needed (or you can do it manually). No idea how that would work with your toolchain though.
– Shawn
Nov 23 '18 at 20:22
add a comment |
0
0
0
in my web app (NodeJS + ExpressJS + SequelizeJS) i have defined the model User:
let User = connection.define('user', {
username: {
type: Sequelize.STRING(40),
unique: true,
allowNull: false,
validate: {
notEmpty: true,
min: 4,
}
},
email: {
type: Sequelize.STRING,
unique: true,
allowNull: false,
notEmpty: true,
validate: {
isEmail: true
}
},
password: {
type: Sequelize.STRING,
allowNull: false,
notEmpty: true,
validate: {
min: 8,
}
},
name: {
type: Sequelize.STRING(50),
validate: {
min: 2
}
},
surname: {
type: Sequelize.STRING(50),
validate: {
min: 3
}
},
bio: Sequelize.TEXT(140),
lastLogin: Sequelize.DATE,
dateJoined: Sequelize.DATE,
active: {
type: Sequelize.BOOLEAN,
defaultValue: false
}
});
I want both username and email to be unique so that no one can reuse an existing email or an existing username.
So i tested it and i noticed that the query built is the following:
Executing (default):
CREATE TABLE IF NOT EXISTS `user`(
`id` INTEGER PRIMARY KEY AUTOINCREMENT,
`username` VARCHAR(40) NOT NULL UNIQUE,
`email` VARCHAR(255) NOT NULL UNIQUE,
`password` VARCHAR(255) NOT NULL,
`nome` VARCHAR(50),
`cognome` VARCHAR(50),
`bio` TEXT,
`lastLogin` DATETIME,
`dateJoined` DATETIME,
`active` TINYINT(1) DEFAULT0,
`createdAt` DATETIME NOT NULL,
`updatedAt` DATETIME NOT NULL);
And the query looks fine.
Then i decided to make the same insert query (whit the same username and email) every time my node server gets on, to test if the NOT NULL constraint is preserved.
User.create({
username: 'MYUSERNAME',
email: 'MYEMAIL@GMAIL.COM',
password: 'MYPASSWORD'
})
.then(newUser => {
console.log('Insertion OK, username:', newUser.username);
})
.catch(error => {
console.log(error);
});
The console output every time i restart my server:
Insertion OK, username: MYUSERNAME
And if i check the database records, in the user table i see multiple rows with the same data in the same fields.
-----------------------------------------------
| Username | Email | Password |
-----------------------------------------------
| MYUSERNAME | MYEMAIL@GMAIL.COM | MYPASSWORD |
-----------------------------------------------
| MYUSERNAME | MYEMAIL@GMAIL.COM | MYPASSWORD |
-----------------------------------------------
| MYUSERNAME | MYEMAIL@GMAIL.COM | MYPASSWORD |
-----------------------------------------------
Why doesn't it preserve the UNIQUE constraint?
I'm using sqlite3 file for the database.
node.js database sqlite3 constraints sequelize.js
asked Nov 23 '18 at 19:35
DamienzOnlyDamienzOnly
367
in my web app (NodeJS + ExpressJS + SequelizeJS) i have defined the model User:
let User = connection.define('user', {
username: {
type: Sequelize.STRING(40),
unique: true,
allowNull: false,
validate: {
notEmpty: true,
min: 4,
}
},
email: {
type: Sequelize.STRING,
unique: true,
allowNull: false,
notEmpty: true,
validate: {
isEmail: true
}
},
password: {
type: Sequelize.STRING,
allowNull: false,
notEmpty: true,
validate: {
min: 8,
}
},
name: {
type: Sequelize.STRING(50),
validate: {
min: 2
}
},
surname: {
type: Sequelize.STRING(50),
validate: {
min: 3
}
},
bio: Sequelize.TEXT(140),
lastLogin: Sequelize.DATE,
dateJoined: Sequelize.DATE,
active: {
type: Sequelize.BOOLEAN,
defaultValue: false
}
});
I want both username and email to be unique so that no one can reuse an existing email or an existing username.
So i tested it and i noticed that the query built is the following:
Executing (default):
CREATE TABLE IF NOT EXISTS `user`(
`id` INTEGER PRIMARY KEY AUTOINCREMENT,
`username` VARCHAR(40) NOT NULL UNIQUE,
`email` VARCHAR(255) NOT NULL UNIQUE,
`password` VARCHAR(255) NOT NULL,
`nome` VARCHAR(50),
`cognome` VARCHAR(50),
`bio` TEXT,
`lastLogin` DATETIME,
`dateJoined` DATETIME,
`active` TINYINT(1) DEFAULT0,
`createdAt` DATETIME NOT NULL,
`updatedAt` DATETIME NOT NULL);
And the query looks fine.
Then i decided to make the same insert query (whit the same username and email) every time my node server gets on, to test if the NOT NULL constraint is preserved.
User.create({
username: 'MYUSERNAME',
email: 'MYEMAIL@GMAIL.COM',
password: 'MYPASSWORD'
})
.then(newUser => {
console.log('Insertion OK, username:', newUser.username);
})
.catch(error => {
console.log(error);
});
The console output every time i restart my server:
Insertion OK, username: MYUSERNAME
And if i check the database records, in the user table i see multiple rows with the same data in the same fields.
-----------------------------------------------
| Username | Email | Password |
-----------------------------------------------
| MYUSERNAME | MYEMAIL@GMAIL.COM | MYPASSWORD |
-----------------------------------------------
| MYUSERNAME | MYEMAIL@GMAIL.COM | MYPASSWORD |
-----------------------------------------------
| MYUSERNAME | MYEMAIL@GMAIL.COM | MYPASSWORD |
-----------------------------------------------
Why doesn't it preserve the UNIQUE constraint?
I'm using sqlite3 file for the database.
node.js database sqlite3 constraints sequelize.js
node.js database sqlite3 constraints sequelize.js
asked Nov 23 '18 at 19:35
DamienzOnlyDamienzOnly
367
asked Nov 23 '18 at 19:35
DamienzOnlyDamienzOnly
367
asked Nov 23 '18 at 19:35
DamienzOnlyDamienzOnly
367
asked Nov 23 '18 at 19:35
DamienzOnlyDamienzOnly
367
asked Nov 23 '18 at 19:35
DamienzOnlyDamienzOnly
367
367
If you look at the definition of the table actually being used (open the database in the sqlite3 shell and run.schema user) does it have the unique constraints? You might be using an older version from before you added those (assuming you didn't have them from the start).
– Shawn
Nov 23 '18 at 20:02
Yes i noticed using DBrowser for SQLite that username field was not set to unique. i manually set it to unique and now everything works but how can i solve this? Once the query is correctly built i'm not supposed to touch the database anymore and change things manually...
– DamienzOnly
Nov 23 '18 at 20:12
Sqlite has a way to store a version number in a database and many ORMs will check this against the expected version and call an upgrade function to give you a chance to create or modify tables as needed (or you can do it manually). No idea how that would work with your toolchain though.
– Shawn
Nov 23 '18 at 20:22
add a comment |
If you look at the definition of the table actually being used (open the database in the sqlite3 shell and run.schema user) does it have the unique constraints? You might be using an older version from before you added those (assuming you didn't have them from the start).
– Shawn
Nov 23 '18 at 20:02
Yes i noticed using DBrowser for SQLite that username field was not set to unique. i manually set it to unique and now everything works but how can i solve this? Once the query is correctly built i'm not supposed to touch the database anymore and change things manually...
– DamienzOnly
Nov 23 '18 at 20:12
Sqlite has a way to store a version number in a database and many ORMs will check this against the expected version and call an upgrade function to give you a chance to create or modify tables as needed (or you can do it manually). No idea how that would work with your toolchain though.
– Shawn
Nov 23 '18 at 20:22
If you look at the definition of the table actually being used (open the database in the sqlite3 shell and run
.schema user) does it have the unique constraints? You might be using an older version from before you added those (assuming you didn't have them from the start).– Shawn
Nov 23 '18 at 20:02
If you look at the definition of the table actually being used (open the database in the sqlite3 shell and run
.schema user) does it have the unique constraints? You might be using an older version from before you added those (assuming you didn't have them from the start).– Shawn
Nov 23 '18 at 20:02
Yes i noticed using DBrowser for SQLite that username field was not set to unique. i manually set it to unique and now everything works but how can i solve this? Once the query is correctly built i'm not supposed to touch the database anymore and change things manually...
– DamienzOnly
Nov 23 '18 at 20:12
Yes i noticed using DBrowser for SQLite that username field was not set to unique. i manually set it to unique and now everything works but how can i solve this? Once the query is correctly built i'm not supposed to touch the database anymore and change things manually...
– DamienzOnly
Nov 23 '18 at 20:12
Sqlite has a way to store a version number in a database and many ORMs will check this against the expected version and call an upgrade function to give you a chance to create or modify tables as needed (or you can do it manually). No idea how that would work with your toolchain though.
– Shawn
Nov 23 '18 at 20:22
Sqlite has a way to store a version number in a database and many ORMs will check this against the expected version and call an upgrade function to give you a chance to create or modify tables as needed (or you can do it manually). No idea how that would work with your toolchain though.
– Shawn
Nov 23 '18 at 20:22
add a comment |
0
active
oldest
votes
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
});
}
});
draft saved
draft discarded
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%2f53452134%2fsequelizejs-allows-unique-field-to-be-duplicated%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
draft saved
draft discarded
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.
draft saved
draft discarded
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%2f53452134%2fsequelizejs-allows-unique-field-to-be-duplicated%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
If you look at the definition of the table actually being used (open the database in the sqlite3 shell and run
.schema user) does it have the unique constraints? You might be using an older version from before you added those (assuming you didn't have them from the start).– Shawn
Nov 23 '18 at 20:02
Yes i noticed using DBrowser for SQLite that username field was not set to unique. i manually set it to unique and now everything works but how can i solve this? Once the query is correctly built i'm not supposed to touch the database anymore and change things manually...
– DamienzOnly
Nov 23 '18 at 20:12
Sqlite has a way to store a version number in a database and many ORMs will check this against the expected version and call an upgrade function to give you a chance to create or modify tables as needed (or you can do it manually). No idea how that would work with your toolchain though.
– Shawn
Nov 23 '18 at 20:22