Testing db with Postgres or Mysql directly

[Robin-Van-de-Merghel]

We currently need to test more than the CI itself, see this comment. We will need stricter tests in the CI.

Because a code can work in an engine but not in the production one, we need to use Postgres/Mysql directly in the CI instead of Sqlite, or using "CI bots" that test DiracX production itself.

It joins #450 and #378

[fstagni]

We have the integration tests which start the diracx demo and uses MySQL as SQL DB. Isn't that enough?

[Robin-Van-de-Merghel]

I wanted to say on the pytest side, sorry for the misunderstanding.

My problem from 450 is that I got this router:

@router.post("/debug")
async def debug(
    pilot_reference: str,
    pilot_db: PilotAgentsDB
):
    try:
        pilot_id = await pilot_db.add_pilot_references(
            pilot_ref=[pilot_reference], 
            vo="diracAdmin",
            grid_type="grid-type"
        )
        return pilot_id
    except Exception as e:
        return e

And this test:

async def test_create_pilot_and_verify_secret(pilot_agents_db: PilotAgentsDB):

    async with pilot_agents_db as pilot_agents_db:
        pilot_reference = "pilot-reference-test"
        pilot_ids = await pilot_agents_db.add_pilot_references(
            vo="lhcb",
            pilot_ref=[pilot_reference],
            grid_type="grid-type",
        )

        assert len(pilot_ids) == 1   

The test passes, I successfully added data on my db because in sqlite, RETURNING is supported.

$ pytest diracx-db/tests/pilot_agents/test_pilot_agents_db.py::test_create_pilot_and_verify_secret
[...]
============================================== 1 passed in 0.23s ==============================================

Whereas on the router, I have an error on the SQL request (with globally the same request) because the RETURNING keyword is not supported:

INSERT INTO `PilotAgents` (`InitialJobID`, `CurrentJobID`, `PilotJobReference`, `PilotStamp`, `DestinationSite`, `Queue`, `GridSite`, `VO`, `GridType`, `BenchMark`, `SubmissionTime`, `LastUpdateTime`, `Status`, `StatusReason`, `AccountingSent`) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) RETURNING `PilotAgents`.`PilotID`

I can't detect this error with pytest itself, but I need to launch it on github workflows or to create a test endpoint to identify the problem.

[fstagni]

The plan is to add also Postgres to the integration tests, which are there to exactly find out about these niche issues. The current pytest use sqlite for snappy tests that would find out about logic issues, while integration tests are there to simulate an environment closer to the one in production. So, I think we'll stay as we are.