# Week 10 -- Task 1: Connecting a NestJS Tasks API to PostgreSQL with TypeORM

Introduction

Until now, the Tasks API stored its data in memory. That was useful
while learning NestJS because we could focus on controllers, services,
modules, and dependency injection without worrying about a database. The
limitation is that in-memory data disappears when the application
restarts.

For this task, I moved the Week 8 Tasks API to PostgreSQL using
TypeORM. The goal was to keep the API structure familiar while
replacing temporary storage with persistent database storage.

Implementation: week-10/Task-01/nest-tasks-api/. The Week 8
implementation remains separate and unchanged.

What the API does

The API manages users and the tasks that belong to them. One user can
have multiple tasks, while each task belongs to exactly one user.

• Create and list users.
• Create, list, retrieve, update, and delete tasks.
• Return the related user when fetching tasks.
• Prevent a task from referencing a user that does not exist.

Authentication and per-user authorization are intentionally left for
Week 10 Task 2.

Tech stack

• NestJS 11 -- application structure and dependency injection.
• TypeScript -- types and application code.
• PostgreSQL -- persistent relational database.
• TypeORM 0.3 -- object-relational mapping.
• ConfigModule -- environment-based database configuration.
• Jest -- automated tests.

1. Entities: mapping classes to tables

An entity is a TypeORM class that represents a database table. Its
properties describe columns, and decorators provide the mapping
information.

User entity

File: src/users/entities/user.entity.ts

The UserEntity maps to the users table. It contains a generated ID,
name, unique email, timestamps, and a collection of tasks.

@Entity({ name: 'users' })
export class UserEntity {
  @PrimaryGeneratedColumn()
  id!: number;

  @Column({ type: 'varchar', length: 100 })
  name!: string;

  @Column({ type: 'varchar', length: 180, unique: true })
  email!: string;

  @OneToMany(() => TaskEntity, (task) => task.user)
  tasks!: TaskEntity[];
}

Task entity

File: src/tasks/entities/task.entity.ts

The TaskEntity maps to the tasks table. It contains a title,
optional description, completion status, timestamps, and a relation to
its owner.

@Entity({ name: 'tasks' })
export class TaskEntity {
  @PrimaryGeneratedColumn()
  id!: number;

  @Column({ type: 'varchar', length: 200 })
  title!: string;

  @Column({ type: 'text', nullable: true })
  description!: string | null;

  @Column({ type: 'boolean', default: false })
  completed!: boolean;

  @ManyToOne(() => UserEntity, (user) => user.tasks, {
    nullable: false,
    onDelete: 'CASCADE',
  })
  @JoinColumn({ name: 'user_id' })
  user!: UserEntity;
}

The decorators tell TypeORM how the TypeScript classes map to database
structures.

2. Understanding the one-to-many relationship

The relationship is one user to many tasks.

• @OneToMany() is declared on the user side.
• @ManyToOne() is declared on the task side.
• @JoinColumn({ name: 'user_id' }) names the foreign-key column in the tasks table.

The actual foreign key lives on tasks because each task stores the ID
of its owner.

users tasks

id (PK) id (PK)
name title
email (unique) description
completed
user_id (FK → users.id)

A task cannot exist without a user because the relation is configured
with nullable: false.

The migration also creates the foreign key with ON DELETE CASCADE.
Deleting a user therefore causes the database to delete that user's
tasks as well.

3. Why use a repository?

A repository provides an interface for working with stored data. Instead
of placing database queries directly inside a controller, persistence
operations can be kept behind a repository.

In this project, the Tasks feature uses a repository abstraction.

File: src/tasks/repositories/tasks.repository.ts

export abstract class TasksRepository {
  abstract findAll(): Promise<TaskEntity[]>;
  abstract findOne(id: number): Promise<TaskEntity | null>;
  abstract create(input: CreateTaskDto): Promise<TaskEntity>;
  abstract update(
    id: number,
    updates: UpdateTaskDto,
  ): Promise<TaskEntity | null>;
  abstract remove(id: number): Promise<boolean>;
}

This abstract class describes the operations the task service needs. It
does not contain TypeORM query logic.

The PostgreSQL implementation is in
src/tasks/repositories/typeorm-tasks.repository.ts. It extends
TasksRepository and uses TypeORM's Repository<TaskEntity> to perform
database operations.

For example:

findAll(): Promise<TaskEntity[]> {
  return this.tasks.find({
    relations: { user: true },
    order: { id: 'ASC' },
  });
}

The relations option tells TypeORM to load the related user along with
each task.

When creating a task, the repository first looks up the requested user.
If that user does not exist, it throws NotFoundException. Otherwise,
it creates and saves the task with that user relation.

How NestJS connects the abstraction to TypeORM

File: src/tasks/tasks.module.ts

providers: [
  TasksService,
  {
    provide: TasksRepository,
    useClass: TypeOrmTasksRepository,
  },
]

This provider tells NestJS to inject TypeOrmTasksRepository whenever a
class requests TasksRepository.

The service can therefore depend on the repository contract instead of
directly creating a TypeORM repository.

A detail worth noticing: the Users feature currently uses
@InjectRepository(UserEntity) directly in UsersService. The custom
repository abstraction is used by the Tasks feature, not uniformly
across both features.

4. Controller and service responsibilities

The request flow is:

HTTP request → Controller → Service → Repository → PostgreSQL

Controller

