Both have their pros and cons.

InMemory is super fast and great for quick logic checks, but it doesn’t behave like a real database — no real SQL, no constraints, so you might miss bugs...

Real database gives you the full picture and catches SQL-related issues, but it’s slower and tricky to manage in parallel tests.

A nice middle ground is using Testcontainers — it spins up a real database (like SQL Server or Postgres) in Docker just for your tests, then tears it down automatically. So you get real behavior without the headache of managing it manually.