PostgreSQL Database Connection

This module handles:

• PostgreSQL connection URL generation
• Database connection pooling
• Environment variable configuration
• SQLx asynchronous connection management

The implementation uses:

• sqlx
• PgPool
• PgPoolOptions

---

File Structure

---

Module Dependencies

use sqlx::{PgPool,postgres::PgPoolOptions};

---

Database Connection Architecture

---

Source Code

use sqlx::{PgPool,postgres::PgPoolOptions};

pub fn database_url() -> String{
    format!(
        "postgres://{}:{}@{}:{}/{}?ssl_mode={}",
        std::env::var("DB_USER").unwrap(),
        std::env::var("DB_PASSWORD").unwrap(),
        std::env::var("DB_HOST").unwrap(),
        std::env::var("DB_PORT").unwrap(),
        std::env::var("DB_NAME").unwrap(),
        std::env::var("DB_SSLMODE").unwrap(),
    )
}

pub async fn connect_db() -> PgPool{

    // db_user,db_password,db_host,db_port,db_name,db_ssl
    // println!("Database URL:{}",database_url);
    let db_url = database_url();

    PgPoolOptions::new()
        .max_connections(5)
        .connect(&db_url)
        .await
        .expect("Failed to connect database")
}

---

Database URL Generator

Function

pub fn database_url() -> String

---

Purpose

Generates a PostgreSQL connection string dynamically using environment variables.

---

URL Format

postgres://username:password@host:port/database?ssl_mode=xxxx

---

Environment Variables

Variable	Description
DB_USER	Database username
DB_PASSWORD	Database password
DB_HOST	Database server host
DB_PORT	Database server port
DB_NAME	Database name
DB_SSLMODE	SSL connection mode

---

Connection URL Construction

Source Code

format!(
    "postgres://{}:{}@{}:{}/{}?ssl_mode={}",

---

URL Component Breakdown

---

Example Generated URL

postgres://xxxx:xxxx@localhost:5432/xxxx?ssl_mode=xxxx

---

Database Connection Pool

Function

pub async fn connect_db() -> PgPool

---

Purpose

Creates and initializes a PostgreSQL connection pool using SQLx.

---

Why Connection Pooling

Connection pooling improves performance by:

• Reusing active database connections
• Reducing connection overhead
• Managing concurrent database requests efficiently

---

Connection Pool Flow

---

Pool Configuration

Source Code

PgPoolOptions::new()
    .max_connections(5)

---

Explanation

Creates a connection pool with:

• Maximum 5 active database connections

---

Max Connections

Configuration	Value
max_connections	5

---

Database Connection Execution

Source Code

.connect(&db_url)
.await

---

Explanation

Performs asynchronous connection initialization using the generated database URL.

---

Error Handling

Source Code

.expect("Failed to connect database")

---

Behavior

If database connection fails:

• Application execution stops
• Panic error is triggered

---

Connection Lifecycle

---

Return Type

PgPool

Represents a shared asynchronous PostgreSQL connection pool.

---

Async Runtime Integration

The connection uses asynchronous execution.

Compatible runtimes:

• Tokio
• Async Rust ecosystem

---

Security Considerations

Environment Variables

Sensitive database credentials are stored in environment variables instead of hardcoded values.

---

SSL Configuration

ssl_mode={}

Allows configurable SSL behavior depending on deployment environment.

---

Production Recommendation

Recommended improvements for production systems:

Improvement	Purpose
Connection timeout	Prevent hanging connections
Retry mechanism	Improve startup resilience
Structured logging	Better debugging
Secure secret manager	Protect credentials
Health checks	Monitor database availability

---

Example .env

DB_USER=xxxx
DB_PASSWORD=xxxx
DB_HOST=localhost
DB_PORT=5432
DB_NAME=xxxx
DB_SSLMODE=disable

---

SQLx Components

Component	Purpose
PgPool	Shared PostgreSQL pool
PgPoolOptions	Pool configuration builder
connect()	Open database connection
max_connections()	Configure pool capacity

---

Design Characteristics

Asynchronous

Uses async connection handling.

---

Reusable

Single shared pool can be injected across services and repositories.

---

Centralized Configuration

Database configuration is isolated in one module.

---

Usage Example

let pool = connect_db().await;

---

Architecture Summary

Layer	Responsibility
Environment Variables	Store database credentials
database_url()	Generate PostgreSQL URL
connect_db()	Initialize connection pool
PgPool	Share database connections
SQLx	Handle async PostgreSQL communication