Using MySQL with Rails 4

In this post, we will see how we can use MySQL with our Rails application.

Here are the steps:

1. Download MySQL for Windows and install.
2. Create a new user named test and password test in MySQL.
3. Next, create a new rails project and name it myproject:

   rails new myproject

4. Edit your Gemfile, remove sqlite3 and add following line:

   gem 'mysql', '~> 0.3.1'

5. Execute command:

   bundle install

6. Next we edit the config/database.yml file.

   default: &default
     adapter: mysql2
     database: mydb
     username: test
     password: test
     pool: 5
     timeout: 5000
   
   development:
     <<: *default
   
   # Warning: The database defined as "test" will be erased and
   # re-generated from your development database when you run "rake".
   # Do not set this db to the same as development or production.
   test:
     <<: *default
   
   production:
     <<: *default

   Rails allows us specify three database at once:
   One that can be used for development, one for testing and one for production. All of them can inherit settings from the default setting. For example:

   default: &default
     adapter: mysql2
     database: mydb
     username: test
     password: test
     pool: 5
     timeout: 5000
   
   development:
     <<: *default
     database: mysql2_development

7. Now move into the project folder on command prompt.
8. Next we will create a controller which will handle tasks for CRUD (Create, Read, Update, Delete) operations on employees. Execute the following command:

   rails g controller employees create save list edit update destroy

9. Since we do not need views for save, update and destroy methods, go ahead and remove the following files

   app\views\employees\save.erb.html
   app\views\employees\update.erb.html
   app\views\employees\destroy.erb.html

10. Now create a model for employee. Execute the following command:

    rails generate model Employee

    This will create a model class and a migration file

11. Open the migration file from db\migrate\xxx_create_employees.rb and make it look like this:

    class CreateEmployees < ActiveRecord::Migration
      def change
        create_table :employees do |t|
          t.string :name, limit: 50
          t.string :email, limit: 100
          t.string :gender, limit: 6
        end
      end
    end

12. Next we will put our CRUD logic in the employee controller. Here is the code for the employee controller:

    class EmployeesController < ApplicationController
    
    def list 
       @employees = Employee.all
       @found = !@employees.empty?
    end
    
    def create
       @employee = Employee.new
    end
    
    def save
       @employee = Employee.create(employee_params)  # pass employee_params function
       if @employee.save
          redirect_to :action => 'list'
       else
          render :action => 'create'
       end
    end
    
    def destroy
       @employee = Employee.find params[:id]
       @employee.destroy
       redirect_to :action => 'list'
    end
    
    def edit
       @employee = Employee.find params[:id]
    end
    
    def update
       @employee = Employee.find params[:id]
       if @employee.update_attributes(employee_params)
          redirect_to :action => 'list'
       end
    end
    
    # this function specifies the fields to be used in mass assignment
    # older way of this was using 'attr_accessible' in model
    def employee_params
      params.require(:employee).permit(:name, :email, :gender)
    end
    
    end

13. Next we need to update our views. Here is code for create.html.erb

    <%= link_to 'All employees', :action => 'list' %>
    <br/><br/>
    <h1>New Employee</h1>
    <%= form_for @employee, :url => {:action => 'save'} do |f| %>
     <p>
     Name: <%= f.text_field 'name' %>
     </p>
     <p>
     Email: <%= f.text_field 'email' %>
     </p>
     <p>
     Gender: <label><%= f.radio_button 'gender', "Male", :checked => true %> Male</label> &nbsp;&nbsp; <label><%= f.radio_button 'gender', "Female" %> Female</label>
     </p>
     <p>
     <%= f.submit "Create" %>
     </p>
    <% end %>

14. Here is the code for list.html.erb

    <%= link_to 'New Employee', :action => 'create' %>
    <br/><br/>
    <% if @found %>
    <h1>Listing Employees</h1>
    <table>
     <tr>
     </tr>
    <% @employees.each do |employee| %>
     <tr>
     <td><%= employee.id %></td>
     <td><%= employee.name %></td>
     <td><%= employee.email %></td>
     <td><%= employee.gender %></td>
     <td><%= link_to 'Edit', :action => 'edit', :id => employee.id %></td>
     <td><%= link_to 'Delete', {:action => 'destroy', :id => employee.id}, :confirm => 'Are you sure to remove this employee?', :method => :post %></td>
     </tr>
    <% end %>
    </table>
    <br />
    <% else %>
     No employees added
     <% end %>

15. Here is the code for edit.html.erb
    

    <h1>Edit employee</h1>
    <%= form_for @employee, :url => {:action => 'update', :id => @employee.id} do |f| %>
     <p>
     Name: <%= f.text_field 'name' %>
     </p>
     <p>
     Email: <%= f.text_field 'email' %>
     </p>
     <p>
     Gender: <label><%= f.radio_button 'gender', "Male", :checked => true %> Male</label> &nbsp;&nbsp; <label><%= f.radio_button 'gender', "Female" %> Female</label>
     </p>
     <p>
     <%= f.submit "Update" %>
     </p>
    <% end %>

16. Finally, here are the entries in my config\routes.rb

    get 'employees/create'
    post 'employees/save'
    get 'employees/list'
    get 'employees/edit'
    post 'employees/destroy'
    patch 'employees/update'

17. Now you can start your server and execute the url:

    http://localhost:3000/employees/list

That’s it from this post.