Can't Get Custom Session Storage Working With MySQL

EightIf
New Member
1 0 0

Hi,

I was able to get custom session storage working with redis based on a modified version of example provided in the Github repo. Here is my code for that:

 

import { Session } from '@shopify/shopify-api/dist/auth/session';
const redis = require("redis");
const client = redis.createClient();

client.on("error", function(error) {
    console.error(error);
});

const { promisify } = require("util");
const getAsync = promisify(client.get).bind(client);
const setAsync = promisify(client.set).bind(client);
const delAsync = promisify(client.del).bind(client);

async function storeCallback(session) {
    try {
        return await setAsync(session.id, JSON.stringify(session))
    } catch (err) {
        throw new Error(err)
    }
}

async function loadCallback(id) {
    try {
        let reply = await getAsync(id);
        if (reply) {
            return JSON.parse(reply);
        } else {
            return undefined
        }
    } catch (err) {
        throw new Error(err)
    }
}

async function deleteCallback(id) {
    try {
        return await delAsync(id)
    } catch (err) {
        throw new Error(err)
    }
}

// Export the class
module.exports = {
    storeCallback,
    loadCallback,
    deleteCallback
}

 

 

I then attempted to switch this over to MySQL rather than redis, basing it off the working redis example above and some other online resources. Here is my MySQL version (I am aware the deleteCallback does nothing at the moment and also apologise for the poorly formatted SQL statement):

 

import { Session } from '@shopify/shopify-api/dist/auth/session';
const mysql = require('mysql');

const connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: '',
    database: 'mydatabase'
});

async function storeCallback(session) {
    try {
        connection.query(`INSERT INTO shops (session_id, shop_url, state, is_online, access_token) VALUES ('${session.id}', '${session.shop}', '${session.state}', '${session.isOnline}', '${session.accessToken}') ON DUPLICATE KEY UPDATE session_id = '${session.id}', shop_url = '${session.shop}', state = '${session.state}', is_online = '${session.isOnline}', access_token = '${session.accessToken}'`, 
            function(error, results, fields) {
                if (error) throw error;
            } 
        );

        return true;
    } catch (err) {
        throw new Error(err)
    }
}

async function loadCallback(id) {
    try {
        await connection.query(`SELECT * FROM shops WHERE session_id = '${id}' LIMIT 1`, 
            function (err, result, fields) {
                if (err) throw err;
                console.log(result);

                if (result.length > 0) {
                    let session = new Session(id);

                    session.shop = result[0].shop_url;
                    session.state = result[0].state;
                    session.isOnline = result[0].is_online == 'true' ? true : false;
                    session.accessToken = result[0].access_token;

                    return session;
                } else {
                    return undefined;
                }
            }
        );
    } catch (err) {
        throw new Error(err)
    }
}

async function deleteCallback(id) {
    try {
        return false;
    } catch (err) {
        throw new Error(err)
    }
}

// Export the class
module.exports = {
    storeCallback,
    loadCallback,
    deleteCallback
}

 

However, as soon as I made the switch, it stopped working as normal. During this entire process of testing both, I was logging the sessions to see what was being received from Shopify. During the redis version, I would always receive two sessions with storeCallback, one without the access token, then one with the access token:

Session {
    id: 'offline_store.myshopify.com',
    shop: 'store.myshopify.com',
    state: '1234',
    isOnline: false
}
{
    id: 'offline_store.myshopify.com',
    shop: 'store.myshopify.com',
    state: '1234',
    isOnline: false,
    accessToken: 'shpat_asdfasdfasdf',
}

But when I switched to the MySQL version, I would only receive the first session without the access token and then I would receive this error 'Cannot complete OAuth process. No session found for the specified shop url: store.myshopify.com'.

I'm not sure what I'm doing wrong, but I feel as though I must be close. 

I really appreciate everyone who has taken the time to read this and look forward to hearing people's solutions

0 Likes
Michal_Morek
Shopify Partner
821 72 137

Hi @EightIf 

Hope you're having a great day!

Is it?

 

 

const mysql = require('mysql');

 

 

Or

 

 

const mysql = require('mysql2');

 

 

 

Try with:

 

 

const mysql = require('mysql2');

 

 

 

Why didn't you try to use an ORM?

You should try Sequelize. Sequelize is a promise-based Node.js ORM for PostgresMySQLMariaDBSQLite, and Microsoft SQL Server. It features solid transaction support, relations, eager and lazy loading, read replication, and more.

It will help you:

  • write correct and more optimized SQL queries
  • make the code easier to update, maintain, and reuse, and manipulate data as objects
  • make you shield your application from SQL injection attacks since the framework will filter the data for you!
  • provide the concept of Database Abstraction which makes switching databases easier and creates a consistent code base for your application.

If you found this comment useful, hit the 'Like' and 'Accepted solution' buttons.

Founder of Shopify agency - Accomplishify.com
If you would like to hire us as Shopify experts, please reach out via:
  • Email michal.morek@accomplishify.com
  • WhatsApp +48 507 613 425
0 Likes