Change our query approach to use DB-level json functions #821
Comments
slifty
added a commit
that referenced
this issue
Mar 11, 2024
We're going to be defining a series of functions in PostgreSQL which will support extracting structured JSON from the database. These functions will be treated as code, rather than migrations (i.e. we won't have a separate copy of the function that changes over time, but will instead update a single function definition file as needs change). We still want to ensure the functions are loaded into the database before the application starts, however, which means we need to improve the application "start" script to force operation resolution BEFORE the application starts. There is a little bit of implicit magic being introduced with this commit, which is that any `.sql` file in the `database/initialization` directory will be loaded into the database at application start. I believe this is akin to the work that nodejs does to add `node_modules` to the class path / it is a simple hook that is part of database initialization and so the implicitness is more acceptable than, say, implicit routing. Issue #821 Change our query approach to use DB-level json functions
slifty
added a commit
that referenced
this issue
Mar 11, 2024
We're going to be defining a series of functions in PostgreSQL which will support extracting structured JSON from the database. These functions will be treated as code, rather than migrations (i.e. we won't have a separate copy of the function that changes over time, but will instead update a single function definition file as needs change). We still want to ensure the functions are loaded into the database before the application starts, however, which means we need to improve the application "start" script to force operation resolution BEFORE the application starts. There is a little bit of implicit magic being introduced with this commit, which is that any `.sql` file in the `database/initialization` directory will be loaded into the database at application start. I believe this is akin to the work that nodejs does to add `node_modules` to the class path / it is a simple hook that is part of database initialization and so the implicitness is more acceptable than, say, implicit routing. Issue #821 Change our query approach to use DB-level json functions
slifty
added a commit
that referenced
this issue
Mar 11, 2024
We're moving to a new approach for our SQL -- instead of returning row sets, we're returning documents using custom functions. This will allow us to ensure that the representation of any given entity is consistent across our queries / API, but it will also allow us to return more complex objects. This commit represents the first step towards this goal by returning json when selecting BaseFields. The only impact on output appears to be that date columns are no longer converted into `Date` objects. I don't believe this has any impact on the API result set. We will need to also update the insert / update statements to use the JSON functions, but that can happen in a future commit. Issue #821 Change our query approach to use DB-level json functions
slifty
added a commit
that referenced
this issue
Mar 11, 2024
This incorporates the new json approach when creating base fields, and also consolidates base field creation logic into a single operation (which is our new best practice anyway). Issue #821 Change our query approach to use DB-level json functions
slifty
added a commit
that referenced
this issue
Mar 11, 2024
Issue #821 Change our query approach to use DB-level json functions
slifty
added a commit
that referenced
this issue
Mar 11, 2024
This incorporates the new json approach when creating base fields, and also consolidates base field creation logic into a single operation (which is our new best practice anyway). Issue #821 Change our query approach to use DB-level json functions
slifty
added a commit
that referenced
this issue
Mar 11, 2024
Issue #821 Change our query approach to use DB-level json functions
slifty
added a commit
that referenced
this issue
Mar 14, 2024
This is the big one... We're changing the way we populate deep proposals, leveraging psql directly instead of putting that burden on the application. We may find that there are optimizations to be made in future, but this removes a large amount of complexity from the application. As part of this we are also improving the deep object so that it includes the base field information directly, meaning a client won't need to load a list of base fields in order to understand the context of an application form field. Issue #821 Change our query approach to use DB-level json functions
slifty
added a commit
that referenced
this issue
Mar 14, 2024
This is the big one... We're changing the way we populate deep proposals, leveraging psql directly instead of putting that burden on the application. We may find that there are optimizations to be made in future, but this removes a large amount of complexity from the application. As part of this we are also improving the deep object so that it includes the base field information directly, meaning a client won't need to load a list of base fields in order to understand the context of an application form field. Issue #821 Change our query approach to use DB-level json functions
slifty
added a commit
that referenced
this issue
Mar 14, 2024
This is the big one... We're changing the way we populate deep proposals, leveraging psql directly instead of putting that burden on the application. We may find that there are optimizations to be made in future, but this removes a large amount of complexity from the application. As part of this we are also improving the deep object so that it includes the base field information directly, meaning a client won't need to load a list of base fields in order to understand the context of an application form field. Issue #821 Change our query approach to use DB-level json functions
slifty
added a commit
that referenced
this issue
Mar 15, 2024
This is the big one... We're changing the way we populate deep proposals, leveraging psql directly instead of putting that burden on the application. We may find that there are optimizations to be made in future, but this removes a large amount of complexity from the application. As part of this we are also improving the deep object so that it includes the base field information directly, meaning a client won't need to load a list of base fields in order to understand the context of an application form field. Issue #821 Change our query approach to use DB-level json functions
slifty
added a commit
that referenced
this issue
Apr 30, 2024
Issue #821 Change our query approach to use DB-level json functions
slifty
added a commit
that referenced
this issue
Apr 30, 2024
Issue #821 Change our query approach to use DB-level json functions
slifty
added a commit
that referenced
this issue
May 1, 2024
Issue #821 Change our query approach to use DB-level json functions
slifty
added a commit
that referenced
this issue
May 2, 2024
Issue #821 Change our query approach to use DB-level json functions
slifty
added a commit
that referenced
this issue
May 2, 2024
Issue #821 Change our query approach to use DB-level json functions
slifty
added a commit
that referenced
this issue
May 2, 2024
Issue #821 Change our query approach to use DB-level json functions
slifty
added a commit
that referenced
this issue
May 2, 2024
Issue #821 Change our query approach to use DB-level json functions
slifty
added a commit
that referenced
this issue
May 2, 2024
Issue #821 Change our query approach to use DB-level json functions
slifty
added a commit
that referenced
this issue
May 2, 2024
Issue #821 Change our query approach to use DB-level json functions
slifty
added a commit
that referenced
this issue
May 10, 2024
Issue #821 Change our query approach to use DB-level json functions
slifty
added a commit
that referenced
this issue
May 10, 2024
Issue #821 Change our query approach to use DB-level json functions
slifty
added a commit
that referenced
this issue
May 15, 2024
Issue #821 Change our query approach to use DB-level json functions
slifty
added a commit
that referenced
this issue
May 15, 2024
Issue #821 Change our query approach to use DB-level json functions
slifty
added a commit
that referenced
this issue
May 15, 2024
Issue #821 Change our query approach to use DB-level json functions
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
A long time ago we had a big conversation about the best way to load deep objects from postgres (e.g. when loading a proposal, also load the versions / field values). We punted on the implementation of our selected path due to priorities in the moment, but as I start working on the new
Organization/Proposalsrelationship (#799) I would like to finally shave that yak.I'm making a separate issue for this, as it will impact essentially every endpoint / query and I think it's functionally a prerequisite to proper implementation of #799
The idea will be to create a series of
foo_to_jsonfunctions in postgres which will return json for a given entity directly (and therefore be able to also query / populate the nested object structures as well since it won't be returned as a traditional row-based result set).The text was updated successfully, but these errors were encountered: