Read my latest article: 8 things I look for in a Ruby on Rails app (posted Thu, 06 Jul 2017 17:59:00 GMT)

Migrating from MySQL to PostgreSQL in 60 seconds (with Rails)

Posted by Sat, 29 Oct 2005 19:23:00 GMT

I do quite a few MySQL to PostgreSQL migrations for clients of PLANET ARGON and over the years I have gone through different methods of handling this procedure. Typically, it involved exporting data, re-importing it through some shell script that does its best to take into consideration the slight differences between the two databases.

While working on a recent project that was to be migrated from MySQL to PostgreSQL, but after some Refactoring had been done to the code base. I had been meaning to spend a few hours coming up with some simplified process of taking data from MySQL and replicating it to a new PostgreSQL database, while maintaining referential integrity.

My original idea was to build a script that referenced two seperate databases and then copied data out and inserted it into the new db after performing a few data changes. This seemed like too much work and I knew that I should be able to harness the power of Ruby and Rails in my process. My next thought? RailsFS.

In a perfect world, I would mount one instance of the application with a mysql database and another with a postgresql database… and just copy the yaml files to the postgresql and bam, it’d work, right? Well, it doesn’t quite work that way. So, I ruled out that idea.

Then I had another idea. I would build a task in my Rakefile that… imported each of those YAML files into PostgreSQL!

We can now type in the following commands:

This first creates my PostgreSQL database tables using Migrations.
rake migrate VERSION=1


rake mysql2pgsql
mysql2pgsql runs…and approx 60 seconds later, I had an exact copy of the MySQL database in PostgreSQL. But wait! PostgreSQL has SEQUENCES… so I then run:
rake migrate VERSION=2

...and my SEQUENCES are ALTER’d.

Yes, I will post some code in the near future. But all I am doing is basically running through all the YAML files that are available from RailsFS and adding them into the new database. It automatically figures out the model name…and is fairly generic at the moment. However, I’d like to spend a bit more time stress-testing it before I post it. :-)

I want to try to work on a RailsFS-less version as well so that I can run this on my PowerBook.

Until next time… have fun!

Using Named Placeholders in Ruby

Posted by Fri, 21 Oct 2005 05:53:00 GMT

2 comments Latest by Sokolov Yura Sun, 27 Aug 2006 08:34:26 GMT

Insert Hip Quote Here:

“In ancient times, hundreds of years before the dawn of history, an ancient race of people… the Druids. No one knows who they were or what they were doing… “ – Nigel Tufnel, Spinal Tap

Story Time…

Earlier, I was giving a customer of ours, Jared from a quick tutorial on some of the features script/console… which lead to helping him with a SQL query. When I provided him with some working code he was curious about what I had done in the SQL query string that I was passing to find_by_sql.


If you have ANY SQL queries that resembles the following, PLEASE READ THE REST OF THIS. :-)

values = params[:search]
RockLegend.find( :all, :conditions => "first_name = '#{values['first_name']}'" )

If you are doing that… then you are opening yourself up to some security problems. Let’s take a few minutes and discuss how you can make this more secure and still keep your code readable. (the best of both worlds!)

The ? Placeholder

Many of you are probably familiar with this approach…
RockLegend.find( :all, :conditions => ['first_name = ?', 'Nigel'] )

RockLegend.find( :all, :conditions => ['first_name = ? AND last_name = ?', 'Nigel', 'Tufnel'] )

You can pass it a hash as well.. and as long as you put everything in the same order as the ?s are placed… then all is well.

My only real problem with this approach is that it requires you to keep things in a specific sequential order… and who wants to keep track of that? So, I would like to recommend that you use named placeholders. Aside from that, it looks magical and I don’t like magical-looking code. I like easy to read code. :-)

Named Placeholders

If you already use these… you know how useful they can be in your SQL queries. If you haven’t seen them before… it’s because the Rails docs don’t really mention it and is something that comes from the underlying database library in Ruby. So what is so great about these?

Let’s first replace the above code with named parameters…
RockLegend.find( :all, :conditions => ['first_name = :first_name', { :first_name => 'Nigel' } ] )

We are passing a hash with a matching key to the conditions option. Pretty neat, right? In this case with just one placeholder we just increased the amount of code to do the same thing. So, it might always be the best solution… but it is easier to read.

Let’s try another with multiple keys in our hash… infact, we’ll build the hash prior to calling find.

values = { :first_name => 'Nigel', :last_name => 'Tufnel'}
RockLegend.find( :all, :conditions => ['first_name = :first_name AND last_name = :last_name', values ] )

It will happily match the hash keys to the named placeholders in the conditions string. Again, nothing terribly exciting…but it is easier to read.

Who cares about order? Not named placeholders!

Okay, let’s mix things up a bit…

values = { :last_name => 'Tufnel', :first_name => 'Nigel' }
RockLegend.find( :all, :conditions => ['first_name = :first_name AND last_name = :last_name', values ] )

The hash keys were not added in the same order… but it still works!

