Update all remaining legacy SQL queries to use the new syntax

[kalbfled]

User Story - Business Need

SQLAlchemy v1.4 supporters two syntaxes for making database queries, but SQLAlchemy v2 only supports the newer syntax. Before we can update to v2, we need to convert all instances of legacy syntax to the newer syntax.

This work began with #1538. This ticket should finish the job.

User Story(ies)

As a VA Notify developer
I want to update legacy database query syntax to the new version
So that we can upgrade to SQLAlchemy v2.

• discuss w/ QA

Additional Info and Resources

• SQLAlchemy v2.x changelog
• Migration Guide

app queries

Engineering Checklist

• Review relevant upgrade/migration docs before getting started

IMPORTANT NOTE: Legacy queries generally start with <model class>.query. Depending on context, updated syntax might use a new style query (i.e. stmt = select(Notification).where(...) followed by an execution statement) or an updated ORM call (i.e. replace Notification.query.count() == 1 with notify_db_session.session.get(Notification, notification_id) is not None, or write a statement to select the count).

• Refactor all query statements the match these regular expressions, which overlap significantly:
  • grep -rn -E "\.query(\.|\()"
  • grep -rn filter_by (not all matches are part of queries)
• Update the following ./app/ queries:
  • app/dao/complaint_dao.py
  • app/dao/services_dao.py
  • The remaining ./app/ queries need a TODO: comment and to be deleted, ensuring the methods return appropriate values.
    • TODO comments have tickets listed. These should be cleanup tickets. We do not use fido2, organisations, nor letters.
• Collect unit tests that need updating and/or investigation into a new ticket. No changes to tests are required for this task!
  • We sometimes see unit tests that query SMS and email but only add notifications for one or the other. Or there are multiple services queried but the test only does one. Add a TODO comment and add these to a ticket, so we can investigate them.

Acceptance Criteria

• No legacy queries remain
• All unit tests pass

QA Considerations

• QA regression passes
• Pay extra care to JSON responses
• Performance Testing to ensure there are no significant slowdowns to investigate

Potential Dependencies

Leave blank if n/a

Out of Scope

• Do not upgrade any dependency. Separate tickets exist for those upgrades.
• Do not update unit tests as part of this

[mjones-oddball]

Hey team! Please add your planning poker estimate with Zenhub @cris-oddball @EvanParish @k-macmillan @ldraney

[kalbfled]

I upgrade all remaining legacy queries in app/ as part of the first task (the grep statements). Ergo, the TODO task became a non-issue.

[cris-oddball]

Needing @kalbfled to check the AC boxes off in this ticket. Will look at performance on Monday.

[cris-oddball]

Checked DD for performance the past month, no significant changes positive or negative.