Sequelize throws error on insert but insert succeeds.

[Joshua Chan]

Sequelize version 3.7.1

I'm trying to insert into a table, but I get an error

Unhandled rejection SequelizeDatabaseError: null value in column "firstName" violates not-null constraint

Nevertheless all columns are populated and the query succeeds.

Do I need to be concerned? Can I fix this? 

[Mick Hansen]

Unhandled rejections are because you're not handling the rejections with a catch handler.
If you are seeing an unhandled rejection that insert would not have worked.

--

Mick Hansen

@mhansendev
mhansen.io

[Joshua Chan]

I am including my code. I have a catch on the promise. Also, I'm sending all fields. The insert still succeeds, but I still get an error.

var bcrypt = require('bcrypt')
    , db = require('../db').sequelize
    , Account = db.model('Account')
    , User = db.model('User')
;


module.exports = function(req, res, next){


    bcrypt.genSalt(10, function(err, salt) {
        bcrypt.hash(req.body.password, salt, function(err, hash) {


            var account = Account.build({
                name:req.body.accountName
            });


            account.save()
                .then(function(){
                    var user = User.build({
                        firstName: req.body.firstName,
                        lastName: req.body.lastName,
                        email: req.body.email,
                        password: hash
                    })


                    user.setAccount(account);


                    return user.save()
                        .then(function(){


                            res.send({
                                id: user.id,
                                accountId: account.id
                            })
                        })
                        .catch(function(err){
                            next(err);
                        });
                })
                .catch(function(err){
                    next(err);
                })






        });
    });
}

[Mick Hansen]

You don't get an error if the insert actually succeeds - Do you have any hooks or similar?

Just for reference you only need the later catch handler.

[Joshua Chan]

Mick,

I get an error but the insert actual succeeds. The error is reported to the console but not from my code. I have no hooks.

Here's my migration:

'use strict';
module.exports = {
    up: function (queryInterface, Sequelize) {
        return queryInterface.createTable('Users', {
            id: {
                allowNull: false,
                autoIncrement: true,
                primaryKey: true,
                type: Sequelize.INTEGER
            },
            accountId: {
                type: Sequelize.INTEGER,
                references: "Accounts",
                referencesKey: "id",
                allowNull: false
            },
            firstName: {
                type: Sequelize.STRING,
                allowNull: false
            },
            lastName: {
                type: Sequelize.STRING,
                allowNull: false
            },
            email: {
                type: Sequelize.STRING,
                allowNull: false,
                unique: true
            },
            password: {
                type: Sequelize.STRING,
                allowNull: false
            },
            createdAt: {
                allowNull: false,
                type: Sequelize.DATE
            },
            updatedAt: {
                allowNull: false,
                type: Sequelize.DATE
            }
        });
    },
    down: function (queryInterface, Sequelize) {
        return queryInterface.dropTable('Users');
    }
};

And here's my model:

'use strict';
var bcrypt = require('bcrypt');

module.exports = function(sequelize, DataTypes) {
  var User = sequelize.define('User', {
    accountId: {
      type: DataTypes.INTEGER,
      references: "Accounts",
      referencesKey: "id",
      allowNull: false
    },
    firstName: {
      type:DataTypes.STRING,
      allowNull:false
    },
    lastName: {
      type:DataTypes.STRING,
      allowNull:false
    },
    email: {
      type:DataTypes.STRING,
      allowNull:false,
      unique: true
    },
    password: {
      type:DataTypes.STRING,
      allowNull:false
    }
  }, {
    classMethods: {
      associate: function(models) {
        User.belongsTo(models.Account, { foreignKey: 'accountId' })
      }
    },
    instanceMethods: {
      verifyPassword: function(password, done) {
        return bcrypt.compare(password, this.password, function(err, res) {
          return done(err, res);
        });
      }
    }
  });
  return User;
};



And firstName definitely isn't null in the code or after the write succeeds against the database. FYI I'm using Postgres 9.4.1 RDS.

[Mick Hansen]

SequelizeDatabaseError is thrown when we get an error from the database.
If the insert is failing in the database, it really shouldn't succeed.

I can't really say what's wrong, best guess is some parallel call that isn't hooked up properly to a promise chain/handler.

[Joshua Chan]

I'm including my log. 

listening at http://:::8288

Executing (default): INSERT INTO "Accounts" ("id","name","updatedAt","createdAt") VALUES (DEFAULT,'Test','2015-09-12 18:30:43.297 +00:00','2015-09-12 18:30:43.297 +00:00') RETURNING *;
Executing (default): INSERT INTO "Users" ("accountId","updatedAt","createdAt") VALUES (7,'2015-09-12 18:30:43.404 +00:00','2015-09-12 18:30:43.404 +00:00');
Executing (default): INSERT INTO "Users" ("id","accountId","firstName","lastName","email","password","createdAt","updatedAt") VALUES (DEFAULT,7,'first','last','em...@email.com','hashedPassword','2015-09-12 18:30:43.404 +00:00','2015-09-12 18:30:43.409 +00:00') RETURNING *;

Unhandled rejection SequelizeDatabaseError: null value in column "firstName" violates not-null constraint

My app starts and I immediately send a request to create the new records.It shows I create a new Accounts record and two attempts to create the Users record. But why? Did I do something wrong in my model definition?

[Joshua Chan]

Changing my code to this fixed the problem, but do you know why?

var bcrypt = require('bcrypt')

    , db = require('../db').sequelize
    , Account = db.model('Account')
    , User = db.model('User')

    , auth = require('../auth')

;


module.exports = function(req, res, next){


    bcrypt.genSalt(10, function(err, salt) {
        bcrypt.hash(req.body.password, salt, function(err, hash) {

            Account.create({
                name:req.body.accountName
            })
            .then(function(account){
                return User.create({

                    firstName: req.body.firstName,
                    lastName: req.body.lastName,
                    email: req.body.email,

                    password: hash,
                    accountId:account.id
                })
            })
            .then(function(user){
                return res.send({token:auth.createToken(user)});

[Mick Hansen]

I didn't notice the dangling `user.setAccount(account)` before, that would have been the issue.
You need to use `{save: false}` so that it doesn't attempt to create the user when you call setAccount (which it will since setAccount internally does a set & save).

[Joshua Chan]

Ok, but at the moment setAccount is called all the necessary information exists on the object. Why didn't it issue the insert with all o f my user fields?

[Mick Hansen]

It's made to work on an existing instance doing an atomic update as possible, use `{save: false}` and it should work.

On Mon, Sep 14, 2015 at 4:59 PM, Joshua Chan <joshua.be...@gmail.com> wrote:

Ok, but at the moment setAccount is called all the necessary information exists on the object. Why didn't it issue the insert with all o f my user fields?