Database Setup

Downloading MySQL:

In WSL, run the command: sudo apt install mysql-server

Creating the database:

Log into mysql as root, by entering the command: sudo mysql -u root Then, run the following SQL commands:

CREATE DATABASE music_app_db;
USE music_app_db;
CREATE TABLE users (
    user_id BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    password_hash CHAR(255) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    creation_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Getting the SQL File

To get the SQL file of the table creation, run the command: sudo mysqldump -u root music_app_db > music_app_db.sql

Creating a root user with permissions

Log into mysql as root, by entering the command: sudo mysql -u root run the following commands:

CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'new_password'; # Put your username and password
GRANT ALL PRIVILEGES ON music_app_db.* TO 'new_user'@'localhost';
FLUSH PRIVILEGES;

Running the SQL file

Run the command: mysql -u user -p < ~/music-app/music_app_db.sql

Then enter the password you put in the previous steps to run the SQL file. To ensure it worked, log into mysql with your user: mysql -u <username> -p

Then run the following commands:

USE music_app_db;
SHOW TABLES;
DESCRIBE users;

Creating the friends (Many-to-Many) Table

(1) Open MySQL and select the database

Run the following line in a BASH terminal to log in:

mysql -u <your-username> -p

Then run the following line in the MySQL terminal to select the database:

USE music_app_db

---

(2) Create the Friends Table (Junction Table)

This table models friendships as a two-column composite primary key, with both columns referencing users.user_id. Run the following lines to create the table locally:

CREATE TABLE friends (
    user_id BIGINT UNSIGNED NOT NULL,
    friend_id BIGINT UNSIGNED NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id, friend_id),
    KEY fk_friends_friend (friend_id),
    CONSTRAINT fk_friends_friend FOREIGN KEY (friend_id)
        REFERENCES users (user_id) ON DELETE CASCADE,
    CONSTRAINT fk_friends_user FOREIGN KEY (user_id)
        REFERENCES users (user_id) ON DELETE CASCADE
);

---

(3) QUICK EXPLANATIONS:

PRIMARY KEY (user_id, friend_id):

• Prevents duplicate friendships (e.g. user 7 -> user 12; cannot happen twice)

FOREIGN KEY (...) REFERENCES users(user_id):

• Ensures both IDs exist in the users table

ON DELETE CASCADE:

• If a user is deleted --> all friendship links are automatically terminated