balderdashy / balderdashy/sails

Uniqueness constraints fail when using non-english locale in PostgreSQL

Open
#4,538 5 comments 0 reactions 0 assignees View on GitHub
resolved try this out please
Dominant language
JavaScript
Stars
22.8k
Forks
1.9k
PR merge metrics
No merged PRs in 30d

Description

**Sails version**: 1.0.2
**Node version**: 8.12.0
**NPM version**: 6.4.1
**DB adapter name**: postgresql
**DB adapter version**: 1.0.1
**Operating system**: Ubuntu 18.04


Using the official postgres adapter with a simple model like this:

### User.js
```javascript
module.exports = {

tableName: 'user',

attributes: {
emailAddress: {
type: 'string',
required: true,
unique: true,
isEmail: true,
maxLength: 200
}
}
```

I'm getting an error when trying to insert a new user with an already registered email:

```javascript
await User.create(Object.assign({
emailAddress: 'test@domain.tld'
}));

await User.create(Object.assign({
emailAddress: 'test@domain.tld'
}))
.intercept('E_UNIQUE', 'emailAlreadyInUse')
.fetch();
```
```
TypeError: Cannot read property '1' of null
at Object.parseNativeQueryError (/home/projectpath/node_modules/machinepack-postgresql/machines/parse-native-query-error.js:89:38)
at wrapper (/home/projectpath/node_modules/@sailshq/lodash/lib/index.js:3275:19)
at Deferred._handleExec (/home/projectpath/node_modules/machine/lib/private/help-build-machine.js:1076:19)
at Deferred.exec (/home/projectpath/node_modules/parley/lib/private/Deferred.js:286:10)
at Deferred.now (/home/projectpath/node_modules/parley/lib/private/Deferred.js:592:8)
at Deferred.now (/home/projectpath/node_modules/machine/lib/private/help-build-machine.js:1317:51)
at Deferred.execSync (/home/projectpath/node_modules/machine/lib/private/help-build-machine.js:1530:23)
at Object.queryFailed (/home/projectpath/node_modules/sails-postgresql/helpers/private/query/run-query.js:59:12)
at /home/projectpath/node_modules/machine/lib/private/help-build-machine.js:1509:52
at proceedToFinalAfterExecLC (/home/projectpath/node_modules/parley/lib/private/Deferred.js:1149:14)
at proceedToInterceptsAndChecks (/home/projectpath/node_modules/parley/lib/private/Deferred.js:909:12)
at proceedToAfterExecSpinlocks (/home/projectpath/node_modules/parley/lib/private/Deferred.js:841:10)
at /home/projectpath/node_modules/parley/lib/private/Deferred.js:303:7
at /home/projectpath/node_modules/machine/lib/private/help-build-machine.js:952:35
at Function.handlerCbs.(anonymous function) [as queryFailed] (/home/projectpath/node_modules/machine/lib/private/help-build-machine.js:945:28)
at Query.query (/home/projectpath/node_modules/machinepack-postgresql/machines/send-native-query.js:116:22)
at Query.handleError (/home/projectpath/node_modules/pg/lib/query.js:131:17)
at Connection. (/home/projectpath/node_modules/pg/lib/client.js:180:26)
at emitOne (events.js:116:13)
at Connection.emit (events.js:211:7)
at Socket. (/home/projectpath/node_modules/pg/lib/connection.js:121:12)
at emitOne (events.js:116:13)
at Socket.emit (events.js:211:7)
at addChunk (_stream_readable.js:263:12)
at readableAddChunk (_stream_readable.js:250:11)
at Socket.Readable.push (_stream_readable.js:208:10)
at TCP.onread (net.js:601:20)
```

Looking into ```parse-native-query-error.js```, I've found that the error is categorized by using a regex on the original message:

https://github.com/sailshq/machinepack-postgresql/blob/1e9b1f8d5dbdca74add8340572bac60efd3cef81/machines/parse-native-query-error.js
```javascript
if (_.isString(err.detail)) {
var matches = err.detail.match(/Key \((.*)\)=\((.*)\) already exists\.$/);
var matchedAttrName = matches[1].replace(/^\"(.*)\"/g, '$1');
footprint.keys.push(matchedAttrName);
}
```

The regex fails if the postgres locale uses a different language. For example, the message in Spanish installations is like this:

```
Ya existe la llave ("emailAddress")=(test@domain.tld).
```
Probably using just ```\((.*)\)=\((.*)\)``` in the regex will work, but I've not tried. A quick workaround is to edit the postgres setting ```lc_messages``` which in Ubuntu is located at ```/etc/postgresql/VERSION/main/postgresql.conf```:

```conf
lc_messages = 'en_US.UTF-8' # locale for system error message
```

Contributor guide

Open the contributing guide

Research direction

Start with machinepack-postgresql/machines/parse-native-query-error.js and inspect how the localized PostgreSQL detail is parsed. Reproduce the duplicate email insertion with a non-English PostgreSQL locale; done means it no longer throws a TypeError and the duplicate is reported through the E_UNIQUE interception.

Written by the indexing model from the issue text.

Assessment

Tech stack
javascript, node.js, postgresql
Domain
backend, databases
Issue type
Bug
Difficulty
2/5
Estimated time
1-3 hours
Activity status
Stale
Clarity
Clearly specified
Newbie friendliness
42/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.