Support closing a DuckDBInstance

[nshiab]

Hi!

With the previous DuckDB node bindings, we could .close() a Database instance to free up memory.

Is there a way to do the same thing with this version?

Thank you!

[jraymakers]

There isn't currently a way to explicitly close an instance. However, it will be closed automatically a short time after the reference goes out of scope (and thus gets garbage collected). This typically happens in under a second, but does depend on the GC behavior so can't be strictly guaranteed.

It wouldn't be hard to expose explicit close for instances - I did this already for connections - but I'm curious about the use case. It's not recommended in general to create more than one instance per process, either at the same time or over time. In which case would you call an explicit close, if it existed?

[nshiab]

Thanks for the answer, @jraymakers !

I’m currently updating my library, simple-data-analysis, with duckdb-node-neo

I just noticed that I had a method for closing an instance with the previous bindings, which is why I’m asking.

But if it’s closed automatically, I guess it’s all good!

Thank you for all your hard work. This is a fantastic improvement! 😊

[jraymakers]

I might add it at some point if I get enough requests. Let me know if you find a need for it. I'll leave this open in the meantime.

Glad you're enjoying the new library!

[albertog]

It is recomendable hold connection open vs. open and close when needed?

[jraymakers]

It is recomendable hold connection open vs. open and close when needed?

Connections in DuckDB (and Node Neo) are fairly lightweight compared to other DBMSs. They're just in-memory objects. I'd usually recommend keeping them around. The only reason I can think of to close one is to release any memory associated with it. This is usually small, but it can get large if you create (large) temporary objects, because these are associated with the connection.

Other semantic properties of connections might affect your decision of how many to use:

• Different connections can run SQL in parallel. Each connection can only have a single active query (and transaction).
• Each connection has its own context for things like the current database & schema, variables, and named prepared statements, in addition to temporary objects.

[kkozler]

I'm curious about the use case.

@jraymakers Here's one ...

I'm trying to run dbt on a DuckDB database.

Here's a example that minimally reproduces my issue. You'll need a DuckDb file and a dbt profile/project.

import { DuckDBInstance } from '@duckdb/node-api';
import { exec } from 'child_process';

async function main() {
	const localDbPath = ...;
	const dbtProfilesDir = ...;
	const dbtProjectDir = ... ;

	var db = await DuckDBInstance.create(localDbPath);

        // do stuff

        // would love to close the instance here

	const dbtConfig = [
		'--profiles-dir',
		dbtProfilesDir,
		'--project-dir',
		dbtProjectDir,
	];
    
	return new Promise<void>((resolve, reject) => {
        exec(\`dbt deps --upgrade ${dbtConfig.join(' ')}\`, (err, stdout, stderr) => {
            if (err) {
                reject(err);
            }
            exec(\`dbt run -s +inference ${dbtConfig.join(' ')}\`, (err, stdout, stderr) => {
                if (err) {
                    reject(err);
				}
				console.log(stdout);
				console.log(stderr);
				resolve();
			});
		});
	});
}

main().catch(err => console.error(err));

Prints out a dbt error:

Runtime Error
  IO Error: Could not set lock on file "<path_to_duckdb_file>": Conflicting lock is held in /Users/kkozler/.nvm/versions/node/v22.9.0/bin/node (PID 51196) by user kkozler. See also https://duckdb.org/docs/connect/concurrency

The PID in question is the PID of the current process. There's no real concurrency conflict here.

[jraymakers]

Got it, thanks for the example, it makes sense, and it's pretty compelling.

It may be possible to convince the finalizer to run by enclosing the use of the instance in nested scope, such as a function call. But that isn't very robust; one might need to sleep for a bit after the call to give the finalizer a chance to run, and even that is no guarantee.

I'll put adding a close to the instance on my queue.

[kkozler]

FWIW, enclosing the DuckDBInstance within a function call like this did resolve my issue without needing to use a sleep.

async function withConnection(duckDbFile, doStuff) {
        var db = await DuckDBInstance.create(duckDBFile);
	var connection = await db.connect();
	try {
		doStuff()
	} finally {
		connection.close();
	}
}