Configure Deno database connections to PostgreSQL and Redis with connection pooling

Install Deno runtime

First, install the latest version of Deno on your system if not already present.

curl -fsSL https://deno.land/install.sh | sh
echo 'export PATH="$HOME/.deno/bin:$PATH"' >> ~/.bashrc
source ~/.bashrc

curl -fsSL https://deno.land/install.sh | sh
echo 'export PATH="$HOME/.deno/bin:$PATH"' >> ~/.bashrc
source ~/.bashrc

Install PostgreSQL and Redis servers

Install PostgreSQL and Redis database servers for local development and testing.

sudo apt update
sudo apt install -y postgresql postgresql-contrib redis-server

sudo dnf install -y postgresql postgresql-server postgresql-contrib redis
sudo postgresql-setup --initdb

Configure PostgreSQL authentication

Set up PostgreSQL with a dedicated database and user for your Deno application.

sudo systemctl enable --now postgresql
sudo -u postgres psql -c "CREATE USER denoapp WITH PASSWORD 'secure_db_password123';"
sudo -u postgres psql -c "CREATE DATABASE denoapp_db OWNER denoapp;"
sudo -u postgres psql -c "GRANT ALL PRIVILEGES ON DATABASE denoapp_db TO denoapp;"

Configure Redis authentication

Enable Redis authentication and set a strong password for secure connections.

bind 127.0.0.1
requirepass redis_secure_password456
maxmemory 256mb
maxmemory-policy allkeys-lru
tcp-keepalive 300

sudo systemctl enable --now redis-server
sudo systemctl restart redis-server

Create project structure

Set up the basic Deno project structure with TypeScript configuration and database modules.

mkdir deno-db-app && cd deno-db-app
mkdir -p src/{config,database,models,utils}
touch src/main.ts src/config/database.ts src/database/postgresql.ts src/database/redis.ts

Configure database connection settings

Create centralized database configuration with environment variable support and connection pooling settings.

export interface DatabaseConfig {
  postgresql: {
    hostname: string;
    port: number;
    database: string;
    username: string;
    password: string;
    pool: {
      max: number;
      min: number;
      idle_timeout: number;
      connect_timeout: number;
    };
    ssl: {
      enabled: boolean;
      ca?: string;
      cert?: string;
      key?: string;
    };
  };
  redis: {
    hostname: string;
    port: number;
    password?: string;
    pool: {
      max: number;
      min: number;
      idle_timeout: number;
    };
    ssl: {
      enabled: boolean;
    };
    cluster: {
      enabled: boolean;
      nodes?: string[];
    };
  };
}

export const databaseConfig: DatabaseConfig = {
  postgresql: {
    hostname: Deno.env.get("PG_HOST") || "localhost",
    port: parseInt(Deno.env.get("PG_PORT") || "5432"),
    database: Deno.env.get("PG_DATABASE") || "denoapp_db",
    username: Deno.env.get("PG_USERNAME") || "denoapp",
    password: Deno.env.get("PG_PASSWORD") || "secure_db_password123",
    pool: {
      max: parseInt(Deno.env.get("PG_POOL_MAX") || "20"),
      min: parseInt(Deno.env.get("PG_POOL_MIN") || "5"),
      idle_timeout: parseInt(Deno.env.get("PG_POOL_IDLE_TIMEOUT") || "10000"),
      connect_timeout: parseInt(Deno.env.get("PG_CONNECT_TIMEOUT") || "5000"),
    },
    ssl: {
      enabled: Deno.env.get("PG_SSL_ENABLED") === "true",
      ca: Deno.env.get("PG_SSL_CA"),
      cert: Deno.env.get("PG_SSL_CERT"),
      key: Deno.env.get("PG_SSL_KEY"),
    },
  },
  redis: {
    hostname: Deno.env.get("REDIS_HOST") || "localhost",
    port: parseInt(Deno.env.get("REDIS_PORT") || "6379"),
    password: Deno.env.get("REDIS_PASSWORD") || "redis_secure_password456",
    pool: {
      max: parseInt(Deno.env.get("REDIS_POOL_MAX") || "10"),
      min: parseInt(Deno.env.get("REDIS_POOL_MIN") || "2"),
      idle_timeout: parseInt(Deno.env.get("REDIS_POOL_IDLE_TIMEOUT") || "30000"),
    },
    ssl: {
      enabled: Deno.env.get("REDIS_SSL_ENABLED") === "true",
    },
    cluster: {
      enabled: Deno.env.get("REDIS_CLUSTER_ENABLED") === "true",
      nodes: Deno.env.get("REDIS_CLUSTER_NODES")?.split(","),
    },
  },
};

