sequelize: Custom datatype not working

The issue lays with classToInvokable, which doesn’t allow access to the actual constructor. The following code shows how we worked around that:

const DataTypes = require('sequelize/lib/data-types');
const { classToInvokable } = require('sequelize/lib/utils/classToInvokable');

// since DataTypes.ABSTRACT is wrapped with classToInvokable(), we can't get to
// the actual class directly, so use this indirection
const ABSTRACT = DataTypes.ABSTRACT.prototype.constructor;

class Custom extends ABSTRACT {
  static key = 'CUSTOM';

  toSql() {
    return 'BIGINT(20) UNSIGNED';
  }

  ...
}
DataTypes.CUSTOM = classToInvokable(Custom);

This issue has been automatically marked as stale because it has been open for 14 days without activity. It will be closed if no further activity occurs within the next 14 days. If this is still an issue, just leave a comment or remove the “stale” label. 🙂

This is a snippet of code that shows how I was able to add the ltree extension as a custom datatype using the workaround of @mcuelenaere

`const util = require('util'); // Built-in Node package
const DataTypes = require('../../../node_modules/sequelize/lib/data-types');
const { classToInvokable } = require('../../../node_modules/sequelize/lib/utils/class-to-invokable');

const ABSTRACT = DataTypes.ABSTRACT.prototype.constructor;

const sequelizeAdditions = (Sequelize) => {
  let DataTypes = Sequelize.DataTypes;
    class LTREE extends ABSTRACT {
    // Mandatory, complete definition of the new type in the database
    toSql() {
      return 'ltree';
    }
    static parse(value) {
      return value;
    }
  }
  DataTypes.LTREE = classToInvokable(LTREE);
  DataTypes.LTREE.prototype.key = 'ltree';
  DataTypes.LTREE.key = DataTypes.LTREE.prototype.key;
  Sequelize.LTREE = Sequelize.Utils.classToInvokable(DataTypes.LTREE)
  DataTypes.LTREE.types.postgres = ['ltree']

  let PgTypes = DataTypes.postgres

  PgTypes.LTREE = function LTREE() {
    if (!(this instanceof PgTypes.LTREE)) return new PgTypes.LTREE();
    DataTypes.LTREE.apply(this, arguments);
  }
  
  util.inherits(PgTypes.LTREE, DataTypes.LTREE);
  PgTypes.LTREE.parse = DataTypes.LTREE.parse;
  PgTypes.LTREE.types = {postgres:['ltree']};
  DataTypes.postgres.LTREE.key = 'ltree';
}

export default sequelizeAdditions;

`

So could anyone please update the documentations to reflect the correct way to implement custom datatypes?

I will try to figure this out 😃

I seem to be stuck on creating custom DataTypes in v5 as well. It never seems to call the toSql() when actually running queries. So instead of trying to execute (CREATE TABLE) 'id' BIGINT(20) UNSIGNED NOT NULL auto_increment it’s trying to do 'id' OBFUSCATEDID NOT NULL auto_increment which obviously doesn’t work.

import { obfuscator } from '../registry';
import { ValidationError } from 'sequelize';
const { ABSTRACT } = require('sequelize/lib/data-types');

export const sequelizeAdditions = (Sequelize: any) => {
  class OBFUSCATEDID extends ABSTRACT {
    escape = false;
    key = 'OBFUSCATEDID';
    dialectTypes = '';

    toSql() {
      return 'BIGINT(20) UNSIGNED';
    }

    validate(value: any) {
      if (typeof value !== 'number') {
        throw new ValidationError(`${value} is not a valid obfuscated ID`);
      }

      return true;
    }

    sanitize(value: any, options: any) {
      if (typeof options !== 'object' || options.raw !== true) {
        return value;
      }

      if (value === null || typeof value === 'undefined') {
        return value;
      }

      return obfuscator.encode(parseInt(value));
    }

    stringify(value: any) {
      if (value === null || typeof value === 'undefined') {
        return value;
      }

      return obfuscator.decode(value);
    }
  }

  Sequelize.DataTypes.OBFUSCATEDID = OBFUSCATEDID;

  Sequelize.DataTypes.OBFUSCATEDID.prototype.key = Sequelize.DataTypes.OBFUSCATEDID.key =
    'OBFUSCATEDID';

  Sequelize.OBFUSCATEDID = Sequelize.Utils.classToInvokable(
    Sequelize.DataTypes.OBFUSCATEDID,
  );
};

