Understanding Databases

Introduction to Databases

Databases are essential tools for storing, organizing, and retrieving data. They allow applications to manage large volumes of data efficiently and enable users to perform complex queries. For a novice, understanding databases can seem daunting, but at its core, a database is like a digital filing cabinet where you can keep your information safe and easily accessible.

Types of Databases on AWS

Amazon Web Services (AWS) offers various database options, broadly categorized into two types:

1. Relational Databases (RDBMS):

   • Example: Amazon RDS (Relational Database Service) with Aurora MySQL.

   • Characteristics: Data is stored in tables with fixed schemas. SQL (Structured Query Language) is used for querying.

   • Use Cases: Best suited for applications that require complex queries and transactions, such as financial applications or e-commerce platforms.

2. NoSQL Databases:

   • Example: Amazon DynamoDB.

   • Characteristics: Schema-less, allowing for flexible data models. It can handle unstructured data and is designed for high performance and scalability.

   • Use Cases: Ideal for applications that need to handle large volumes of unstructured data, such as real-time analytics, gaming, or IoT applications.

Benefits of Using AWS Databases

• Scalability: AWS databases can easily scale to accommodate growth in data and traffic.

• Managed Services: AWS takes care of backups, updates, and patching, allowing developers to focus on application logic.

• Performance: Optimized for high performance and availability.

• Security: Built-in security features like encryption and access control.

Choosing the Right Database

When deciding which type of database to use, consider the following:

• Data Structure: If your data is structured and requires complex queries, go for a relational database like RDS. For unstructured or semi-structured data, choose a NoSQL option like DynamoDB.

• Scalability Needs: If you expect rapid growth or need to handle high traffic, DynamoDB can automatically scale. RDS can scale vertically.

• Development Speed: If you need to iterate quickly and the data model is likely to change, NoSQL databases allow for more flexibility.

Setting Up Amazon RDS (Aurora MySQL)

1. Launch an RDS Instance:

   • Log in to the AWS Management Console.

   • Navigate to RDS and select "Create Database."

   • Choose the "Aurora" engine and "MySQL" compatibility.

   • Configure settings, including DB instance class and storage.

   • Create a new database and set the username and password.

Setting Up Amazon DynamoDB

1. Create a DynamoDB Table:

   • In the AWS Management Console, navigate to DynamoDB.

   • Choose "Create Table" and define the primary key (e.g., userid).

   • Configure settings like read/write capacity.

2. Integrate with PHP:

   • Install the AWS SDK for PHP using Composer.

   • Use the credentials from IAM (Identity and Access Management) to allow access to DynamoDB.

Sample PHP Application Setup

Here’s how to set up a simple signup application on EC2 ,using the script provided:

Step 1: Launch and Configure EC2
On AWS Management console, launch an EC2 instance in public subnet, Connect to the ec2 instance, and proceed with Step 2 below

Step 2: Install LAMP Stack (Linux, Apache, MySQL, PHP)

1. Install Apache:

    sudo apt install apache2 -y
   

2. Install PHP:

    sudo apt install php libapache2-mod-php php-mysql php-json php-xml php-zip php-curl php-mbstring -y
   

3. Install Composer (PHP dependency manager):

    sudo apt install composer -y
   

4. Install MySQL Client ( for connecting to Amazon RDS locally if needed):

    sudo apt install mysql-client -y
   

5. Restart Apache to load PHP modules:

    sudo a2enmod php8.3 #confirm the version of php installed using php -v
    sudo systemctl restart apache2
   

Step 3: Install and Configure AWS CLI

1. Install AWS CLI (required for managing AWS resources, including DynamoDB):

    curl "https://awscli.amazonaws.com/awscli-exe-linux-x86_64.zip" -o "awscliv2.zip"
    unzip awscliv2.zip
    sudo ./aws/install
    aws --version
   

2. Configure AWS CLI:

    aws configure
   

Step 4: Install AWS SDK for PHP

composer require aws/aws-sdk-php

1. Navigate to your web server’s document root:

   
    cd /var/www/html
   

2. Create a directory for your project:

    mkdir php-signup-app
    cd php-signup-app
   

3. Install AWS SDK for PHP using Composer:

    composer require aws/aws-sdk-php
   

Create the signup.php

error_reporting(E_ALL); //comment to stop error reporting
ini_set('display_errors', 1); // comment out to stop error reporting
require 'config.php';


if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    $username = $_POST['username'];
    $password = password_hash($_POST['password'], PASSWORD_BCRYPT);

    // Connect to RDS
    $pdo = new PDO(
        'mysql:host=' . RDS_HOST . ';dbname=' . RDS_DB,
        RDS_USER,
        RDS_PASS
    );
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // Insert into SQL Database
    $stmt = $pdo->prepare('INSERT INTO users (username, password) VALUES (?, ?)');
    $stmt->execute([$username, $password]);

    // Insert into DynamoDB
    $dynamoDbClient->putItem([
        'TableName' => DYNAMODB_TABLE,
        'Item' => [
            'UserID' => ['S' => $username],
            'Password' => ['S' => $password]
        ]
    ]);

    echo 'User registered successfully!';
}
?>

<!DOCTYPE html>
<html>
<head>
    <title>Signup</title>
</head>
<body>
    <h1>Signup</h1>
    <form method="POST">
        <label for="username">Username:</label>
        <input type="text" id="username" name="username" required>
        <br>
        <label for="password">Password:</label>
        <input type="password" id="password" name="password" required>
        <br>
        <input type="submit" value="Sign Up">
    </form>
</body>
</html>

Create config.php

<?php
// config.php

// RDS Configuration
define('RDS_HOST', 'your-rds-endpoint.amazonaws.com');
define('RDS_USER', 'your-username');
define('RDS_PASS', 'your-password');
define('RDS_DB', 'database-name');

// DynamoDB Configuration
require 'vendor/autoload.php';
use Aws\DynamoDb\DynamoDbClient;

define('DYNAMODB_REGION', 'your-region');
define('DYNAMODB_TABLE', 'your-table-name');

$dynamoDbClient = new DynamoDbClient([
    'region'  => DYNAMODB_REGION,
    'version' => 'latest'
]);

Set Up Connect to MySQL: Update Your Config.php

After creating the database, update your config.php file with the correct database name:

phpCopy codedefine('RDS_DB', 'my_new_database'); // Use the name of the database you just creat

mysql -h your-rds-endpoint -u your-username -p

Step 1: Select Your Database

Once you're in the MySQL prompt, select the database you just created:

USE database-name;

Step 2: Create the Users Table

Now, run the following SQL command to create the users table:

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Explanation of the Table Structure

• id: An auto-incrementing primary key for each user.

• username: A string field that must be unique (to prevent duplicate usernames).

• password: A string field to store the hashed password.

• created_at: A timestamp to track when the user was created (defaulting to the current time).

Step 3: Confirm Table Creation

To verify that the table was created successfully, you can run:

SHOW TABLES;

And to see the structure of the users table, use:

DESCRIBE users;

Update Your Config.php

After creating the database, update your config.php file with the correct database config for MySQL and DynamoDB ( remember Dynamodb requires you to create an IAM Access Key and Users on AWS)

Test the APP on your browser

Viewing Insertions in the Database

• For RDS: Use a MySQL client (like MySQL Workbench or phpMyAdmin) to connect to your RDS instance and run the following query:

    mysql -h your-rds-endpoint -u your-username -p;
    USE database_name;
    SELECT * FROM users;
  

• 

• For DynamoDB: Use the AWS Management Console to navigate to DynamoDB. Select your table, and you can view items stored in it.