Guide to Using AWS RDS

Introduction

Amazon RDS (Relational Database Service) is a fully managed relational database service provided by Amazon Web Services. It makes it easy to set up, operate, and scale popular databases in the cloud without worrying about hardware management or complex software installation.

Key advantages

Automated Management & Time Saving

RDS automates time-consuming administrative tasks such as:
Installation & Configuration: Launch a database with just a few clicks.
Patching: Automatically applies security patches and software updates.
Backup: Automatically performs daily backups and allows point-in-time recovery within 35 days.

Flexible Scalability

Vertical Scaling: Easily adjust CPU and RAM resources to match workload demands.
Horizontal Scaling (Read Replicas): Create read-only replicas to offload traffic from the primary database, improving performance for read-heavy applications.

High Availability & Reliability

Multi-AZ Deployment: RDS automatically replicates data to another Availability Zone. If the primary zone fails, the system automatically fails over to the standby instance without interrupting the application.

Strong Security

Encryption: Supports encryption both at rest and in transit.
Network Isolation: Runs inside Amazon VPC for controlled network access.
Access Control: Integrates with AWS IAM for fine-grained permission management.

Multiple Database Engine Support

You can choose from six popular database engines:
Open Source: MySQL, PostgreSQL, MariaDB
Commercial: Oracle, Microsoft SQL Server
AWS-Optimized: Amazon Aurora (offers significantly higher performance compared to standard MySQL/PostgreSQL)

Prerequisites

You can refer to my previous article where I demonstrated how to set up a NestJS project connected to PostgreSQL. In this article, I will guide you on using NestJS to connect to RDS PostgreSQL by extending the previous setup.

Details

First, use AWS CDK to create the required resources. Create the file lib/rds-stack.ts

import * as cdk from "aws-cdk-lib"
import * as ec2 from "aws-cdk-lib/aws-ec2"
import * as rds from "aws-cdk-lib/aws-rds"
import * as secretsmanager from "aws-cdk-lib/aws-secretsmanager"
import * as ssm from "aws-cdk-lib/aws-ssm"
import * as iam from "aws-cdk-lib/aws-iam"

export class RdsStack extends cdk.Stack {
  constructor(scope: cdk.App, id: string, props?: cdk.StackProps) {
    super(scope, id, props)

    const dbConfig = {
      databaseName: "my_custom_db",
      dbPort: 5432,
      schema: "app_schema",
    }

    const vpc = new ec2.Vpc(this, "MyVpc", {
      maxAzs: 2,
      natGateways: 0,
      subnetConfiguration: [
        { name: "Public", subnetType: ec2.SubnetType.PUBLIC },
        { name: "Isolated", subnetType: ec2.SubnetType.PRIVATE_ISOLATED },
      ],
    })

    const dbSecret = new secretsmanager.Secret(this, "DbSecret", {
      secretName: "my-postgres-credentials",
      generateSecretString: {
        secretStringTemplate: JSON.stringify({
          username: "postgres",
          dbname: dbConfig.databaseName,
          port: dbConfig.dbPort,
          schema: dbConfig.schema,
        }),
        generateStringKey: "password",
        excludeCharacters: '"@/\\',
      },
    })

    const dbInstance = new rds.DatabaseInstance(this, "MyDb", {
      engine: rds.DatabaseInstanceEngine.postgres({
        version: rds.PostgresEngineVersion.VER_15,
      }),
      instanceType: ec2.InstanceType.of(
        ec2.InstanceClass.T3,
        ec2.InstanceSize.MICRO,
      ),
      vpc,
      vpcSubnets: { subnetType: ec2.SubnetType.PRIVATE_ISOLATED },
      credentials: rds.Credentials.fromSecret(dbSecret),
      allocatedStorage: 20,
      port: dbConfig.dbPort,
      databaseName: dbConfig.databaseName,
      removalPolicy: cdk.RemovalPolicy.DESTROY,
    })

    const bastionRole = new iam.Role(this, "BastionRole", {
      assumedBy: new iam.ServicePrincipal("ec2.amazonaws.com"),
      managedPolicies: [
        iam.ManagedPolicy.fromAwsManagedPolicyName(
          "AmazonSSMManagedInstanceCore",
        ),
      ],
    })

    const bastion = new ec2.Instance(this, "BastionHost", {
      vpc,
      instanceType: ec2.InstanceType.of(
        ec2.InstanceClass.T3,
        ec2.InstanceSize.MICRO,
      ),
      machineImage: ec2.MachineImage.latestAmazonLinux2023(),
      vpcSubnets: { subnetType: ec2.SubnetType.PUBLIC },
      role: bastionRole,
    })

    dbInstance.connections.allowFrom(bastion, ec2.Port.tcp(dbConfig.dbPort))

    dbSecret.addRotationSchedule("RotationSchedule", {
      hostedRotation: secretsmanager.HostedRotation.postgreSqlSingleUser(),
      automaticallyAfter: cdk.Duration.days(30),
    })

    new cdk.CfnOutput(this, "DbHost", {
      value: dbInstance.dbInstanceEndpointAddress,
    })
    new cdk.CfnOutput(this, "BastionInstanceId", { value: bastion.instanceId })
  }
}

