Synchronization between two heterogeneous databases

Hello everybody! I decided to write about what I'm was doing. As you know, I work as a web developer and now I continue to develop in this direction. Recently, for educational purposes, I opened my resume and received a test task from one of the employers. In this post I will tell you about this task and how I completed it.

It was necessary to develop a system that synchronizes data between two heterogeneous databases. The synchronization is performed on demand and can occur from both applications. The ability to add, update and delete items should also be in done. By condition, it was necessary to use the sqlite and php database. It is desirable that the application be console.

Honestly, I did not even know at first what heterogeneous databases were! : D I took the task as a challenge and this allowed me to find the necessary information. I understand the basic principles of object-oriented programming and follow them to combat complexity. I think this is the main thing that allowed me to solve the task. Plus, earlier I already had experience working with the Docker, otherwise I simply would not physically did not have time to complete the work in a week.

The databases schema looks like this:

I found two theoretical materials describing the algorithm of how this can be implemented, which made me worried because it seemed too complicated. But from them I understood two important things. Firstly, I will need a unique key for storage, because the primary keys may differ. Secondly, I need the ability to create queues with a reliable system for sending data to another database.

I also found some articles that were useful to me. But in fact, I had half a hundred open tabs in the browser. Thanks to the Vivaldi browser for grouping the tabs. In addition to everything else, I had to try to complete tasks at my main job and devote time to my family! In general, it was hard, but interesting! ;)

As a result of familiarizing myself with all the materials that I managed to find and brainstorming, I realized that I needed to use rabbitmq and in general I began to better understand how to solve the problem! Thanks to all the people who share information and write articles! Thanks to them, we have the opportunity to develop and progress!

In this post I will not describe in detail the whole process. If you are interested or something is not clear, then feel free to ask questions in the comments.

It turned out two console applications using the same source code, but with different databases, different configurations and some differences in business logic. Applications are launched using "Docker" in two different containers and exchange data with each other using "rabbitmq".

You can see and try it here: https://github.com/spiritabsolute/database-synchronization-system

The technology stack, libraries and recommendations that I used:

• Php (https://www.php.net/)
• Psr (https://www.php-fig.org/psr/)
• Composer (https://getcomposer.org/)
• Symfony components for cli (https://symfony.com/doc/current/components/console.html)
• Phinx component from CakePhp for migrations (https://book.cakephp.org/phinx/0/en/intro.html)
• Docker (https://www.docker.com/)
• Rabbitmq (php-amqplib) (https://github.com/php-amqplib/php-amqplib)

P.S.
Not a lot about Hive blog. Thanks to all the people who made this hard fork possible. I am very interested to follow the further development. Let's keep getting better and create better content! Do what you love and help other people with useful information!