Use 2 Databases for Rails: MySQL or SQLite for Testing and Developmnet and PostgreSQL for Production

Because I wanted to use PostgreSQL for my rails app, I set it up for Rails. However, I always seem to get relation does not exist, and many other related errors when Rails is cleaning and repopulating the test database, which I don't encounter with MySQL or SQLite.

So is it possible to set this up? MySQL for testing or development and PostgreSQL for production?

If it is, will maintaining the same schema across different databases be so much of a h开发者_JAVA百科assle?

Maintaining the schema across multiple databases won't be much of a problem. Your problem will be in other areas:

1. MySQL and SQLite are pretty loose with their type conversions, PostgreSQL is strict.
2. MySQL will often silently truncate strings whereas PostgreSQL will complain.
3. MySQL and SQLite will allow you to have things in your SELECT that aren't in a GROUP BY but PostgreSQL will complain.
4. Booleans are treated differently in all three:
   • PostgreSQL uses literal 't' and 'f' (and a few others) for booleans.
   • MySQL uses 1 and 0.
   • SQLite uses 1 and 0 but ActiveRecord uses 't' and 'f' anyway, but this works because SQLite treats everything as a string.

Those are some of the issues off the top of my head but there are certainly others. No ORM can protect you from database differences and quirks.

Spend some time searching for things like "it worked fine until I deployed to Heroku" or even just the tags "[heroku] [postgresql]" and you'll find lots of examples of people that got into trouble by developing on top of MySQL or SQLite but deploying on PostgreSQL.

Short answer: don't do it, develop on top of PostgreSQL if you're planning to deploy on PostgreSQL. And use the same version too.

The critical issue to me is using different systems for development/testing and deployment will increase bugs and make it very hard to find these bugs. Also you'll blame bugs on this difference when there is another answer.

It is always better to minimise your variables and keep things homogeneous.

It's possible, sure. You can use database.yml to specify whatever kinds of databases you want to for the different environments.

That said, it's a bad idea. Even with ActiveRecord sitting as an abstraction layer between you and your database, there will be differences between MySQL and SQLite and Postgres, and some of them will be subtle, and the correct time for them to bite you is in the development and test environments, not production.

A far better idea would be to figure out and fix the errors you're getting from Postgres in development/test mode. Postgres is a great database - it'll be worth the effort.