Transferring data from 1 web app into another offline

Question

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.

Answer 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.

Answer 2

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.