Sails: Datetime column type on attribute is broken in sails-mysql

[megakoresh]

Waterline version:0.13-rc9
Node version:7.9.0
NPM version:5.0.2
Operating system:Windows 10 Professional x64

---

Since removal of datetime attribute type, the console suggested I set the type to string and columnType to 'datetime'. The data is storged with that config, but my console is spammed with the following:

Warning: After transforming columnNames back to attribute names, a record
in the result has a value with an unexpected data type for property `startDate`.
The corresponding attribute declares `type: 'string'` but instead
of that, the actual value is:
    ```
    2017-06-04T17:58:34.000Z
    ```

Attribute definition in question:

...
startDate: {
  type: 'string',
  columnType: 'datetime'
},
endDate: {
  type: 'string',
  columnType: 'datetime'
},
...

This is either a bug or a serious design flaw.

[sailsbot]

@megakoresh Thanks for posting, we'll take a look as soon as possible.

---

For help with questions about Sails, click here. If you’re interested in hiring @sailsbot and her minions in Austin, click here.

[billyshena]

@megakoresh 'datetime' attribute does no longer exist. You should use a "bigint" field to store an unix timestamp instead :)

[megakoresh]

Datetime attribute absolutely does exist and is not going away any time soon. Datetime attribute type does not exist anymore, which is why a string is used.

Update: When I set the attributes to

{
  type: "ref",
  columnType: "datetime"
}

the tables are created with the correct format and error messages disappear, however migrate: ' alter' breaks the app completely with mysql adapter by throwing a mesaage:
Error: One of the values being set has an attribute type ofrefbut the value is not a Buffer. This adapter only accepts buffers for typeref.
When I enter data I can wrap it in Buffer.from(...), however it appears that waterline does not do the same thing.

[megakoresh]

A temporary solution would be to allow an option like meta.autoWrap in the attribute definition. Then here:
https://github.com/balderdashy/sails-mysql/blob/master/helpers/private/query/pre-process-record.js#L87
check the options for this attribute and automatically wrap value in Buffer.from(value). I don't see any problems with this, as long as it's not on by default.

UPDATE: Just tested with

if (attrDef.type === 'ref' && _.has(record, columnName) && !_.isNull(record[columnName])) {
        var isBuffer = record[columnName] instanceof Buffer;
        if (!isBuffer) {
          if(attrDef.meta.autoWrap === true){
            try {
              record[columnName] = Buffer.from(record[columnName]);
            } catch (err){
              throw new Error("meta.autoWrap was true for a 'ref' type of record, but Buffer.from(recordValue) has failed with the following message: "+err.message);
            }
          } else {
          throw new Error('One of the values being set has an attribute type of `ref` but the value is not a Buffer. This adapter only accepts buffers for type `ref`. If you would like to store other types of data perhaps use type `json`.');
          }
        }
      }

in above linked file and

{
  meta: {
    autoWrap: true
  }
  type: 'ref',
  columnType: 'datetime'
}

Works fine for now.

[megakoresh]

UPDATE:
The issue is caused by the native mysqljs dependency of machinepack-mysql that adapter uses, which converts any MySQL/MariaDB columns with date format into Javascript Date objects.

Waterline has no more the 'datetime' type, so, while inserting data works fine, fetching produces the warnings, since waterline is getting Date objects instead of expected string, jsons or numbers.

[luislobo]

I think this PR is related with this issue
https://github.com/balderdashy/waterline/pull/1496

[Josebaseba]

@luislobo mix fix is just for the autoCreatedAt/autoUpdatedAt cases... But more or less, yeah, they're related.

[sailsbot]

@megakoresh,@sailsbot,@billyshena,@luislobo,@Josebaseba: Hello, I'm a repo bot-- nice to meet you!

It has been 30 days since there have been any updates or new comments on this page. If this issue has been resolved, feel free to disregard the rest of this message and simply close the issue if possible. On the other hand, if you are still waiting on a patch, please post a comment to keep the thread alive (with any new information you can provide).

If no further activity occurs on this thread within the next 3 days, the issue will automatically be closed.

Thanks so much for your help!

[megakoresh]

Considering the fact that issues and pull requests on anything sails-related can remain hanging for a year or more, it seems highly inappropriate to auto-close them in a month.

I might open a PR with the fix I proposed since I have been running with it for a month, but it'll likely also remain open for much more than a month.

UPDATE:
Ok I opened the PR.

[KB1RMA]

Thanks for the fix on this. I've been running into this issue as well and it's starting to become a big hindrance to the project.

Has anyone taken a look at your PR? I've been using it as my own patch, but it would be nice to have this merged in.

Thanks!

[sgress454]

As of sails-mysql v1.0.0-15, you can use type: 'ref' with columnType: 'datetime' to pass JavaScript date objects between Sails and MySQL unmolested. Thanks @megakoresh for lighting a fire on this one.