The issue lays with classToInvokable, which doesn’t allow access to the actual constructor. The following code shows how we worked around that:

const DataTypes = require('sequelize/lib/data-types');
const { classToInvokable } = require('sequelize/lib/utils/classToInvokable');

// since DataTypes.ABSTRACT is wrapped with classToInvokable(), we can't get to
// the actual class directly, so use this indirection
const ABSTRACT = DataTypes.ABSTRACT.prototype.constructor;

class Custom extends ABSTRACT {
  static key = 'CUSTOM';

  toSql() {
    return 'BIGINT(20) UNSIGNED';
  }

  ...
}
DataTypes.CUSTOM = classToInvokable(Custom);

This works perfect for me, Thank you soo much

@sm2017 as mentioned by @ktret overriding the key propperty seems to work at least for the created sql statements, I implemented it like this:

    class CUSTOM extends DataTypes.ABSTRACT {
        constructor() {
            super();
            this.key = 'VARCHAR(255)';
        }

but as he mentioned it is not a solid workaround, since it fails for instanceof checks. Other overrides like _stringify() and parse() are also never called, so it seems like this feature is completely screwed right now.

RE: It never seems to call the toSql() when actually running queries.

More-so it seems to call ABSTRACT’s toSql. My new data type extends ABSTRACT, and ABSTRACT’s toSql is return this.key so if when I manage to set key to the postgres type, it creates the column in the database fine. But an instance x of said type for a field does not pass x instanceof NEWTYPE check. Sorry, probably not making much sense, and no time to provide code right now.

Anyway, if you can make your key be ‘BIGINT(20) UNSIGNED’ or whatever you want the database to get, it might create the column fine, but you may still have issues if you check instanceof and such.

@papb Oh nice, I’ll take a look at those then. If I can get something working, maybe it can serve as a good example.

@moberegger It should be, I believe, although the docs really need fixing. I will hopefully be able to take a look soon. If you could dig a bit and find out how to do it, that would be awesome… But if you don’t have the time, I shall do it in the near future.

Taking a peak at your reproduction, I think the steps from https://sequelize.org/master/manual/data-types.html#postgresql are missing, where it says:

After creating this new datatype, you need to map this datatype in each database dialect and make some adjustments.

It is hard to make sense of that example, though. The example given is for Postgres, so it’s not clear what is required for other dialects (they all seem to have types assigned slightly differently in the source). Furthermore, it uses an inherits API which isn’t available anywhere (I think it used to be part of utils in V4).

Another issue (https://github.com/sequelize/sequelize/issues/10964) was posted where the author was having the same problem.

Is this feature fully supported?

I added a little sample project in this repo: https://github.com/Bene-Graham/SequelizeCustomColumnTypeBug

Couple of things I have noticed:

1. Copying the custom type that is shown in the doc does nto work either: https://sequelize.org/master/manual/data-types.html
2. .sync() does not work with custom types it puts the wrong DB type in the create table statement
3. It does nto work either in sqllite

CREATE TABLE IF NOT EXISTS `MyTestModel` 
(
`id` INTEGER UNSIGNED auto_increment , 
`amount` DECIMAL, 
`myInt` ABSTRACT, 
`createdAt` DATETIME NOT NULL, 
`updatedAt` DATETIME NOT NULL, 
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

Edit: To switch between dialects just comment / uncomment the import in connectionToggle.ts