Automatically synchronize data to the server

[greenking19]

Is your feature request related to a problem? Please describe.
My application requires a feature that can also be used offline, and Drift happens to meet my needs.
I currently have the following questions👇

1. How to automatically upload data to the server during creation, modification, and deletion.
2. Do not upload automatically when the device is offline, upload automatically when online
3. How to determine whether a certain piece of data has been uploaded

Describe the solution you'd like

1. Take corresponding actions when there are changes to the table data, such as inserting, updating, and deleting.

2. To determine whether the data has been uploaded, my method is to add the "is_sync" root "is_delete" field in the table. When the data is uploaded to the server after creation and modification, the success rate is "is_sync=1". When deleting a piece of data, the success rate is "is_sync=0, is_delete=1". Then, I perform an API operation to delete it. Once successful, the data is directly deleted

The current challenge is how to watch the data changes in the table and take corresponding actions.
Can this function be achieved？
Or are there any other solutions?

I don't have a solution at the moment, I hope to get your help! 🙋
Thank you! 🥳

[simolus3]

See also: #136, perhaps #635 or this talk for ideas.

The current challenge is how to watch the data changes in the table and take corresponding actions.

I think triggers are well-suited for this. You don't need them for inserts (just set the is_sync field to false by default), but you can use them for updates and deletions:

CREATE TRIGGER tbl_update AFTER UPDATE ON tbl WHEN old.is_sync BEGIN
  UPDATE tbl SET is_sync = false WHERE tbl.id = old.id;
END;

CREATE TRIGGER tbl_delete INSTEAD OF DELETE ON tbl WHEN NOT old.is_delete BEGIN
  UPDATE tbl SET is_sync = false, is_delete = true WHERE tbl.id = old.id;
END;

You can create these triggers in drift files, or manually by overriding allSchemaEntities in the database to include Trigger elements with the desired name and SQL.

Or are there any other solutions?

You could keep a separate table storing all pending changes and populate that with triggers. The benefit is that this avoids having to add a WHERE NOT is_deleted clause to queries in the app itself.

[greenking19]

I think triggers are well-suited for this. You don't need them for inserts (just set the is_sync field to false by default), but you can use them for updates and deletions:

CREATE TRIGGER tbl_update AFTER UPDATE ON tbl WHEN old.is_sync BEGIN
  UPDATE tbl SET is_sync = false WHERE tbl.id = old.id;
END;

CREATE TRIGGER tbl_delete INSTEAD OF DELETE ON tbl WHEN NOT old.is_delete BEGIN
  UPDATE tbl SET is_sync = false, is_delete = true WHERE tbl.id = old.id;
END;

You can create these triggers in drift files, or manually by overriding allSchemaEntities in the database to include Trigger > elements with the desired name and SQL.

Thank you, I have some ideas now.👨🏻‍💻
I think the trigger meets my basic needs.
Are "tbl_update" and "tal_delete" methods in the dart file?
Can Dart's methods be called in the drift file? If possible, that would be very convenient!

[greenking19]

Can I call the methods in Dart when the table data is modified?
I couldn't find it in the document.
For example, in the Todo table, when there is a data update, call Dart's method to upload it to the database

[greenking19]

May I ask how to use TRIGGER? I couldn't find any relevant examples.
Maybe I didn't look carefully enough.
Can you write a simple example?
Thank you! 🙋

[greenking19]

I can use it now, Drift files are really convenient! 🥳🥳
At the beginning, there was an error message when running, but later I uninstalled the app and debugged it again, and it was successful.
If during the development process, adding fields to the table later requires uninstallation and reinstallation?
Lastly, I have a question. I want to use where with watch, but it doesn't seem to support it?
Because I want to listen for data with is_sync=false to be uploaded to the server
Similar to this 👇

  void addWatcher() {
    Stream<List<TodoItem>> stream = db
        .select(
          db.todoItems,
        )
        .where((tbl) => tbl.isSync.equals(false))
        .watch();
    subscriptionTodo = stream.listen((todos) {
         uploadToServer(todos);
    });
  }

I am using a timer to loop now, but I feel that this method is a bit lacking.
There should be a better solution. Do you have any suggestions?
My current problem is 👇🏻

1. Listen for data with is_sync=false and upload it to the server in a timely manner

[greenking19]

I've done it now, and it's okay to write it this way.👇

  void addWatcher() {
    Stream<List<TodoItem>> stream = (db
        .select(
          db.todoItems,
        )
        ..where((tbl) => tbl.isSync.equals(false)))
        .watch();
    subscriptionTodo = stream.listen((todos) {
         uploadToServer(todos);
    });
  }

Do you have any good advice for syncing data to the server? 🤔

[simolus3]

If during the development process, adding fields to the table later requires uninstallation and reinstallation?

You either need to write a migration or re-install the app, yes.

Do you have any good advice for syncing data to the server? 🤔

First, I think you may want to avoid running multiple synchronization tasks at the same time:

  void addWatcher() {
    Stream<List<TodoItem>> stream = (db
        .select(
          db.todoItems,
        )
        ..where((tbl) => tbl.isSync.equals(false)))
        .watch();
    subscriptionTodo = stream.listen(null);
    subscriptionTodo.onData((todos) async {
         subscriptionTodo.pause();
         try {
          await uploadToServer(todos);
        } finally {
           subscriptionTodo.resume();
         }
    });
  }

Apart from that I do't reaally know how to give specific advice since this very much depends on the architecture you have in your server and which protocols you use.

[greenking19]

@simolus3 Thank you!
I have a clear idea now