Aurora Database Setup

Having been on the development side of Aurora, I’ve been curious about how customers use Aurora databases. Here’s a quick tutorial on it:

Step 1: Create Aurora PostgreSQL Cluster

Via AWS Console:

Navigate to RDS Console
- Go to AWS Console → RDS → Databases
- Click “Create database”
Database Creation Method
- Select “Standard create”
Engine Configuration
- Engine type: Amazon Aurora
- Edition: Amazon Aurora PostgreSQL-Compatible Edition
- Version: Aurora PostgreSQL 15.4 (or latest available)
Templates
- Select “Dev/Test” for cost optimization
Settings
- DB cluster identifier: hello-world-aurora-cluster
- Master username: postgres
- Password: Create a strong password (save it securely)
Instance Configuration
- DB instance class: db.t4g.medium (burstable, cost-effective)
- Multi-AZ deployment: No (for this tutorial)
Connectivity
- VPC: Default VPC
- Subnet group: default
- Public access: No
- VPC security group: Create new
- Security group name: aurora-postgres-sg
Additional Configuration
- Initial database name: hello_world_db
- Backup retention: 1 day
- Monitoring: Disable enhanced monitoring for cost savings
Click “Create database”

The cluster will take 5-10 minutes to create.

Step 2: Configure Security Group

Find your Aurora security group
- Go to EC2 Console → Security Groups
- Find aurora-postgres-sg
Add inbound rule
- Type: PostgreSQL
- Protocol: TCP
- Port: 5432
- Source: Custom (we’ll update this after creating EC2)

Step 3: Create EC2 Instance

Launch EC2 Instance
- Go to EC2 Console → Launch Instance
- Name: aurora-client-ec2
- AMI: Amazon Linux 2023
- Instance type: t3.micro (free tier eligible)
- Key pair: Create new or use existing
- VPC: Same VPC as Aurora cluster
- Subnet: Public subnet
- Auto-assign public IP: Enable
- Security group: Create new called ec2-client-sg
Configure EC2 Security Group
- Add inbound rule for SSH (port 22) from your IP
Launch instance

Step 4: Update Aurora Security Group

Get EC2 security group ID
- Note the security group ID of ec2-client-sg
Update Aurora security group
- Edit aurora-postgres-sg inbound rules
- Modify the PostgreSQL rule source to reference ec2-client-sg

Step 5: Connect to EC2 and Install Dependencies

# SSH into your EC2 instance
ssh -i your-key.pem ec2-user@your-ec2-public-ip

# Update system
sudo dnf update -y

# Install PostgreSQL client
sudo dnf install postgresql15 -y

# Install Node.js for our application
sudo dnf install nodejs npm -y

# Install Python and pip (alternative option)
sudo dnf install python3 python3-pip -y

Step 6: Test Database Connection

First, get your Aurora cluster endpoint from the RDS console.

# Test connection (replace with your actual endpoint)
psql -h your-aurora-cluster.cluster-xxxxxx.us-east-1.rds.amazonaws.com -U postgres -d hello_world_db

# You'll be prompted for the password you set earlier

Step 7: Create Hello World Application

I’ll provide both Node.js and Python examples:

// package.json
{
  "name": "aurora-hello-world",
  "version": "1.0.0",
  "description": "Hello World app with AWS Aurora PostgreSQL",
  "main": "app.js",
  "dependencies": {
    "pg": "^8.11.3",
    "express": "^4.18.2"
  },
  "scripts": {
    "start": "node app.js"
  }
}

// app.js
const express = require('express');
const { Pool } = require('pg');

const app = express();
const port = 3000;

// Database configuration
const pool = new Pool({
  host: 'your-aurora-cluster.cluster-xxxxxx.us-east-1.rds.amazonaws.com', // Replace with your Aurora endpoint
  user: 'postgres',
  password: 'your-password', // Replace with your password
  database: 'hello_world_db',
  port: 5432,
  ssl: {
    rejectUnauthorized: false // For Aurora, SSL is enabled by default
  }
});

