How to set primary key type to UUID via Sequelize CLI

Question

I'm creating a DB model via Sequelize CLI with this command:

sequelize model:create --name User --attributes "firstname:string, lastname:string"

This creates the corresponding migration script:

'use strict';
module.exports = {
  up: function(queryInterface, Sequelize) {
    return queryInterface.createTable('Users', {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER
      },
      firstname: {
        type: Sequelize.STRING
      },
      lastname: {
        type: Sequelize.STRING
      },
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE
      },
      updatedAt: {
        allowNull: false,
        type: Sequelize.DATE
      }
    });
  },
  down: function(queryInterface, Sequelize) {
    return queryInterface.dropTable('Users');
  }
};

As shown, the primary key is set to integer.

Is there a way to default the CLI to use UUID instead?

Brewis

Answer 1

You can simple use the type UUIDV4 that comes with Sequelize. Here is more details : UUIDV4

For example making this definition:

id: {
  type: Sequelize.UUID,
  defaultValue: Sequelize.UUIDV4,
  allowNull: false,
  primaryKey: true
}

This is not using the Sequelize CLI but you can use that native UUIDV4 by manually changing that.

Trela

This answer is severely underrated. It's way simpler/better than anything involving specifying beforeCreate hooks, migration scripts, etc. This is the simplest answer, and it just plain works. (I had been trying to use a set(value) function to set it.) I believe you can make this CLI, maybe. I think you can set certain properties when they're simple like that. – Staple

This answer worked for me. if you are using TypeScript and import { DataTypes } from "sequelize" the usage is id: { type: DataTypes.UUID, defaultValue: DataTypes.UUIDV4, allowNull: false, primaryKey: true, } – Adrenocorticotropic

Depending on what version of sequelize you are using, and if you are using postgres this might not work. If you are using a later version of sequelize with postgres I recommend using the uuid-ossp extension – Halley

Answer 2

You'd have to edit the generated file manually, changing Sequelize.INTEGER to Sequelize.UUID, then remove the autoIncrement: true.

npm install uuid

So your model would look like this:

const uuid = require('uuid/v4'); // ES5

'use strict';

module.exports = {
  up: function(queryInterface, Sequelize) {
    return queryInterface.createTable('Users', {
      id: {
        allowNull: false,
        primaryKey: true,
        type: Sequelize.UUID
      },
      /* Other model properties */
    });
  },
  down: function(queryInterface, Sequelize) {
    return queryInterface.dropTable('Users');
  }
};

Update your beforeCreate hook as follows:

const uuid = require('uuid/v4');

export default (sequelize, DataTypes) => {
  const User = sequelize.define('User', {
    id: {
      allowNull: false,
      primaryKey: true,
      type: Sequelize.UUID
    },
    ...
  }, {});

  User.beforeCreate(user => user.id = uuid());

  return User;
};

This assumes you are using Sequelize v4.

Sprain

This doesn't seem to work for MySQL. On insert I get: "Field 'id' doesn't have a default value" – Efficient

You could use an npm package for generating UUID. npm install uuid or npm install uuidv4 ES5 const uuidv4 = require('uuid/v4'); or const uuid = require('uuidv4') uuidv4(); or uuid() ES6 import uuid from 'uuid/v4'; uuid() On doing that replace the defaultValue property's value with a call to the required/imported uuid. Doing this will allow auto generation of UUIDs on every save. – Sprain

Just a tip. I feel like I'm having some success with Bookshelfjs and Knex, something else that bothers me about Sequelize is the difficulty of using unix timestamps for created/updated. – Efficient

Does this work as expected? I tried this and it added the UUID correctly in the first attempt. On adding a second record, I got an error saying that the UUID already exists. So, I think that the uuid() line runs only once and gets set as the same for all records – Butyraceous

@NikhilBaliga can you try it with the bottom part of the answer? – Sprain

returning () => uuid() to defaultValue solves my problem in mysql db. – Debose

defaultValue: Sequelize.UUIDV4 solved my problem with repeated uuid. – Georgiana

Answer 3

The last answer will not work because the uuid() function will set a unique default value for all users in your database. Since they are a primaryKey, you will be able to persist only one user in the database, then everyone will receive the same uuid value and, of course, will not be persisted.

So... You have to:

1. In your migration file, remove autoIncrement: true, and change the type of your id from type: Sequelize.INTEGER to type: Sequelize.UUID
2. Install the uuid generator function from npm i uuid --save

3. In your generated User Model, change the beforeCreate function to generate a new uuid before insert it in the database, like this:

   const uuid = require('uuid/v4');
   /*...*/
   module.exports = (sequelize, DataTypes) => {
     const User = sequelize.define('User', {
       /* Your User Model Here */
     }, {});
     User.beforeCreate((user, _ ) => {
       return user.id = uuid();
     });
     return User;
   };
   