Implement PostgreSQL connection manager

Create a PostgreSQL connection pool with SSL support, health checks, and proper error handling.

import { Pool, PoolClient } from "https://deno.land/x/postgres@v0.19.3/mod.ts";
import { databaseConfig } from "../config/database.ts";

export class PostgreSQLManager {
  private static instance: PostgreSQLManager;
  private pool: Pool;
  private isConnected = false;

  private constructor() {
    const config = databaseConfig.postgresql;
    
    this.pool = new Pool({
      hostname: config.hostname,
      port: config.port,
      database: config.database,
      user: config.username,
      password: config.password,
      connection: {
        attempts: 3,
        interval: 1000,
      },
      tls: config.ssl.enabled ? {
        enabled: true,
        enforce: false,
        caCertificates: config.ssl.ca ? [await Deno.readTextFile(config.ssl.ca)] : undefined,
      } : undefined,
    }, config.pool.max, true);

    this.setupEventHandlers();
  }

  public static getInstance(): PostgreSQLManager {
    if (!PostgreSQLManager.instance) {
      PostgreSQLManager.instance = new PostgreSQLManager();
    }
    return PostgreSQLManager.instance;
  }

  private setupEventHandlers(): void {
    this.pool.addEventListener("error", (event) => {
      console.error("PostgreSQL pool error:", event.error);
      this.isConnected = false;
    });

    this.pool.addEventListener("connect", () => {
      console.log("PostgreSQL connection established");
      this.isConnected = true;
    });

    this.pool.addEventListener("end", () => {
      console.log("PostgreSQL connection ended");
      this.isConnected = false;
    });
  }

  public async connect(): Promise {
    try {
      await this.pool.connect();
      await this.healthCheck();
      console.log("PostgreSQL pool initialized successfully");
    } catch (error) {
      console.error("Failed to connect to PostgreSQL:", error);
      throw error;
    }
  }

  public async getClient(): Promise {
    if (!this.isConnected) {
      throw new Error("PostgreSQL pool is not connected");
    }
    return await this.pool.connect();
  }

  public async query(text: string, params?: any[]): Promise {
    const client = await this.getClient();
    try {
      const result = await client.queryObject(text, params);
      return result.rows;
    } catch (error) {
      console.error("PostgreSQL query error:", error);
      throw error;
    } finally {
      client.release();
    }
  }

  public async transaction(callback: (client: PoolClient) => Promise): Promise {
    const client = await this.getClient();
    try {
      await client.queryObject("BEGIN");
      const result = await callback(client);
      await client.queryObject("COMMIT");
      return result;
    } catch (error) {
      await client.queryObject("ROLLBACK");
      console.error("PostgreSQL transaction error:", error);
      throw error;
    } finally {
      client.release();
    }
  }

  public async healthCheck(): Promise {
    try {
      const result = await this.query("SELECT 1 as health");
      return result.length > 0 && result[0].health === 1;
    } catch (error) {
      console.error("PostgreSQL health check failed:", error);
      return false;
    }
  }

  public getPoolStats(): { size: number; available: number; waiting: number } {
    return {
      size: this.pool.size,
      available: this.pool.available,
      waiting: this.pool.waiting,
    };
  }

  public async close(): Promise {
    await this.pool.end();
    this.isConnected = false;
  }
}

Implement Redis connection manager

