Deep (nested) Transforms?

[karlhorky]

Hi there @porsager and team ! 👋

Was just thinking, it would be nice to have nested transforms with the postgres.toCamel and such, so that using json_agg within a query would still return camelcase keys on the 2nd and subsequent nesting levels in objects.

Eg. the keys in availability_tags would also be camelcased:

const [candidate] = await sql<[Candidate]>`
  SELECT
    (
      SELECT
        json_agg(candidate_creation_request_availability_tags)
      FROM (
        SELECT
          availability_tags.id AS availability_tag_id,
          availability_tags.slug AS availability_tag_slug
        FROM
          availability_tags
      ) AS candidate_availability_tags
    ) AS availability_tags

Currently, when using transform.column.from = postgres.toCamel, they show up as this mixed data structure:

candidate = {
  availabilityTags: [
    {
      availability_tag_id: 1,
      availability_tag_slug: 'berlin',
    }
  ]
}

Alternatives Considered

Aliasing the fields as camelcase (eg. availability_tags.id AS "availabilityTagId") would also be an option, but I ultimately chose to not go this route, since it hinders ability to move query parts around and copy/paste without remembering this detail of how Postgres.js treats nested JSON data structures.

[porsager]

@karlhorky Wouldn't that be better to do in the actual query like this?

const [candidate] = await sql<[Candidate]>`
  SELECT
    (
      SELECT
        json_agg(candidate_creation_request_availability_tags)
      FROM (
        SELECT
          availability_tags.id AS "availabilityTagId",
          availability_tags.slug AS "availabilityTagSlug"
        FROM
          availability_tags
      ) AS candidate_availability_tags
    ) AS availability_tags

I can see the issue in then having to know you need to quote the identifiers. You can get your desired output by using transform.value like this:

const sql = postgres({
  transform: {
    value: {
      from: jsonToCamel
    }
  }
})

function jsonToCamel(x) {
  return x && typeof x === 'object' && x instanceof Date === false
    ? Array.isArray(x)
      ? x.map(jsonToCamel)
      : Object.entries(x).reduce((acc, [k, v]) => Object.assign(acc, { [postgres.toCamel(k)]: v }), {})
    : x
}

Now the check if it's JSON is a bit too arbitrary, so I think I'd like to add in Postgres.js that the column is included as a second parameter for the value transform function. If I do that the function would look like this instead:

function jsonToCamel(x, column) {
  return column.type === 114 || column.type === 3802
    ? Array.isArray(x)
      ? x.map(jsonToCamel)
      : Object.entries(x).reduce((acc, [k, v]) => Object.assign(acc, { [postgres.toCamel(k)]: v }), {})
    : x
}

Now im not sure if something like that should be included in this library, but since it's so small (needs more thought though) it could be ok. Then using it would perhaps be:

const sql = postgres({
  transform: {
    from: {
      column: postgres.toCamel
      value: postgres.jsonToCamel
    }
  }
})

What do you think? I've never used the transform options myself, but I'm glad to hear it's at least of use to others.

[karlhorky]

Wouldn't that be better to do in the actual query like this?

Yes, good point 👍 I considered this as well, but ultimately chose to not go this route, since it hinders ability to move query parts around and copy/paste without remembering this detail of how Postgres.js treats nested JSON data structures. I added this now to the original issue under "Alternatives Considered".

What do you think?

const sql = postgres({
  transform: {
    from: {
      column: postgres.toCamel
      value: postgres.jsonToCamel
    }
  }
})

Hm, could it become the default instead? (just thinking in terms of aiming for zero-configuration and simplicity in the library) Eg. that postgres.toCamel becomes a function similar to the jsonToCamel function? Without any new configuration options?

So that when the following code from the readme is run, it would "just work" for the majority of cases:

const sql = postgres('connectionURL', {
  transform: {
    column: {
      to: postgres.fromCamel,
      from: postgres.toCamel,
    },
  },
})

[porsager]

Perhaps the postgres.toCamel function could have the properties column and value added so that you could do:

const sql = postgres({
  transform: {
    from: postgres.toCamel
  }
})

No breaking changes :)

[karlhorky]

This looks nice, even simpler configuration!

[porsager]

Awesome :) Are you up for that PR ? :)

[karlhorky]

I can give it a try, I'm assuming I have to make changes in src/index.js, src/types.js and tests/index.js?

[porsager]

Neat - yeah, that sounds right..

[karlhorky]

Ok, I'll jump on Gitter, in case I have any other questions 👍

[karlhorky]

@porsager Thanks for the merge of #460!

Can we expect this to be published in a new release with version 3.3.0?

[karlhorky]

v3.3.0 has the nested transforms feature! 🙌