4. Apply the changes of your migration by doing: sequelize db:migrate:undo follow by sequelize db:migrate

5. Test it.

Transcendentalistic

Answer 4

You can modify your migration script with this script

'use strict';
module.exports = {
  up: function(queryInterface, Sequelize) {
    return queryInterface.createTable('Users', {
      id: {
        primaryKey: true,
        type: Sequelize.UUID,
        defaultValue: Sequelize.literal('uuid_generate_v4()')
      },
      firstname: {
        type: Sequelize.STRING
      },
      lastname: {
        type: Sequelize.STRING
      },
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE
      },
      updatedAt: {
        allowNull: false,
        type: Sequelize.DATE
      }
    });
  },
  down: function(queryInterface, Sequelize) {
    return queryInterface.dropTable('Users');
  }
};

It works for me.

Cinchonine

That will work if you db ever execute this sql command: CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; That is if you use Postgresql. – Gosling

Why would your UUID auto increment? – Welford

I've just edited, we don't need that line of code bro. And it still works. And make sure CREATE EXTENSION IF NOT EXISTS "uuid-ossp" is executed first like what @LEMUELADANE wrote. – Cinchonine

Answer 5

If you need to make Postgres generate UUID on the insert as a default value, this approach defaultValue: Sequelize.UUIDV4 would not work. Anyway, Sequelize generates NULL value.

Instead, Sequelize.literal('uuid_generate_v4()') must be used. That shall produce query CREATE TABLE IF NOT EXISTS "table" ("id" UUID NOT NULL DEFAULT uuid_generate_v4()).

id: {
    allowNull: false,
    primaryKey: true,
    type: Sequelize.DataTypes.UUID,

    defaultValue: Sequelize.literal('uuid_generate_v4()'),
},

Rohrer

uuid_generate_v4() won't work of the box on Postgres, an extension is required. – Conni

to install the extension: CREATE EXTENSION IF NOT EXISTS "uuid-ossp"; as reported here : #22446978 – Metaphrase

Answer 6

• No auto increment. UUID are randomly generated.
• Ensure you use DataType.UUID and default value to UUIDV4

No Auto increment. UUID are generated

export const User = db.define('user',
    {
        id: {
            type: DataTypes.UUID,
            defaultValue: DataTypes.UUIDV4,
            primaryKey: true,
        },

Lease

Is it possible to get a shorter uid? Like 5-6 chars. UUIDV4 is rather excessive. – Plausive

Answer 7

Any of the solutions mentioned above did not work for me. But was much helpful to arrive on my own resolution of the problem.

I'm using ES6+ syntax and solved this way:

PS: Sequelize on v5.x

Migration

'use strict';

module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.createTable('users', {
      id: {
        type: Sequelize.UUID,
        primaryKey: true,
        allowNull: false,
        defaultValue: Sequelize.UUIDV4,
      },
      name: {
        type: Sequelize.STRING,
        allowNull: false,
      },
      created_at: {
        type: Sequelize.DATE,
        allowNull: false,
      },
      updated_at: {
        type: Sequelize.DATE,
        allowNull: false,
      }
    });
  },

  down: (queryInterface) => {
    return queryInterface.dropTable('users');
  }
}

Model

import { uuid } from 'uuidv4';
import Sequelize, { Model } from 'sequelize';

class User extends Model {
  static init(sequelize) {
    super.init(
      {
        name: Sequelize.STRING,
      },
      {
        sequelize,
      }
    );

    this.addHook('beforeSave', async (user) => {
      return user.id = uuid();
    });

    return this;
  }
}

export default Users;

Controller

import User from '../models/User';

class UserController {
  async store(request, response) {
    const { user } = request.body;

    const { id, name } = await User.create(users);

    return response.json({
      id,
      message: `User ${name} was register successful`,
    });
  }

Boyhood

Answer 8

I tried many options, everything turned out to be very simple. Try like this

Model:

import { Model, DataTypes } from 'sequelize';
import { v4 as uuidv4 } from 'uuid';

import sequelize from '..';

class User extends Model {}

User.init(
  {
    id: {
      type: DataTypes.UUID,
      primaryKey: true,
      allowNull: false,
      defaultValue: () => uuidv4(),
    }
  },
  {
    sequelize,
    tableName: 'user',
    timestamps: false,
  },
);

export default User;

Migration:

    const { v4: uuidv4 } = require('uuid');

module.exports = {
  up: async (queryInterface, DataTypes) => {
    await queryInterface.createTable('user', {
      id: {
        type: DataTypes.UUID,
        primaryKey: true,
        allowNull: false,
        defaultValue: uuidv4(),
      }
    });
  },

  down: async queryInterface => {
    await queryInterface.dropTable('user');
  },
};

Benignant

Answer 9

id: {
  type: Sequelize.UUID,
  defaultValue: Sequelize.UUIDV4,
  allowNull: false,
  primaryKey: true
}

The answer from @Ismael Terreno is true; however, there is a bug that I'm not experienced enough to explain: if your model contains that format while using the examples provided from sequelize it will NOT work. Even so, you don't need to import UUID outside of sequelize; you can still take advantage of sequelize providing it, but you have to require it in for some reason like so:

"use strict";
module.exports = (sequelize, DataTypes) => {
  const User = sequelize.define(
    "User",
    {
      id: {
        primaryKey: true,
        type: DataTypes.UUID,
        defaultValue: require("sequelize").UUIDV4
      },
    {}
  );
  User.associate = function(models) {
    //enter associations here
    });
  };
  return User;
};`

I tested that and it worked, but I only found this out because of a previous success I had early on where my set up looked like this for the model, where I required everything before defining the model:

const { Sequelize, DataTypes, Model } = require("sequelize");
const db = require("../../config/database");

const userModel = db.define("users", {
  id: {
    type: DataTypes.UUID,
    defaultValue: Sequelize.UUIDV4,
    primaryKey: true
  }
});

Hinkle

Thank you @TobySpeight for pointing me to improve. I went ahead and edited my answer accordingly. I'm new to this but totally agree with the importance specificity. – Hinkle

defaultValue: require("sequelize").UUIDV4 this worked for me. Thanks – Unwish

Answer 10

Make sure your package.json has updated uuid module

"uuid": "^8.2.0",

This was the problem in my case as it was 3.4.0 before and sequelize does not work with that.

accessSecret: {
      type: DataTypes.UUID,
      defaultValue: Sequelize.UUIDV4,
      allowNull: false,
      unique: true,
    },

This works just fine after the uuid package upgrade on MySQL

Don't forget to change your imports to

const { v4: uuid } = require('uuid')

Tested on "sequelize": "^5.21.13"

Bolus

Answer 11

For sequelize v6.5.0 using Postgres specifying defaultValue: Sequelize.UUIDV4 did not work. Instead I had to do this:

'use strict';

module.exports = {
  up: async (queryInterface, Sequelize) => {

      await queryInterface.sequelize.query('CREATE EXTENSION IF NOT EXISTS "uuid-ossp";')
      await queryInterface.addColumn('MyTable', 'id', {
        type: Sequelize.UUID,
        defaultValue: Sequelize.literal('uuid_generate_v4()'),
        allowNull: false,
        primaryKey: true
      })

  },
  down: async (queryInterface, Sequelize) => {

  }
};

Halley

Answer 12

simply and work for all databases

 const uuidv4 = require('uuid/v4');

   id: {
      type: DataTypes.UUID,
      unique: true,
      primaryKey: true,
      isUUID: 4,
      defaultValue: uuidv4()
    },

Carton

Right on target – Brout

This is a bad idea, it'll use the first-value returned by uuidv4() because we're not passing the function to Sequelize, but the return value (which is probably just one id) – Suggestibility

Answer 13

To set the default value in migration you will need to write like this

id: {
      primaryKey: true,
      allowNull: false,
      defaultValue: Sequelize.literal("gen_random_uuid()"),
      type: Sequelize.UUID,
    },

Marigolde

Answer 14

This might help. Install uuid-ossp extension in postgres so that you would be able to run uuid_generate_v4() function.

'use strict';
/** @type {import('sequelize-cli').Migration} */
module.exports = {
  async up(queryInterface, Sequelize) {
    await queryInterface.sequelize.query('CREATE EXTENSION IF NOT EXISTS "uuid-ossp"');

    await queryInterface.createTable('users', {
      user_id: {
        type: Sequelize.INTEGER,
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
      },
      user_uuid: {
        type: Sequelize.UUID,
        defaultValue: Sequelize.literal('uuid_generate_v4()'),
        allowNull: false,
        unique: true
      },
      first_name: {
        type: Sequelize.STRING(50),
        allowNull: false
      },
      created_at: {
        type: Sequelize.DATE,
        allowNull: false,
        defaultValue: Sequelize.literal('CURRENT_TIMESTAMP')
      },
      updated_at: {
        type: Sequelize.DATE,
        allowNull: false,
      }
    });

    // INDEXES
    await queryInterface.addIndex('users', ['user_uuid']);
  },
  async down(queryInterface, Sequelize) {
    await queryInterface.dropTable('users');
  }
};

Constrained