// Test database connection
async function testConnection() {
  try {
    const client = await pool.connect();
    console.log('Connected to Aurora PostgreSQL successfully!');
    
    // Create a simple table if it doesn't exist
    await client.query(`
      CREATE TABLE IF NOT EXISTS greetings (
        id SERIAL PRIMARY KEY,
        message VARCHAR(255) NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
      )
    `);
    
    console.log('Table created/verified successfully');
    client.release();
  } catch (err) {
    console.error('Database connection error:', err);
    process.exit(1);
  }
}

// Routes
app.get('/', async (req, res) => {
  try {
    const result = await pool.query('SELECT NOW() as current_time, version() as db_version');
    res.json({
      message: 'Hello World from Aurora PostgreSQL!',
      timestamp: result.rows[0].current_time,
      database_version: result.rows[0].db_version,
      connection_info: {
        host: pool.options.host,
        database: pool.options.database
      }
    });
  } catch (err) {
    console.error('Query error:', err);
    res.status(500).json({ error: 'Database query failed' });
  }
});

app.get('/greetings', async (req, res) => {
  try {
    const result = await pool.query('SELECT * FROM greetings ORDER BY created_at DESC LIMIT 10');
    res.json({
      greetings: result.rows,
      count: result.rowCount
    });
  } catch (err) {
    console.error('Query error:', err);
    res.status(500).json({ error: 'Failed to fetch greetings' });
  }
});

app.post('/greetings/:message', async (req, res) => {
  const message = req.params.message;
  try {
    const result = await pool.query(
      'INSERT INTO greetings (message) VALUES ($1) RETURNING *',
      [message]
    );
    res.json({
      message: 'Greeting added successfully!',
      greeting: result.rows[0]
    });
  } catch (err) {
    console.error('Insert error:', err);
    res.status(500).json({ error: 'Failed to add greeting' });
  }
});

// Health check endpoint
app.get('/health', async (req, res) => {
  try {
    await pool.query('SELECT 1');
    res.json({ status: 'healthy', database: 'connected' });
  } catch (err) {
    res.status(500).json({ status: 'unhealthy', database: 'disconnected', error: err.message });
  }
});

// Initialize and start server
async function start() {
  await testConnection();
  
  app.listen(port, '0.0.0.0', () => {
    console.log(`Hello World app listening at http://0.0.0.0:${port}`);
    console.log('Available endpoints:');
    console.log('  GET  /           - Hello world with DB info');
    console.log('  GET  /greetings  - List recent greetings');
    console.log('  POST /greetings/:message - Add new greeting');
    console.log('  GET  /health     - Health check');
  });
}

// Graceful shutdown
process.on('SIGINT', async () => {
  console.log('Shutting down gracefully...');
  await pool.end();
  process.exit(0);
});

start().catch(console.error);

# requirements.txt
psycopg2-binary==2.9.7
flask==2.3.3

# app.py
import os
import json
from datetime import datetime
from flask import Flask, jsonify, request
import psycopg2
from psycopg2.extras import RealDictCursor
import logging

# Configure logging
logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)

app = Flask(__name__)

# Database configuration
DB_CONFIG = {
    'host': 'your-aurora-cluster.cluster-xxxxxx.us-east-1.rds.amazonaws.com',  # Replace with your Aurora endpoint
    'user': 'postgres',
    'password': 'your-password',  # Replace with your password
    'database': 'hello_world_db',
    'port': 5432,
    'sslmode': 'require'  # Aurora requires SSL
}

def get_db_connection():
    """Create and return a database connection."""
    try:
        conn = psycopg2.connect(**DB_CONFIG)
        return conn
    except psycopg2.Error as e:
        logger.error(f"Database connection error: {e}")
        raise

