Simple API CRUD done with ExpressoTS and Postgres
Tags: typescript, api, database, expressots
Published: 2023-09-26 07:37:54

Choosing the right database connection strategy for your backend application often feels like navigating through a maze. There's no one-size-fits-all answer, as the best approach varies based on your application's needs. However, this post offers a strong, opinionated guide to help you think about what strategy to use.

When considering the database connection architecture, I break it down into two broad categories and then narrow down the choice based on specific application requirements.

## High Performant Applications
These are applications that handle large data volumes, transactions, or numbers of users, focusing on low latency and high reliability. Note that "high-performance" can vary by domain and isn't just about speed. Features of High-Performance Applications include:

- High Throughput
- Data Intensive
- Real Time
- Low Latency
- Event Driven
- Stream Processing
 
## Enterprise Applications

These are often large-scale applications built to serve specific business functions or industry requirements. Features include:

- Integration capabilities
- Extensible
- User friendly
- Data integrity
- Compliance
- Reporting and analytics

Exampled include ERP, CRM, SCM, CMS, Bi, E-commerce, etc. 

## Which Strategy is Right for You?
Here's a quick comparison table for Single Connection vs Connection Pooling:

## (Pros) of Single Connection vs Connection Pool

| Aspect           | Single Connection (Pros)                                 | Connection Pool (Pros)                     |
|------------------|----------------------------------------------------------|--------------------------------------------|
| Scalability      | Simpler to set up and manage.                            | More scalable.                             |
| Performance      | No overhead for creating and destroying connections.     | Higher performance for concurrent requests.|
| Reliability      | Easier to debug since all queries go through one connection. | Higher reliability.                       |
| Resource Utilization | Lower resource utilization.                            | Better resource utilization.               |
| Complexity       | Less code complexity, easier to implement.               | Handles multiple connections gracefully.   |
| Use Case         | Suitable for simple applications with limited concurrency. | Suitable for read-heavy or write-heavy applications. |

## (Cons) of Single Connection vs Connection Pool

| Aspect           | Single Connection (Cons)                                 | Connection Pool (Cons)                     |
|------------------|----------------------------------------------------------|--------------------------------------------|
| Scalability      | Limited scalability.                                     | Slightly more complex to manage.           |
| Performance      | Lower performance for concurrent requests.               | Some overhead for managing the pool.       |
| Reliability      | A single failure can impact all interactions.            | Possible connection leaks if not managed.  |
| Resource Utilization | Limited ability to utilize multiple CPU cores.        | Requires more memory and CPU.              |
| Complexity       | May require application-level queueing.                  | Increased code complexity.                 |
| Use Case         | Not suitable for read-heavy or write-heavy applications. | May be overkill for very simple applications. |

## Use Case Recommendations
- Connection Pooling: Best for read-heavy or write-heavy applications or those with many concurrent database operations.

- Single Connection: Good enough for simple apps with limited concurrent database operations.

Most modern web apps benefit from connection pooling due to its scalability and performance advantages.

## Sample Code with ExpressoTS and Postgres
Here's a quick code snippet to show how you might implement a connection pool using the ExpressoTS framework and Postgres' "pg" driver.

### Provider responsible to create the pool
```typescript
import { provideSingleton } from "@expressots/core";
import { Pool } from "pg";

/**
 * Configuration of the database pool.
 */
const pool = {
    host: "localhost",
    port: 5432,
    user: "postgres",
    password: "postgres",
    database: "expressots",
};

/**
 * Provider to inject the database pool into the container.
 */
@provideSingleton(PostgresProvider)
export class PostgresProvider {
    public get Pool(): Pool {
        return new Pool(pool);
    }
}
```

### Generic Repository pattern
```typescript
@provide(BaseRepository)
export class BaseRepository<T extends IEntity> implements IBaseRepository<T> {
    protected db!: Pool;
    protected tableName: string;

    constructor() {
        this.db = container.get(PostgresProvider).Pool;
    }

    async create(item: T): Promise<T | null> {
        const client = await this.db.connect();

        try {
            const fields = Object.keys(item).join(", ");
            const values = Object.values(item);
            const placeholders = values
                .map((_, index) => `$${index + 1}`)
                .join(", ");
            const res: QueryResult = await client.query(
                `INSERT INTO ${this.tableName} (${fields}) VALUES (${placeholders}) RETURNING *`,
                values,
            );

            return res.rows[0] as T;
        } finally {
            client.release();
        }
    }

    async delete(id: string): Promise<boolean> {
        const client = await this.db.connect();

        try {
            const res: QueryResult = await client.query(
                `DELETE FROM ${this.tableName} WHERE id = $1`,
                [id],
            );
            return res.rowCount > 0;
        } finally {
            client.release();
        }
    }

    async update(item: T): Promise<T | null> {
        const client = await this.db.connect();

        try {
            const fields = Object.keys(item)
                .map((key, index) => `${key} = $${index + 1}`)
                .join(", ");
            const values = Object.values(item);
            const res: QueryResult = await client.query(
                `UPDATE ${this.tableName} SET ${fields} WHERE id = $${values.length} RETURNING *`,
                values,
            );
            return res.rows[0] as T;
        } finally {
            client.release();
        }
    }

    async find(id: string): Promise<T | null> {
        const client = await this.db.connect();

        try {
            const res: QueryResult = await client.query(
                `SELECT * FROM ${this.tableName} WHERE id = $1`,
                [id],
            );
            return res.rows[0] as T;
        } finally {
            client.release();
        }
    }

    async findAll(): Promise<T[]> {
        const client = await this.db.connect();
        console.log(this.tableName);
        try {
            const res: QueryResult = await client.query(
                `SELECT * FROM ${this.tableName}`,
            );
            return res.rows as T[];
        } finally {
            client.release();
        }
    }
}
```

### A Create User use-case example
```typescript
// Controller
@controller("/user/create")
export class UserCreateController extends BaseController {
    constructor(private createUserUseCase: CreateUserUseCase) {
        super();
    }

    @Post("/")
    async execute(
        @body() payload: CreateUserRequestDTO,
        @response() res: Response,
    ): Promise<CreateUserResponseDTO> {
        return this.callUseCase(
            await this.createUserUseCase.execute(payload),
            res,
            StatusCode.Created,
        );
    }
}

// Use Case
@provide(CreateUserUseCase)
export class CreateUserUseCase {
    constructor(
        private userRepository: UserRepository,
        private user: User,
        private report: Report,
    ) {}

    async execute(
        payload: CreateUserRequestDTO,
    ): Promise<CreateUserResponseDTO | AppError> {
        try {
            this.user.name = payload.name;
            this.user.email = payload.email;

            const userExists: User | null =
                await this.userRepository.findByEmail(this.user.email);

            if (userExists) {
                const error = this.report.error(
                    "User already exists",
                    StatusCode.BadRequest,
                    "create-user-usecase",
                );

                return error;
            }

            await this.userRepository.create(this.user);

            return {
                id: this.user.id,
                name: this.user.name,
                email: this.user.email,
                message: "user created successfully",
            };
        } catch (error: any) {
            throw error;
        }
    }
}
```

For more details, the complete code can be found [here!](https://github.com/expressots/examples/tree/main/05-postgres-connection-pool-repository)

By considering these aspects, you'll be better prepared to choose the appropriate database connection strategy for your next project.