Sails: Using PostgreSQL timestamp column with a "string" model attribute displays errors

[magiksd]

Sails version: 1.0.0-36
Node version: 8.2.1
NPM version: 5.3.0
Operating system: Debian 9.0

---

When using a "timestamp" column in PostgreSQL and mapping it to a "string" model attribute in Sails v1, the validation check fails when fetching data because the postgres-date module parse date columns and converts them to a javascript Date object which is not a string :

Warning: After transforming columnNames back to attribute names, a record
in the result has a value with an unexpected data type for property `expires_at`.
The corresponding attribute declares `type: 'string'` but instead
of that, the actual value is:
2017-07-18T09:38:56.000Z

I've made a pull request to fix the problem, but this is not perfect because it convert back the Date object to a string because postgres-date converted it to a Date object, and maybe the developer will convert it again to a ... Date object. So maybe there is another solution like bypassing validations if a Date object is detected for a "string" model attribute (but it will be more dirty IMO).

On a related topic, could you add a "isDate" validation to allow Date strings ? I had to put a custom validation when migrating to Sails v1 :

date: {
  type: 'string',
  required: true,
  custom: value => Date.parse(value)
}

Thanks !

[sailsbot]

@magiksd 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.

[sgress454]

Hi @magiksd, could you try setting the type to json instead of string? Since Javascript dates are JSON-serializable, I think this should pass through those timestamp columns without issue.

[sgress454]

@magiksd never mind; I tested using json and it's problematic. We just published sails-postgresqlv1.0.0-11 which will let you use timestamp columns with the ref attribute type, so instead of stringifying the dates you'll just pass Date object back and forth:

date: {
  type: 'ref',
  required: true,
  columnType: 'timestamp'
}

When you do .find(), the returned records will have Javascript Date objects for their date properties.

[magiksd]

@sgress454 thanks for the update. I have upgraded and tested with your parameters but it still don't work with an "autoCreatedAt" column :

Error: The attribute `created_at` on the `item` model contains both an `autoCreatedAt` flag
but is not a proper type. Auto timestamps must have either a `number` or `string` type set.

[sgress454]

@magiksd You might need to just wipe out node_modules and re-install everything; there was a related change in the waterline-schema library as well.

[magiksd]

@sgress454 I just tried, deleted my entire node_modules folder, did a npm cache clean --force and then installed modules again, but still getting an error :

Warning: After transforming columnNames back to attribute names, a record
in the result has a value with an unexpected data type for property `created_at`.
The model's `created_at` attribute declares itself an auto timestamp with
`type: 'ref'`, but instead of a valid timestamp, the actual value
in the record is:
2016-11-20T10:19:53.000Z

Maybe because you fixed it in Waterline v0.13.0-rc12 but sails-hook-orm (v2.0.0-20) still reference v0.13.0-rc11 in its dependencies ?

By the way, theses are my sails dependencies (maybe one thing is missing ?) :

    "sails": "1.0.0-36",
    "sails-hook-orm": "2.0.0-20",
    "sails-postgresql": "1.0.0-11",

[sgress454]

Maybe because you fixed it in Waterline v0.13.0-rc12 but sails-hook-orm (v2.0.0-20) still reference v0.13.0-rc11 in its dependencies ?

Ah, yep, that would do it...gonna see about bumping Waterline to a version that doesn't include rc, so that we can set sails-hook-orm's dependency back to a semver range. In the meantime if you just do npm install [email protected] I think it will allow you to test, since it'll go at the top level of node_modules anyway.

[sgress454]

@magiksd Ok -- published Waterline 0.13.1-0 and sails-hook-orm 2.0.0-21. The ORM hook should pick up new patch releases of Waterline going forward. When you get a chance, try again -- sorry for the hassle!