In most descriptions of unit testing as a methodology there's an idea of unit tests being as independent of implementation as possible.
While I'm not sure if this is the core of your current question, look up black box testing vs white box testing. The former intentionally assumes no knowledge of the internals, whereas the latter permits it.
This destroys the very essence of unit testing, since slightest change in the code forces us to update unit tests
Very much make a distinction here between needing to refactor test code so that it compiles, and needing to revisit test cases because the behavior has changed.
When you make intentional changes to the behavior, it is inevitable that you need to revisit your test cases. You may need to add new tests, deprecate now obsolete ones, or adjust existing cases to match the new behavior.
When you make intentional changes to the interface, but not the underlying behavior, then it is inevitable that you have to update your test code to work with this new interface. However, the test cases themselves don't need to be updated since the behavior remains the same.
Unit testing degrades to series of EXPECT_CALL, since the only thing we may check is that calls are made with right arguments and in right order. This destroys the very essence of unit testing
I very much disagree. It is the very essence of unit tests that you only look at the unit and mock everything outside of it.
The things that you have excluded from your unit test (i.e. your external dependencies) are not under test, and therefore their implementation should not have an impact on this test's results.
When you cannot account for the actual implementation, the only remaining verification is to make sure that the interface is being called correctly, which is what you're doing with your EXPECT_CALL assertions.
so there's no persistent snapshot of a logic
I don't understand what you mean here. A snapshot of the logic would be a commit history in your versioning system, including the tests in that history which can at all times be re-run if their outcome was not already recorded.
If, instead, you are worried that a refactor of your code (without an adjustment of your test case) is going to lead your developers to accidentally make changes to your test cases anyway, then you have a different fish to fry. Whether this is low developer skill, unreasonably high test code complexity, or just a mistrust of your developers' abilities; I cannot judge this based on the question as asked.
Even if that type of code is moved out to certain IO module, that IO module still has to be tested.
I'm not sure what your argument here is. The core of unit testing is that each unit has its own dedicated tests. So yeah, a module would have tests written for that module - assuming there is any meaningful behavior worth testing, which might not be the case for IO specifically.
And if app mostly works with db/io/etc., unit tests seem to become unusable in a project.
If your application straight up serves the data from the data source without any real alteration or logic on top of it; then there's not much behavior to test. On the low end of the spectrum, you'd not be writing much more than a mapping test (which I don't considered relevant to test but opinions vary here).
Whether or not your unit tests have purpose directly correlates to how much behavior there is to meaningfully test.