import old database in new schema with mysql and rails

posted by Matthias Frick in Ruby on Rails / 4 Comments

In this tutorial I will show you how you could import an old database into a new schema with mysql and rails. I will explain the process with an example scenario.


Imagine you have an old php project with an old database schema and you want to completely refactor this project. Firstly you want to use rails instead of php and create a new rails project with the standard rails conventions. Secondly you want to restructure the old database into a new scheme without losing data and make it more "railsish". You just have a mysql dump of your old database.

Old database:
You have a mysql database with a few tables:

  • "Person" with: pid (bigint 20), uid (varchar 8), firstname (varchar 40), lastname (varchar (50), mail (varchar 40), isfemale (tinyint 4)
  • "Work" with: wid (bigint 20), headline (varchar 250), text_facts (text), text_long_facts(text),  pub_date (date), hyperlink (varchar 200)

Now you want to change this scheme to the following one:

New database:
Your new database contains the following tables:

  • "users" with: id (int 11), first_name ( varchar 255), last_name (varchar 255), email (varchar 255), isfemale (tinyint 1)
  • "projects" with: id (int 11), title (varchar 255), teaser (text), description (text), published_at (date), link (varchar 255)

Now you have good the background information, let us move one step further:


Model creation

You have to create migrations for your new models. Just open a terminal and generate them with the normal rails generators:

rails g model User first_name:string last_name:string email:string isfemale:boolean

rails g model Project title:string teaser:text description:text published_at:date, link:string

Do not forget to  migrate your database:

rake db:migrate

Now you have generated your models and you can add some validations. In this scneario I add the "presence: true" validation to every model attribute.


Importer Structure

You have a mysql dump and want to import this dump into the new schema. I will now explain how you can do that through a Raketask.

You need a Importer class and a normal Raketask. For a better structure I will split up the Importer class into different modules. The structure of the whole importer looks like as follows:

  • /lib/tasks/project_name.rake: this file contains the Raketasks
  • /lib/tasks/importer/importer.rb: Importer class which includes the different sub-importer modules and all necessary extension files.
  • /lib/tasks/importer/users_importer.rb: sub-importer module which contains the users importer stuff.
  • /lib/tasks/importer/projects_importer.rb: sub-importer module which contains the projects importer code.

Subsequently I want explain each of this classes and modules. Let's start with the Raketask file



namespace :project_name do
  require Rails.root + "lib/tasks/importer"

  desc "Import old database, usage: rake project_name:import['old_database_name']"
  task :import, :oldDatabase, needs::environment do |t, args|
    args.with_defaults(oldDatabase: "import")

    oldDatabaseName = args.oldDatabse
    newDatabaseName = YAML::load("config/database.yml")))[Rails.env]["database"]

    importer = newDatabaseName, oldDatabaseName

This is the .rake-File and contains just one normal Raketask. This Raketask requires the name of the old database. For example you create a new database called "old_database" with phpmyadmin or a tool like that. After that you have to import the mysql dump from your old database in the currently new created one. The next step is to call the Raketask in your terminal: "rake project_name:import['old_database']. This will trigger the import. At this moment the import will fail, because you do not have neither the Importer class nor the instance method "execute" called at the end of the Raketask. So let's move further to the Importer class.



require File.dirname(__FILE__) +  "/users_importer"
require File.dirname(__FILE__) +  "/projects_importer"

class Importer

  # include sub-modules
  include UsersImporter
  include ProjectsImporter

  # initializer for a new importer
  def initialize new, old
    # feedback for the programmer
    puts "Importing from #{old} to {new}"

    @newDb = new
    @oldDb = old

  # execute the import
  def execute
    # if you do not like to import data, if the new database
    # already contains data, just a security hint..
    if User.count > 0
      raise "Import aborted! There already are users in the database."
    if Project.count > 0
     ..and so on..
    # call sub-importer modules

  # later in the import process you have to switch beween
  # the old and the new database.

  # use new database (= switch to new database)
  def use_new_database
    ActiveRecord::Base.connection.execute("use #{@newDb}")

  # use old database (= switch to old database)
  def use_old_database
    ActiveRecord::Base.connection.execute("use #{@oldDb}")


The importer.rb contains the Importer class with a normal initializer and an execute method. This method just calls all sub-module importer methods to completely start the import. At this moment it will not work, because the sub-modules do not exist yet. But in the end everthing will work together very good :-). Furthermore the Importer class has two helper methods to switch between the old and the new database.

Do not forget to include the different sub-modules in the Importer class, otherwise it will not work and you will get include errors and undefined methods.

For convenience you can write following mysql extensions and put them in the importer.rb File under the Importer class, but not in the Importer class.

# custom mysql row to facilite access
class Row
  def initialize fields, values
    @fields = fields
    @values = values

  def get field

# Add get_row method to Mysql2::Result class
class Mysql2::Result
  def get_row index self.fields, self.to_a[index].to_a

Now let's start with the first import script:



module UsersImporter

  # import users
  def import_users
    puts "Importing users..."
    users = ActiveRecord::Base.connection.execute('
      SELECT pid, uid, firstname, lastname, mail, isfemale FROM Person
    for i in 0...users.count do
      row = users.get_row i
      user = User.where(first_name: row.get("firstname"), 
                        last_name: row.get("lastname"),
                        email: row.get("mail"),
                        isfemale: row.get("isfemale"))
      unless user
        user = row.get("firstname"), 
                        last_name: row.get("lastname"),
                        email: row.get("mail"),
                        isfemale: row.get("isfemale"))
      rescue Exception => e
        puts "Failed to save #{row.get("firstname")} #{row.get("lastname"): #{e.message}"


