How to Securely Connect SQL Databases to Digital Signage

Picture this: You're the owner of a bustling coffee shop chain, "Caffeine Dream", with multiple locations across the city. Your business is thriving, so you're always looking for innovative ways to enhance the customer experience and streamline operations.

One day, it hits you. You could use digital signage to display a live counter of the number of coffees sold each day. But how would you go about achieving that?

You already know that your point-of-sale system feeds into a company database, logging every coffee sold. So the number’s there, with dates attached. It’s just a matter of fetching it from your database and displaying it on your digital signage.

Regardless of whether you sell lattes or health insurance, connecting to an SQL database poses security risks. You have to ensure you don’t create any vulnerabilities in your point-of-sale systems that could expose customer data.

If you’re using ScreenCloud to power your digital signage, there’s a very straightforward way to achieve this using some JavaScript, Zapier, and ScreenCloud Playgrounds. 

The best part? You can use this same repeatable process to query and display any kind of data from a database onto your digital signage. You could:

• Display real-time inventory levels, sales data, or special promotions at retail outlets.
• Show real-time KPIs, project statuses, or employee recognition data in offices.
• Show real-time schedules, delays, or passenger counts in public transit stations.
• Showcase interest rates, stock market data, or customer testimonials for financial institutions.

Follow along for a list of detailed steps, along with any relevant code snippets and tooling suggestions.

Keeping your SQL databases secure

Your SQL databases probably contain sensitive information. They don’t only log how many cups of coffee you’ve sold, they also log customer names, contact information, and more. Remember, while you might only be pulling non-sensitive data for display on your signage, the connection itself could be exploited if not properly secured. 

ScreenCloud, by default, doesn’t include any built-in ways to access your SQL databases and other internal data directly. If you write JavaScript code in ScreenCloud Playgrounds to fetch data from your database, ScreenCloud will block it from running. This helps protect your database from SQL injection or DDoS attacks.

You can, however, use an automation platform like Zapier or a server-side script coded in Python or Node.js to query data from your SQL database and push it to ScreenCloud’s API. From there, displaying the data in Playgrounds is as easy as writing a few lines of basic JavaScript.

Here’s how.

How to automatically send new SQL data to your ScreenCloud signage

No matter the industry you’re in, your company likely has a lot of data across sales, marketing, finance, and human resources living in company databases. These databases may be stored on-site, or hosted in the cloud using a service provider like Microsoft Azure’s SQL Database, Google Cloud SQL, Amazon RDS, or even a MySQL or SQLite database in a shared hosting service like Digital Ocean. No matter where your database is, if you can query it, you can connect it to your digital signage.

In this tutorial, we'll assume that you’re working on a sales team at a retail company—a coffee shop—with an existing database for tracking daily orders and revenue. We’ll query that database to grab today’s total coffee sales.

We'll then create a new Playgrounds instance in ScreenCloud and use automation tool Zapier to automatically pull data from the MySQL database each time it’s updated, then push the new sales data to ScreenCloud’s Webhooks URL.

Finally, we’ll write a basic JavaScript query that allows us to display the data pushed into the ScreenCloud API in the signage using Playgrounds.

Start out with your database. If you have an existing database and know what to query, jump to the next section. Our MySQL database will be named ‘retail_db’ and contain a table called ‘sales_data’. For this exercise, we want to display the total revenue for the current day in our digital signage. This data is stored in the ‘revenue’ field in our ‘sales_data’ table. Every time that ‘revenue’ field is updated, we want to push it to ScreenCloud.

Now, time to build.

Step 1: Create a new Playgrounds instance in ScreenCloud

Let’s create a new instance of ScreenCloud Playgrounds. This will enable you to build custom content for your digital signage using JavaScript, HTML, and CSS. 

Add the Playgrounds app to your account from the dashboard and click New Instance, followed by Start with a blank template. You can then add CSS to customize the Playgrounds—check out our Playgrounds Guide for more ideas there, including how to use ChatGPT to code new signage.

Then, you need to grab your Webhooks URL from the Application Data pane (look for the button in the top right corner of Playgrounds). Copy the "API URL" and save it for later.

Step 2: Pull the data from your MySQL database using Zapier

Now, we need a way to get data out of our database, and onto our signage. The easiest way is with Zapier, Make, Microsoft Power Automate, or other similar tools to grab data from MySQL and push it to Playgrounds’ Webhook URL.

We’ll start by creating a new automated workflow in Zapier—known as a Zap—to connect the SQL database to ScreenCloud. For your Trigger, select MySQL and choose the New or Updated Row event. Connect your MySQL account by entering the necessary credentials (in our case that means selecting 'retail_db' and the 'sales_data' table). 

Step 3: Push the queried data to ScreenCloud’s Webhook URL

