Using sqlite inside a long-lived isolate

[sabin26]

Hi, I read this specific feature:

Direct synchronous access in an isolate is supported for performance-sensitive use cases.

But came across computeWithDatabase method.

I am managing long-lived isolates on my own for specific use-cases such as http, websocket and sqlite. So, how can I synchronously access the database inside these isolates? I do not need any cross-isolate communication.

[rkistner]

You can use db.isolateConnectionFactory() to get a "factory" object to send over to different isolates, which allows you to open a connection in them:

sqlite_async.dart/lib/src/sqlite_database.dart

Lines 156 to 164 in 08495cf

	/// A connection factory that can be passed to different isolates.
	///
	/// Use this to access the database in background isolates.
	IsolateConnectionFactory isolateConnectionFactory() {
	return IsolateConnectionFactory(
	openFactory: openFactory,
	mutex: mutex.shared,
	upstreamPort: _eventsPort.client());
	}

The factory then has a method IsolateConnectionFactory.openRawDatabase() that opens a synchronous connection:

sqlite_async.dart/lib/src/isolate_connection_factory.dart

Lines 46 to 58 in 08495cf

	/// Opens a synchronous sqlite.Database directly in the current isolate.
	///
	/// This gives direct access to the database, but:
	/// 1. No app-level locking is performed automatically. Transactions may fail
	/// with SQLITE_BUSY if another isolate is using the database at the same time.
	/// 2. Other connections are not notified of any updates to tables made within
	/// this connection.
	Future<sqlite.Database> openRawDatabase({bool readOnly = false}) async {
	final db = await openFactory
	.open(SqliteOpenOptions(primaryConnection: false, readOnly: readOnly));
	return db;
	}
	}

The caveat is that you don't get automatic transaction locking (relevant for writes), and don't get automatic notification of updates to other connections - it is effectively the same as opening a sqlite3 connection directly in the Isolate. You'll have to directly use the mutex and upstreamPort on the factory to handle those two if relevant.

Transaction locking: If you don't use global transaction locks in every connection, it will fall back to the SQLite file-level locks. These will cause "SQLite database is locked"-style errors when attempting to create a write transaction (or any write statement) in multiple connections at the same time, instead of waiting for the other transaction to complete. You can handle these by retrying the transaction (will need the retry logic on both sides), or by using the mutex on the factory for locking (requires async code). Note that since we're using WAL mode, locks are not required for read queries/transactions.

Update notifications: This is what is used for the SqliteDatabase.onChange and SqliteDatabase.watch. SQLite itself only reports database changes made within a single connection, so for this to work across isolates, you need to pass the notifications through a SendPort/ReceivePort.

We do something similar as part of the PowerSync SDK. The logic is scattered between other code, but you can see some relevant parts here:

Spawning a new Isolate with a factory:

https://github.com/powersync-ja/powersync.dart/blob/87753640d9b831d72d779c24c6c790a670307242/packages/powersync/lib/src/powersync_database.dart#L248-L253

Opening the connection:

https://github.com/powersync-ja/powersync.dart/blob/87753640d9b831d72d779c24c6c790a670307242/packages/powersync/lib/src/powersync_database.dart#L555-L559

Getting a lock for write transactions:

https://github.com/powersync-ja/powersync.dart/blob/87753640d9b831d72d779c24c6c790a670307242/packages/powersync/lib/src/bucket_storage.dart#L686-L697

Transaction wrapper for the synchronous connection:

https://github.com/powersync-ja/powersync.dart/blob/87753640d9b831d72d779c24c6c790a670307242/packages/powersync/lib/src/database_utils.dart#L6-L37

Propagating update notifications to the upstream database:

https://github.com/powersync-ja/powersync.dart/blob/87753640d9b831d72d779c24c6c790a670307242/packages/powersync/lib/src/powersync_database.dart#L572-L589

As you can see, it is unfortunately still quite a bit of messy code if you want to preserve the transaction locking and especially update notifications. If there is demand, we can add those as helper functions in this library.