PieHost

Rabikant Singh

Posted on November 15th

Track MySQL Database Changes With WebSockets Using PieSocket

"Let's look at how to track MySQL database changes with WebSockets using PieSocket"

We know that WebSockets can help us build real-time applications. If you are not familiar with WebSockets yet, I suggest you read our guide What is WebSocket, it covers a brief introduction of WebSockets and their use cases.

Today we are going to talk about one of many such use-cases and help you track your MySQL data in real-time. We will use PieSocket WebSocket Channels in this example because PieSocket provides the needed libraries to make this possible in few simple lines of code.

There are two parts of this solution to track the MySQL database in real-time and push the changes immediately to the end-user as they happen.

We are going to use the following example to demonstrate how to track MySQL changes in real-time with WebSockets.

Let’s say there is a table named users in your MySQL database and you have a page that lists all the users in a HTML table. Our goal is to update the user’s list on this page immediately as soon as a new user entry is made in the users table.

Examples in this tutorial use PHP, however the same can be implemented using any programming language. You will not have difficulties understanding the example code even if you are from Python, Node.js, or Go background.

We have added examples for the following back-end frameworks in the sections below:

Setup Frontend

Let’s start by creating a file that lists all existing users in an HTML table, we will call this file index.php for simplicity.

File: index.php

<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <meta http-equiv="X-UA-Compatible" content="IE=edge">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <title>MySQL Tracker</title>
  <!-- UIkit CSS -->
  <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/uikit@3.7.4/dist/css/uikit.min.css" />
</head>
<body>
  <div class="uk-container uk-padding">
    <h2>Users</h2>
    <p>Tracking users table with WebSockets</p>
    <table  class="uk-table">
      <thead>  
        <tr>
          <th>User ID</th>
          <th>User Name</th>
        </tr>
      </thead>  
    <tbody>  
        <?php
        $connection = mysqli_connect(
            'localhost',
            'root',
            PASSWORD,
            DB_NAME
        );
        $users = mysqli_query($connection, 'SELECT * FROM users');
        while ($user = mysqli_fetch_assoc($users)) {
            echo '<tr>';
            echo '<td>' . $user['id'] . '</td>';
            echo '<td>' . $user['name'] . '</td>';
            echo '</tr>';
        }
        ?>
    </tbody>  
    </table>
    </div>
</body>
</html>

The file above displays current users in the users table but does not update automatically when a new user is added to MySQL on the backend. That is where WebSockets come in. To save ourselves the hassles of creating and hosting a WebSocket server, we will use the managed WebSocket Channels from PieSocket.

So start by creating a PieSocket API key from here. PieSocket has a free plan which should be enough for small projects.

Next, add the PieSocket-JS WebSocket library in your HTML file with the following code:

<script src="https://unpkg.com/piesocket-js@1"></script>

Now, we are ready to connect to the WebSocket channel and start receiving instantaneous updates from the server. Add the following code to your index.php file to make a websocket connection.

<script>
  var piesocket = new PieSocket({
    clusterId: 'CLUSTER_ID',
    apiKey: 'API_KEY'
  });
  // Connect to a WebSocket channel
  var channel = piesocket.subscribe("my-channel");
  channel.on("open", ()=>{
    console.log("PieSocket Channel Connected!");
  });
  //Handle updates from the server
  channel.on('message', function(msg){
    var data = JSON.parse(msg.data);
    if(data.event == "new_user"){
      alert(JSON.stringify(data.data));
    }
  });
</script>

This is all for the frontend side. This code shows an alert when a new database entry is available, we leave it up to you to append the incoming data to the existing HTML table. Now it is time to push events from the backend when a new entry is available.

Setup Backend

Let’s create a file called admin.php which adds an entry to the user’s table, every time it is visited.

<?php
$connection = mysqli_connect(
    'localhost',
    'root',
    PASSWORD,
    DB_NAME
);
mysqli_query($connection, "INSERT INTO users .....");
?>

The code snippet given above adds an entry in the users table every time its execute either via CLI or from a webserver.

Our frontend table still stays the same, the changes are visible only after a page refresh.
To fix that, we need to make a REST API call to PieSocket server every time we modify the users table.

Use the following PHP function to do that.

