Stela uses a dynamic query generator for patch queries right now; I understand why, but dynamic SQL means we lose the benefit of static analysis and so I wanted to float the idea of an alternative approach.
In another project I work on we have been adding PATCH endpoints to an express REST api. The ultimate pattern is to have (1) a static query which (2) accepts parameters that indicate whether a given field is intended as being patched and (3) uses a custom function to "update if".
Here's an example file: https://github.com/PhilanthropyDataCommons/service/blob/main/src/database/queries/applicationFormFields/updateById.sql
And the update_if function is defined here: https://github.com/PhilanthropyDataCommons/service/blob/main/src/database/migrations/0077-create-function-update_if.sql
This would mean that patch endpoints get a patch / update query (PUT and PATCH can actually use the same query if we wanted, we'd just need the controller to validate PUT correctly).
Note that all of the above is agpl so I believe we are able to use it here too (🎉), though we would want to be sure to include the appropriate copyright notice.
Stela uses a dynamic query generator for patch queries right now; I understand why, but dynamic SQL means we lose the benefit of static analysis and so I wanted to float the idea of an alternative approach.
In another project I work on we have been adding PATCH endpoints to an express REST api. The ultimate pattern is to have (1) a static query which (2) accepts parameters that indicate whether a given field is intended as being patched and (3) uses a custom function to "update if".
Here's an example file: https://github.com/PhilanthropyDataCommons/service/blob/main/src/database/queries/applicationFormFields/updateById.sql
And the update_if function is defined here: https://github.com/PhilanthropyDataCommons/service/blob/main/src/database/migrations/0077-create-function-update_if.sql
This would mean that patch endpoints get a patch / update query (PUT and PATCH can actually use the same query if we wanted, we'd just need the controller to validate PUT correctly).
Note that all of the above is agpl so I believe we are able to use it here too (🎉), though we would want to be sure to include the appropriate copyright notice.