knex with postgres using wrong SQL

[pbrink231]

I have setup knex config to work with postgres:

const knexConfig = {
  client: 'pg',
  debug: true,
  connection: DATABASE_URL,
  ssl: true
};

const shopifyConfig = {
  host: SHOPIFY_APP_HOST,
  apiKey: SHOPIFY_APP_KEY,
  secret: SHOPIFY_APP_SECRET,
  shopStore: new SQLStrategy(knexConfig),
  scope: ['write_orders, write_products'],
  //shopStore: new MemoryStrategy(),
  afterAuth(request, response) {
    const { session: { accessToken, shop } } = request;

    registerWebhook(shop, accessToken, {
      topic: 'orders/create',
      address: `${SHOPIFY_APP_HOST}/order-create`,
      format: 'json'
    });

    //return response.redirect('/');
    return response.redirect('/shopifyclient');
  },
};

But the query when a new store is connecting is failing because INSERT OR IGNORE is not compatible with postgres:

{ method: 'raw',
  sql: 'INSERT OR IGNORE INTO shops (shopify_domain, access_token) VALUES (\'jldesigndevstore.myshopify.com\', \'4637d9f05328b543fa5d82c68094a7a2\')',
  bindings: [],
  options: {},
  __knexQueryUid: '099e900d-af29-4920-b416-1adeb26b7cc4' }
Unhandled rejection error: syntax error at or near "OR"
    at Connection.parseE (C:\source\repos\prodo-server\node_modules\pg\lib\connection.js:553:11)
    at Connection.parseMessage (C:\source\repos\prodo-server\node_modules\pg\lib\connection.js:378:19)
    at Socket.<anonymous> (C:\source\repos\prodo-server\node_modules\pg\lib\connection.js:119:22)
    at emitOne (events.js:116:13)
    at Socket.emit (events.js:211:7)
    at addChunk (_stream_readable.js:263:12)
    at readableAddChunk (_stream_readable.js:250:11)
    at Socket.Readable.push (_stream_readable.js:208:10)
    at TCP.onread (net.js:607:20)

[inomn]

You can create rule for your table, like this:

CREATE RULE shops_on_duplicate_ignore AS ON INSERT TO shops WHERE EXISTS ( SELECT 1 FROM shops WHERE shopify_domain=NEW.shopify_domain ) DO INSTEAD NOTHING;

and use simple INSERT

[pbrink231]

I ended up creating a custom strategy which took me down a whole road. The documentation needs to be updated since the strategy now returns a formula instead of a value. Also can show this example since you cant find one anywhere. Its strange because my node module is different than the code here?
My version: "@shopify/shopify-express": "^1.0.0-alpha.7" This is the strategy that ended up working:

class SequelizeStrategy {
  async getShop({ shop }) {
    console.log('check domain', shop)
    let foundShop = await db.Shop.find({
      where: {
        shopify_domain: shop
      }
    })
    .catch(err => {
      console.log('get shop error', err)
    })

    return done(null, {accessToken: foundShop.access_token});
  }
  async storeShop({ shop, accessToken }, done) {
    let storedShop = await db.Shop.find({
      where: {
        shopify_domain: shop
      }
    })
    .then(foundShop => {
      if (foundShop) {
        foundShop.access_token = accessToken
        foundShop.save()
        return foundShop;
      } else {
        return db.Shop.create({ shopify_domain: shop, access_token: accessToken })
      }
    })
    .catch(err => {
      console.log('store shop error happened', err);
      return done(err);
    })
    console.log('stored shop', {accessToken})
    return done(null, accessToken);
  }
}

[janfabian]

@pbrink231 yes, npm package 1.0.0-alpha.7 is currently 15 commits behind the master branch.

[pbrink231]

ok understand. Thank you.