<?php
function publishUser($event){
  $curl = curl_init();
  $post_fields = [
    "key" => "PIESOCKET_API_KEY",
    "secret" => "PIESOCKET_API_SECRET",
    "channelId" => "my-channel",
    "message" => $event
  ];
  curl_setopt_array($curl, array(
    CURLOPT_URL => "https://PIESOCKET_CLUTER_ID.piesocket.com/api/publish",
    CURLOPT_RETURNTRANSFER => true,
    CURLOPT_CUSTOMREQUEST => "POST",
    CURLOPT_POSTFIELDS => json_encode($post_fields),
    CURLOPT_HTTPHEADER => array(
      "Content-Type: application/json"
    ),
  ));
  $response = curl_exec($curl);
  print_r($response);
}
$connection = mysqli_connect(
    'localhost',
    'root',
    PASSWORD,
    DB_NAME
);
mysqli_query($connection, "INSERT INTO users .....");
$payload = json_encode([
  "event" => "new_user",
  "data" => [
  "id"=> 1,
  "name"=>"Test user"]
]);
publishUser($payload);
?>

This is it, call the function publishUser every time you modify your users table and the frontend will show an alert with the user payload information. You can automate this process with help of Observers if you are using frameworks like Laravel, Django, Ruby on rails, SpringBoot, etc.

Track MySQL changes in real-time with Laravel

The backend setup part as mentioned above becomes even easier while using a framework like Laravel. You can add publishUser call in a Model observer and Laravel will publish the event to the WebSocket channel automatically when a Model is created, updated, or deleted.

For example, in your User model under app/model/User.php add the following code block

public static function boot() {
    parent::boot();
    static::created(function($user) {
        publishUser(json_encode($user));
    });
}

Track MySQL changes in real-time with Django

Django supports signals which can be used to publish events on the frontend automatically when a new record is created in the database.

import requests
import json
url = "https://CLUSTER_ID.piesocket.com/api/publish"
payload = json.dumps({
    "key": "API_KEY",
    "secret": "API_SECRET",
    "channelId": 1,
    "message": { "text": "Hello world!" }
});
headers = {
  'Content-Type': 'application/json'
}
response = requests.request("POST", url, headers=headers, data = payload)
print(response.text.encode('utf8'))

Track MySQL changes in real-time with Node.js

It is even easier to publish database change events using Node.

First, install the piesocket-node library with the following command:

npm i piesocket-nodejs

Then, use the code snippet below to send an event.

const PieSocket = require("piesocket-nodejs");
var piesocket = new PieSocket({
    clusterId: 'YOUR_CLUSTER_ID',
    apiKey: 'YOUR_API_KEY',
    secret: 'YOUR_API_SECRET'
});
piesocket.publish(channelId, data);

Alternatively, you can make a CURL request from your node service, see the documentation: here.  

Track MySQL changes in real-time with Ruby On Rails

Use ActiveRecord::Observer with Ruby on rails to publish an event on the frontend automatically when a model is created or updated. Following is a Ruby code snippet you can use.

require "uri"
require "net/http"
require "json"
url = URI("https://CLUSTER_ID.piesocket.com/api/publish")
https = Net::HTTP.new(url.host, url.port);
https.use_ssl = true
request = Net::HTTP::Post.new(url)
request["Content-Type"] = "application/json"
request.body = {
    key: "API_KEY",
    secret: "API_SECRET",
    channelId: 1,
    message: { "text": "Hello world!" }
}.to_json
response = https.request(request)
puts response.read_body

Track MySQL changes in real-time with SpringBoot

Use the following class to publish events when a Model changes in your SpringBoot application. This tutorial explains in-depth how to implement Observer patterns in SpringBoot to automate this process.

public class Publish {
  public static void main(String[] args) {
    OkHttpClient client = new OkHttpClient().newBuilder().build();
    MediaType mediaType = MediaType.parse("application/json");
    RequestBody body = RequestBody.create(
      mediaType,
      "{n    "key": "API_KEY",n    "secret": "API_SECRET",n    "channelId": 1,n    "message": "Hello world!"n}"
    );
    Request request = new Request.Builder()
      .url("https://CLUSTER_ID.piesocket.com/api/publish")
      .method("POST", body)
      .addHeader("Content-Type", "application/json")
      .build();
    Response response = client.newCall(request).execute();
  }
}Visit PieSocket Documentation to learn about to use the service with various programming languages and frameworks. Hope this helps!

Comments

Leave a comment.

Share your thoughts or ask a question to be added in the loop.