08. Data Persistence & ORM

1. The Vanilla Mechanics

Interacting with a database is a fundamental part of most backend applications. In raw Node.js or basic Express, this often involves manually managing connection pools and writing raw SQL strings.

Concept: Data Access Layer

Connection Pools: Managing a set of reusable database connections.
Manual SQL: Writing raw SQL queries like SELECT * FROM users.
Row Mapping: Converting raw database rows to JavaScript objects.

The “Raw” Implementation (Example)

const { Pool } = require('pg');
const pool = new Pool();

app.get('/users', async (req, res) => {
  try {
    const { rows } = await pool.query('SELECT * FROM users');
    res.json(rows);
  } catch (err) {
    res.status(500).send('Database error');
  }
});

Challenges:

Maintenance: Changing the database schema requires manually updating all SQL strings.
SQL Injection: Vulnerability to attacks if SQL is not properly sanitized.
Boilerplate: Manually handling connection opening, closing, and error handling for every query.

2. The NestJS Abstraction

NestJS integrates with powerful Object-Relational Mapping (ORM) libraries like TypeORM and Prisma through its modular system.

Key Advantages:

Entities: Define your database schema using TypeScript classes and decorators.
Repository Pattern: A standardized way to perform CRUD operations without writing raw SQL.
Integration: NestJS provides modules (@nestjs/typeorm, @nestjs/prisma) that manage the connection lifecycle automatically.

The NestJS Implementation (TypeORM):

@Entity()
export class User {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  name: string;
}

@Injectable()
export class UsersService {
  // Inject the repository directly into the service!
  constructor(
    @InjectRepository(User)
    private usersRepository: Repository<User>,
  ) {}

  findAll(): Promise<User[]> {
    return this.usersRepository.find(); // Clean, type-safe method!
  }
}

3. Engineering Labs

Lab 8.1: Set up a local PostgreSQL or MongoDB database and perform a manual INSERT and SELECT operation using the standard driver in a plain Node.js script.
Lab 8.2: Re-implement the same in NestJS using either TypeORM or Prisma. Define an Entity and use the Repository/Client to perform the same operations.