There are three ways to manage database tables. 1) use legacy database tables, 2) manually manage database tables using SQL in Terminal, 3) use Ruby on Rails migrations
Setting Up the Database
From the terminal prompt in mysql.
Creating a new database:
SHOW DATABASES;
CREATE DATABASE db_name;
Create user access:
SELECT host, user FROM mysql.user;
CREATE USER ‘username’@’localhost’
IDENTIFIED BY ‘password’;
Grant ability to access all database:
SHOW GRANTS FOR ‘username’@’localhost’;
GRANT ALL PRIVILEGES ON db_name.* TO ‘user_name’@’localhost’;
Configure the project to use database:
config/database.yml
test to make sure the db is configured to the project by running db:schema:dump in terminal. This outputs a file containing the db structure, proving that your project was able to connect to the db as the user -log in, get info and dump to a file.
Example config/database.yml:
default: &default
adapter: mysql2
encoding: utf8mb4
pool: <%= ENV.fetch(“RAILS_MAX_THREADS”) { 5 } %>
username: dircster
password: superSecretPassword
socket: /tmp/mysql.sock
Option 3 | Migration
Migration is an action of bringing one database from one state to another state.
Terminal command line allows for db migrations and model creations:
rails generate migration MigrationName
rails generate model ModelName
The migration command is to modify an existing table or doing most other changes. The model command is to create a new model and corresponding database table.
There are up and down methods in the migration class you create. Any ruby code can go inside either of the methods. Common examples:
| create_table | drop_table | rename_table |
| add_column | remove_column | rename_column |
Default naming in ruby is lower case and plural. Rails automatically keeps a timestamp on db changes. Rails automatically creates a unique ID (identifier).
Running migrations
Running migrations puts the database in the state of the commands within the migration files. Once a migration (file) has been created it can be run. Run from the terminal in the root of the project file:
rails db:migrate
This is will run any migrations that have not been run before. To verify the migration modifications have been performed (example):
mysql -u rails_user -p simple_cms_development
after the password
mysql> SHOW TABLES;
mysql> SHOW FIELDS FROM table_name
Other migration commands:
The status of which migrations have been run:
rails db:migration:status
To down migrate all migrations:
rails db:migrate VERSION=0
To migrate up to a specific version number and stop on that version. This will not go past that version number:
rails db:migrate VERSION=2020122723590101