Sqlite Plugin - only one db allowed

[thisanimus]

I'm playing around with the capacitor Sqlite plugin and i'm getting an error when i try to use more than one database at a time. Is this to be expected? Are there any workarounds?

[robingenz]

Multiple databases should be supported. Which platform do you use? What's the error message?

[thisanimus]

just the web opfs/sqlite-wasm version for now. If i init just one or the other, it works fine, but if I init both i get "Database not found." for both.

[robingenz]

Thanks for reporting that. Seems like an issue within SQLite WASM. I've just created a bug report: sqlite/sqlite-wasm#113

[thisanimus]

If I use sqlite-wasm directly, I can create a reusable class that does not seem to have the same limitation. Do you know if there is a good way to set up a similar interface using the capacitor plugin?

eg:

import { sqlite3Worker1Promiser } from '@sqlite.org/sqlite-wasm';

type ExecuteOptions = {
	params: unknown[];
	rowMode: 'array' | 'object';
};

export class SQLiteCore {
	private opfsFilePath: string;
	private promiser: any = null;
	private dbId: string | null = null;
	private isInitialized = false;

	constructor(opfsFilePath: string) {
		this.opfsFilePath = opfsFilePath;
	}

	private async initializePromiser() {
		return new Promise<any>((resolve) => {
			const _promiser = sqlite3Worker1Promiser({
				onready: () => resolve(_promiser),
			});
		});
	}
	private async openDatabase(p: any) {
		const response = await p('open', { filename: `file:${this.opfsFilePath}?vfs=opfs` });
		if (response.type === 'error') {
			console.error(response);
			throw new Error(response.result.message);
		}
		console.log(response);
		return response.result.dbId as string;
	}
	async initialize() {
		if (this.isInitialized) return true;
		try {
			console.log('Initializing SQLite database...');
			this.promiser = await this.initializePromiser();
			if (!this.promiser) throw new Error('Failed to initialize promiser');
			await this.promiser('config-get', {});
			this.dbId = await this.openDatabase(this.promiser);
			console.log('Database opened successfully with ID:', this.dbId);
		} catch (err) {
			console.error(err);
			throw err;
		}
	}

	async executeQuery(
		sql: string,
		{ params = [], rowMode = 'array' }: Partial<ExecuteOptions> = {},
	) {
		if (!this.dbId || !this.promiser) await this.initialize();
		try {
			console.log('Executing query:', sql, 'with params:', params);
			const result = await this.promiser('exec', {
				dbId: this.dbId,
				sql,
				rowMode,
				bind: params,
				returnValue: 'resultRows',
			});

			console.log('Query result:', result);

			if (result.type === 'error') {
				console.error(result);
				throw new Error(result.result.message);
			}

			return result;
		} catch (err) {
			console.error(err);
			throw err;
		}
	}
}

Then this seems to work ok:

const db1 = new SQLiteCore(`db1.sqlite3`);
const db2 = new SQLiteCore(`db2.sqlite3`);

[robingenz]

Update! This problem will be fixed in one of the next versions of @sqlite.org/sqlite-wasm, see this comment. In the meantime, you can simply patch the dependency (e.g. using patch-package).

[thisanimus]

Awesome! thanks for the quick turnaround!

[robingenz]

Good news! We found a workaround. I've just published a new version with a fix.