Create a Redis connection pool with clustering support, authentication, and automatic reconnection.

import { connect, Redis } from "https://deno.land/x/redis@v0.32.3/mod.ts";
import { databaseConfig } from "../config/database.ts";

export class RedisManager {
  private static instance: RedisManager;
  private connections: Redis[] = [];
  private currentIndex = 0;
  private isConnected = false;
  private reconnectAttempts = 0;
  private maxReconnectAttempts = 5;

  private constructor() {
    this.setupConnectionPool();
  }

  public static getInstance(): RedisManager {
    if (!RedisManager.instance) {
      RedisManager.instance = new RedisManager();
    }
    return RedisManager.instance;
  }

  private async setupConnectionPool(): Promise {
    const config = databaseConfig.redis;
    const poolSize = config.pool.max;

    for (let i = 0; i < poolSize; i++) {
      try {
        const redis = await this.createConnection();
        this.connections.push(redis);
      } catch (error) {
        console.error(Failed to create Redis connection ${i}:, error);
        throw error;
      }
    }

    this.isConnected = true;
    console.log(Redis connection pool initialized with ${poolSize} connections);
  }

  private async createConnection(): Promise {
    const config = databaseConfig.redis;
    
    const connectionOptions: any = {
      hostname: config.hostname,
      port: config.port,
      maxRetryCount: 3,
      retryDelayMs: 1000,
    };

    if (config.password) {
      connectionOptions.password = config.password;
    }

    if (config.ssl.enabled) {
      connectionOptions.tls = {
        enabled: true,
      };
    }

    const redis = await connect(connectionOptions);

    redis.on("error", (error) => {
      console.error("Redis connection error:", error);
      this.handleConnectionError();
    });

    redis.on("connect", () => {
      console.log("Redis connection established");
      this.reconnectAttempts = 0;
    });

    return redis;
  }

  private async handleConnectionError(): Promise {
    this.isConnected = false;
    
    if (this.reconnectAttempts < this.maxReconnectAttempts) {
      this.reconnectAttempts++;
      const delay = Math.pow(2, this.reconnectAttempts) * 1000;
      
      console.log(Attempting to reconnect Redis (attempt ${this.reconnectAttempts}/${this.maxReconnectAttempts}) in ${delay}ms);
      
      setTimeout(async () => {
        try {
          await this.reconnect();
        } catch (error) {
          console.error("Redis reconnection failed:", error);
        }
      }, delay);
    } else {
      console.error("Max Redis reconnection attempts reached");
    }
  }

  private async reconnect(): Promise {
    try {
      this.connections = [];
      await this.setupConnectionPool();
    } catch (error) {
      console.error("Redis reconnection failed:", error);
      throw error;
    }
  }

  public async connect(): Promise {
    if (!this.isConnected && this.connections.length === 0) {
      await this.setupConnectionPool();
    }
  }

  private getConnection(): Redis {
    if (!this.isConnected || this.connections.length === 0) {
      throw new Error("Redis pool is not connected");
    }

    const connection = this.connections[this.currentIndex];
    this.currentIndex = (this.currentIndex + 1) % this.connections.length;
    return connection;
  }

  public async set(key: string, value: string, options?: { ex?: number; px?: number }): Promise {
    try {
      const redis = this.getConnection();
      if (options?.ex) {
        return await redis.setex(key, options.ex, value);
      } else if (options?.px) {
        return await redis.psetex(key, options.px, value);
      } else {
        return await redis.set(key, value);
      }
    } catch (error) {
      console.error("Redis SET error:", error);
      throw error;
    }
  }

  public async get(key: string): Promise {
    try {
      const redis = this.getConnection();
      return await redis.get(key);
    } catch (error) {
      console.error("Redis GET error:", error);
      throw error;
    }
  }

  public async del(key: string): Promise {
    try {
      const redis = this.getConnection();
      return await redis.del(key);
    } catch (error) {
      console.error("Redis DEL error:", error);
      throw error;
    }
  }

