Postgres autogenerated UUIDs with Sequelize
I’ve already talked about setting UUIDs as defaults for Elixir, but I ran into a similar issue when setting up a node.js project with Sequelize (an excellent ORM). I wanted an id column that would autogenerate UUIDs on insert. However, it wasn’t as clear-cut as I thought to set up via the docs.
When setting up my migration file, I originally tried this:
'use strict';
module.exports = {
up: function(queryInterface, Sequelize) {
return queryInterface.createTable('user', {
id: {
allowNull: false,
primaryKey: true,
type: Sequelize.DataTypes.UUID,
defaultValue: Sequelize.UUIDV4, // I expected this set the column default
},
email: {
type: Sequelize.STRING,
allowNull: false,
},
});
},
};
which gave me a Postgres table of the following:
Column | Type | Modifiers
------------+--------------------------+-----------
id | uuid | not null
email | character varying(255) | not null
Indexes:
"user_pkey" PRIMARY KEY, btree (id)
"user_email" UNIQUE, btree (email)
As you can see the id field is of type uuid, is NOT NULL & is the primary key - however, it won’t autogenerate UUID values on insert.
In order to do so in Postgres, it needs the uuid-ossp extension. One of the nice parts about Postgres is its extensibility, in which we can add features that have been developed independently of the core project. We’ll need to add a line in our migration to download the extension if it isn’t included in Postgres yet, and then execute a function from the uuid-ossp extension to generate the UUIDs on insert. I ended up with the following:
'use strict';
module.exports = {
up: function(queryInterface, Sequelize) {
return queryInterface.sequelize.query('CREATE EXTENSION IF NOT EXISTS "uuid-ossp";')
.then(() => {
return queryInterface.createTable('user', {
id: {
allowNull: false,
primaryKey: true,
type: Sequelize.DataTypes.UUID,
defaultValue: Sequelize.literal('uuid_generate_v4()'),
},
email: {
type: Sequelize.STRING,
allowNull: false,
},
});
});
},
};
which gives us:
Column | Type | Modifiers
------------+--------------------------+-------------------------------------
id | uuid | not null default uuid_generate_v4()
email | character varying(255) | not null
Indexes:
"user_pkey" PRIMARY KEY, btree (id)
"user_email" UNIQUE, btree (email)
Nice! Now we’ve set the default modifier to call uuid_generate_v4(), so that we’ll have a unique identifier for each row we insert.