dbConfig: PostgreSQL configuration values you can modify.

databaseName: default is postgres
dbPort: default is 5432
schema: default is public
dbSecret: Contains database connection information including username, dbname, port, and schema.
The password is randomly generated.
All information is stored in AWS Secrets Manager.
The password is automatically rotated every 30 days (minimum rotation interval is 1 hour, configurable).

Bastion: Since the VPC is configured with ec2.SubnetType.PRIVATE_ISOLATED, external connections are not allowed.

Therefore, we create a bastion EC2 instance with permissions to access the RDS instance.
This acts as an intermediary to create an SSH tunnel between your local machine and the RDS database.

Update the file bin/aws-cdk.ts

#!/usr/bin/env node
import * as cdk from "aws-cdk-lib/core"
import { RdsStack } from "../lib/rds-stack"

const app = new cdk.App()
new RdsStack(app, "RdsStack")

After successfully deploying the stack:

✅  RdsStack
✨  Deployment time: 484.81s

Outputs:
RdsStack.DbHost = rdsstack-mydb3c7179b2-h4a1c6gmeqdt.clk0e4sk6z9o.ap-southeast-1.rds.amazonaws.com
RdsStack.BastionInstanceId = i-0d64a4aa6bb7f2ffc

✨  Total time: 490.78s

The corresponding resources will appear in the AWS Console.

Next, in your NestJS project, use the generated information to connect to RDS.

Download the global-bundle.pem root certificate (CA) then copy it into your project.

By default, non-code files are not included in the dist folder after build. You need to update nest-cli.json to include the folder containing global-bundle.pem:

{
  "$schema": "https://json.schemastore.org/nest-cli",
  "collection": "@nestjs/schematics",
  "sourceRoot": "src",
  "compilerOptions": {
    "deleteOutDir": true,
    "assets": [
      {
        "include": "assets/**/*" // here
      }
    ]
  }
}

Create a tunnel connection to RDS using the deployment information from CDK:

$ aws ssm start-session \
    --target {BastionInstanceId} \
    --document-name AWS-StartPortForwardingSessionToRemoteHost \
    --parameters '{
        "host":["{DbHost}"],
        "portNumber":["5432"],
        "localPortNumber":["5432"]
    }'

# example
$ aws ssm start-session \
    --target i-0d64a4aa6bb7f2ffc \
    --document-name AWS-StartPortForwardingSessionToRemoteHost \
    --parameters '{
        "host":["rdsstack-mydb3c7179b2-h4a1c6gmeqdt.clk0e4sk6z9o.ap-southeast-1.rds.amazonaws.com"],
        "portNumber":["5432"],
        "localPortNumber":["5432"]
    }'