  public async exists(key: string): Promise {
    try {
      const redis = this.getConnection();
      return await redis.exists(key);
    } catch (error) {
      console.error("Redis EXISTS error:", error);
      throw error;
    }
  }

  public async healthCheck(): Promise {
    try {
      const redis = this.getConnection();
      const result = await redis.ping();
      return result === "PONG";
    } catch (error) {
      console.error("Redis health check failed:", error);
      return false;
    }
  }

  public getPoolStats(): { size: number; connected: boolean } {
    return {
      size: this.connections.length,
      connected: this.isConnected,
    };
  }

  public async close(): Promise {
    for (const connection of this.connections) {
      await connection.quit();
    }
    this.connections = [];
    this.isConnected = false;
  }
}

Create database initialization module

Implement a centralized database initialization and health monitoring system.

import { PostgreSQLManager } from "./postgresql.ts";
import { RedisManager } from "./redis.ts";

export class DatabaseManager {
  private static instance: DatabaseManager;
  private postgresql: PostgreSQLManager;
  private redis: RedisManager;
  private healthCheckInterval?: number;

  private constructor() {
    this.postgresql = PostgreSQLManager.getInstance();
    this.redis = RedisManager.getInstance();
  }

  public static getInstance(): DatabaseManager {
    if (!DatabaseManager.instance) {
      DatabaseManager.instance = new DatabaseManager();
    }
    return DatabaseManager.instance;
  }

  public async initialize(): Promise {
    try {
      console.log("Initializing database connections...");
      
      await Promise.all([
        this.postgresql.connect(),
        this.redis.connect(),
      ]);

      await this.runHealthChecks();
      this.startPeriodicHealthChecks();
      
      console.log("All database connections initialized successfully");
    } catch (error) {
      console.error("Database initialization failed:", error);
      throw error;
    }
  }

  public async runHealthChecks(): Promise<{ postgresql: boolean; redis: boolean }> {
    const [postgresHealth, redisHealth] = await Promise.all([
      this.postgresql.healthCheck(),
      this.redis.healthCheck(),
    ]);

    const healthStatus = {
      postgresql: postgresHealth,
      redis: redisHealth,
    };

    console.log("Database health check:", healthStatus);
    return healthStatus;
  }

  private startPeriodicHealthChecks(): void {
    this.healthCheckInterval = setInterval(async () => {
      const health = await this.runHealthChecks();
      
      if (!health.postgresql || !health.redis) {
        console.warn("Database health check failed:", health);
      }
    }, 60000); // Check every minute
  }

  public getPostgreSQL(): PostgreSQLManager {
    return this.postgresql;
  }

  public getRedis(): RedisManager {
    return this.redis;
  }

  public getStats(): any {
    return {
      postgresql: this.postgresql.getPoolStats(),
      redis: this.redis.getPoolStats(),
    };
  }

  public async close(): Promise {
    if (this.healthCheckInterval) {
      clearInterval(this.healthCheckInterval);
    }

    await Promise.all([
      this.postgresql.close(),
      this.redis.close(),
    ]);

    console.log("All database connections closed");
  }
}

export { PostgreSQLManager, RedisManager };

Create example application

Build a sample Deno application demonstrating database connections, error handling, and connection pooling.

import { Application, Router } from "https://deno.land/x/oak@v16.1.0/mod.ts";
import { DatabaseManager } from "./database/index.ts";

const app = new Application();
const router = new Router();
const db = DatabaseManager.getInstance();

// Initialize database connections
try {
  await db.initialize();
} catch (error) {
  console.error("Failed to initialize databases:", error);
  Deno.exit(1);
}

// Health check endpoint
router.get("/health", async (ctx) => {
  try {
    const health = await db.runHealthChecks();
    const stats = db.getStats();
    
    ctx.response.body = {
      status: "ok",
      timestamp: new Date().toISOString(),
      databases: health,
      stats: stats,
    };
  } catch (error) {
    ctx.response.status = 500;
    ctx.response.body = {
      status: "error",
      message: error.message,
    };
  }
});

