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
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
@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
// 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!
By considering these aspects, you'll be better prepared to choose the appropriate database connection strategy for your next project.
Top comments (0)