How can I wrap a value in a postgres function prior to insert, update?

[green3g]

I'd like to take an incoming value, and wrap it in a postgres function when a value gets inserted in the database. Use case for this is to perform the heavy lifting of converting values in the database itself rather than my app.

Example:

{
    geom: "{...}"
}

What I'd like sql to look like:

INSERT INTO table_name (geom, column2, column3, ...)
VALUES (ST_SetSRID(ST_GeomFromGeoJSON("{...value to convert...}"), 4326), value2, value3, ...);

Is there a good way to do this in objection? Or would this be something perhaps I'd need on to add as a database trigger?

[dekelev]

You might be able to use the $modify query operator with arg (e.g. geom) and using KnexJS to change the query to your needs before it runs.

[green3g]

Okay - this is a good start.

First, I found some very helpful github resources on this:

• Override select statement at model level Vincit/objection.js#1491
• using postgres functions from knex knex/knex#185

I was able to get select operations working using the modify parameter:

    static get modifiers() {
        return {
            toGeoJSON(builder: QueryBuilder<ProjectsFiles>) {
                if (!builder.has('count')) {
                    if (!builder.hasSelects()) {
                        builder.select('projects_files.*');
                    }
                    builder.select(raw('st_asgeojson(?)::json', [ref('geom')]).as('geom'));
                }

                return builder;
            },

My next step though is to try and get updates/patches working. For this, I've tried setting up a modifier like:

            fromGeoJSON(builder: QueryBuilder<ProjectsFiles>) {
                builder.update({
                    geom: raw('ST_SetSRID(ST_GeomFromGeoJSON(?), 4326) ?', [
                        ref('geom'),
                        ref('geom'),
                    ]).as('geom'),
                });
            }

My request to Patch looks like this:

And when the query executes - I see the knex log this data:

error: [GeneralError: update "projects_files" set "createdAt" = $1, "updatedAt" = $2, "createdBy" = $3, "modifiedBy" = $4, "project_id" = $5, "program_id" = $6, "file_id" = $7, "file_type" = $8, "user_tags" = $9, "ai_tags" = $10, "geom" = $11, "file_name" = $12, "thumbnail_id" = $13 where "id" = $14 - parse error - invalid geometry]

// Data:

          data: {
            id: 375,
            createdAt: '2021-03-10T17:08:22.556Z',
            updatedAt: '2021-03-10T17:08:27.057Z',
            createdBy: null,
            modifiedBy: null,
            project_id: 24,
            program_id: 1,
            file_id: '0a7864e5fa07cc118f3df1e38f31e5181350c30010e8115c536c7a8a664c9f13.jpeg',
            file_type: 'image',
            user_tags: null,
            ai_tags: null,
            geom: [Object],

So I'm wondering, why is geom [Object] - it doesn't appear that the modifier is even executing in this instance.

[green3g]

Is it possible to use modifier on patch/update ?

[dekelev]

It might be possible, but currently eager & modifiers are only used in get/find requests. A PR is welcome if it's working for you to run $modify in update/patch as well.