How to build a music player part 2: Introduction MySQL database

Hey, in our previous article we created a basic music player using HTML, CSS and javascript. If you have not seen that article click here. In the previous article we hard coded all our songs inside a javascript object.

Now we are going to create a database where we can store our music so we don’t need to hard code anything. For this project we are going to need a database and so we are going to use MySQL for this project.

Step 1: Setup

You are first going to download and install MySQL. In order to do that you going to head to the mysql website https://dev.mysql.com/downloads/mysql/ and select the OS you using then download, then follow all the prompts and install MySQL

Step 2: Create a database

After installing mysql, you going to open your command prompt or terminal and enter.

Mysql.server start

This will start a mysql service

Next you will create a new user and password by running

mysql -u root -p

You will be asked to enter a password, enter a password you will remember as you will need to know this in the future to access your mysql.

After that you will run this command to create a new user

CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'mypassword';

As you can see from the above code, when you create something in mysql, you have to start by writing “CREATE”.

Now in order for you to create a database all you have to do is run

CREATE DATABASE music_database;

And last but not least you going to grant the new user all the privileges so they can edit the database

GRANT ALL PRIVILEGES ON mydatabase.* TO 'myuser'@'localhost';

Step 2: Creating the tables

You now have a database but your database has no tables. Tables are essential in relational databases because they help to structure and organise our data. Each table represents an entity and each table is made up of columns which are attributes of that entity. So for our music_database you need a table where you going to store the meta data of your song, but before we create the table you have to know what columns you need inside your table. So by looking at our javascript object you can already see that you going to need a column for name of song, artist and url, but we will also add an additional column called date_created cause its just good practice to always know when a piece of data was created and also updated but I doubt we will be editing our music information so you do not need to add that one for this project. So here are our columns;

• Name_of_song
• Name_of_artist
• Url
• Date_created

In SQL each column has to be assigned a datatype, there are about 20+ datatypes in mysql but the most common ones are; VARCHAR, INTEGER, TINYINT, CHAR, DATETIME.

Oops before we create our table I almost forgot a very important thing when creating a table, the ID. Each row in a table needs to have an ID, most times the ID needs to be unique meaning there will be only be one instance of that particular ID in the table and no replica. The ID is essential in relational databases cause without it we can not use foreign keys and which is one of the things that make relational databases awesome, but we won’t get into that today so just to clarify these are our columns that we need for our music table with their datatypes;

• Id INT
• Name_of_song VARCHAR
• Name_of_artist VARCHAR
• Url VARCHAR
• Date_created DATETIME

Lets create.

First run

USE music_databse

Create table music…

CREATE TABLE music (
    Id int(9) AUTO_INCREMENT PRIMARY KEY,,
    Name_of_song varchar(255) NOT NULL,
    Name_of_artist varchar(255) NOT NULL,
    Url varchar(255) NOT NULL,
    Date_created datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO music
(name_of_song, name_of_artist, url)
VALUES 
("One More Time", "Daft Punk", "/songs/One more time.mp3"),
("Lost One", "Jay-Z", "/songs/05 Lost One.mp3"),
("Otis", "Jay Z and Kanye West", "/songs/04 Otis.mp3"),
("U don't know", "Jay-Z", "/songs/06 U Don't Know.mp3"),
("Threat", "Jay-Z", "/songs/07 Threat.mp3");

Cool, now your database is set.

In the next article we will create a backend for our music player which will help us connect our database with our frontend using fetch api.