def init_database():
    """Initialize database with required tables."""
    try:
        conn = get_db_connection()
        cursor = conn.cursor()
        
        # Create greetings table
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS greetings (
                id SERIAL PRIMARY KEY,
                message VARCHAR(255) NOT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        """)
        
        conn.commit()
        cursor.close()
        conn.close()
        logger.info("Database initialized successfully")
        
    except psycopg2.Error as e:
        logger.error(f"Database initialization error: {e}")
        raise

@app.route('/', methods=['GET'])
def hello_world():
    """Main hello world endpoint with database info."""
    try:
        conn = get_db_connection()
        cursor = conn.cursor(cursor_factory=RealDictCursor)
        
        cursor.execute("SELECT NOW() as current_time, version() as db_version")
        result = cursor.fetchone()
        
        cursor.close()
        conn.close()
        
        return jsonify({
            'message': 'Hello World from Aurora PostgreSQL!',
            'timestamp': result['current_time'].isoformat(),
            'database_version': result['db_version'],
            'connection_info': {
                'host': DB_CONFIG['host'],
                'database': DB_CONFIG['database']
            }
        })
        
    except psycopg2.Error as e:
        logger.error(f"Database query error: {e}")
        return jsonify({'error': 'Database query failed'}), 500

@app.route('/greetings', methods=['GET'])
def get_greetings():
    """Get recent greetings from database."""
    try:
        conn = get_db_connection()
        cursor = conn.cursor(cursor_factory=RealDictCursor)
        
        cursor.execute("SELECT * FROM greetings ORDER BY created_at DESC LIMIT 10")
        greetings = cursor.fetchall()
        
        # Convert datetime objects to strings for JSON serialization
        for greeting in greetings:
            greeting['created_at'] = greeting['created_at'].isoformat()
        
        cursor.close()
        conn.close()
        
        return jsonify({
            'greetings': greetings,
            'count': len(greetings)
        })
        
    except psycopg2.Error as e:
        logger.error(f"Database query error: {e}")
        return jsonify({'error': 'Failed to fetch greetings'}), 500

@app.route('/greetings/<message>', methods=['POST'])
def add_greeting(message):
    """Add a new greeting to the database."""
    try:
        conn = get_db_connection()
        cursor = conn.cursor(cursor_factory=RealDictCursor)
        
        cursor.execute(
            "INSERT INTO greetings (message) VALUES (%s) RETURNING *",
            (message,)
        )
        new_greeting = cursor.fetchone()
        new_greeting['created_at'] = new_greeting['created_at'].isoformat()
        
        conn.commit()
        cursor.close()
        conn.close()
        
        return jsonify({
            'message': 'Greeting added successfully!',
            'greeting': new_greeting
        }), 201
        
    except psycopg2.Error as e:
        logger.error(f"Database insert error: {e}")
        return jsonify({'error': 'Failed to add greeting'}), 500

@app.route('/health', methods=['GET'])
def health_check():
    """Health check endpoint."""
    try:
        conn = get_db_connection()
        cursor = conn.cursor()
        cursor.execute("SELECT 1")
        cursor.close()
        conn.close()
        
        return jsonify({
            'status': 'healthy',
            'database': 'connected'
        })
        
    except psycopg2.Error as e:
        logger.error(f"Health check failed: {e}")
        return jsonify({
            'status': 'unhealthy',
            'database': 'disconnected',
            'error': str(e)
        }), 500

@app.route('/stats', methods=['GET'])
def get_stats():
    """Get database and application statistics."""
    try:
        conn = get_db_connection()
        cursor = conn.cursor(cursor_factory=RealDictCursor)
        
        # Get table stats
        cursor.execute("SELECT COUNT(*) as total_greetings FROM greetings")
        greeting_count = cursor.fetchone()['total_greetings']
        
        # Get database size
        cursor.execute("""
            SELECT pg_size_pretty(pg_database_size(current_database())) as db_size,
                   current_database() as db_name
        """)
        db_info = cursor.fetchone()
        
        # Get connection info
        cursor.execute("SELECT inet_server_addr() as server_ip, inet_server_port() as server_port")
        server_info = cursor.fetchone()
        
        cursor.close()
        conn.close()
        
        return jsonify({
            'database_stats': {
                'name': db_info['db_name'],
                'size': db_info['db_size'],
                'total_greetings': greeting_count,
                'server_ip': server_info['server_ip'],
                'server_port': server_info['server_port']
            },
            'application_stats': {
                'endpoints': [
                    'GET /',
                    'GET /greetings',
                    'POST /greetings/<message>',
                    'GET /health',
                    'GET /stats'
                ]
            }
        })
        
    except psycopg2.Error as e:
        logger.error(f"Stats query error: {e}")
        return jsonify({'error': 'Failed to fetch stats'}), 500

if __name__ == '__main__':
    try:
        logger.info("Initializing database...")
        init_database()
        logger.info("Database initialized successfully")
        
        logger.info("Starting Flask application...")
        logger.info("Available endpoints:")
        logger.info("  GET  /           - Hello world with DB info")
        logger.info("  GET  /greetings  - List recent greetings")
        logger.info("  POST /greetings/<message> - Add new greeting")
        logger.info("  GET  /health     - Health check")
        logger.info("  GET  /stats      - Database and app statistics")
        
        app.run(host='0.0.0.0', port=5000, debug=False)
        
    except Exception as e:
        logger.error(f"Application startup failed: {e}")
        exit(1)

Step 8: Deploy and Run the Application

For Node.js Application:

# Create project directory
mkdir aurora-hello-world
cd aurora-hello-world

# Create package.json and app.js files (copy from artifact above)
# Update the Aurora endpoint and password in app.js

# Install dependencies
npm install

# Start the application
npm start

For Python Application:

# Create project directory
mkdir aurora-hello-world-python
cd aurora-hello-world-python

# Create requirements.txt and app.py files (copy from artifact above)
# Update the Aurora endpoint and password in app.py

# Install dependencies
pip3 install -r requirements.txt

# Start the application
python3 app.py

Step 9: Update EC2 Security Group for Web Access

# Add HTTP access to EC2 security group
# Go to EC2 Console → Security Groups → ec2-client-sg
# Add inbound rule:
# - Type: Custom TCP
# - Port: 3000 (for Node.js) or 5000 (for Python)
# - Source: Your IP address

Step 10: Test the Application

# Get your EC2 public IP
curl http://your-ec2-public-ip:3000/  # Node.js
curl http://your-ec2-public-ip:5000/  # Python

> {"connection_info":{"database":"hello_world_db","host":"hello-world-aurora-cluster.cluster-cbumcykauek9.us-west-2.rds.amazonaws.com"},"database_version":"PostgreSQL 16.6 on aarch64-unknown-linux-gnu, compiled by aarch64-unknown-linux-gnu-gcc (GCC) 9.5.0, 64-bit","message":"Hello World from Aurora PostgreSQL!","timestamp":"2025-06-01T17:22:24.085040+00:00"}

# Test adding greetings
curl -X POST http://your-ec2-public-ip:3000/greetings/Hello%20Aurora
curl -X POST http://your-ec2-public-ip:5000/greetings/Hello%20from%20Python
> {"greeting":{"created_at":"2025-06-01T17:23:05.126090","id":1,"message":"Hello from Python"},"message":"Greeting added successfully!"}

# Get greetings
curl http://your-ec2-public-ip:3000/greetings
curl http://your-ec2-public-ip:5000/greetings
> {"count":1,"greetings":[{"created_at":"2025-06-01T17:23:05.126090","id":1,"message":"Hello from Python"}]}

# Health check
curl http://your-ec2-public-ip:3000/health
curl http://your-ec2-public-ip:5000/health
> {"database":"connected","status":"healthy"}

Appendix

Provide a step by step tutorial of how to create an AWS Aurora Postgres Database and use it for a Hello-World example with an EC2 instance. Start small, use smaller instance types for smaller workloads. Provide concrete steps. Provide useable application code to demonstrate how to interact with the database from EC2 instance. 

For context, I am a software engineer working at AWS Aurora's Storage team with technical and coding background. However, I have not used AWS Aurora from a customer's perspective and I am looking to learn about customer's use cases.

Step 1: Create Aurora PostgreSQL Cluster#

Via AWS Console:#

Step 2: Configure Security Group#

Step 3: Create EC2 Instance#

Step 4: Update Aurora Security Group#

Step 5: Connect to EC2 and Install Dependencies#

Step 6: Test Database Connection#

Step 7: Create Hello World Application#

Step 8: Deploy and Run the Application#

For Node.js Application:#

For Python Application:#

Step 9: Update EC2 Security Group for Web Access#

Step 10: Test the Application#

Appendix#

Step 1: Create Aurora PostgreSQL Cluster

Via AWS Console:

Step 2: Configure Security Group

Step 3: Create EC2 Instance

Step 4: Update Aurora Security Group

Step 5: Connect to EC2 and Install Dependencies

Step 6: Test Database Connection

Step 7: Create Hello World Application

Step 8: Deploy and Run the Application

For Node.js Application:

For Python Application:

Step 9: Update EC2 Security Group for Web Access

Step 10: Test the Application

Appendix