CODEWITHSUNDEEP
typescriptpostgresqlexpresssequelize-typescript
Yosua Kristianto
Yosua Kristianto

Reputation: 80

Sequelize Typescript null value in column "id" violates not-null constaint

Background

I'm setting up a database table using Sequelize-Typescript recently and using this ORM to assist me with database operations.

So I was created a table called uma_tbl_users with the snippet codes below:


interface UserInterface {

  id: number;

  ... Other attributes...  


}

@Table(
  {
    tableName    : 'uma_tbl_users',
    timestamps   : false,
    underscored  : true,
    schema       : 'po_uma'
  }
)
class User extends Model implements UserInterface {

  @Column({
    autoIncrement: true,
    primaryKey: true,
    field: "user_id",
    type: DataTypes.BIGINT
  })
  id!: number;

  ... Other Implemented Attributes from the interface

}

After creating this table, I do the same things with other tables and run Sequelize Database Syncer in force mode.

Problem

When I run the code to run the CREATE data statement for this table, I'm always receiving this error below:

SequelizeDatabaseError: null value in column \"user_id\" violates not-null constraint

But what went weird is, this issue only occurs for this table. Every other table works just fine. And I've already made sure there is no validation interfering with the create statement.

Here's my create statement:

    const execution: User = await User.create({
      role_id: request.role,
      email: request.email,
      phone: request.phone,
      password: request.password
    });

    return execution;

What I've already tried?

  1. I've tried using @AutoIncrement and @PrimaryKey annotation instead of load all options inside @Column, but the problem still persists.
  2. I've tried to re-sync the database by using sync({ force: true }) but the problem still persists.
  3. I'm using a Barbaric way where I ignored my issue above, and just go with "get the latest ID, and put the ID to the create statement", and the weird thing is, when I return the created model, instead of returning the inserted ID, it return the count the last row index. (Let's say if I had 6 data of uma_tbl_users, the ID will return 6 instead of what I've to insert)
  4. I've looked over Github and Stackoverflow threads regarding this problem, and I always receive "You've to force the database sync", but this solution not solving my problem.
  5. I've already made sure that the Auto Increment is worked perfectly. I tested a create statement directly in the database and it generates the ID automatically.

What I'm currently using?

Framework / Language / Database

  1. Typescript 4.2.4 (@types/node 14.14.41)
  2. Express 4.17.1 (@types/express 4.17.11)
  3. PostgreSQL 12.3

Dependencies (npm 6.9.0)

  1. Sequelize 6.6.2
  2. Sequelize-Typescript 2.1.0
  3. pg 8.6.0
  4. pg-hstore 2.3.3

Upvotes: 2

Views: 2071

Answers (2)

Ucha
Ucha

Reputation: 184

You are probably supplying the primary keys yourself while seeding this particular table.

Tables with custom PKs and AutoIncrement set to true should not be seeded with explicitly set primary keys.

Just seed the other columns. Sequelize-Typescript will enter the primary keys on its own.

Upvotes: 1

Yosua Kristianto
Yosua Kristianto

Reputation: 80

I don't know if it's a bug from Sequelize-Typescript or what, but when I change the "user_id" into "id" by removing field parameter, it works.

Final @Column setting

  @Column({
    autoIncrement: true,
    primaryKey: true,
    type: DataTypes.BIGINT
  })
  id!: number;

Feel free to post your answer/opinion.

Upvotes: 1

Related Questions