Pg-promise: UPDATE and INSERT convenience methods

[RyanMcDonald]

Hey there,

I am switching from mysql to postgres, and am having to rewrite some of my inserts and updates. I was wondering if there were some convenience methods or something that I'm missing that would help simplify building these queries. For example, the mysql library allowed this kind of syntax:

var insertUserQuery = 'INSERT INTO user SET ?';

and then:

mysql.query(insertUserQuery, userObject, callback);

but doing it with pg-promise can become cumbersome if you're working with an object that has a lot of properties, especially if you're doing an update with an object and aren't updating all of the fields.

Thanks.

[vitaly-t]

This library formats and executes queries in their natural form. It does not provide any ORM facility beyond variable query formatting, since it is not an ORM.

It does however support Custom Types Formatting which allows complete freedom in formatting queries. You can implement your own type that represents SET ? and then inject it via your custom, pre-formatted type, which can achieve the same what you described.

[vitaly-t]

@RyanMcDonald Did you get this working?

[RyanMcDonald]

@vitaly-t I'm not sure I understand how to apply the custom formatting in this scenario. Could you provide an example?

[vitaly-t]

@RyanMcDonald The Custom Type Formatting is very flexible, and allows formatting override in many ways. I won't be going into every case possible. You should try it yourself first, and see how it works.

I will give a quick example of what can be done, similar to what you described. But that's not the only approach, there can be many ways, depending on what you want precisely.

var pgp = require('pg-promise');

// sets all the object's properties as they are;
function SetValues(obj) {
    this.obj = obj;
    this._rawDBType = true; // raw-text output override;
    this.formatDBType = function () {
        var props = Object.keys(this.obj);
        var s = props.map(function (m) {
            return m + '=${' + m + '}'; // creating the formatting parameters;
        });
        return pgp.as.format(s.join(", "), this.obj); // returning the formatted string;
    }
}

Test

var testObj = {
    first: 123,
    last: "text"
};

var result = pgp.as.format("UPDATE users SET $1", new SetValues(testObj));

console.log(result);

Output

UPDATE users SET first=123, last='text'

[vitaly-t]

@RyanMcDonald Did this answer your question? If so, then please close the issue.

[vitaly-t]

Closing due to inactivity.

[RyanMcDonald]

Sorry, I just got around to this. Thanks so much, @vitaly-t, this was exactly what I needed!

[vitaly-t]

@RyanMcDonald You are welcome! :)

[vitaly-t]

There is a further progress with this in 4.0.6 update ;)

[vitaly-t]

F.Y.I. Important breaking change in v6.5.0.