Squarespace and MySQL

I have a MySQL driven web application that acts as an in-house management system and is partly accessible to external users.

I also have a Squarespace web site.

Does anyone have any ideas as to how I could query the MySQL database either directly from Squarespace site (don't think this is possible) or indirectly somehow so that I can display the query results in my Squarespace site?  I have looked at possibly using Firebase and have also briefly looked at Caspio but nothing seems obvious at the moment.

Thank you

G

Hi @Graemetg. Squarespace doesn't support server side code, so you're pretty much stuck using JavaScript. What that means is you have to either:

• Setup your endpoint/database to be accessible cross-domain via  XMLHttpRequest (account for CORS) or
• Setup your endpoint so that its data is accessible via JSONP or
• Do one of the above with a proxy server instead, and have the proxy get your data or
• Do one of the above with a separate service like Firebase, Caspio or Google Sheets/AppsScript whereby you essentially host a copy of your database for access via JS (via XMLHttpRequest or JSONP depending on the service's limitations/features)

This limitation isn't necessarily unique to Squarespace, but to JavaScript (though since Squarespace limits us to JavaScript, it is indirectly a limitation of Squarespace).

I've setup various similar solutions using AppsScript (JDBC may be of interest in this case) where no/low additional monthly fees were important. Of course, the upfront cost could be significant if paying a developer to accomplish it, depending on the complexity of the data/operation and the experience of the dev.

-Brandon

Hi @brandonthank you that is very helpful I will investigate and comment back here.

G

2 hours ago, brandon said:

• Setup your endpoint so that its data is accessible via JSONP 

I used this approach on a recent project. We only needed to read the data - there was no writing back. Also, the data updates infrequently so the client set up a job on one of their servers to generate the data table on an hourly basis. 

The process from my side was:

• On first page load access the JSONP and parse it, merging with some config info on the Squarespace site, to create a lookup table that was saved to the user's browser as Session Storage
• On each subsequent page load check check if the Session Storage doesn't exist *and* whether a new version of the feed will have been created. If so, parse the JSONP again and create Session Storage
• NB. The Session Storage variable contained full html coding for the data table so that it can be pulled into the page and pasted as formatted html and/or filtered before insertion.
• If Session Storage exists read the content into variable and insert into page as formatted html
• Build a filtering interface that allows the end user to narrow down to the desired content. 

The decision to store in Session Storage was made because we know the data only updates once an hour, so we need to grab it at the start of the session and also when a new feed becomes available. This vastly reduces the workload and improves response times after initial load and parse of the data.