Why Oracle On Rails?

Well, the answer is quite simple.

First, I will admit, I never tried Postgre SQL database, and, that's why it is not included in this post.
As for SQL-Server, I believe that there are lots of people more qualified than me for writing about it. So, I'll let them write their own blog.
The main problem with SQL-Server for Rails development, is of course the fact it requires the Windows OS for it to run, even in the free (Express) version. And Windows isn't free yet.

The main database for most Rails applications on the Web is the MySQL database, for the following reasons:

1. Installation : It is very simple to install on almost any Operating system, and on most of the main Linux distributions, MySQL even comes pre-installed.
2. First use : Once you install it, no adjustment has to be done (From the database side) for RAILS to access the database.
3. Costs : MySQL is an open-source product, is free, and will always remain free. We like to support this kind of projects, anytime we can.
4. Weight : The development in a typical rails project is using a database on each developer's machine, and MySQL is a fairly small application which doesn't consume too much memory and CPU. Very convinient.
   
5. Widely used : The main reason - each and every one of the Rails books, PDF's and web tutorials use MySQL. why should I be different?

But, MySQL has some limitations, that most of us don't even consider when we start developing.
Here are some of them:

1. Data size limit : MySQL has a data storage limitation. Did you know it? MySQL is limited to 4GB of data per table, due to it's 32-bit pointers.
   So, if you expect, or even suspect that your application might grow to a table with size larger than that, MySQL is not the answer.
   There are workarounds for that (http://jeremy.zawodny.com/blog/archives/000796.html), but in practice, MySQL becomes slow even in smaller tables due to :
   
2. Complex selects : MySQL has a pretty naive optimizer. For example, if there are 5 indexes on one table, even if it's much more efficient to use 2 or 3 of them, MySQL will always use one, and only one. he's not any smarter than that.
   This is just a small example, and even if you didn't understand a word of it, trust me :
   Even if it works fast for you on a developer machine, when your database grows you don't want to use MySQL for complex selects. Use something else.

Is that it?
Well, yes, that's it.

This are the cases you souldn't use MySQL:

• Database that is expected or suspected to grow large.
• Application that requires (or will require) complex selects or reports.
  Even one select should be considered. Remember : those selects are very hard (and sometimes impossible) to tune on MySQL. Be warned.
  

If none of these cases is true for your application (Your application uses a small database in a very simple way, with no complex select, no reports, and so on), your BEST choice is MySQL. Have fun with it. It's much simpler.

If any of these cases is true. You are in big trouble. You're gonna have to learn using another database, which doesn't appear in almost any book about rails.

Let's talk about Oracle database (for PostgreSQL or SQL-Server, refer to the beginning of the post):

So, let's test it with the set of reasons that led us to choose MySQL in the first place:

1. Installation : Oracle has a product called Oracle database XE (Express edition). It is a fully functional oracle database, with few limitations :
   
   • 4GB of data.
     
   • 1 CPU.
   It's easy to install the XE on windows and linux, but you need to have admin permissions. I never tried to install it on unix machine, and actually, I see no reason for it...
   
2. First use : Once installed, a user has to be created for the rails application to use, and has to be given the right permissions. Only after you've done that, rails can access the database.
   More safe, less convinient.
   
3. Costs : Well, you'll be surprised. Oracle XE's licence allows you to do anything you want using it, for both commercial or non-commercial uses, as long as you don't break any of the limitations mentioned in Installation.
   Practically, up to the 4GB limit it's just as free as MySQL.
   After the 4GB limit, well, MySQL is just not a player in this league.
4. Weight : This is the main problem with Oracle XE compared with MySQL. It's much heavier, and you'll want at least 512MB RAM on the machine running it.
   So installing it on every developer machine wouldn't be that wise.
   But hey, who needs it, when you can create as many database users as you wish on you unlimited Oracle XE instance?
   Just create one free database (XE on linux) and create a user for each environment, or, if you want to stick to the Database For Each Developer concept (Why woul you do that?) you can create a user for each developer. Aha, just like the old MySQL days...
   
5. Widely used : So here is the common problem. Oracle is not widely used and documented inside the Rails community.
   And here are some good news : Oracle is now starting, slowly, to understand the potential of the Rails community, and starts guiding the community on how to use Rails with Oracle.
   But, in a larger perspective, in the real world, Oracle database is used much more than Rails and MySQL Togegher. It means that it's more documented and supported, and very reliable and powerful.

So, conclusion for this post: You can work,for free, with a much more powerful, and reliable database, that will let your application scale smoothly. Actually, when your database grows, all you have to do is buy a licence to a bigger instance of Oracle database.

And hey, if you have a Rails web 2.0 app that holds more than 4GB of user content in your database, man, your traffic rocks - you can afford yourself a licence of a good reliable database.
Or, at least, you can not afford yourself a slow database or a non-reliable one.

Next posts :
Installing Oracle XE on a server (Debian or Windows), Connecting to it from a development machine, and fixing the most common problem of rails and oracle (the date columns).

If you have any questions or cases regarding Oracle and rails don't hesistate sending them to oraclerails@gmail.com. I'll be happy to help you and post a note about the problem and solvement in this blog.