Do sequelize support using UUID type id that is auto generated in postgreSQL dialect? Very surprised about CREATE TABLE sql generated, which does not seem to be correct for postgreSQL dialect.
sequelize queryInterface.createTable via queryGenerator.createTableQuery on PostgreSQL with PK id of type UUID with defaultValue: Sequelize.DataTypes.UUIDV4 does NOT seem to create proper CREATE TABLE that will create default UUID values for id when id value is not provided.
Below call of queryInterface.createTable
await queryInterface.createTable( // 'tableName' 'User2s', // 'attributes' { id: { allowNull: false, // autoIncrement: true, // with this "npx sequelize-cli db:migrate" fails with 'ERROR syntax error at or near "SERIAL"' for type UUID. So not using this. This would work if id type was INTEGER (without setting 'defaultValue') defaultValue: Sequelize.UUIDV4, // defaultValue: Sequelize.DataTypes.UUIDV4, // tried this instead of above too // defaultValue: require('sequelize').UUIDV4, // tried this instead of above too primaryKey: true, type: Sequelize.UUID }, firstName: { type: Sequelize.STRING }, lastName: { type: Sequelize.STRING }, email: { type: Sequelize.STRING }, createdAt: { allowNull: false, type: Sequelize.DATE }, updatedAt: { allowNull: false, type: Sequelize.DATE } }, // 'options' {schema: process.env.DB_POSTGRESQL_SCHEMA_NAME} ); creates below SQL (with dialect set as postgreSQL);
CREATE TABLE exp15c."User2s" ( id uuid NOT NULL, "firstName" character varying(255) COLLATE pg_catalog."default", "lastName" character varying(255) COLLATE pg_catalog."default", email character varying(255) COLLATE pg_catalog."default", "createdAt" timestamp with time zone NOT NULL, "updatedAt" timestamp with time zone NOT NULL, CONSTRAINT "User2s_pkey" PRIMARY KEY (id) ) But above generated CREATE TABLE for postgreSQL probably should had been;
CREATE TABLE exp15c."User2s" ( id uuid DEFAULT uuid_generate_v4(), "firstName" character varying(255) COLLATE pg_catalog."default", "lastName" character varying(255) COLLATE pg_catalog."default", email character varying(255) COLLATE pg_catalog."default", "createdAt" timestamp with time zone NOT NULL, "updatedAt" timestamp with time zone NOT NULL, CONSTRAINT "User2s_pkey" PRIMARY KEY (id) ) When I look at documents at; https://github.com/sequelize/sequelize/blob/9f950cbcbdd659d559496b77c40e0f827b108561/docs/manual/core-concepts/model-basics.md UUIDs For UUIDs, use DataTypes.UUID. It becomes the UUID data type for PostgreSQL and SQLite, and CHAR(36) for MySQL. Sequelize can generate UUIDs automatically for these fields, simply use Sequelize.UUIDV1 or Sequelize.UUIDV4 as the default value:
{ type: DataTypes.UUID, defaultValue: Sequelize.UUIDV4 // Or Sequelize.UUIDV1 } It basically says, my above usage is correct.
When I look at sequelize source code, I see at; https://github.com/sequelize/sequelize/blob/9f950cbcbdd659d559496b77c40e0f827b108561/lib/utils.js
const uuidv4 = require('uuid').v4; function toDefaultValue(value, dialect) { if (typeof value === 'function') { const tmp = value(); if (tmp instanceof DataTypes.ABSTRACT) { return tmp.toSql(); } return tmp; } if (value instanceof DataTypes.UUIDV1) { return uuidv1(); } if (value instanceof DataTypes.UUIDV4) { return uuidv4(); } if (value instanceof DataTypes.NOW) { return now(dialect); } if (Array.isArray(value)) { return value.slice(); } if (_.isPlainObject(value)) { return { ...value }; } return value; } exports.toDefaultValue = toDefaultValue; and above method being used in https://github.com/sequelize/sequelize/blob/9f950cbcbdd659d559496b77c40e0f827b108561/lib/model.js _initValues(values, options) { but not sure if it really applies to my use case.
The above generated CREATE TABLE sql executes and creates the table, but will not work when we try to INSERT without id value, which is what I am trying to do.
To be very detailed; I am using sequelize via sequelize-cli commands like below; 0) make sure your postgreSQL DB has new schema 'exp15c' initialize sequelize directories and files npx sequelize-cli init
create model and migrations npx sequelize-cli model:generate --name User2 --attributes firstName:string,lastName:string,email:string which creates model as;
'use strict'; const { Model } = require('sequelize'); module.exports = (sequelize, DataTypes) => { class User2 extends Model { static associate(models) { // define association here } }; User2.init({ firstName: DataTypes.STRING, lastName: DataTypes.STRING, email: DataTypes.STRING }, { sequelize, modelName: 'User2', }); return User2; }
Manually update generated migration file to have above code in it's "up"
'use strict'; module.exports = { up: async (queryInterface, Sequelize) => { // above provided code fragment with "await queryInterface._createTable(" goes here }, down: async (queryInterface, Sequelize) => { await queryInterface.dropTable('User2s'); } };
execute above migration npx sequelize-cli db:migrate Observe that it creates the User2s table with above CREATE TABLE sql
Create 'seed' file for User2 npx sequelize-cli seed:generate --name init-user2
Manually update above created seed of User2 as (NOTE not specifying id value, for it to be auto assigned via default mechanism);
'use strict'; module.exports = { up: async (queryInterface, Sequelize) => { await queryInterface.bulkInsert( {tableName: 'User2s', schema: 'exp15c'}, [ { firstName: 'ilker', lastName: 'kiris', email: '[email protected]', createdAt: new Date(), updatedAt: new Date() }, { firstName: 'selcuk', lastName: 'gecer', email: '[email protected]', createdAt: new Date(), updatedAt: new Date() }, { firstName: 'turhan', lastName: 'bozkurt', email: '[email protected]', createdAt: new Date(), updatedAt: new Date() } ], {} ); },
down: async (queryInterface, Sequelize) => { await queryInterface.bulkDelete('User2s', null, {}); } };
execute above seed for User2 npx sequelize-cli db:seed:all NOTE above fail with ERROR: null value in column "id" of relation "User2s" violates not-null constraint because the generated CREATE TABLE sql is not really using defaultValue variable
