TypeORM synchronize dropped my indexes: declare them

If you run TypeORM synchronize against a database, it makes the tables match your entities. That includes indexes. Any index your entities do not declare is treated as something to remove, so an index you built by hand in psql will be dropped the next time the app starts with sync on. I lost seven indexes this way on a side project. This post covers what happened, how to declare every index on the entity with @Index (partial indexes included), and the rule I follow now on every TypeORM codebase.

How seven indexes disappeared on a deploy

The side project has a feed with several tabs and sort orders over a large product table. Some of those queries were slow, and I measured them on the server before touching anything. One tab read all 380k product rows on every request and took 2.3 to 3.8 s. The other sorts and filters took about 1 s each.

The fix for those queries was partial indexes: one per sort, covering only the rows that are listed. I created them by hand, straight in the database.

Then a deploy restarted the app. The DataSource had synchronize enabled, so on startup TypeORM compared the entities with the database, saw seven indexes the entities did not mention, and dropped all of them. The queries those indexes served had nothing to use any more.

That is what makes this one dangerous. Schema sync is doing exactly what it was built to do: make the database look like the code. If an index only exists in the database, the code says it should not exist.

Why TypeORM synchronize drops indexes it does not know about

Synchronize builds the schema from entity metadata. Columns, relations, unique constraints and indexes all come from decorators. When it diffs that metadata against the live database, anything extra is a difference to resolve, and for an index the resolution is DROP INDEX.

The TypeORM indexes documentation says this directly in its section on indexes TypeORM cannot express: if you create such an index manually, you need to mark it so it is not deleted on the next schema sync. Every other index has to be declared, or it is fair game.

So the fix is to declare every index on the entity, in a form schema sync understands.

Declaring indexes with the @Index decorator

@Index goes on a column for a single-column index, or on the entity class for a composite one. The options include unique and where, and where is how you write a partial index. Here is the shape I use, with names simplified:

import { Entity, PrimaryGeneratedColumn, Column, Index } from "typeorm";

@Entity("products")
@Index("idx_products_listed_newest", ["createdAt"], { where: `"is_listed" = true` })
@Index("idx_products_listed_price", ["price"], { where: `"is_listed" = true` })
export class Product {
  @PrimaryGeneratedColumn()
  id: number;

  @Column({ name: "is_listed", default: true })
  isListed: boolean;

  @Column("numeric")
  price: string;

  @Index()
  @Column({ name: "category_id" })
  categoryId: number;

  @Column({ name: "created_at", type: "timestamptz" })
  createdAt: Date;
}

A few details that catch people:

• The where string is raw SQL. It uses database column names (quoted if needed), so "is_listed" = true works and isListed = true fails.
• Name your indexes. Without a name, TypeORM generates one. An explicit name makes the migration readable and makes EXPLAIN output easy to match to the code.
• Composite indexes take property names. The column list in a class-level @Index uses entity properties (createdAt), which TypeORM maps to columns.

For an index that TypeORM cannot describe at all, such as one on an expression, the docs show @Index("name", { synchronize: false }). That tells schema sync the index exists and should be left alone, and the index itself is created in a migration.

Partial indexes need queries that match

A partial index only helps queries whose WHERE clause implies its predicate. If the index says "is_listed" = true and the query forgets that condition, the planner cannot use it. Partial unique indexes are declared the same way; I used one to stop the duplicates in fixing race condition duplicate rows. My default is to keep the predicate in one place in the query code, so the tab queries and the index definitions cannot drift apart.

Use migrations in production and keep synchronize for development

The other half of the fix is to stop letting the app rewrite its own schema in production. Synchronize is handy on a laptop, where the database is disposable and you want it to follow the entities as you edit them. In production it turns every deploy into an unreviewed schema change. Pramod Sadalage and Martin Fowler's Evolutionary Database Design makes the case for the alternative, where every database change is a migration script in version control.

My default for any TypeORM app that has real data:

• synchronize: false in every environment that is not a throwaway local database.
• Schema changes go through migrations, generated with migration:generate and read before they are committed. The TypeORM migrations guide covers the commands.
• Indexes are declared on the entity and created by a migration. The entity keeps the code honest; the migration is what runs against production.

That last point matters because the two protect against different mistakes. The decorator means a generated migration or a dev sync will never drop the index. The migration means the index exists in production with a reviewed history behind it.

The same rule on a production backend

On Buffdudes, the fitness platform whose backend I run, the rule is the same: every index lives on its model and in a migration, so the models and the database agree. That agreement is useful in its own right. When I ran an index audit there, TypeORM's schema diff between the models and the database was the first step, and it turned up one index that existed only in the database: a plan-streak index that enforces one open streak per user and plan. It was then declared on its model. I wrote up the whole process in how I run a Postgres index audit on a production snapshot.

If you are picking an ORM and wondering whether TypeORM is worth these sharp edges, I covered why I still chose it in TypeORM vs Prisma.

Is it safe to use TypeORM synchronize in production?

I would not. Synchronize applies whatever the diff says with no review step, and it will drop indexes, and in some cases columns, that the entities no longer describe. Use migrations for production schema changes, keep synchronize: false there, and treat sync as a local development tool.

Where I'd start

1. Check every DataSource config and turn synchronize off outside local development.
2. List the indexes in the database (\di in psql) and compare them with your @Index declarations.
3. Declare every missing one on its entity, using where for partial indexes and synchronize: false for anything TypeORM cannot express.
4. Make sure each one also has a migration, so production and code tell the same story.

Some of the database work behind these posts is on my projects page.