Testing with a different database would only be good if you:
a) Also test with your real production database
b) You restrict yourself to using the lowest common feature denominator of both databases (which is probably a pretty low figure)
Still, different databases behave differently in many aspects. And despite how cool SQLite is, PostgreSQL is so much more advanced (specially in SQL querying) that I don't see the point losing all those features. As I have already mentioned in HN, check this amazing presentation: http://use-the-index-luke.com/blog/2015-02/modern-sql before deciding to restrict yourself to a subset of the SQL supported by PostgreSQL.
Given that it's easy to start PostgreSQL from a unit test, and how lightweight PostgreSQL is, I see no real point in using SQLite for testing. Use PostgreSQL!
Comments
Testing with a different database would only be good if you:
a) Also test with your real production database b) You restrict yourself to using the lowest common feature denominator of both databases (which is probably a pretty low figure)
Still, different databases behave differently in many aspects. And despite how cool SQLite is, PostgreSQL is so much more advanced (specially in SQL querying) that I don't see the point losing all those features. As I have already mentioned in HN, check this amazing presentation: http://use-the-index-luke.com/blog/2015-02/modern-sql before deciding to restrict yourself to a subset of the SQL supported by PostgreSQL.
Given that it's easy to start PostgreSQL from a unit test, and how lightweight PostgreSQL is, I see no real point in using SQLite for testing. Use PostgreSQL!