I'm thinking of making a realtime chat app that would allow me to store user messages into a MySQL database. So far these are the two ideas I have.

1) Create and API which the message is sent to and then saved in the database. After the message is saved into the database a push notification is sent to the person who is to receive the message and then a script is run in the background to download the message from the database.

2) Use websockets or XMPP to allow messages to be sent and received by the sever and just save the message to the MySQL database when it reaches the server.

Which one of these methods would be best to implement and scale for a realtime chat application. Method one seems pretty good but I'm not sure if constant SQL transactions are good for a server, the payload might be too much.


So after do some more digging around I see that I can use either XMPP with web sockets or with HTTP to create my real time chat app. My question is what would be a simple but effective way to save these messages to a MySQL database.

  • Creating an API to interact with a database, Websockets, and XMPP are orthogonal concepts you do not have to choose between them. XMPP is a protocol (or API) for chat clients and instant messengers to communicate. You decide whether to use XMPP or a simpler API/protocol? Websockets is a technique to push raw data to clients (in webbrowsers). Using websockets needs an interpretation of the raw data on top of it: perhaps XMPP (don't know whether that is possible) or your own protocol. On the server-database side you have to decide whether to use an API. Can you reframe your question? Jan 13, 2018 at 11:19
  • @KaspervandenBerg: How do you want it reframed? Jan 13, 2018 at 17:34
  • #1 cannot be real-time no matter where you look at it. If by API you mean REST API and by REST we mean web, let me say that REST API webs are far to be real-time. At least not over HTTP/1.1. On the other hand, push notification providers (mainly Google and Apple) don't guarantee the delivering of the messages. Push notifications have no acknowledgement of receipt (in case you n need it) what lead you to a continuously pushing it via HTTP request. Only this ridiculous "pong" might cause your server to collapse during a peak of concurrency if the whole server-side is not right dimensioned.
    – Laiv
    Jan 13, 2018 at 18:58
  • If we were speaking about mobile apps, the things go for worse, because both Android and iOS performs different push notification handling, being android the weirdest (depending on whether the app is running in fore/background, not running at all, etc). This can lead you to have different UX depending on the user's smartphone...Definitively #1 is the worse of both. (To my experience implementing something similar)
    – Laiv
    Jan 13, 2018 at 19:06
  • @KaspervandenBerg I've edited the question Jan 13, 2018 at 22:53

2 Answers 2


Which one of these methods would be best to implement and scale for a realtime chat application. Method one seems pretty good but I'm not sure if constant SQL transactions are good for a server, the payload might be too much.

It seems that one of your main requirements is the technologies of your system to be scalable.

In such case you might like to move to big data platforms such as Apache Kafka, Apache flume, or RabbitMQ.

In that case I would recommend to have a look to Apache Kafka to create an scalable pipeline in which:

  1. A process receives SQL queries from the mobile devices. (Kafka Producer)
  2. A group of servers execute the SQL transaction and reply to the mobile device. (Kafka consumers)

Kafka API will allow you to easily setup this pipeline. While things can get more sophisticated, I hope I gave you an idea of how could you scale your service.

Get started with Apache Kafka: https://kafka.apache.org/documentation/#gettingStarted

  • 1
    "A process receives SQL queries from the mobile devices using REST". This seems like a terrible idea. What happens when you send a drop table sql query?
    – Carra
    Jan 17, 2018 at 12:31
  • 1
    Transmiting SQL queries through REST does not imply not having some sanity checks in the server side code. When you send a drop table query a Kafka filter in the Kafka Stream for instance could remove undesirable queries. Jan 17, 2018 at 15:07
  • However, using REST interface or not was not the point of my answer, I will edit it to remove it. Jan 17, 2018 at 15:07

You should have a look at Firebase. It solves a lot of your problems. Chat application comes out of the box with it. You can customise it within an hour or two.

https://firebase.google.com/use-cases/ ---> 'Add chat to your app' or https://github.com/firebase/quickstart-js/blob/master/messaging/README.md

There are SDKs for IOS and Android as well.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.