NestJS – Databases with TypeORM

TypeORM is the ORM Nest documents first, and @nestjs/typeorm is a thin wrapper that makes its connection and repositories injectable. Most of what follows is about the decisions the wrapper does not make for you.

Connecting

TypeOrmModule.forRoot(buildDataSourceOptions()),

One connection for the application. forRoot in the root module, then forFeature in each feature module to register the repositories that module uses:

@Module({
  imports: [TypeOrmModule.forFeature([Project, Quote, ServiceCategory])],
  controllers: [ProjectsController],
  providers: [ProjectsService],
  exports: [ProjectsService],
})
export class ProjectsModule {}

forFeature does not open a second connection. It makes Repository<Project> and the other two injectable inside this module — and the list doubles as documentation, telling you which tables a feature touches before you open its service.

Entities

@Entity('users')
export class User extends BaseEntity {
  @Index({ unique: true })
  @Column({ type: 'varchar', length: 255 })
  email: string

  @Column({ type: 'varchar', length: 72, name: 'password_hash' })
  passwordHash: string

  @Column({ type: 'varchar', length: 80, name: 'first_name' })
  firstName: string
  // ...
}

Three habits in there are worth adopting wholesale.

Every column has an explicit type. TypeORM can infer one from the TypeScript type, and the inference is a guess — string becomes varchar(255) whether you wanted 40 or 5000.

Every multi-word column has an explicit name. The default is the property name verbatim, so publicId becomes a column called "publicId" — quoted, case-sensitive, and unpleasant to type in psql. A global SnakeNamingStrategy also works and makes the mapping invisible; spelling it out costs one option and removes the question.

Shared columns live on a base class. A plain class with no @Entity(), so no table of its own:

export abstract class BaseEntity {
  @PrimaryGeneratedColumn('increment', { type: 'bigint' })
  id: string

  @Column({ type: 'uuid', unique: true, name: 'public_id' })
  @Generated('uuid')
  publicId: string

  @CreateDateColumn({ type: 'timestamptz', name: 'created_at' })
  createdAt: Date

  @UpdateDateColumn({ type: 'timestamptz', name: 'updated_at' })
  updatedAt: Date
}

Defining those once is what stops the sixth entity from quietly getting timestamp instead of timestamptz. A bare timestamp stores no zone, so the same instant written from a laptop in Austin and a server in UTC comes back as two different times, and nothing records which was meant.

Two ids, and why

id is a bigint for internal foreign keys. publicId is a UUID and the only one the API ever serialises.

Exposing the sequential id leaks the row count — a competitor reads how many jobs the site has had off a single URL — and makes every record trivially enumerable by counting upward. The UUID costs sixteen bytes and closes both.

@Generated('uuid') puts the default in Postgres rather than in the application, so a row inserted by a seed script, a migration or by hand in psql gets one too.

The type surprises

Two of them, and both bite once.

bigint comes back as a JavaScript string. Postgres bigint reaches 9.2×10¹⁸ while Number.MAX_SAFE_INTEGER is 9×10¹⁵, so returning a number would silently lose precision on large ids. Compare these with === on strings and never do arithmetic on them.

numeric comes back as a string too, for the same reason — a float cannot represent every decimal exactly, which is why you used numeric for money. The fix is a column transformer, so services see numbers and the precision decision stays at the boundary.

The same "already the right type" thinking applies to dates:

preferredStartDate: project.preferredStartDate,

No .toISOString() — the column is a date and pg returns those as YYYY-MM-DD text. Calling toISOString on it is a TypeError, and wrapping it in new Date() first would reintroduce exactly the timezone shift the date type exists to avoid.

Relations, and the ESM cycle

@OneToOne('ContractorProfile', (profile: ContractorProfile) => profile.user)
contractorProfile: ContractorProfile | null

@OneToMany('Project', (project: Project) => project.homeowner)
projects: Project[]

The target is a string and a thunk, not a class reference. user.entity.ts and contractor-profile.entity.ts import each other, and a direct class reference would be evaluated while the other module is still initialising and read as undefined. The function defers it until both exist.

The matching import type is the other half — a type-only import is erased at compile time, so it cannot create the runtime cycle in the first place.

Migrations, and the setting that must stay false

synchronize: false,
migrationsRun: false,
logging: ['error', 'warn', 'migration'],

synchronize is the most tempting setting in TypeORM: turn it on and the schema appears from your entities with no migration to write. It also silently drops columns it believes are gone, and it leaves no record of how the schema reached its current state. Migrations are that record.

migrationsRun: false is the second half. Two application instances booting together would both try to migrate, and the loser fails against a half-applied schema. Running migrations is a deliberate, single-threaded step:

