Database modelling
Before I start doing anything about creating database or things like that. I should 'draw' out the database tables to help me visualise the relationship between the tables and how I can access them.
I used Navicat Data Modeler to help me model how the database should look like for my telegram bot project. This will help me visualise the data and make sure that my thoughts are clear when I am coding the actual thing
Below are an attached of my database design.
Navicat data modeler guide
This app is very simple to use and free to use with some advance features can be unlocked too.
First, select which version of the design of the database that you want to create.
Conceptual: General overview of the database. More conceptualised so that even those without coding background can understand easily too
Logical: Logical model of the database and the basic architectural layout
Physical: Actual details of the database which includes things such as specific field and relations between the entities
Use physical model as an example
Once selected which model to create. A blank canvas will appear and you can start creating.
Simply click on the table icon at the top and click anywhere in the canvas that you wish place the table and the table will appear.
Give the table a suitable name and we can start adding fields.
Double click to get into the table and we can start editing
Click on this icon to add a field to the table.
Enter the name of the field and select a relevant type of the field. If you want it to be the primary field then click on the Key column and a key icon should appear as shown above.
If you want to add a foreign key then go to the Foreign Keys tab
Fields: select the field that you have created in the Fields tab just now.
Referenced Table: Choose the table that the foreign key is coming from
Referenced Fields: Choose the field in that table that you have chosen to be the referenced foreign key
On Delete/On Update: choose the behavior of this field when the foreign key is updated or deleted.
Once you are done with this table. Click ok to save this table. You should see the relevant fields being created in the table and lines linking this table to other tables where its foreign key is.
Views
Installing MySQL
Mac
Follow the guide here to install the package on your mac machine.
After you have installed it, you can go to System Preferences on Mac and there should be an MySQL icon appearing there.
Once you click open and you should see this page which you will be able to start or stop the SQL server and edit the configurations too.
To locate your sql directory simple open finder and press
Command + Shift + G on the key board or go to the top menu bar Go >>Go to Folder as show on the right A dialogue window should pop up key in
/usr/local/mysql and press enter and should bring you to the location of the sql folders.This is good to know if you need to adjust some configurations in future but if you want to run sql command then you will need terminal.
Running SQL in terminal
If you run
mysql --version in your mac terminal it should response with a command not found :mysql This is because your terminal cannot find the directory that contains the mysql command.In order to solve this, you need to export the path to your sql command to the system
$PATH So that when the you key in mysql the terminal know where to find the sql command.Run the following in your terminal and now you should be able to run
mysql --version with no problem.export PATH=${PATH}:/usr/local/mysql/bin
However, this is a one time solution. If you want this command to run automatically when you open terminal, you should save it in the .bash_profile which is run when terminal open.
To do that, go to your home directory by key in
cd ~ in the terminal and then key in open -t .bash_profile in the terminal, if it says that the file does not exist then run tocuh .bash_profile to create the file and try open the file again.Once the file is open using the text editor, simply add the export command above to the file and save it will do.
After adding the command and everything should work fine. Now to use sql commands simply key in the following
mysql -u root -p
Then enter the password of your database that you have created earlier and you should be into the sql terminal to let you access the database.
Change Password
To change the password of your sql database, enter the following into the sql terminal with
ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';
Connect to sql using third party apps
On the same machine, open the third party software or command.
Connect to the sql data base with the following parameters
Host:localhost
Port:3306
User Name:root
Password:the_password_of_the_database
One suggestion is to use MySQL Workbench
Docker
Why use docker?
Docker is container technology that encapsulates an entire app in a container. Container includes the software dependencies and environment that this particular app needs hence it is easy to easy to setup across multiple machines with different configurations regardless of its Operating system or physical hardware.
How to setup
Follow the instructions and install docker.
Once docker is install, you can follow through the tutorial for more understanding about how docker works and how to use docker.
In the terminal run
docker pull mysql and in your docker dashboard you should see mysql image is downloaded. To start the sql container. Run the follow command
docker run --name some-mysql -e MYSQL_ROOT_PASSWORD=my-secret-pw -p 3306:3306 -d mysql
Where
Some-mysql is the name of the sql container that you want to create. my-secret-pw is the password for your sql container. -p 3306:3306 basically maps the port 3306 on your docker container to your host machine's port 3306. Do take note that if you have a service running on the port 3306 on your host machine this command will run into error. Simply change to a free port on you host machine by -p <any_freeport>:3306 .More details of how the setup and customise your sql server can be found here
Persisting SQL server data
Since each SQL docker container is independent. Every time you restart the container, a new and fresh container will be created. Hence the data on your old container will be erased. That's not what we want for an SQL server. Therefore we need to persist the data on the host machine such that when we create a new SQL server container, we can point it to this data that is store on the host machine.