Although Arseni Mourzenko’s answer covers a lot of the points, it’s important to make a distinction between different databases.
Tests, especially those that manipulate data, should probably be run against a database that is instantiated for those tests. Assuming that your pre-prod environment is also used by people to perform manual testing or demonstrations, you don’t know what the state of the database is. If you ensure that the database starts in a known state, you can make stronger assertions about what the end state will be. If the database isn’t in a known start at the start of the test, the test could erroneously fail or succeed based on the state of existing data or the assertions will need to account for unknown data in the database.
I do think that it makes sense to run tests against a real database on a regular basis, especially if there are stored procedures in the database that need to be tested in conjunction with the application code. However, this should be a database that you have good control over so you can assert a state at the start and end of tests.