// PostgreSQL example endpoint
router.get("/users/:id", async (ctx) => {
  try {
    const userId = ctx.params.id;
    const postgresql = db.getPostgreSQL();
    
    // Example query with parameter binding
    const users = await postgresql.query(
      "SELECT id, username, email, created_at FROM users WHERE id = $1",
      [parseInt(userId)]
    );
    
    if (users.length === 0) {
      ctx.response.status = 404;
      ctx.response.body = { error: "User not found" };
      return;
    }
    
    ctx.response.body = {
      user: users[0],
      cached: false,
    };
  } catch (error) {
    console.error("Database query error:", error);
    ctx.response.status = 500;
    ctx.response.body = {
      error: "Internal server error",
      details: error.message,
    };
  }
});

// Redis caching example endpoint
router.get("/cache/:key", async (ctx) => {
  try {
    const key = ctx.params.key;
    const redis = db.getRedis();
    
    const value = await redis.get(app:cache:${key});
    
    if (value) {
      ctx.response.body = {
        key: key,
        value: JSON.parse(value),
        cached: true,
        timestamp: new Date().toISOString(),
      };
    } else {
      ctx.response.status = 404;
      ctx.response.body = { error: "Cache key not found" };
    }
  } catch (error) {
    console.error("Redis error:", error);
    ctx.response.status = 500;
    ctx.response.body = {
      error: "Cache operation failed",
      details: error.message,
    };
  }
});

// Set cache endpoint
router.post("/cache/:key", async (ctx) => {
  try {
    const key = ctx.params.key;
    const body = await ctx.request.body.json();
    const redis = db.getRedis();
    
    await redis.set(
      app:cache:${key},
      JSON.stringify(body),
      { ex: 3600 } // Expire in 1 hour
    );
    
    ctx.response.body = {
      message: "Cache set successfully",
      key: key,
      expires_in: 3600,
    };
  } catch (error) {
    console.error("Redis set error:", error);
    ctx.response.status = 500;
    ctx.response.body = {
      error: "Cache set operation failed",
      details: error.message,
    };
  }
});

// Transaction example endpoint
router.post("/users", async (ctx) => {
  try {
    const body = await ctx.request.body.json();
    const postgresql = db.getPostgreSQL();
    
    const result = await postgresql.transaction(async (client) => {
      // Insert user
      const userResult = await client.queryObject(
        "INSERT INTO users (username, email) VALUES ($1, $2) RETURNING *",
        [body.username, body.email]
      );
      
      // Insert user profile
      await client.queryObject(
        "INSERT INTO user_profiles (user_id, full_name) VALUES ($1, $2)",
        [userResult.rows[0].id, body.full_name]
      );
      
      return userResult.rows[0];
    });
    
    ctx.response.status = 201;
    ctx.response.body = {
      message: "User created successfully",
      user: result,
    };
  } catch (error) {
    console.error("Transaction error:", error);
    ctx.response.status = 500;
    ctx.response.body = {
      error: "User creation failed",
      details: error.message,
    };
  }
});

app.use(router.routes());
app.use(router.allowedMethods());

// Graceful shutdown
const controller = new AbortController();
const { signal } = controller;

Deno.addSignalListener("SIGINT", async () => {
  console.log("\nReceived SIGINT, shutting down gracefully...");
  controller.abort();
  await db.close();
  Deno.exit(0);
});

