Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Node.js, PHP and APIs

TypeORM sync fails when a new column is NOT NULL on an existing table

ErrorQueryFailedError: column "price" of relation "product" contains null values

Short answer

TypeORM columns are NOT NULL by default. When it adds a new column or turns one NOT NULL on a table that already has rows, PostgreSQL rejects it because the old rows would hold NULL. Give the column a default, make it nullable, or fill the old rows first.

  • Applies to TypeORM 0.3, PostgreSQL 16, Node.js 22
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Count the rows that block the change. If the table has data, the column needs a value for each of them.

    SELECT count(*) AS total, count(*) FILTER (WHERE price IS NULL) AS nulls FROM product;
  2. 2Match what you see, then jump to its fix

    1. 1The error appears right after you added a property with @Column() to an entity whose table already holds rows.A new NOT NULL column has no defaultFix
    2. 2Some products legitimately have no price.The column should be optionalFix
    3. 3The column existed and was nullable before, and you removed nullable.Existing rows already hold NULL and you want NOT NULLFix

What does the error look like?

  • QueryFailedError: column "price" of relation "product" contains null values
  • QueryFailedError: column "price" contains null values

Cause 1 of 3

A new NOT NULL column has no default

1Check

TypeORM sets nullable to false unless you say otherwise. PostgreSQL fills old rows with NULL when there is no default, which breaks NOT NULL.

-- in psql
\d product

2Fix

Add a database default so existing rows receive a value. A non-volatile default is stored in table metadata and needs no rewrite.

@Column({ type: 'numeric', precision: 10, scale: 2, default: 0 })
price: number;

3Verify

Run the migration or sync again and check the column.

-- in psql
SELECT count(*) FROM product WHERE price IS NULL;

Confirmed by postgresql.org

Did this fix it?

Cause 2 of 3

The column should be optional

1Check

If NULL is a real state, NOT NULL is the wrong rule for it.

SELECT count(*) FROM product WHERE price IS NULL;

2Fix

Declare it nullable so TypeORM stops requiring a value.

@Column({ type: 'numeric', nullable: true })
price: number | null;

3Verify

The schema applies without error.

npx typeorm migration:run -d src/data-source.ts

Confirmed by typeorm.io

Did this fix it?

Cause 3 of 3

Existing rows already hold NULL and you want NOT NULL

1Check

SET NOT NULL only works when no row has NULL in that column. Fill them first, then apply the constraint, ideally in a migration, not through synchronize.

SELECT id FROM product WHERE price IS NULL LIMIT 10;

2Fix

Update the old rows, then add the constraint.

UPDATE product SET price = 0 WHERE price IS NULL;
ALTER TABLE product ALTER COLUMN price SET NOT NULL;

3Verify

The ALTER finishes without error.

SELECT count(*) FROM product WHERE price IS NULL;

Confirmed by postgresql.org

Did this fix it?

How do I stop it coming back?

  • Use migrations on any database with data. The TypeORM docs warn that synchronize can lose production data.
  • Give each new required column a default in the same change that adds it.
  • Test every schema change against a copy of production data.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Projects of mine built on the same technologies:

Related fixes

Back end

NestJS GraphQL cannot work out the output type of a field or query

Cannot determine a GraphQL output type for the "<name>". Make sure your class is decorated with an appropriate decorator.

In code-first mode Nest reads TypeScript types through decorators, and arrays, promises and unions disappear at runtime.

2 causes, each with a fix

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.