Which database to choose for a new Rails application - MySQL


#1

Hi

I’m choosing a database for a new Rails application. I pretty much
ruled out PostgreSQL, since despite good reviews of the database
itself, it seems it has countless issues with Rails (for example: in
recent releases migrations don’t work, and unit-tests fail).

So I should now choose between MySQL 4.1, which as I understand enjoys
widespread popularity - perhaps the most widely used database for
Rails, and MySQL 5.0, which is not as popular and tested, but
obviously has certain advatages.

Which would you choose?

Regards,
Alder


#2

Just curious, why do you say unit tests fail with PostgreSQL? I have
had
some problems with unit tests lately, so any info you have on that would
be
great.

On the other hand, I haven’t had any problems at all with migrations in
rails 1.1.2. I was a little worried about using PostgreSQL because it
seemed to have a high learning curve when I looked at it six months ago,
but
I have been doing all my development work with it lately with no
problems.


#3

Not trying to sound unhelpful here, but does it really matter? I
guess, we’d need to know more information. Where are you going to
host it? Are there constraints there? That might be a determining
factor. What compelling features do you find in 5.0 that would apply
to Rails development?

Michael


#4

On 4/18/06, Eden B. removed_email_address@domain.invalid wrote:

Just curious, why do you say unit tests fail with PostgreSQL? I have had
some problems with unit tests lately, so any info you have on that would be
great.

Hi Eden

Check out this thread:
http://wrath.rubyonrails.org/pipermail/rails/2006-April/030091.html

and this:
http://wrath.rubyonrails.org/pipermail/rails/2006-April/030188.html

WRT migrations, look here:
http://wrath.rubyonrails.org/pipermail/rails/2006-April/031813.html

I’m not so much bothered by the issues with migrations - which I’m
unlikely to use - but searching this list’s archives gave the
impression that PostgreSQL is less supported in Rails, backwards
compatibility to it is deemed less crucial, and in any case - people
using it in Rails tend to have more issues with it than with MySQL,
which for the Rails framework seems to be the database most supported.

-Alder


#5

On 4/18/06, Michael T. removed_email_address@domain.invalid wrote:

Not trying to sound unhelpful here, but does it really matter? I
guess, we’d need to know more information. Where are you going to
host it?

A dedicated server.

Are there constraints there?

It’s a dedicated Sempron 2800 with 512 Mbs of memory. The application
is completely new and no backward compatibility issues. Can’t see any
constraints, really.

What compelling features do you find in 5.0 that would apply
to Rails development?

Generally the performance enhancements. For my application it’s
crucial that SELECT operations would be as fast as possible. Beyond
performance, I could manage with the feature-set of MySQL 4.1. Version
5 has some neat new features (triggers, stored procedures) but I
really doubt they are very important for Rails development, and in any
case they’re much less important than performance and stability

-Alder


#6

It looks like you can do whatever you want, and in that case I’d
probably go with 5.0 for the performance reasons.

I have Rails applications running on 4.0, 4.1 and 5.0, but none of
mine are intensive. I do not have any issues with any of them.

Good luck.

Michael


#7

Of the three threads you sent us off to, the first was resolved by
switching Postgres client drivers, the second turned out to be user
error, and the third is… well… one person reporting a problem with
specific actions in her/his migrations and then not posting the
migrations, which for all I know might have escaped out to
MySQL-specific SQL. These do not sound like widespread problems or in
the first two cases, problems at all.

If you have the luxury of picking a database, I assume you’re writing a
new application from the ground up. If that’s the case, why on earth
wouldn’t you want to use migrations?

Alder G. wrote:

On 4/18/06, Eden B. removed_email_address@domain.invalid wrote:

Just curious, why do you say unit tests fail with PostgreSQL? I have had
some problems with unit tests lately, so any info you have on that would be
great.

Hi Eden

Check out this thread:
http://wrath.rubyonrails.org/pipermail/rails/2006-April/030091.html

and this:
http://wrath.rubyonrails.org/pipermail/rails/2006-April/030188.html

WRT migrations, look here:
http://wrath.rubyonrails.org/pipermail/rails/2006-April/031813.html

I’m not so much bothered by the issues with migrations - which I’m
unlikely to use - but searching this list’s archives gave the
impression that PostgreSQL is less supported in Rails, backwards
compatibility to it is deemed less crucial, and in any case - people
using it in Rails tend to have more issues with it than with MySQL,
which for the Rails framework seems to be the database most supported.

-Alder


#8

Since you seem inclined to go with MySQL, go for it. Each different
database has its strengths and weaknesses but in case of Rails, both
PostgreSQL and MySQL are fully usable.

