Node.js Sequelize UUID primary key + Postgres
Solution 1
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
touuid
. - 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);
}));
Solution 2
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()' )
Related videos on Youtube
Number16BusShelter
I am a Full-Stack developer with more than 6 years of experience developing in Python and Node.js. For the last 4 years, I have been actively involved in projects of developmental and application of blockchain technologies, as well as consulting services in this area. I consider myself as a Software Architect. In all projects, I always look for the most effective solutions for business. My specialization is High-loaded systems with scalable micro-service architecture. I compensate my age with persistence and thirst for work. My key skills: Global process understanding High communication skills Deep product understanding Immersion not only from the development side but also from the market side Deeply understand people I work with and how to connect them My strengths: Stress resistance Work to failure Hackathon mode development skills
Updated on June 06, 2020Comments
-
Number16BusShelter almost 4 years
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!
-
Number16BusShelter almost 6 yearsActually, 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 almost 6 yearsSure. Take a look.
-
AbhinavD almost 6 yearsThe model still shows
id
field? it should beuuid
. You should also make it primary keyprimaryKey: true
-
Number16BusShelter almost 6 yearsI'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 almost 6 yearsModel 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
-
Number16BusShelter almost 6 yearsOk, now I get it. Thanks a lot.