Implement ON CONFLICT builder

[dzxt]

Is your feature request related to a problem? Please describe.
This feature provides the ability to create a query using the ON CONFLICT construct for INSERT, as it is not currently possible to use metasql in places - where this construct is needed.
The postgres syntax is as follows:

ON CONFLICT target action;

The action can be one of the following:

• DO NOTHING - means do nothing if the row already exists in the table.
• DO UPDATE SET column_1 = value_1, . WHERE condition - update some fields in the table.

Describe the solution you'd like
for example, there is a record

const record = {
  id:100,
  subId:32,
  title:'Title text',
  price:12.2,
  description:'Details about product',
  delivery:'pickup'
}

1.

// I would like to have the freedom to choose the style of code, for example:
domain.db.insert('Products', record).onConflict(['id','subId']).doNothing()
//or
domain.db.insert('Products', record).onConflict(['id','subId'])

equals:

INSERT INTO...
... ON CONFLICT (id,subId)
    DO NOTHING

2.

domain.db.insert('Products', record).onConflict(['id','subId']).doUpdate()
//or
domain.db.insert('Products', record).onConflict(['id','subId']).update()

equals:

INSERT INTO...
... ON CONFLICT (id,subId)
    DO UPDATE
    SET
      title= EXCLUDED.title,
      price= EXCLUDED.price,
      description= EXCLUDED.description,
      delivery= EXCLUDED.delivery;

That is, we are updating non-conflicting fields by default.

3.

domain.db.insert("Products", record).onConflict(["id","subId"]).doUpdate(["!price"])
//or
domain.db.insert("Products", record).onConflict("id","subId").update("!price")
//or
domain.db.insert("Products", record).onConflict("id","subId").doUpdate().exclude("price")

equals:

INSERT INTO...
... ON CONFLICT (id,subId)
    DO UPDATE
    SET
      title= EXCLUDED.title,
      description= EXCLUDED.description,
      delivery= EXCLUDED.delivery;

That is, we are updating non-conflicting fields by default, minus the specified fields with an exclamation mark (price)

4

domain.db.insert('Products', record).onConflict(['id','subId']).doUpdate('price',{delivery:'cashOnDelivery'})
/**
Inside, doUpdate you can check -
if the value type is an array or string - these are the field names
otherwise if it is an object, then it is a "Where" condition
**/
//or
domain.db.insert('Products', record).onConflict(['id','subId']).update(['price'],{delivery:'cashOnDelivery'})

equals:

INSERT INTO...
... ON CONFLICT (id,subId)
    DO UPDATE
    SET
      price= EXCLUDED.price
      WHERE delivery='cashOnDelivery';

updates only the specified field (price) and only according to the "where" condition

Describe alternatives you've considered
I'm using this code right now

const fields = ['id','subId','title','price','description','delivery']
function setExcluded(fields){
 return fields.map( (field, index, fields_) => `${field} = EXCLUDED.${field} ${(fields_.length - 1) == index ? ';' : ','}`).join('')
}
var updateList = fields.filter(field => field !== 'id' && field !== 'subId');
const query = `INSERT INTO...
    ON CONFLICT (id,subId)
    DO UPDATE 
    SET 
    ${setExcluded(updateList)}
    `

Additional context
also implement the builder when there is a conflict on CONSTRAINT:

INSERT INTO customers (user, email)
VALUES('user','user@mail.com') 
ON CONFLICT ON CONSTRAINT usersname_name_key 
DO NOTHING;

[tshemsedinov]

Thanks for proposal but metasql have no goal to replace SQL, metasql concept is to use JavaScript syntax for a few simple and short cases but all complex cases should be implemented using SQL syntax. You can send raw queries with: db.query(sql, values).