Use a different tool (or no tool at all) for object-relational mapping (ORM) and schema migrations

[adambkaplan]

Results currently uses gorm as an object-relational mapping library. This provides a convenient API for defining the database schema, querying objects, and updating records. It is also vendor-agnostic, which in the early days allowed the backend database to be pluggable.

Results now has a hard dependency on Postgres as its database. We have also found a few issues of late where gorm has proved to be a hinderance:

• Provide Database Migration Scripts for #334 #429 (auto migration does not work for Upgrade Gorm models and make database auto-migration optional #334)
• Make the database auto-migration optional on the API server's startup #311 (make auto migration optional)
• The API server causes almost 100% CPU consumption on Amazon RDS  #336 (basic query did not take advantage of index)
• Paging with Pagetoken & Filter doesn't work properly #379 (comment) (new feature works best with gin indexes)

I personally have concerns with the way gorm's auto migrator works. The auto migrator does not have a dry run/generator capability, which means we can't preview the schema initialization or update scripts. There is also no version tracking mechanism - it is unclear if a failed migration would lead to an inconsistent database state that would require backups to restore. Many projects have adopted migrate to manage schema changes, including ArgoCD.

I'd like to discuss if we should continue to utilize gorm as an ORM library, and/or adopt other tools to manage schema changes.

[adambkaplan]

cc @alan-ghelardi @manuelwallrapp @dibyom

[dibyom]

The auto migrator does not have a dry run/generator capability, which means we can't preview the schema initialization or update scripts.

yikes 😱

Are we going to continue using gorm and use a different migration tool? Or take out gorm altogether? I don't have a strong preference on this other than making sure the Results API itself does not end up being dependent on features that are postgres specific

[alan-ghelardi]

Generally speaking, I don't believe that the Results API is strongly dependent on an ORM tool. Currently, there are only two entities and the SQL operations are extremely simple. Most of the time, we aren't taking advantage of the facilities provided by the ORM. #404 introduces a more advanced usage of the database for querying and paginating, but it isn't GORM dependent too.

On the other hand, I admit that GORM provides conveniences and dropping it entirely would impact several areas of the codebase and would require a reasonable effort in terms of refactoring. In addition, I am not certain that there are so many disadvantages of keeping GORM and some of the mentioned issues aren't directly related to GORM (the bug in the pagination has to do with the current approach for querying data - combining database and in-memory resolution - and the issue related to 100% of cpu usage was caused by a programming mistake - it might happen without the ORM).

In terms of migration, I believe that we should strive to avoid breaking changes as much as possible in the schema. We could make breaking changes by creating new versions of the API and the models. If we decide that we'd like to evolve always the same version of the API, providing migration scripts sounds more sensible to me.

In regards to the auto migration, in my opinion it only makes sense for simplifying development and setting up the database automatically in the first installation. Once the Results API is deployed to production, people should consider disabling it by default.