This sub-module contains the users importer. You can see that you have to use pure Mysql for the queries in the old database. That's because you do not have a model and cannot use the ActiveRecord ORM.



module ProjectsImporter

  # import projects
  def import_projects
    puts "Importing projects..."
    projects = ActiveRecord::Base.connection.execute('
      SELECT wid, headline, text_facts, text_long_facts, pub_date, hyperlink FROM work
    for i in 0...projects.count do
      row = projects.get_row i
      project = Project.where(title: row.get("headline"), 
                        teaser: row.get("text_facts"),
                        published_at: row.get("pub_date"))
      unless project
        project = row.get("headline"), 
                              teaser: row.get("text_facts"),
                              description: row.get("text_long_facts"),
                              published_at: row.get("pub_date"),
                              link: row.get("hyperlink"))
      rescue Exception => e
        puts "Failed to save #{row.get("title")} #{row.get("published_at"): #{e.message}"


The projects importer works exactly like the users importer. There is one method which handles the import.



Now everything should work and you are done. I know that my example import scripts are not really heavy to program, but I just wanted to explain how you could structure such an importer. If you have a database with a lot of joins and a tricky schema your import script will be much much more complicated. But with this importer-structure you just have to add a new module which handels the import part, call the method from the module in the Importer class and you are done.

You always should keep a very close eye on the topic "Testing". You should test your importer script before starting an import on your production system, that is very important! Otherwise you can destroy your production database and that never is funny ;-)

Feel free to comment on this blogpost, I will be happy about that! If you are interested in a more difficult importer script do not hestitate to ask me.


Simple mysql search with rails and jquery.highlight

posted by Matthias Frick in Ruby on Rails, JavaScript / 1 Comment

I re-designed my whole website a few month ago and now I added one more key feature - a search for my blog. My blog is not as big, as maybe others are, so I decided to create a normal mysql search. I shortly thought about integrating a solution with solr, like the sunspot_rails gem, but then I thought that is to much and I simple chose mysql. Btw Ryan Bates did a very nice railscasts about the sunspot rails gem. Just check it out here: [LINK]

You can imagine how the search works with mysql already? I think so, but will give you a short overview for better understanding. I simple build a form in the views and an action in my controller. A little method in my posts model handles the querying.

# posts_controller.rb
def index
  if params[:search]
    @posts =[:search])
    # somtething else ..

# “.search” is my class method in the posts model, which handles the querying

I think that's easy so far. Now I will show you how you can implement an highlighting for your search. I have done this with jquery. I integrated the jquery.highlight plugin. You can find it here: [LINK]

I coded two little javascript functions. The first one is for rendering the highlights in nearly real time. If an user types in a search word, it will highlight the potential results automatically. The code looks like following snippet:

function realtimeHighlightText() {
  var self = this;
  self.input = $("#search").select().focus();
  self.performSearch = function() {
    var phrase = $("#search").val();
    if (phrase.length < 2) { return; }
    self.input.keyup(function(e) {
      $('.your-div-which-contains-the-content ').removeHighlight();
      if ( { clearTimeout(; } = setTimeout(self.performSearch, 300);

The second function is for rendering the highlights after an user submits a search word. For example she/he fills in the search form with “Rails” and further submits the query, all results will be displayed on the monitor, and all matches with the search word “Rails” in the result posts contents will be highlighted. The code looks like the following few lines:

function highlightText() {
  if (phrase.length > 0) {
     $('.your-div-which-contains-the-content ').highlight(phrase);

Now you just have to put some styles for “. your-div-which-contains-the-content” in one of your css files. An example for that could be:

# your-css.css
. your-div-which-contains-the-content {
  background-color: yellow;
  color: #000;

And you are done and will have a simple mysql search with highlights for search terms.


rails and mongoid i18n model attributes

posted by Matthias Frick in Ruby on Rails / 1 Comment

Today we had a little problem in translate model attributes in one of my recent projects. We use mongodb with the mongoid gem as orm in this project. We create the project with multilingualism and we use the normal i18n gem for that.

Now here is a little tip for fixing model attributes translations in the .yml-Files:

Instead of this:

        user: "Showmaster"
          email: "E-Mail"

Just write this into your .yml-File:

        user: "Showmaster"
          email: "E-Mail"

Now you have the normal access to the i18n gem helpers and so on.

This is just a little hint, maybe it will save you some time somedays.


i18n yaml generator

posted by Matthias Frick in Ruby on Rails / 0 Comments

In this short blogpost I want present you a nice little generator gem called i18n-yaml-generator from the company Skalar from norway.

The guys there created a nice gem which invokes an i18n yaml generator when calling the standard scaffold model or controller generators provided from the rails framework itself. The locale files are generated for all locales listed in I18n.available_locales.

The gem generates i18n locale files for models and views. I think it is very convenient and worth a try to check it out if you often need i18n yaml files and you are bored from creating them by yourself all the time.

You can find further information about the generator gem and detailed instructions how to use it on github:

Have a nice day! :-)