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.