const PORT = parseInt(Deno.env.get("PORT") || "8000");
console.log(Server starting on http://localhost:${PORT});
console.log("Available endpoints:");
console.log("- GET  /health - Database health check");
console.log("- GET  /users/:id - Get user by ID");
console.log("- GET  /cache/:key - Get cached value");
console.log("- POST /cache/:key - Set cache value");
console.log("- POST /users - Create new user");

try {
  await app.listen({ port: PORT, signal });
} catch (error) {
  if (error instanceof Deno.errors.Interrupted) {
    console.log("Server interrupted");
  } else {
    console.error("Server error:", error);
  }
}

Create environment configuration

Set up environment variables for database connections and application configuration.

# PostgreSQL Configuration
PG_HOST=localhost
PG_PORT=5432
PG_DATABASE=denoapp_db
PG_USERNAME=denoapp
PG_PASSWORD=secure_db_password123
PG_POOL_MAX=20
PG_POOL_MIN=5
PG_POOL_IDLE_TIMEOUT=10000
PG_CONNECT_TIMEOUT=5000
PG_SSL_ENABLED=false

Redis Configuration
REDIS_HOST=localhost
REDIS_PORT=6379
REDIS_PASSWORD=redis_secure_password456
REDIS_POOL_MAX=10
REDIS_POOL_MIN=2
REDIS_POOL_IDLE_TIMEOUT=30000
REDIS_SSL_ENABLED=false
REDIS_CLUSTER_ENABLED=false

Application Configuration
PORT=8000
NODE_ENV=development

Create database setup script

Generate initial database tables for testing the PostgreSQL connection and transactions.

-- Create users table
CREATE TABLE IF NOT EXISTS users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create user profiles table
CREATE TABLE IF NOT EXISTS user_profiles (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    full_name VARCHAR(100),
    bio TEXT,
    avatar_url VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create indexes
CREATE INDEX IF NOT EXISTS idx_users_username ON users(username);
CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);
CREATE INDEX IF NOT EXISTS idx_user_profiles_user_id ON user_profiles(user_id);

-- Insert sample data
INSERT INTO users (username, email) VALUES 
    ('alice', 'alice@example.com'),
    ('bob', 'bob@example.com')
ON CONFLICT (username) DO NOTHING;

INSERT INTO user_profiles (user_id, full_name, bio) VALUES 
    (1, 'Alice Johnson', 'Software developer'),
    (2, 'Bob Smith', 'System administrator')
ON CONFLICT DO NOTHING;

Initialize database schema

Execute the database setup script to create tables and sample data for testing.

sudo -u postgres psql -d denoapp_db -f setup_db.sql

Create development runner script

Set up a convenient script to run the Deno application with proper permissions and environment loading.

#!/bin/bash
set -e

Load environment variables
export $(grep -v '^#' .env | xargs)

Run Deno application with required permissions
deno run \
  --allow-net \
  --allow-env \
  --allow-read \
  --allow-write \
  --unstable \
  src/main.ts

chmod +x run.sh

Generate SSL certificates for PostgreSQL

Create self-signed certificates for PostgreSQL SSL connections in development environments.

sudo mkdir -p /etc/postgresql/ssl
sudo openssl req -new -x509 -days 365 -nodes \
  -out /etc/postgresql/ssl/server.crt \
  -keyout /etc/postgresql/ssl/server.key \
  -subj "/C=US/ST=State/L=City/O=Organization/CN=localhost"
sudo chown postgres:postgres /etc/postgresql/ssl/*
sudo chmod 600 /etc/postgresql/ssl/server.key
sudo chmod 644 /etc/postgresql/ssl/server.crt

Enable PostgreSQL SSL

Configure PostgreSQL to accept SSL connections and require encrypted communication.

ssl = on
ssl_cert_file = '/etc/postgresql/ssl/server.crt'
ssl_key_file = '/etc/postgresql/ssl/server.key'
ssl_ca_file = ''
ssl_crl_file = ''
ssl_prefer_server_ciphers = on
ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL'
ssl_ecdh_curve = 'prime256v1'

sudo systemctl restart postgresql

Update environment for SSL

Configure the application to use SSL connections for both PostgreSQL and Redis in production.

# PostgreSQL SSL Configuration
PG_SSL_ENABLED=true
PG_SSL_CA=/etc/postgresql/ssl/server.crt

Redis SSL Configuration (for Redis with TLS)
REDIS_SSL_ENABLED=true
REDIS_PORT=6380

Stricter connection settings
PG_POOL_MAX=15
PG_CONNECT_TIMEOUT=3000
REDIS_POOL_MAX=8