CODEWITHSUNDEEP
node.jspostgresqlormsequelize.jsgraphql
Number16BusShelter
Number16BusShelter

Reputation: 630

Node.js Sequelize UUID primary key + Postgres

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

Thanks in advance!

Upvotes: 9

Views: 29038

Answers (2)

Tom
Tom

Reputation: 9127

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:

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

Upvotes: 15

AbhinavD
AbhinavD

Reputation: 7282

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

  1. The database has been manually changed from id to uuid.
  2. 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);
  }));

Upvotes: 14

Related Questions