Next, add a Webhooks by Zapier as the Action, choose Custom Request, set the method to PUT, and paste API URL you copied from Playgrounds earlier. This is the easiest way to send data to your ScreenCloud signage if there isn’t a built-in integration. 

In Zapier’s Data field, there are two columns. Enter “data” in the left column, click on the dropdown menu, and select the "Revenue" field from your MySQL database in the right column. This tells Zapier to push the data from your SQL database Revenue field to the “data” field in the Playgrounds application data store.

Finally, test your Zap to make sure it works correctly. First wait for an all-clear from Zapier, then reopen Playgrounds and look for updated data in the Application Data pane.

Step 4: Write Javascript to display the SQL data in Playgrounds

Now, let’s write the JavaScript query to display the data you just pushed into the ScreenCloud API onto your signage, in the left-hand column of Playgrounds. The most important thing is getting the getData syntax correct:

getData().then(data => { 
  // Get a reference to the div element 
  const todaysRevenue = document.getElementById('todaysRevenue'); 
 
  // Update the div element with the message 
  todaysRevenue.innerText = data; 
});

In your HTML column, make sure you have an element with the ID 'todaysRevenue' where the data can be displayed, such as <div id="todaysRevenue"></div>. Now, whenever ScreenCloud loads your Playgrounds dashboard, the JavaScript code will fetch the latest revenue data from your Application Data, and replace the “todaysRevenue” div element in the HTML with that data before displaying it.

That’s it! Save your changes and preview your digital signage. Now, whenever someone makes an order, a database query, a Zap, and a Webhook payload later, your ScreenCloud digital signage will show an updated number of how many cups of coffee your team has served for that day.

How to push SQL data directly from your database server to your ScreenCloud signage

If you want to achieve the same results as the above but without Zapier, you would need to set up a server-side script that connects to your MySQL database, queries the data, and then sends it to the ScreenCloud Webhooks URL. This script would need to be hosted on a server and run at regular intervals, perhaps using a cron job or similar scheduling mechanism. You can also run the script using a local machine if the database is hosted locally or using an in-house server.

This approach requires more setup and maintenance than using Zapier, but it gives you more control over the process and eliminates the need for a third-party service.

Step 1: Set up a server-side script with Node.js

You’ll need to write a bit of code to grab the data from your database, and push it to ScreenCloud. You could use a server-side language like Node.js, Python, or PHP for this. The script would need to do the following:

• Connect to your MySQL database and query the 'revenue' field from the 'sales_data' table.
• Send a PUT request to the ScreenCloud Webhook URL with the queried data. 

Here’s an example of what the script might look like:

const express = require('express');
const mysql = require('mysql');
const axios = require('axios');

const app = express();

const db = mysql.createConnection({
  host: 'your_host',
  user: 'your_user',
  password: 'your_password',
  database: 'retail_db'
});

app.get('/updateData', (req, res) => {
  db.query('SELECT revenue FROM sales_data', (err, results) => {
    if (err) throw err;
    
    const revenue = results[0].revenue;

    axios.put('https://api.screencloud.com/v1.0/playgrounds/data/{instance_id}', {
      data: {
        revenue: revenue
      }
    })
    .then(response => {
      res.send('Data updated successfully');
    })
    .catch(error => {
      res.send('Failed to update data');
    });
  });
});

app.listen(3001, () => {
  console.log('Server is running on port 3001');
});

Step 2: Host the script on a server

Now, you need to get the code running somewhere where it can run at regular intervals. This could be a server in your own infrastructure or on a desktop computer you keep running in your office. An easier option could be to use a cloud hosting provider like AWS Lambda, Google Cloud’s App Engine, or Heroku. 

Step 3: Set up a scheduler to run the script

Finally, you'll need to set up a scheduler to run your script at regular intervals. This could be using a task scheduler if you're running the script in Windows, or, if you’re running the script on your server, you could add the script to your cron jobs.

Of course, most cloud hosting services have built-in scheduling tools, such as AWS Lambda’s Scheduled Events. Tweak the settings to get your script running as often as you want, check for new data, and push anything new to ScreenCloud.

Then, jump back to the final step above, and customize your ScreenCloud Playgrounds design to showcase how many cups of coffee you’ve sold today—or any other data you want to display.

Put your data on the big screen

Now it’s time to go further. ScreenCloud’s Application Data can store as many variables as you want—which means you could query your SQL database for more than one value, and send them all in a single Webhooks payload. And with several variables, you can do interesting stuff like building a dashboard with Chart.js to showcase, say, a graph of your sales over time.

Or, if you’d rather dig deeper into code, you could use ScreenCloud’s GraphQL API to remotely change content on your signage and turn off screens from anywhere.

Image Credit: Cover Photo by Austin Distel via Unsplash.