Okay, now for one last quick example (it’s late and I am tired…).

I have a search mechanism on a site that allows you to search for a string of text across multiple fields. So, I have one string… but several fields to compare against.

Here is a string that I will pass to the find method.
conditions = "role = :role AND (first_name ~* :str OR last_name ~* :str OR nick_name ~* :str)"

Note: this string is using PostgreSQL regular expressions... (~*).

Here is a hash that with that matches the keys, :str and :role

values = { :str => '^(Nigel|Tufnel)$', :role => 'Guitar' }

If you look above, you’ll see that the conditions string contains four named placeholders… but the hash only has two keys. With the ? placeholder, we would have to pass the same vaule three times… which isn’t any fun to read or maintain. ;-/

So, with our new friends, named parameters, we can call find (or any find-like method) using this technique for placeholders.

RockLegend.find( :all, :conditions => [ conditions, values ] )

...and hopefully this is useful to you. :-)

Have fun!

New Active Record Options for Associations

Posted by Wed, 19 Oct 2005 23:00:00 GMT

7 comments Latest by dfhsdfh Mon, 21 Aug 2006 10:51:08 GMT

Two months ago today I posted about a bug in Active Record. A bug that reminded me to remain cautious about how much trust I put into a database abstraction layer. I am happy to now say that this particular bug has been fixed, and I got to help! In the process, I also got to add some new features. (see my original rant, Active Record, I love U but I still trust my database server a tiny bit more.

I discovered this bug when I was working on a chapter in my book on Active Record. I’m known to gladly take advantage of database constraints and triggers, and it was when I decided to test my code without these constraints, I discovered the bug. “Hey, Active Record isn’t doing what it’s supposed to!” I’ve since had a number of people ask me if what a more pragmatic way to work around this issue is, rather than go my route by adding a constraint/trigger.

You can now DO AWAY with ON DELETE CASCADE! (sort of)

The new release of Rails 1.0 Release Candidate includes some new options for the has_many and has_one declarations.

Previously, you could do the following:

class Customer < ActiveRecord::Base
  has_many :orders, :dependent => true

This was supposed to nullify the dependent records, but it didn’t!

My patch not only fixes this, but also gives more control with what :dependent does. Now, you can pass the :dependent option to the has_many and has_one declarations with either :nullify or :destroy. This has a similar affect as ON DELETE CASCADE in those fancy RDBMs like PostgreSQL.

Let’s take a closer look at these new options:


A spider has_one web, and the web belongs_to one spider. If you destroy the spider, you would most likely want to destroy the web as well.

class Spider < ActiveRecord::Base
  has_one :web, :dependent => :destroy

On the other hand, in the case of a snail that has_one shell (and the one shell belongs_to the snail), if you destroy the snail, you may want to keep the shell. Remember, your crazy Aunt Ruby collects snail shells.

class Snail < ActiveRecord::Base
  has_one :shell, :dependent => :nullify

Now, for every snail we destroy, the shells, though once dependent on the snail, are now available for Aunt Ruby.


The same rules apply to the has_many association. Most people assume that if you destory a beehive that you would destroy all the bees. I’d like to think that they wander around until they find a new beehive to join. So, we can :nullify their relationship when the beehive is destroyed, thus making them homeless, but available for future hives.

class Beehive < ActiveRecord::Base
  has_many :bees, :dependent => :nullify

Sadly, some people might want to destroy the bees along with the destruction of the beehive. So, those people can pass the :dependent option, :destroy.

class Beehive < ActiveRecord::Base
  has_many :bees, :dependent => :destroy

Don’t worry, your usage of :dependent => true will now work, even though it wasn’t working before.

I’m going to try to put more trust into Active Record now, and I hope that this new addition to the library finds itself useful for you. :-)

Take a peak at the cool new features in the latest version of Rails and see the Active Record CHANGELOG for more information.


A PostgreSQL tip

Posted by Wed, 05 Oct 2005 06:07:00 GMT

I promised that I would send a link out or something… so here goes.

Tonight at the Portland Ruby Brigade meeting, the question about having Active Record READ from one database and WRITE to another database was brought up. The discussion was going in the route of, “How can we make AR do this?”

I chimed in with my usual PostgreSQL bias. “pgpool.”

Let something else handle this. Infact, with PgPool, you don’t have to change any code in your Rails application to make this work. :-)

So, take a look at: pgpool


Portland Ruby Brigade meets CD Baby

Posted by Wed, 05 Oct 2005 05:25:00 GMT

This is what happened to the Portland Ruby Brigade today:
  • Got double booked on meeting night with the PHP group
  • Got asked to leave after getting threats of the lights being turned off
  • Stayed almost til closing time at a brew… we heard, “Last Call!” and still continued talking…

Tonight I finally got to meet Ben, Caleb, Justin Watkins, Marcus Estes, Lennon Day-Reynolds, Jeremy Kempter (bitsweat), Aaron Johnson, and Peat Bakke-each for the first time. I also got to see Scott Laird (of Typo fame) and John Labovitz again. Scott and I met at FOSCON and John once found me when he was trying to hack people at a coffee shop and saw that I was on the wireless network. Okay, he wasn’t hacking… but found me at Urban Grind. :)