Starting session with SessionId: user1-4iou9v9vxj3zqs7c69sdc3hlxy
Port 5432 opened for sessionId user1-4iou9v9vxj3zqs7c69sdc3hlxy.
Waiting for connections...

Connection accepted for session [user1-4iou9v9vxj3zqs7c69sdc3hlxy]

Create database.service.ts

import {
  GetSecretValueCommand,
  SecretsManagerClient,
} from '@aws-sdk/client-secrets-manager'
import {Injectable} from '@nestjs/common'
import {TypeOrmModuleOptions} from '@nestjs/typeorm'
import * as fs from 'fs'
import * as path from 'path'

@Injectable()
export class DatabaseService {
  private readonly client = new SecretsManagerClient()

  async getFreshConfig(): Promise<TypeOrmModuleOptions> {
    const command = new GetSecretValueCommand({
      SecretId: 'my-postgres-credentials',
    })
    const response = await this.client.send(command)
    const secrets = JSON.parse(response.SecretString || '{}')

    return {
      type: 'postgres',
      host: '127.0.0.1',
      port: secrets.port,
      username: secrets.username,
      password: secrets.password,
      schema: secrets.schema,
      database: secrets.database,
      autoLoadEntities: true,
      synchronize: true,
      logging: ['error', 'warn'],
      ssl: {
        ca: fs
          .readFileSync(path.join(__dirname, '../assets/global-bundle.pem'))
          .toString(),
        rejectUnauthorized: true,
        checkServerIdentity: () => undefined,
      },
    } as TypeOrmModuleOptions
  }
}

host: '127.0.0.1', this connects to localhost on your machine. Since you started a session to create a tunnel to RDS, any operation on localhost:5432 is effectively executed on the RDS PostgreSQL instance.
ssl configuration: required because RDS PostgreSQL (from version 15 onward) enforces SSL/TLS connections by default. Alternatively, you may disable strict validation using: rejectUnauthorized: false

Create database.module.ts

import {Global, Module} from '@nestjs/common'
import {TypeOrmModule} from '@nestjs/typeorm'
import {DataSource} from 'typeorm'
import {DatabaseService} from './database.service'
import {TestEntity} from 'src/entity/test.entity'
import {TestEntityService} from 'src/service/test-entity.service'

@Global()
@Module({
  providers: [DatabaseService, TestEntityService],
  imports: [
    TypeOrmModule.forRootAsync({
      inject: [DatabaseService],
      useFactory: async (configService: DatabaseService) => {
        return await configService.getFreshConfig()
      },
      dataSourceFactory: async options => {
        const dataSource = new DataSource(options!)
        const originalQuery = dataSource.query.bind(dataSource)
        dataSource.query = async (query, parameters, queryRunner) => {
          try {
            return await originalQuery(query, parameters, queryRunner)
          } catch (error) {
            if (error.code === '28P01') {
              console.warn('Password expired or invalid. Refreshing...')
              await dataSource.destroy()
              const newConfig = await new DatabaseService().getFreshConfig()
              Object.assign(dataSource.options, newConfig)
              await dataSource.initialize()
              return await dataSource.query(query, parameters, queryRunner)
            }
            throw error
          }
        }
        return dataSource
      },
    }),
    TypeOrmModule.forFeature([TestEntity]),
  ],
  exports: [DatabaseService, TestEntityService],
})
export class DatabaseModule {}

TestEntityService: Example service mentioned in the previous article. You can replace it with any service that interacts with PostgreSQL.
dataSourceFactory: Handles error 28P01 (invalid password). Since password rotation is enabled in Secrets Manager, when the password changes:

Catch this error
Destroy the old connection
Recreate a new connection using the updated password

Add DatabaseModule to app.module.ts:

import {Module} from '@nestjs/common'
import {TestEntityController} from './controller/test-entity.controller'
import {DatabaseModule} from './database/database.module'

@Module({
  imports: [DatabaseModule],
  controllers: [TestEntityController],
})
export class AppModule {}