I can write a module with integration tests at the module level and unit tests on its functions.
I can now write an application that uses my module. From the perspective of my application, my module's integration tests look like unit tests.
My module might, for example, implicitly depend on the test suite of CPython, the C compiler, the QA at the chip fab. But I don't need to run those tests any more.
In your case you hope the in-memory database matches the production one enough that you can write fast isolated unit tests on your application logic. You can trust this works because something else unit-tested the in-memory database, and integration tested the db client against the various db backends.
That's because it is both confusing and inconsistent. In my experience, every company uses slightly different names for different types of tests. Unit tests are generally fairly well understood as testing the single unit (a method/function) but after that things get murky fast.
For example, integration tests as reflected by the confused conversation in this thread already has wildly different definitions depending on who you ask.
For example, someone might interpret them as "unit integration tests" where it reflects a test that tests a class, builder, etc. Basically something where a few units are combined. But, in some companies I have seen these being called "component tests".
Then there is the word "functional tests" which in some companies means the same as "manual tests done by QA" but for others simply means automated front-end tests. But in yet other companies those automated tests are called end 2 end tests.
What's interesting to me when viewing these online discussions is the complete lack of awareness people display about this.
You will see people very confidently say that "test X should by done in such and such way" in response to someone where it is very clear they are actually talking about different types of tests.
In fact, when I first saw Kent Beck's definition I did a double take because it covered what I would have called hermetic end to end tests.
The industry badly needs new words because it's barely possible to have a coherent conversation within the confines of the current terminology.