Up until the 2010s the vast majority of applications were deployed on-premises to companies who employed dedicated DBAs and operations staff to manage their preferred database. Developers needed to write SQL that would work on Oracle, MySQL and SQL Server as well as being able to mock out the database with something like H2 to write tests and run locally, because it was not easy or cheap to run a database on your local machine. This led to the proliferation of ORMs like Hibernate to solve for this problem. ORMs offered a bespoke language which translated into the SQL dialect of many databases, as well as tools to map result sets to Java objects.

The price of this solution was an extra layer of complexity and indirection, and the ability to only use the lowest common denominator feature set of the databases. Personally I spent more time reading the docs and debugging the ORM code than I did developing the application, mentally translating between the ORM language and the SQL I knew. Was it ever worth it? Who knows, software development is rife with cargo cults. I have heard database portability touted as an advantage by devs on projects where we’re definitely only using one database, “in case we need to switch later”. But whatever, it got so much traction at the time that it became THE way of doing things.

Time moved on, containers took off and running a database locally was no big deal. DB migration tools like Liquibase got pretty good. Outsourcing your ops team to a cloud provider became the default. Although many security-conscious and highly regulated industries will not be entrusting their data to public SaaS services any time soon, they are more and more likely to be putting their applications on a self-managed PaaS or a company-controlled cloud account where clustered database instances can be launched at the click of a button. For devs, supporting the company’s favorite database is just not an issue any more.

Most application backends are not algorithm heavy. 90% of their code involves reading and writing to databases, transforming data and integrating that data with third-party sources. To draw the module boundary for testing at the data access object layer is to test a lobotomized application. Mocking at that juncture produces low-value tests: they are labour-intensive and don’t give you the confidence to change code without breaking things.

                                                                    .───────. 
                                                                   (         )
                                                                   │`───────'│
                                                                   │         │
┌─────────────────┐                ┌───────────────┐               │         │
│   Core logic    ├    MOCK HERE   │     Data      │               │         │
│    classes      │────────────────▶    access     │───────────────▶   DB   │
│                 │                │    classes    │               │         │
└─────────────────┘                └───────────────┘               │         │
                                                                   │.───────.│
                                                                   (         )
                                                                    `───────' 

The old way is to unit test the core logic classes and mock out the data access classes (and by extension the database). In the new world it’s easy to test with a real database. On a project I work on we use Liquibase for database migrations, JDBI for basic data access classes (written in Kotlin because data classes and multi-line strings) and Testcontainers to spin up a PostgreSQL container with a fresh schema for each test. The penalty is that each test is slower than a unit test, but I find that we need far less tests to achieve the same outcome. It has many of the benefits of mocked unit tests: fast, isolated and no special environment setup needed, but without the high effort and uncertainty that comes with mocking at this level.

Give it a try on your next project and see how it compares.