For example, in one of my projects I use TestNG to instantiate a MariaDB container, run the Flyway migrations on it and then populating the tables with the test data:

  mAPIDBContainer = new MariaDBContainer<>(DockerImageName.parse(MARIADB_CONTAINER_TAG))
              .withDatabaseName("apidb")
              .withPassword("password")
              .withNetwork(containerNetwork)
              .withNetworkAliases("apidb")
              .withExposedPorts(3306);
      mAPIDBContainer.start();
      Flyway flyway = Flyway.configure()
              .dataSource(mAPIDBContainer.getJdbcUrl(), mAPIDBContainer.getUsername(), mAPIDBContainer.getPassword())
              .encoding("UTF-8")
              .locations("classpath:apidb/migrations")
              .load();
      flyway.migrate();
      ScriptUtils.runInitScript(new JdbcDatabaseDelegate(mAPIDBContainer, ""), "sql/apidb/apidb-test-data.sql");

I agree. I'm in .NET land, I use EFCore as my ORM, and I use EFCore's migration features. My ORM models are in a separate project (same git repo) from both the web server project and test project, and any hand written SQL gets added to the migration scripts that EFCore generates.

I spin up a docker container for postgres, my test code clears any existing DB, creates a DB in the container, and then runs the EFCore migrations on the DB. I have simple tests that make sure my CTEs are working correctly and that things like expected unique indices are also setup.

This works both locally and in Github Actions.

I just wouldn't call any of this a "unit" test. I put all this squarely in my integration test suite. I figure if IO is happening as a consequential part of the test (i.e. not setup/teardown), it's an integration test. I wonder how much that distinction is tripping people up? A lot of people think of unit tests as small, independent, and quick, so by only thinking about unit testing, they automatically rule out tests that have app code call out to a DB.

Based on the sibling comment, I'm going to have to take a look at Testcontainers. I'm not sure how much it'll simplify at this point, but who knows!