{
  "scripts": {
    "migration:run": "npm run build && node ./node_modules/typeorm/cli.js migration:run -d dist/database/data-source.js",
    "migration:revert": "npm run build && node ./node_modules/typeorm/cli.js migration:revert -d dist/database/data-source.js"
  }
}

Two rules go with them. Never edit an applied migration — Postgres has already run it and the migrations table has recorded it, so an edit only changes what new databases get, and the two diverge forever with nothing to detect it. Write another one. And order matters: TypeORM runs them in the order listed, which is why each class name carries a timestamp prefix.

Repositories and the DataSource

Two ways to reach the database, and this codebase uses the second:

// One repository per entity, injected.
constructor(@InjectRepository(Project) private readonly projects: Repository<Project>) {}

// Or the connection, from which any repository is available.
constructor(@InjectDataSource() private readonly dataSource: DataSource) {}

@InjectRepository is tidier when a service touches one or two entities. @InjectDataSource wins when a service spans several — ProjectsService touches four — and it is the only way to open a transaction.

Transactions, which is the point

Accepting a quote must also decline every other pending quote and hire the project. If any part fails, none may land: halfway through, the project is hired with two pending quotes on it, and a homeowner refreshing at that instant sees two people they can both still accept.

await this.dataSource.transaction(async (manager) => {
  const project = await manager.findOne(Project, {
    where: { publicId: projectPublicId },
    lock: { mode: 'pessimistic_write' },
  })

  if (!project || project.homeownerId !== user.id) {
    throw new NotFoundException('That project no longer exists.')
  }
  if (!QUOTABLE_STATUSES.includes(project.status)) {
    throw new ConflictException('You have already hired someone for this project.')
  }
  // ...
  await manager.update(
    Quote,
    { projectId: project.id, status: QuoteStatus.PENDING, publicId: Not(winner.publicId) },
    { status: QuoteStatus.DECLINED },
  )
  await manager.update(Quote, { id: winner.id }, { status: QuoteStatus.ACCEPTED })
  await manager.update(Project, { id: project.id }, { status: ProjectStatus.HIRED })
})

Two things make this correct rather than merely atomic.

Every operation goes through manager. The callback's EntityManager is bound to the transaction; a stray this.dataSource.getRepository(...) inside the callback runs on a different connection, outside the transaction, and will not roll back with it. That is the classic way to get a transaction that does not do what it says.

The row is locked. lock: { mode: 'pessimistic_write' } issues SELECT … FOR UPDATE. Without it, two accepts arriving together both read a quoted project, both pass the status check, and both write — leaving two accepted quotes. The lock makes the second transaction wait, then re-read a hired project and fail the check properly.

Throwing inside the callback rolls back, which is why the checks are in there rather than before.

Loading relations, and the cost

const FULL_RELATIONS = {
  homeowner: true,
  category: true,
  review: { homeowner: true },
  quotes: { contractor: { user: true, categories: true, portfolio: true } },
} as const

Named once so no route can load half of it. That matters because the serialisers assume what they ask for is present — a route that forgets a relation gets a loud undefined in a test rather than a quietly incomplete response in production.

It is also where an ORM gets expensive. Relations are not free, and the two failure modes pull in opposite directions: loading too little produces N+1 queries as each item fetches its own children; loading too much produces one enormous join. TypeORM's relations option uses joins, which is right here — one project with its quotes and their contractors — and would be wrong for a list of a thousand rows.

When a query gets complicated enough that the options object stops being readable, drop to the query builder or to raw SQL. An ORM is a convenience for the common case, not an obligation:

const [category] = await dataSource.query<Array<{ public_id: string }>>(
  `SELECT public_id FROM service_categories WHERE slug = 'plumbing'`,
)

Note the type parameter — raw queries return any otherwise, and that any spreads. And note the snake_case keys: raw SQL bypasses the entity mapping entirely, so you get the column names, not the property names.

Where transactions belong

A transaction is a service concern, not a controller one, and it is worth being explicit about why: the callback defines the boundary, and a controller has no way to extend one across two service calls.

That constrains how services compose. If two services must both write inside one transaction, one of them has to accept an EntityManager:

async createWithin(manager: EntityManager, dto: CreateProjectDto) { /* ... */ }

It is not elegant, and the alternatives are worse. Nest's docs describe a request-scoped transaction provider using async local storage, which removes the parameter and adds machinery that is genuinely hard to reason about. Passing the manager is explicit, and the type makes it obvious which methods participate.

Ordering is not free

const project = await this.dataSource.getRepository(Project).findOne({
  where: { publicId },
  relations: FULL_RELATIONS,
  order: { quotes: { createdAt: 'ASC' } },
})

Without that order, the quotes come back in whatever order Postgres felt like — which for a small table is usually insertion order, and therefore looks correct in development right up until it does not. An ORDER BY is the only thing that makes a list ordered.

Next: authentication with JWT, which is where the users table above starts being useful.