TypeORM sync fails when a new column is NOT NULL on an existing table
QueryFailedError: column "price" of relation "product" contains null valuesShort 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.
Fix it fast
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;2Match what you see, then jump to its fix
- 1
The 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
Some products legitimately have no price.The column should be optionalFix - 3
The column existed and was nullable before, and you removed nullable.Existing rows already hold NULL and you want NOT NULLFix
- 1
What does the error look like?
QueryFailedError: column "price" of relation "product" contains null valuesQueryFailedError: 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 product2Fix
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
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.tsConfirmed by typeorm.io
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
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:
- TaxiDexMy own multi-tenant taxi dispatch SaaS: API, dispatcher portal, driver and passenger apps, and the servers underneath.
- QistLockA NestJS and React SaaS driving an Android Device Owner agent that locks financed phones when payments lapse.
- WorkforceA field app, an admin console and a self-hosted Node.js server for push and reconciliation.
Related fixes
Back end
PostgreSQL error: bind message has N parameter formats but 0 parameters
QueryFailedError: bind message has N parameter formats but 0 parameters
One query is carrying more bound values than the Postgres protocol can describe.
2 causes, each with a fixBack end
TypeORM migration:generate finds no changes to put in the migration
typeorm migration:generate finds no changes in the database schema, writes no file and exits with code 1
migration:generate compares the entities your DataSource loads with the real database, and writes only the difference.
4 causes, each with a fixBack end
TypeORM connects but creates no tables or columns
A new column is missing, with no error at startup
TypeORM does not create tables by itself.
3 causes, each with a fixBack end
TypeScript cannot find module '@nestjs/core' or its type declarations
error TS2307: Cannot find module '@nestjs/common' or its corresponding type declarations.
TS2307 means TypeScript searched node_modules and found neither the package nor its types.
3 causes, each with a fixBack end
Node.js in Docker Compose cannot reach MySQL on 127.0.0.1:3306
Error: connect ECONNREFUSED 127.0.0.1:3306
Inside a container, 127.0.0.1 is that container, not MySQL.
3 causes, each with a fixBack 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