3 ms·

I've had some idle thoughts about creating a Postgres extension that would make some of these concepts easy to implement and work with. I modeled a sum type/dis

I've had some idle thoughts about creating a Postgres extension that would make some of these concepts easy to implement and work with. I modeled a sum type/discriminated union a while back, I was able to make it work with constraints, but it was fairly verbose.

pdimitar 2y ago
Which part was verbose? I feel such an extension will immediately find its audience.
- DylanSp 2y ago
  Writing out the constraints. The starting point was an admin_notes table to start with, where each note initially had the same fields; we later added different categories of notes, where two of the categories had fields that were only meaningful for that category. What I did was add nullable columns for all of the category-specific fields to the table, with constraints to make the category-specific columns only have values for rows of the appropriate category. The type looked roughly like this (using Rust syntax) [1]: struct AdminNote { id: String, text: String, category: CategorySpecificData, } enum CategorySpecificData { GeneralRequest, InitialRequestForm { appliesToBasicRequestDetails: bool, appliesToSubjectAreas: bool, appliesToAttendees: bool, }, SupportingDocuments, ConsultSession, AdviceLetter { appliesToMeetingSummary: bool, appliesToNextSteps: bool, }, } Setting up the constraints took about 30 lines of SQL just for those couple of boolean fields on some of the categories, you can see the details here [2]. As far as making this a Postgres extension - I'm not sure how useful it would be when the application language doesn't have a notion of sum types. Thinking about it, what might be more useful would be a language-specific library for data validation/constraints that sets up the database constraints as well. I'm not sure, though. [1] We weren't using Rust, this was in Go, but I figured this was the most succinct way to summarize it. Our GraphQL schema for this type was basically the equivalent of this. [2] https://github.com/CMSgov/easi-app/blob/main/migrations/V164__Add_Category_Specific_Columns_To_TRB_Admin_Notes.sql#L33-L65 https://github.com/CMSgov/easi-app/blob/main/migrations/V164...
  - pdimitar 2y ago
    Nice, thank you. This is informative. Though I really have to wonder if there couldn't be a way to generate the SQL constraints out of the data declaration as well. Golang's struct tags are likely not going far enough though.

pdimitar 2y ago

Which part was verbose? I feel such an extension will immediately find its audience.

DylanSp 2y ago
Writing out the constraints. The starting point was an admin_notes table to start with, where each note initially had the same fields; we later added different categories of notes, where two of the categories had fields that were only meaningful for that category. What I did was add nullable columns for all of the category-specific fields to the table, with constraints to make the category-specific columns only have values for rows of the appropriate category. The type looked roughly like this (using Rust syntax) [1]: struct AdminNote { id: String, text: String, category: CategorySpecificData, } enum CategorySpecificData { GeneralRequest, InitialRequestForm { appliesToBasicRequestDetails: bool, appliesToSubjectAreas: bool, appliesToAttendees: bool, }, SupportingDocuments, ConsultSession, AdviceLetter { appliesToMeetingSummary: bool, appliesToNextSteps: bool, }, } Setting up the constraints took about 30 lines of SQL just for those couple of boolean fields on some of the categories, you can see the details here [2]. As far as making this a Postgres extension - I'm not sure how useful it would be when the application language doesn't have a notion of sum types. Thinking about it, what might be more useful would be a language-specific library for data validation/constraints that sets up the database constraints as well. I'm not sure, though. [1] We weren't using Rust, this was in Go, but I figured this was the most succinct way to summarize it. Our GraphQL schema for this type was basically the equivalent of this. [2] https://github.com/CMSgov/easi-app/blob/main/migrations/V164__Add_Category_Specific_Columns_To_TRB_Admin_Notes.sql#L33-L65 https://github.com/CMSgov/easi-app/blob/main/migrations/V164...
- pdimitar 2y ago
  Nice, thank you. This is informative. Though I really have to wonder if there couldn't be a way to generate the SQL constraints out of the data declaration as well. Golang's struct tags are likely not going far enough though.