5

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?

Spencer Hire
  • 735
  • 3
  • 14
  • 32
  • 2
    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;` –  Apr 20 '15 at 09:13
  • ok, so your saying there is alot more you can do with it in dev mode? – Spencer Hire Apr 20 '15 at 09:15
  • 4
    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. –  Apr 20 '15 at 09:20
  • great, thanks for your input horsey. – Spencer Hire Apr 20 '15 at 09:26
  • 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. – Max Williams Apr 20 '15 at 09:37
  • @MaxWilliams: good point. I changed the subject –  Apr 20 '15 at 09:44
  • Ah ok Max, good call, i think, but you and everyone else knew what i was asking to be fair :-) – Spencer Hire Apr 20 '15 at 09:44
  • 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". – Max Williams Apr 20 '15 at 09:55

1 Answers1

7

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.

Community
  • 1
  • 1
Jack Pope
  • 164
  • 2
  • 7