Socketio chat app with node and mysql
Tags: node, socketio, mysql
Published: 2020-08-10 12:05:00

---
title: Socketio chat app with node and mysql
published: true
description: 
tags: #nodejs #socketio #mysql
//cover_image: https://direct_url_to_image.jpg
---

Hello guys, am building a chat app with node and socket.io . I have persisted the data to mysql database successfully. Now, my next task is to retrieve the data and display on the web page so when it refreshes, it doesn't go away. This is what  I have done so far.
Below is my code.

```
server.js
const db = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "",
  database: "live_chat",
});
app.use("/chat_messages", (req, res) => {
  db.query("SELECT * FROM chat_messages", (error, messages) => {
    res.end();
  });
});
app.use(express.static(path.join(__dirname, "public")));
const botName = "Admin✅";
io.on("connection", socket => {
  socket.on("joinRoom", ({ username, room }) => {
    const user = userJoin(socket.id, username, room);
    db.query("INSERT INTO users (username) VALUES (' " + username + " ')");
    socket.join(user.room);
    socket.emit(
      "message",
      formatMessage(
        botName,
        `${username} you're welcome to laronshalley customer support`
      )
    );
    // broadcast a message when a user connects
    socket.broadcast
      .to(user.room)
      .emit(
        "message",
        formatMessage(
          botName,
          ` ${user.username} has connected to the live chat`
        )
      );
    // get room users
    // io.to(user.room).emit("rootUsers", {
    //   room: user.room,
    //   users: getRoomUsers(room),
    // });
  });
  // listen for events from the client
  socket.on("chatMessage", msg => {
    const user = getCurrentUser(socket.id);
    io.to(user.room).emit("message", formatMessage(user.username, msg));
    db.query("INSERT INTO chat_messages (messages) VALUES (' " + msg + " ')");
  });
  // disconnects when a user closes the tab
  socket.on("disconnect", () => {
    const user = userLeave(socket.id);
    if (user) {
      io.to(user.room).emit(
        "message",
        formatMessage(botName, `oops ${user.username} is disconnected`)
      );
    }
    // // get room users
    // io.to(user.room).emit("rootUsers", {
    //   room: user.room,
    //   users: getRoomUsers(room),
    // });
  });
});
const PORT = 5000 || process.env.PORT;
http.listen(PORT, () => console.log("server is listening on port 5000"));
```

```
frontend file
chat.js


const socket = io();
const serverUrl = "http:localhost:5000/chat_messages";
// get username and room from the url
const { username, room } = Qs.parse(location.search, {
  ignoreQueryPrefix: true,
});
// select room
socket.emit("joinRoom", { username, room });
// socket.on("roomUsers", ({ room, users }) => {
//   outputRoomName(room);
//   outputRoomUsers(users);
// });
fetch(serverUrl)
  .then(response => response.json())
  .then(data => console.log(JSON.parse(data)))
  .catch(error => console.log("Oops, something went wrong", error));
socket.on("message", msg => {
  outputMessage(msg);
  // scroll
  chatMessages.scrollTop = chatMessages.scrollHeight;
});
chatForm.addEventListener("submit", event => {
  event.preventDefault();
  const msg = event.target.elements.msg.value;
  // send data to the server
  socket.emit("chatMessage", msg);
  // clear form field
  event.target.elements.msg.value = "";
  event.target.elements.msg.focus();
});
const outputMessage = message => {
  const div = document.createElement("div");
  div.classList.add("message");
  div.innerHTML = ` <p class="meta">${message.username} <span>${message.time}</span></p>
  <p class="text">
   ${message.text}
  </p>`;
  document.querySelector(".chat__messages").appendChild(div);
};
```