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?

Nightdress

For starters: try the following on SQLite and Postgres: create table foo (foo_date date); insert into foo values ('2015-02-31'), ('XXX'); select * from foo; – Rehnberg

ok, so your saying there is alot more you can do with it in dev mode? – Nightdress

SQLite is quite limited compared to Postgres. The inability to validate a date value is just one. If your application for one reasons sends an invalid date to the database all your tests would pass but it will fail miserably on production. There are many more things like that. In a nutshell: testing on a different DBMS than you use in production is futile because you still don't know if your application will work in production. – Rehnberg

great, thanks for your input horsey. – Nightdress

To be a bit pedantic, your question should be titled "Why use the same DBMS for development and production?". Postgres, Mysql, Sqlite etc are all different database management systems, or DBMSs for short. Within an install of any of them you might have several different databases. Your question sounds like you are asking about using the same actual database (ie the same actual data) in development mode as production. – Correction

@MaxWilliams: good point. I changed the subject – Rehnberg

Ah ok Max, good call, i think, but you and everyone else knew what i was asking to be fair :-) – Nightdress

After i read the question, yes. On my local machine i use the same actual database in dev and production mode, and there is some controversy about whether it's a good idea to do this, and so i thought your question was about this subject. There is no controversy about the question with regards to DBMSs, they should always be the same in all environments, as a part of the general policy of "Make your dev environment as close as possible to your production environment". – Correction

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.

Hygrothermograph