SQLite Session Extension

[rodydavis]

What would be the best way to explore using the official SQLite Session Extension with Drift?

https://sqlite.org/sessionintro.html

From my research the API is in C, and would need some compile flags passed to the custom sqlite build.

I did find an interesting example for iOS/MacOS:
https://github.com/gerdemb/SQLiteChangesetSync

[simolus3]

At the moment (I hope this gets a lot easier with the native assets SDK feature), you can't pass custom compiler flags to the buildscripts in sqlite3_flutter_libs. So the first step is to fork that package and add the relevant definitions for the platforms you need unfortunately.

Once you have the functions available, you can write bindings to them using dart:ffi. You'll need the sqlite3* pointer to the database connection drift uses. You get access to the underlying database in the setup method on NativeDatabase constructors and you can use .handle to get the native pointer to use when calling other sqlite3 functions (it might be easier to start with a local database instead of using background isolates to have everything available in the same isolate).

With the database pointer, it should be possible to create changesets and apply them. How to best integrate that functionality into drift is another question. You could call the methods to create sessions synchronously, then use drift methods to run statements and once that's done, use another C call to obtain a changeset.

[rodydavis]

Node.js is about to unflag the SQLite API and I saw it supports the session extension!

https://nodejs.org/api/sqlite.html

I will be looking at it on how it maps to the c code from JS.

Here is a nice example on the docs:

const sourceDb = new DatabaseSync(':memory:');
const targetDb = new DatabaseSync(':memory:');

sourceDb.exec('CREATE TABLE data(key INTEGER PRIMARY KEY, value TEXT)');
targetDb.exec('CREATE TABLE data(key INTEGER PRIMARY KEY, value TEXT)');

const session = sourceDb.createSession();

const insert = sourceDb.prepare('INSERT INTO data (key, value) VALUES (?, ?)');
insert.run(1, 'hello');
insert.run(2, 'world');

const changeset = session.changeset();
targetDb.applyChangeset(changeset);
// Now that the changeset has been applied, targetDb contains the same data as sourceDb.