Jump to content

Squarespace and MySQL

Recommended Posts

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

 

 

 

Link to comment
  • Replies 3
  • Views 5.9k
  • Created
  • Last Reply

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

 

If a response helped you out, send a 'Like' 👍 (bottom-right) and/or 'Upvote' vote.jpg.c260784ece77aec852b0e3049c77a607.jpg (top-left)

Link to comment
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. 

I'm Colin Irwin aka silvabokis.  I've been a Squarespace designer & developer since 2013. 
I remember when it was all wild prairies round these here parts. 🐃🤠
Advice I give on here is free, though I may sometimes post an affiliate link or promote something I've written.
That reminds me.. ..you might want to check out my
Squarespace template finder or have a look at my other Squarespace tips
Speaking of tips, 💲I've got a tip jar that you're welcome to throw a few quid into if you think I've helped you. 
If you're looking for a Squarespace developer 
Book a chat or Drop me a line - first meeting is always free  

 

Link to comment

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Squarespace Webinars

Free online sessions where you’ll learn the basics and refine your Squarespace skills.

Hire a Designer

Stand out online with the help of an experienced designer or developer.