Exactly, if you’re not familiar enough with the reasons to choose one or
the other go with which ever you prefer. I do all of my development on
postgres and deploy to mysql. I’ve even been known to demo my
applications using sqlite. They all work fine.

As for being bothered by issues with migrations…you should be if it
were true. Migrations abstracts the actual database used so you could
easily move between different db’s and test them out for yourself if you
chose. At this point, knowing what I have learned in the last 3 months,
I wouldn’t develop Rails without migrations and svn.

Craig

I couldn’t agree more. If you are starting from scratch you will find
that “script/generate model” creates migrations any way. Learn them use
them!

This will keep your code more portable, which is always a good thing,
but even more so they are extremely useful! Being able to roll the
database forward/back during development is an extremely useful thing.
Not to mention how easy it makes life when your trying to deploy a new
version of your application.

In a past life I used to end up re-writing scripts for this
functionality with every project, now it comes for free with Rails =)


#9

On Tue, 2006-04-18 at 17:34 +0200, Alder G. wrote:

and this: http://wrath.rubyonrails.org/pipermail/rails/2006-April/030188.html

WRT migrations, look here:
http://wrath.rubyonrails.org/pipermail/rails/2006-April/031813.html

I’m not so much bothered by the issues with migrations - which I’m
unlikely to use - but searching this list’s archives gave the
impression that PostgreSQL is less supported in Rails, backwards
compatibility to it is deemed less crucial, and in any case - people
using it in Rails tend to have more issues with it than with MySQL,
which for the Rails framework seems to be the database most supported.


It appears that you are making judgments based upon initial reactions to
changes that occurred and I can assure you that migrations works
perfectly with postgresql and I’m working towards unit tests but as had
been stated by another on this thread, they aren’t an issue with
postgresql.

Recognize that this list has people with all varying levels of knowledge
of ruby/rails - in fact, most are probably less knowledgeable and thus
you get questions like the ones you listed but often, they are not
indicative of any real problems.

Since you seem inclined to go with MySQL, go for it. Each different
database has its strengths and weaknesses but in case of Rails, both
PostgreSQL and MySQL are fully usable.

As for being bothered by issues with migrations…you should be if it
were true. Migrations abstracts the actual database used so you could
easily move between different db’s and test them out for yourself if you
chose. At this point, knowing what I have learned in the last 3 months,
I wouldn’t develop Rails without migrations and svn.

Craig


#10

On Apr 18, 2006, at 8:34 AM, Alder G. wrote:

http://wrath.rubyonrails.org/pipermail/rails/2006-April/030091.html
You have to use the release version of the driver, not the head version,
that’s all.

and this: http://wrath.rubyonrails.org/pipermail/rails/2006-April/
030188.html

That has to do with default now() values, IIRC. Don’t use them.

WRT migrations, look here:
http://wrath.rubyonrails.org/pipermail/rails/2006-April/031813.html

I haven’t seen that, but I must admit changing types on columns is
pretty darn rare…

I’m not so much bothered by the issues with migrations - which I’m
unlikely to use

!!! Yikes! Migrations are one of the BEST features of Rails!

  • but searching this list’s archives gave the
    impression that PostgreSQL is less supported in Rails, backwards
    compatibility to it is deemed less crucial, and in any case - people
    using it in Rails tend to have more issues with it than with MySQL,
    which for the Rails framework seems to be the database most supported.

I’ve had few problems, and I can assure you that if you did a
statistical
study, you’d see that there are more MySQL problems on the lists, as
there
are more MySQL users. :slight_smile:

Give PostgreSQL a shot. You won’t look back.

The ability to use transactions in migrations is reason enough alone!


– Tom M.


#11

On Apr 18, 2006, at 11:14 AM, Alder G. wrote:

Which version of PostgreSQL would you recommend? The application is
being written with Ruby 1.8.4 and Rails 1.1.2

That’s the spirit!

Hell, if we all wanted to go with the popular solution, we woulnd’t be
here anyway. :slight_smile:

I’m using 8.1.3 and having zero problems.

Oh, and please look into migrations. I’m finding it hard to believe
that I used to work without them. Imagine a completely natural and
structured way to change the schema and underlying data in synch with
your code.

And now imagine it in a DB independent form!


– Tom M.


#12

On 4/18/06, Tom M. removed_email_address@domain.invalid wrote:

are more MySQL users. :slight_smile:
removed_email_address@domain.invalid
http://lists.rubyonrails.org/mailman/listinfo/rails

Hmmm, maybe I should give it a shot :slight_smile:

Which version of PostgreSQL would you recommend? The application is
being written with Ruby 1.8.4 and Rails 1.1.2

-Alder