Bad date format with SQL database

[sylvainlap]

With SQL database (PostgreSQL), feathers-auth-management doest not work out of the box, because of the use of Date.now() instead of new Date().

I got errors like this:

{
  "name": "BadRequest",
  "message": " date/time field value out of range: \"1516020161065\"",
  "code": 400,
  "className": "bad-request",
  "errors": {}
}

Maybe you should consider using new Date() instead of Date.now(), to use actual Date.

Regards.

[eric-burel]

Hi, I got this error error: RequestError: Operand type clash: bigint is incompatible with date when using the date type in SQL Server and RequestError: Arithmetic overflow error converting expression to data type datetime. when using the timestamp type or datetime type.

I am not used to SQL but I guess it is the same issue. Any worlaround yet?

Edit: I switched to a BigInteger for now, I can generate the link but I am not sure that it will respect the expiration date anymore, I am currently testing it.

[eddyystop]

You can write a hook to reformat the date on write, and another to reconvert on read.

[eddyystop]

These issues are handled by the conversionSql hook provided in the a-l-m rewrite. Full details are at https://github.com/feathers-plus/authentication-local-management/blob/master/misc/upgrading.md

Please make any further comments in a-l-m. Thanks.

[morenoh149]

if anyone ever tries to use knex/objection and Postgres for this lib. Here's a migration that finally worked for me

// db/migrations/1234_add_auth_to_users.js
exports.up = function(knex, Promise) {
  return knex.schema.table("users", function(table) {
    table.string("phoneNumber");
    table.boolean("isVerified");
    table.string("verifyToken");
    table.string("verifyShortToken");
    table.bigInteger("verifyExpires");
    table.jsonb("verifyChanges");
    table.string("resetToken");
    table.bigInteger("resetExpires");
  });
};

exports.down = function(knex, Promise) {
  return knex.schema.table("users", function(table) {
    table.dropColumn("phoneNumber");
    table.dropColumn("isVerified");
    table.dropColumn("verifyToken");
    table.dropColumn("verifyShortToken");
    table.dropColumn("verifyExpires");
    table.dropColumn("verifyChanges");
    table.dropColumn("resetToken");
    table.dropColumn("resetExpires");
  });
};

[eddyystop]

Read this about coercing data types for Sequelize and Knex https://github.com/feathers-plus/authentication-local-management/blob/master/misc/upgrading.md

You can do so using a hook similar to this https://github.com/feathers-plus/authentication-local-management/blob/master/src/hooks/conversion-sql.js

[hashcutdev]

I realize this is a few years later but I ran into the same problem and have a simpler fix. The above solution from @morenoh149 works but then the data is stored as an int rather than the proper datetime format. I'm using Objection, and it was easy to do the data conversion in the Objection model file thusly:

users.model.js:

...
class users extends Model {
    static get tableName() {
        return 'users';
    }

    static get jsonSchema() {
        return {
            type: 'object',
            required: ['password'],

            properties: {
                email: { type: ['string', 'null'] },
                password: 'string',

                auth0Id: { type: 'string' },

                googleId: { type: 'string' },

                facebookId: { type: 'string' },

                twitterId: { type: 'string' },
                isVerified: { type: 'boolean' },
                verifyToken: { type: 'string' },
                verifyExpires: { type: 'date' },
                verifyChanges: { type: 'object' },
                resetToken: { type: 'string' },
                resetExpires: { type: 'date' },
            },
        };
    }

    $beforeInsert() {
        // eslint-disable-next-line no-multi-assign
        this.createdAt = this.updatedAt = new Date().toISOString();
        if (typeof(this.resetExpires) === 'number') {
            this.resetExpires = new Date(this.resetExpires).toISOString();
        }
        if (typeof(this.verifyExpires) === 'number') {
            this.verifyExpires = new Date(this.verifyExpires).toISOString();
        }
    }

    $beforeUpdate() {
        this.updatedAt = new Date().toISOString();
        if (typeof(this.resetExpires) === 'number') {
            this.resetExpires = new Date(this.resetExpires).toISOString();
        }
        if (typeof(this.verifyExpires) === 'number') {
            this.verifyExpires = new Date(this.verifyExpires).toISOString();
        }
    }
}
...

The main part is the $beforeInsert and $beforeUpdate functions, which check to see if the verifyExpires or resetExpires fields are numbers, and then recasts them to the proper Date value. This works and leaves your postgresql database with the proper field types.