sql: sequelize support is blocked on stored procedures

[jordanlewis]

Sequelize's postgres dialect currently uses stored procedures for all database updates: see upsertQuery.

This is a pretty serious blocker to supporting Sequelize out of the box. As an alternative, we could consider creating a simple custom Sequelize dialect that simply overrides upsertQuery from the postgres dialect to do something without stored procedures.

[cuongdo]

Because Sequelize ships with few dialects and it's going to take us a while to support stored procedures, the custom dialect seems like the fastest path to supporting Sequelize.

So, is it possible to create a minimally hacky solution for patching the postgres dialect at runtime, which can be delivered to developers in a reasonable manner?

[cuongdo]

FYI upsertQuery creates sequelize_upsert, which executes an upsert with the same return values that MySQL has for INSERT .. ON DUPLICATE KEY UPDATE. Relevant MySQL reference:

http://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html

Relevant quote:

With ON DUPLICATE KEY UPDATE, the affected-rows value per row is 1 if the row is inserted as a new row, 2 if an existing row is updated, and 0 if an existing row is set to its current values.

[bdarnell]

Another option would be to submit a PR upstream to sequelize that would make the postgres dialect use ON CONFLICT DO UPDATE instead of the stored procedure when the server version >= 9.5.0.