Local MySQL

When working with the database, we will follow this general guideline:

1. Test our database changes on a local MySQL database
2. When the next public release is ready, we will update the live database.

There are several ways to setup and access your local machine's MySQL database.

MySQL Workbench

The easiest way to setup and access your local machine's MySQL database is to use MySQL Workbench. Download from here.

First-time setup

1. Setup a valid instance and connection (read here)
2. Create a database by running the query: CREATE DATABASE PlanToGrad;

Populate/test your database

1. In Workbench, open the schema.sql and data.sql files in the backend/ directory of the repository.
2. Run schema.sql to initialize the tables.
3. Run data.sql to populate the tables.

When testing any changes, you only need to run schema.sql if you are making changes to the tables' design (e.g. adding new columns to a table). If you just need to add/remove from the tables, you only need to run data.sql.

IMPORTANT: Running the above files will overwrite your tables. This is why it is especially important not to run it on the live database until it's ready to be updated.

MySQL through Unix CLI (macOS / Linux)

You can also access your local database through the Unix command line interface.

Ubuntu / Debian Installation

sudo apt-get update && sudo apt-get install mysql

macOS

Use Homebrew to install:

1. brew install mysql
2. brew tap homebrew/services to install brew services
3. brew services start mysql to start MySQL with brew services If you don't have it, use ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)" to install it.

First-time setup

1. Access your local mysql with mysql -u root. Your username will be root by default and have no password.
2. When you see the command line prompt, run CREATE DATABASE PlanToGrad;

Populate/test your database

1. Navigate to the backend/ directory of the repository. If you are in the top directory, run cd setup
2. Run mysql -u root -D PlanToGrad < schema.sql to initialize the tables.
3. Run mysql -u root -D PlanToGrad < data.sql to populate the tables with data.

Do the above steps whenever you want to test your changes to the .sql files.