Let's not conflate unit testing with integration testing. SQLite should, in most cases, work just fine for unit testing.

First off, many people would argue unit testing should never really require a database. I'm not of that opinion, but I'm not writing CRUD apps, and since there's not really a SQL unit testing framework, using SQLite is a nice compromise, especially as unit testing should necessarily be limited in scope.

Integration testing should definitely be done on a system that is in parity with prod.