I get the error on build :

Server failed to start due to error: SequelizeDatabaseError: syntax error at or near "SERIAL"

This error ONLY appears when the parameter autoIncrement=true is given to the primary key.

'use strict';

export default function(sequelize, DataTypes) {
  return sequelize.define('Ladder', {
    ladder_id: {
      type: DataTypes.UUID,
      allowNull: false,
      primaryKey: true,
      autoIncrement: true //<------- If commented it works fine
    },
    ladder_name: {
      type: DataTypes.STRING(50),
      allowNull: false,
      unique: true
    },
    ladder_description: {
      type: DataTypes.TEXT,
      allowNull: true
    },
    ladder_open: {
      type: DataTypes.BOOLEAN,
      allowNull: false
    },
    ladder_hidden: {
      type: DataTypes.BOOLEAN,
      allowNull: false
    },
    ladder_creation_date: {
      type: DataTypes.DATE,
      allowNull: false
    },
    ladder_fk_user: {
      type: DataTypes.INTEGER,
      allowNull: false
    },
    ladder_fk_game: {
      type: DataTypes.UUID,
      allowNull: false
    },
    ladder_fk_platforms: {
      type: DataTypes.ARRAY(DataTypes.UUID),
      allowNull: false
    }

  },
    {
      schema: 'ladder',
      tableName: 'ladders'
    });
}

I have Sequelize 3.30.4 and postgreSQL 9.6.

I want autoIncrement at true because I am generating the UUID with postgreSQL uuid_generate_v4().

I get the error on build :

Server failed to start due to error: SequelizeDatabaseError: syntax error at or near "SERIAL"

This error ONLY appears when the parameter autoIncrement=true is given to the primary key.

'use strict';

export default function(sequelize, DataTypes) {
  return sequelize.define('Ladder', {
    ladder_id: {
      type: DataTypes.UUID,
      allowNull: false,
      primaryKey: true,
      autoIncrement: true //<------- If commented it works fine
    },
    ladder_name: {
      type: DataTypes.STRING(50),
      allowNull: false,
      unique: true
    },
    ladder_description: {
      type: DataTypes.TEXT,
      allowNull: true
    },
    ladder_open: {
      type: DataTypes.BOOLEAN,
      allowNull: false
    },
    ladder_hidden: {
      type: DataTypes.BOOLEAN,
      allowNull: false
    },
    ladder_creation_date: {
      type: DataTypes.DATE,
      allowNull: false
    },
    ladder_fk_user: {
      type: DataTypes.INTEGER,
      allowNull: false
    },
    ladder_fk_game: {
      type: DataTypes.UUID,
      allowNull: false
    },
    ladder_fk_platforms: {
      type: DataTypes.ARRAY(DataTypes.UUID),
      allowNull: false
    }

  },
    {
      schema: 'ladder',
      tableName: 'ladders'
    });
}

I have Sequelize 3.30.4 and postgreSQL 9.6.

I want autoIncrement at true because I am generating the UUID with postgreSQL uuid_generate_v4().

• javascript
• angularjs
• postgresql
• sequelize.js

3 Answers 3

Not a regular sequelize user here but let me point out that using autoIncrement for non sequential column is not the right way in postgreql. Postgresql does not provide a default uuid number generator but an extension can be added easily https://www.postgresql.org/docs/9.4/static/uuid-ossp.html. I believev you have already done so.

The next step then is to us the sequelize.fn function.

Creates an object representing a database function. This can be used in search queries, both in where and order parts, and as default values in column definitions.

so we have

ladder_id: {
    type: DataTypes.UUID,
    allowNull: false,
    primaryKey: true,
    default: sequelize.fn('uuid_generate_v4')
}

My guess is that autoIncrement for PostgreSQL is hardcoded to use SERIAL type for column and this conflicts with your choice of UUID.

Try removing autoincrement parameter and instead use defaultvalue:

return sequelize.define('Ladder', {
    ladder_id: {
      type: DataTypes.UUID,
      allowNull: false,
      primaryKey: true,
      defaultValue: UUIDV4
    },

child_id: {
    primaryKey: true,
    autoIncrement: true,
    type: Sequelize.INTEGER
},
parent_id: {
    references: {
        model: 'parent',
        key: 'parent_id'
    },
    type: Sequelize.INTEGER
}