Adding SQLite Database Integration to an Obsidian Plugin

Developers: Plugin & API
,
1

Hello everyone,

I’m developing an ObsidianMd plugin using a Node.js environment with TypeScript and React. Currently, I’m working on integrating a SQLite database to read data from a .sqlite file.

However, I’ve encountered an issue when trying to run the plugin within the Electron environment. I’m using the better-sqlite3 npm package. The error message I receive is:

Uncaught TypeError: Cannot read properties of undefined (reading 'indexOf')
    at Function.getFileName (plugin:my-plugin...)

I’ve found a similar issue described here: Github WiseLibs - Better-SQLite3 Issue #488, but I’m still struggling to understand the root cause or how to resolve it.

Has anyone here integrated SQLite with an ObsidianMd plugin or experienced similar issues?

I’m particularly interested in understanding if there are limitations or best practices when working with databases in Electron-based environments. Any advice or guidance on troubleshooting this would be greatly appreciated!

Thank you in advance for your help!

1 Like
3

I succeeded in doing this recently using SQL.js.

DB loading and saving is done with app.vault.adapter.readBinary() and .writeBinary().

Have a read of the repo README for the other SQLite options I tried and why they didn’t work:

Database initialisation is like this:

1 Like
4

I was just debugging this exact issue. TL;DR I dont think that better-sqlite3 is compatible, sadly.

What I determined was that I think this is due to the bindings package used by the better-sqlite3 package:

bindings attempts to search all relevant locations for a native addon, and in doing so it does some fancy hackery like generating and parsing a stack trace to get the file path of the file that imported it. getFileName is a function built in there to do that. Because of the format of the stack trace output from running inside the obsidian runtime, it fails to capture a fileName and throws an exception when attempting to parse it.

I’ll be researching Alan G’s answer and other SQLite Libraries.

5

I was able to get SQLite to work by using Libsql. It is a fork of SQLite that is used for some additional use cases.

To do this I need to manage the native node addons myself. So I download the .node binding files into the plugin directory and I use the module-alias package to patch the module path to the plugin’s download path. This is because the electron runtime won’t have these dependencies.

Here’s a snippet of that plugin code:

export default class MyPlugin extends Plugin {
	settings: MyPluginSettings;
	_dbClient: Client | null = null;
	currentFilePath: string = path.join(__dirname, 'main.ts');
	__fsAdapter: FileSystemAdapter;
	__pluginDirectory: string;
	__libsqlModule: any;

	constructor(app: App, manifest: any) {
		super(app, manifest);
		this.__fsAdapter = app.vault.adapter as FileSystemAdapter;
		this.__pluginDirectory = `${this.__fsAdapter.getBasePath()}/${this.manifest.dir}/`;
		const libsqlBinaryName = 'libsql-darwin-arm64.node';

		// Define the expected path for the binary
		const customBinaryPath = path.resolve(`${this.__pluginDirectory}/${libsqlBinaryName}`);

		// Remap the binary resolution, not shown: downloading the right binary module.
		moduleAlias.addAlias('@libsql/darwin-arm64', customBinaryPath);
		this.__libsqlModule = require("@libsql/client");

	}

Once I tell it where to look for the binary I can require the client library dynamically and start using it like this.

...
client= this.__libsqlModule.createClient({
	url: `file:${this.__pluginDirectory}kb.db`
});

This is from a debug button I made to test this out. Don’t judge the promise hell, I was in a hurry :slight_smile: :

			this.getDbClient().then((dbClient) => {
				dbClient?.execute("CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT, email TEXT)").then(() => {
					dbClient?.execute("SELECT * FROM users").then((result) => {
						if (result.rows.length === 0) {
							return dbClient?.execute("INSERT INTO users (id, name, email) VALUES (1, 'Alice', '[email protected]')");
						}
					}).then(() => {
						return dbClient?.execute("SELECT * FROM users WHERE id = 1");
					}).then((result) => {
						const row = result.rows[0];
						console.log(`Name: ${row.name}, email: ${row.email}`);
					}).catch((error) => {
						console.error('Error executing query:', error);
					});
				});
			});

Not shown is needing to download the right binary for the platform youre on.