We had about ~25 people show up. I don’t think that there was a head count made. We sat around in a big circle and fired questions at both Derek Sivers and Jeremy Kemper. Topics ranged from how Derek found his way to PostgreSQL to why he thinks that he is an amazing web designer (smirk) to why Ruby and Rails was picked as the platform to move to from a PHP/MySQL system. Jeremy shared some insights into some problems with Rails that need to be worked on (boolean-stuff for example), tips on debugging, and many other things that I don’t recall off the top of my head.

It was a very relaxed and open conversation with some cool local developers who are working on getting their Rails/PostgreSQL system finished.

After we got kicked out of FreeGeek most of migrated to the Lucky Lab Pub… and then we realized that Derek and Jeremy didn’t show up. So, we found our ways to a big table and broke up into smaller conversations. I got stuck at the table with Ben, Scott, Caleb, and John. Actually, it was quite enjoyable. Scott talked about his work with Typo and Ben and I gave him some of our thoughts on things.

This is a horrible summary of the whole evening. It’s what I remember… and it’s late and I look forward to getting to hang out with this gang again in the future. I think that it was great to get to finally meet a few of the people that I have worked on some smaller projects with. If everything works out well, expect there to be some more projects coming out of the PDX.rb in the coming months. :-)

Thanks again to Derek Sivers and Jeremy Kemper for taking time out of their busy schedule to meet & greet with the Portland Ruby people. :-)

The bitter-sweet taste of agnostic database schemas

Posted by Tue, 27 Sep 2005 13:41:00 GMT

If you know me at all by now… you might know that I am a huge fan of PostgreSQL. Coming to the Rails camp was a bit of an eye-opener./ Working with PostgreSQL used to save me quite a bit of work and let me sleep at night. I have dealt with the problems that many developers face while working with MySQL. Like many of us in the Open Source world, I was exposed to MySQL more often than PostgreSQL. For several years, I convinced myself that MySQL was awesome because it was fast… but speed isn’t always the most important factor in the technology world. Otherwise, we’d all be coding in C. What PostgreSQL provided to me as a developer was better relational integrity. Foo cannot exist unless Bar does, or vice versa. ON DELETE CASCADE was a godsend and saved me quite a bit of work when building an application. Triggers… Procedural Languages… oh so beautiful.

PostgreSQL was (and is) the Open Source database server that both the Enterprise DBA and Developer could agree on.

Enter Ruby on Rails

At first, I just ignored my instincts and jumped in head first. Pluralization? Primary keys named id? Blasphemy! Yet, I moved forward. After a few days of consistently reminding myself of these new-fangled conventions (I even printed out a little cheat sheet and taped it to my monitor)... I was hooked. Rails worked with PostgreSQL. Let me say that again.


Don’t freak out. Yes, the person who started the Rails project is a MySQL fan... but Rails is database agnostic. It’s true.

I wanted to write an entry to clear up a few myths about Rails.

  • Table names are not forced
  • Primary Key field names are not forced
  • Foriegn Key field names are not forced
  • Legacy databases can work with Rails
  • Active Record (like much of Rails) can be customized for your particular application’s needs

I was, and still am, concerned about trusting Active Record with handling my relational data… because in some cases it wasn’t doing what it should. But, that is being worked on… and because Rails is Open Source, the issue is transparent for us developers to see and help fix.

In the past, you might have found me advocating the addition of business logic in your database server, whenever possible. It is the gate keeper. The key master. It is not meant to be flexible with your data. If business rules are to change, you change them there so that if you have one or more INPUT streams into your database, the data will not get into your server unless your business rules are met. PostgreSQL even provides you with a nice ERROR message, which some other servers do not. If you can control everything through one INPUT stream (Application Database), then putting these constraints solely in your database abstraction layer is a-ok. However, if you are interfacing with your server through a variety of avenues (Integration Database), you probably already know that your system is going to be a pain to maintain in the long run. So, what are you to do?

Handling Legacy systems is going to be a headache, regardless of what you do… but you can’t always rebuild the whole thing and migrate your data. Add that to your Someday-Maybe list.

When you’re adding new pieces to your application, start by using good tools, frameworks, and practices. For example, try your best to follow the Application Database path to pragmatic enlightenment. Begin working on a new layer for your legacy database. Add on web services that use this layer. Refactor your existing applications. Can they use these new services? ReThink everything. That is… if you have the time and resources to do so.

If you are questioning whether or not to put your business logic in your database abstraction layer, stop. Just do it™. But, do it with caution. Test it. Test it well. I’ll admit that I still add some constraints into my database schemas-I am not ready to give up that extra layer of data security. But, that’s just me. :)

PostgreSQL works with Rails!

...and I promise to show you how to do some fun legacy stuff with Rails in my book. :-)

Older posts: 1 2 3 4 5 6