Don't test with SQLite when you use Postgres in Production

I tend to abstract away my queries using database views. This way the query in application becomes very simple (select * from view where $simplepredicate).

In my test database (sqllite,hsqldb,derby,...) i create actual tables containing test data with the same definition as the views.

This allows me to have rather complex queries that are database specific and still have fast running database queries.

The views themselves i test separately. They are also easier to fix in production than code releases, and can also be replaced by materialized views if necessary.

Inserts/updates are typically simple too.

The comments here are full of people bucking against this advice. I've worked at TWO companies now where people said NO we want to use SQLIte in dev. Both switched in under a year despite fierce internal opposition. Everyone had changed their tune once they hit growth in users and complexity. Why? Because being ideologically right is not as nice as being sure you're writing code that works.

The fact is if you have a serious site there's a good chance SOME specific feature will start looking appealing ( maybe array values, JSON , window functions, a pg extension...). These features are written because people use them, we aren't all writing dead simple CRUD apps. Or you your ORM will interact with one slightly differently than the other. I don't expect to convince any of you doubters, but the first time you get a bug in prod that slipped through the testing process but really could have been caught in dev, you'll ask yourself why you weren't using pg there too.

There's no sane reason to complicate your life by running two databases unless you either have a dead simple app, or are one of the jabbering idealists I see on here. Productive programmers simplify the problem and minimize their tech stacks.

This depends on what the other options are. If you have

* test with SQLite

* test with Postgres

then using the same environment as production (Postgres) is of course better, but if the real options are

* test with SQLite

* don't test

SQLite is much better!

Ive found running the tests in docker works quite well.

Seriously. How is someone that does not even know the correct name of the software supposed to know enough about said software to know whether it is similar enough to be a good test replacement?

I know this is a bit too much against the person, but here that is directly relevant to the topic at hand.

Not that he is totally wrong, but he is wrong in the universality of his argument. It depends on what postgres-specific features the software is using and how the communication with the database is handled. If it is handwritten SQL, it is not a good idea regardless. If it is a ORM that maps the two very well and there is noting postgres-specific about the software, it is unlikely to be a problem.