sequelize: findOrCreate fails concurrently when unique attribute contains comma in value. Postgres unique constraint error incorrectly parses error message 23505 when the value contains a ','.

What you are doing?

Trying to concurrently use findOrCreate when an attribute set to unique, would contain a comma.

'use strict';

const Sequelize = require('sequelize');

//first connect to the database
const database = new Sequelize('database', 'user', 'password', {
    dialect: 'postgres',
    host: '127.0.0.1',
    port: 5432,

    pool: {
        max: 5,
        min: 0,
        idle: 10000
    },
});

return database.authenticate()
.then(() => {
    //success connecting,
    console.log('database connected');
    return database;
})
.then(database => {
    const containerModel = database.define('TestContainer', {
        name: {
            type: Sequelize.DataTypes.STRING(32),
            allowNull: false,
            unique: 'name',
        },
    });

    return database.sync({
        alter: true,
        force: false,
    })
    .then(database => {
        return database.transaction({
            autoCommit: false,
        })
        .then(transaction => {
            return Promise.all(['hello, world', 'hello, world'].map(name => {
                console.log(`findOrCreate({name: '${name}'})`);
                return containerModel.findOrCreate({
                    where: {
                        name: name,
                    },
                    defaults: {
                        name: name,
                    },
                    transaction: transaction,
                    returning: true,
                });
            }))
            .then(() => {
                transaction.rollback();
            })
            .catch(() => {
                transaction.rollback();
            });
        });
    });
})
.catch(err => {
    //failed to connect
    console.error(err);
    process.exit(1);
});

What do you expect to happen?

concurrent operations to succeed without fail!

What is actually happening?

So lets say i set attribute of my Model attribute ‘name’ to unique. If i were to try and findOrCreate multiple of these models concurrently, and there is a unique validation error, then it fails!

It fails because the findOrCreate checks the fields in the err object generated by postgres/query.js. The parsing of said fields is done from the error message generated by postgres. In the postgres/query.js it is splitting the regex matched values by ‘,’ for both the field name and value. This incorrectly truncates the field value if it contained a ‘,’. When the findOrCreate attempts to check for concurrency, it fails because the reported value from the error and the value its expecting do not match!

Get an error of

TestContainer#findOrCreate: value used for name was not equal for both the find and the create calls, ‘hello, world’ vs ‘hello’

Dialect: postgres Database version: 9.6 Sequelize version: master