Node.js Sequelize UUID primary key + Postgres

Question

I try to create database model by using sequelize but I'm facing a problem with model's primary key.

Setting

I'm using Postgres (v10) in docker container and sequalize (Node.js v10.1.0 ) for models and GraphQL (0.13.2) + GraphQL-Sequalize (8.1.0) for request processing.

Problem

After creating models by sequelize-cli I've manually tried to replace id column with uuid. Here's my model migration that I'm using.

'use strict';
const DataTypes = require('sequelize').DataTypes;

module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.createTable('Currencies', {
      uuid: {
        primaryKey: true,
        type: Sequelize.UUID,
        defaultValue: DataTypes.UUIDV4,
        allowNull: false
      },
      name: {
        type: Sequelize.STRING
      },
      ticker: {
        type: Sequelize.STRING
      },
      alt_tickers: {
        type: Sequelize.ARRAY(Sequelize.STRING)
      },
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE
      },
      updatedAt: {
        allowNull: false,
        type: Sequelize.DATE
      }
    });
  },
  down: (queryInterface, Sequelize) => {
    return queryInterface.dropTable('Currencies');
  }
};

Model:

'use strict';
module.exports = (sequelize, DataTypes) => {
    const Currency = sequelize.define('Currency', {
        uuid: DataTypes.UUID,
        name: DataTypes.STRING,
        ticker: DataTypes.STRING,
        alt_tickers: DataTypes.ARRAY(DataTypes.STRING)
    }, {});
    Currency.associate = function(models) {
        // associations can be defined here
    };
    return Currency;
};

Due to some problem sequalize executes next expression:

Executing (default): SELECT "id", "uuid", "name", "ticker", "alt_tickers", "createdAt", "updatedAt" FROM "Currencies" AS "Currency" ORDER BY "Currency"."id" ASC;

That leads to "column 'id' doesn't exist" error.

Alternatively, I've tried to fix it by renaming uuid column to id at migration:

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

And at the model:

'use strict';
module.exports = (sequelize, DataTypes) => {
    const Currency = sequelize.define('Currency', {
        id: DataTypes.INTEGER,
        name: DataTypes.STRING,
        ticker: DataTypes.STRING,
        alt_tickers: DataTypes.ARRAY(DataTypes.STRING)
    }, {});
    Currency.associate = function(models) {
        // associations can be defined here
    };
    return Currency;
};

but the result was the following error at the start of the program:

Error: A column called 'id' was added to the attributes of 'Currencies' but not marked with 'primaryKey: true'

Questions

So, is there a way to force sequelize to use UUID as the tables primary key without defining id column?
Is there a way to create columns without id columns?
What possibly caused this errors and how should fix it?

Thanks in advance!

Tom · Accepted Answer · 2019-06-05 16:09:44Z

15

This is just about the only resource I've found online that explains what it takes to set up a UUID column that the database provides defaults for, without relying on the third-party uuid npm package: https://krmannix.com/2017/05/23/postgres-autogenerated-uuids-with-sequelize/

Short version:

You'll need to install the "uuid-ossp" postgres extension, using a sqlz migration
When defining the table, use this defaultValue: Sequelize.literal( 'uuid_generate_v4()' )

answered Jun 5, 2019 at 16:09

Tom

9,2087 gold badges56 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

AbhinavD · Accepted Answer · 2018-05-19 11:18:54Z

14

What you have not posted here is your model code. This is what I think has happened

The database has been manually changed from id to uuid.
Your model does not reflect this change.

Hence the query is searching for both id and uuid.

You can fix this my defining uuid in your model like below and making it a primary key

const User = sequelize.define('user', {
  uuid: {
    type: Sequelize.UUID,
    defaultValue: Sequelize.UUIDV1,
    primaryKey: true
  },
  username: Sequelize.STRING,
});

sequelize.sync({ force: true })
  .then(() => User.create({
    username: 'test123'
  }).then((user) => {
    console.log(user);
  }));

answered May 19, 2018 at 11:18

AbhinavD

7,3825 gold badges32 silver badges41 bronze badges

6 Comments

Number16BusShelter Over a year ago

Actually, the model has been changed in the first place. I've also tried to rollback to Integer "id", but the primary key was not set in the database.

Number16BusShelter Over a year ago

Sure. Take a look.

AbhinavD Over a year ago

The model still shows id field? it should be uuid. You should also make it primary key primaryKey: true

Number16BusShelter Over a year ago

I've provided two cases. First is the table with uuid as UUID data type and second with id as UUID data type, so two models are provided, one after another. If I understood you correct you want me to add a primary key to migration but that is already done.

AbhinavD Over a year ago

Model and DB declaration of primary key should happen independently and recommended. So if you are solving `Error: A column called 'id' was added to the attributes of 'Currencies', you should make it primary key in the model as well as in the db as the query is generated by the Sequelize

|

Collectives™ on Stack Overflow

Node.js Sequelize UUID primary key + Postgres

Setting

Problem

Questions

2 Answers 2

Comments

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Setting

Problem

Questions

2 Answers 2

Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related