File: src/tasks/tasks.controller.ts

The controller defines these HTTP routes:

Method Route Purpose

GET /tasks List tasks
GET /tasks/:id Get one task
POST /tasks Create a task
PATCH /tasks/:id Update a task
DELETE /tasks/:id Delete a task

The controller receives the request, extracts route parameters or the
request body, and calls the service. ParseIntPipe converts and
validates the task ID route parameter as an integer.

Service

File: src/tasks/tasks.service.ts

The service contains task-related application logic and delegates
persistence work to TasksRepository.

It also handles missing tasks. For example, findOne() asks the
repository for a task and throws NotFoundException if the repository
returns null.

This keeps HTTP-level error decisions out of the TypeORM implementation.

5. Environment-based database configuration

Database credentials should not be hardcoded into application source
code.

File: src/app.module.ts

The application imports:

ConfigModule.forRoot({ isGlobal: true })

This makes configuration values available throughout the application.

The TypeORM connection is configured asynchronously using
TypeOrmModule.forRootAsync() and ConfigService. The connection reads
DB_HOST, DB_PORT, DB_USERNAME, DB_PASSWORD, and DB_DATABASE
from the environment.

The .env.example file documents the expected variable names. A local
.env file should contain the actual database connection values and
should not be committed to Git.

Why is synchronize false?

TypeORM's synchronize option can automatically modify the database
schema based on entity definitions. That may be convenient for a quick
experiment, but it is risky for an application where schema changes need
review and control.

This project uses migrations instead:

synchronize: false,
migrationsRun: false,

The database schema is changed deliberately through migration files.

6. Migrations: controlled database changes

A migration is a version-controlled set of database changes.

Migration file:
src/database/migrations/1700000000000-CreateUsersAndTasks.ts

It implements two methods:

• up() -- applies the schema changes.
• down() -- reverses those changes.

What happens in up()?

1. Creates the users table.
2. Adds a primary key to users.
3. Adds a unique constraint on users.email.
4. Creates the tasks table.
5. Adds a primary key to tasks.
6. Adds the user_id foreign key referencing users.id.
7. Configures ON DELETE CASCADE.
8. Creates an index on tasks.user_id.

What happens in down()?

The rollback removes the index and then drops the tasks and users
tables.

The order matters: the dependent tasks table is dropped before the
referenced users table.

Running migrations

From the nest-tasks-api directory:

npm run migration:run
npm run migration:show

Undo the most recently applied migration:

npm run migration:revert

Generate a migration after changing entities:

npm run migration:generate

Generated migrations should always be reviewed before being committed or
applied.

7. Setting up and running the project

From the repository root:

cd week-10/Task-01/nest-tasks-api
npm install
cp .env.example .env

Create a PostgreSQL database named tasks_db, then fill in the database
credentials in .env.

Run the migration:

npm run migration:run

Start the development server:

npm run start:dev

The API listens on port 3002 by default.

Other useful commands:

npm run build
npm test
npm run migration:show
npm run migration:revert

8. Example API workflow

A task needs an existing user, so create a user first.

Create a user

POST /users
Content-Type: application/json

{
  "name": "Alex",
  "email": "[email protected]"
}

Use the returned user ID when creating a task.

Create a task

POST /tasks
Content-Type: application/json

{
  "title": "Learn TypeORM",
  "description": "Understand entities, repositories and migrations",
  "userId": 1
}

The repository checks that user 1 exists before saving the task.

List tasks

GET /tasks

The task repository loads the related user, so the response can include
the task and its owner.

Update a task

PATCH /tasks/1
Content-Type: application/json

{
  "completed": true
}

Delete a task

DELETE /tasks/1

The API also provides GET /tasks/:id to retrieve one task and
GET /users to list users with their tasks.

9. What I learned

This task helped connect NestJS concepts to a real relational database.

• An entity maps a TypeScript class to a database table.
• TypeORM decorators describe columns, keys, and relationships.
• A one-to-many relationship is represented by @OneToMany() and @ManyToOne().
• The foreign key belongs on the many side of the relationship.
• A repository keeps persistence operations separate from the service.
• NestJS providers connect an abstraction to a concrete implementation.
• Environment variables keep database configuration outside source code.
• Migrations make schema changes reviewable and reversible.
• Database constraints protect data integrity.
• Loading relations lets the API return associated records.

10. A few things to remember

Entity vs migration: An entity describes how TypeORM maps a table in
application code. A migration describes the actual database changes
needed to create or modify that table.

Repository vs service: The repository handles persistence. The
service coordinates application behavior and decides how to respond when
data is missing.

Foreign key vs relation decorator: TypeORM relation decorators
describe the object relationship, while the database foreign key
enforces that relationship at the database level.

synchronize vs migrations: synchronize tries to update the
schema automatically. Migrations let the developer review and control
schema changes.

Tasks repository vs users repository in this implementation: Tasks
use a custom abstract repository and a TypeORM implementation. Users use
TypeORM's injected repository directly. Both approaches work, but the
project does not apply the custom repository abstraction uniformly.

Conclusion

Week 10 Task 1 was the step from a simple in-memory API to an API backed
by a relational database. The important part was not just connecting
PostgreSQL; it was understanding how entities, relationships,
repositories, dependency injection, configuration, and migrations fit
together.

The result is a small NestJS Tasks API where data survives application
restarts and the database itself enforces important relationships
between users and tasks.