Hey @meshelleva4815
It would definitely be easier for you if both databases were using the identical version/dialect. Firstly, for writing/debugging you can test both sides of the setup without needing to translate between them. Secondly, you know any operations you do one one can be done on the other – different databases have different limitations, particularly around more complex field types, etc.
That said, using sqlite on your local database does have the advantage that it’s very portable and doesn’t require a separate database installation. It’s a trade off, which will depend on what sort of data you’re storing. For simple stuff, and sticking to standard SQL you should be OK with sqlite + mysql/postgresql.
As for how to log these changes… you have a lot of options. The simplest would be to simply log the SQL operations to file or and to submit these to the server (executing them) on reconnection. Another would be to define a custom “language” to describe the record mutations used to regenerate the SQL operation on the target system, but probably a bit redundant.
This only works if you have a single client though. Do you? If not, you’re going to need to think of a way to handle and resolve conflicts – e.g. if two clients edit the same record, and sync later, what do you do? As soon as one operation fails, any subsequent operation cannot be assumed to work (use transactions and you can roll back, but then?)