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.
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
After installing mysql, you going to open your command prompt or terminal and enter.
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';
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
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.