0

I have a web app that will be used locally on 2 different site. The program is a Vue web app made with node.js and express, using MySQL database. Due to limitation (no internet in site 1), there is no central server, so the web app will function similarly to a desktop app. However, there is a need to sync the data from 1 site to the other (1 way only) using a flash drive. Site 1 would send the data to site 2 to be edited further, but the data would be locked before being send (ensuring site 1 can't change the data). Site 2 would use that data for other purposes. Basically, it's just a 1-way flow.

In what way should I do this? I've tried to search solution but there is no general answer, rather than specific format (export to csv/excel). Is that the only way and should I use that? Should (and can) I just dump the DB through the frontend? I'm pretty new at this so any understanding would be appreciated.

EDIT: added more clarification on the flow.

2
  • 1
    That's a very worrying requirement, and I would be looking at overcoming your "limitations" rather than trying to work with them! You lose referential integrity and lots of other protection by doing this. At the very least I would suggest you ensure that site 1 can view but not edit site 2's data, and vice versa. Nov 15, 2021 at 7:05
  • @thinkOfaNumber I would have to clarify better on it. Site 1 would send the data to site 2 to be edited further, but the data would have to be locked before being send (ensuring site 1 can't change the data). Site 2 would use that data for other purposes. Basically, it's just a 1-way flow. Any idea on how to do this?
    – Aditya D.
    Nov 15, 2021 at 8:02

2 Answers 2

1

As Doc Brown said, there's no generic solution for that!

I would still advise you revisit your limitations, as you need to consider the total cost of ownership, IMHO. A cloud database may end up cheaper in the long run than building and maintaining this system, especially considering fixing the bugs over time due to its complexity.

If your limitations are legitimate (such as an air-gapped computer for security purposes) then be prepared to deal with lots of locking and merging...! A process for a user exporting from system 1 may be:

  1. user selects "parent" rows to be exported, and starts the process
  2. the system will mark those rows as "exported" or "locked". From then on they can't be edited
  3. the system exports the data (this is tricky, if you have a relational system, so you're going to have to look into CSV vs backup scripts vs ETL jobs); including generating checksums
  4. the user loads these files to a flash drive and "imports" them into system 2
  5. system 2 checks checksums and loads data back into a similar relational model
  6. system 2 stores the "source system ID" for each row, which you may decide will be the PK on system 2, or you may decide to use a separate PK.
  7. potentially if system 2 can create data of it's own, then use a nullable source system ID to identify this

You would have to ensure that:

  • since system 2 can edit the data from system 1, you can't import the same source ID twice, lest you overwrite your own changes
  • system 1 can't edit data that is exported
  • the user can't export rows that have already been marked as exported

Now this is getting into the realms of data warehousing, so I would recommend you read up on that (I don't have a link, there's heaps on the web around that). Also read up on ETL (Extract, Transform and Load).

The only free ETL tool I found for MySQL is Benetl. I have no idea how good it is, but if you read the documentation you can decide if that's easier to work into your process than doing it manually.

2
  • Oh my, this is precisely the answer I'm looking for, are you experienced at this? You hit all the problem I thought, and also the one I didn't even thought. I just met with the user and they did mention wanting to "lock" data, but using month (basically locks all transaction done on last month). I have to look at that, but if you don't mind, do you know the best way to do this? I can only think of adding an attribute (e.g isLocked) to the records. Also, any reading/link about point 3? Since you seems to understand it well (but if you do mind, just tell me it's fine! You've helped a lot)
    – Aditya D.
    Nov 16, 2021 at 15:03
  • I haven't done this exactly but lots of stuff like it. A "locked" column is probably simple, but you have to make sure your application respects it. That means lots of testing. With a "locked" column, you could set it every month, or set it on export, whether in-app or from your ETL process. I don't have anything specific for point 3 sorry, except to recommend ETL tools to do it for you. They can usually be set on schedule and can generate csv files for you if required, but usually need a server or at least task scheduler to run... Nov 18, 2021 at 5:07
1

I've tried to search solution but there is no general answer

Yep, that's correct, there is no general answer.

There is data in your original database which has to be exported to another one. However, the granularity in which this must happen depends on your very special, individual requirements, which will undoubtly be different for other systems.

So first you have to plan and analyse by yourself which part of the data you have to sync, and in which portions. When the result of that analysis tells you you always need all data from the first database, or maybe the full data which lives in one schema, and it does not have to be integrated with data already in the second db, then a standard database dump can be used.

Otherwise, you will probably need to create an export/import process which might choosing a subset of all tables and the rows in those tables, an intermediate file format and an import process which merges the data into an existing db. Since your original data is structured by tables, using some tabular structured file format should be obvious. If you prefer CSV, Excel, or some XML or JSON format is up to you, your taste and requirements. CSV is pretty compact, XML is more standard with more meta data, but pretty verbose. Excel formats are mostly proprietary, sometimes handy, but come with their own quirks and risks. JSON's verbosity is somewhere between XML and CSV, being less complex than XML, but with some limitations.

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.