16. Live Project: Building a Reels App with EC2, S3 and RDS

16.4 Database Schema

By Ravindra Bagale · Last updated: 27 September 2026

Teen tables: users, posts (video kiwa text), likes. Part 5 madhle keys aani constraints ithe kharya project madhe vaparle aahet – PRIMARY KEY, UNIQUE, FOREIGN KEY, composite key, CHECK, DEFAULT, AUTO_INCREMENT.

schema.sql

-- schema.sql : run once as the admin user
-- mysql -h <rds-endpoint> -u admin -p < schema.sql
CREATE DATABASE IF NOT EXISTS reelsdb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE reelsdb;

CREATE TABLE IF NOT EXISTS users (
    id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username      VARCHAR(30)  NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at    TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT uq_users_username UNIQUE (username)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS posts (
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id     INT UNSIGNED    NOT NULL,
    post_type   ENUM('video','text') NOT NULL,
    caption     VARCHAR(500)    NOT NULL DEFAULT '',
    body_text   VARCHAR(1000)   NULL,              -- text posts
    bg_color    CHAR(7)         NOT NULL DEFAULT '#0E7C86',
    s3_key      VARCHAR(255)    NULL,              -- video posts: key inside the bucket
    mime_type   VARCHAR(50)     NULL,
    size_bytes  BIGINT UNSIGNED NULL,
    created_at  TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_posts_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT chk_post_content CHECK (
        (post_type = 'video' AND s3_key IS NOT NULL) OR
        (post_type = 'text'  AND body_text IS NOT NULL)
    ),
    INDEX idx_posts_user (user_id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS likes (
    user_id    INT UNSIGNED    NOT NULL,
    post_id    BIGINT UNSIGNED NOT NULL,
    created_at TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id, post_id),                -- composite key: one like per user per post
    CONSTRAINT fk_likes_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_likes_post FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE
) ENGINE=InnoDB;

Run it as the admin user, then create the app user (Chapter 15.4) with only four privileges:

mysql -h reels-db.abcdefgh1234.ap-south-1.rds.amazonaws.com -u admin -p < schema.sql
mysql -h reels-db.abcdefgh1234.ap-south-1.rds.amazonaws.com -u admin -p -e "
  CREATE USER IF NOT EXISTS 'reels_app'@'%' IDENTIFIED BY 'Use-A-Long-Random-Password-Here!';
  GRANT SELECT, INSERT, UPDATE, DELETE ON reelsdb.* TO 'reels_app'@'%';"
mysql -h reels-db.abcdefgh1234.ap-south-1.rds.amazonaws.com -u reels_app -p reelsdb -e "SHOW TABLES;"