Rails databases, why use same DBMS for development and production?

Question

I really can't seem to find an accurate explanation on why you should use the same database in development as in production. I personally love using sqlite3 in development (it's light, easy to use, and has a sweet sqlite browser GUI tool), then use postgres in production with heroku. But, I keep hearing whether from RBates or Michael Hartl or someone else that you should use the same database in both, why exactly is this?

Answer 1

I have used SQLite3 in development and PostgreSQL in production for my first few Rails projects. Just today I switched my current project's development environment over to PostgreSQL.

When I first deployed my app to Heroku, I spent two days correcting errors caused by database migrations. The migrations worked fine in SQLite3 but broke in PostgreSQL. If I had used one DB, I would have avoided this time debugging.

This morning I was trying to use some SQL queries. If you are only using rails queries, you can get away with switching DBs. When you start writing SQL, you begin to see the problems. Check out my question and the solution here: Ruby strftime '%-m' not working in query

If I had ever gotten my original SQL queries to work using strftime, they would have broke in production.

Save yourself the future headache by creating matching development and production environments.