Yes, I often see Java developers using an embedded H2 database [1] for development and test, and a "real" database in production, and there are always unexpected issues of false-positive tests and functionality due to this. Terrible anti-pattern. I am glad "Dev/prod parity" is recognised in The Twelve-Factor App manifesto [2].
I do what you suggest: run dev/test against a real PostgreSQL (of the same version) running in a container. In fact, I take this further and run builds against an actual PostgreSQL, since I like to generate code based on the DB schema using jOOQ [3].
The build proceeds like so:
1. Spin up a fresh PostgreSQL container
2. Run my schema migrations against it
3. Run the jOOQ code generator against it
4. Compile code
This provides some additional guarantees, including:
1. My queries are fully type-safe from my code down to the database schema level
2. My schema migrations can be run against an empty database
I do what you suggest: run dev/test against a real PostgreSQL (of the same version) running in a container. In fact, I take this further and run builds against an actual PostgreSQL, since I like to generate code based on the DB schema using jOOQ [3].
The build proceeds like so:
1. Spin up a fresh PostgreSQL container
2. Run my schema migrations against it
3. Run the jOOQ code generator against it
4. Compile code
This provides some additional guarantees, including:
1. My queries are fully type-safe from my code down to the database schema level
2. My schema migrations can be run against an empty database
[1] https://www.h2database.com/html/main.html
[2] https://12factor.net/dev-prod-parity
[3] https://www.jooq.org/doc/3.13/manual/code-generation/