Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Add DuckDB node.js API #1112

Merged
merged 37 commits into from Nov 12, 2020
Merged

Add DuckDB node.js API #1112

merged 37 commits into from Nov 12, 2020

Conversation

hannes
Copy link
Member

@hannes hannes commented Nov 12, 2020

This adds a node.js API for DuckDB in /tools/nodejs due to popular demand. The API for this client is somewhat compliant to the SQLite node.js client for easier transition (and transition you must eventually). The npm package is called 'duckdb' thanks to @twilson63 who graciously allowed us to take over that package name.

Install the node.js API like so (for now, binaries & npm will follow)

npm install duckdb

(this could take a while)

Then load the package and create a database object:

var duckdb = require('duckdb');

var db = new duckdb.Database(':memory:'); // or a file name for a persistent DB

Then you can run a query:

db.all('SELECT 42 AS fortytwo', function(err, res) {
  if (err) {
    throw err;
  }
  console.log(res[0].fortytwo)
});

Other available methods are each, where the callback is invoked for each row, run to execute a single statement without results and exec, which can execute several SQL commands at once but also does not return results. All those commands can work with prepared statements, taking the values for the parameters as additional arguments. For example like so:

db.all('SELECT ?::INTEGER AS fortytwo, ?::STRING as hello', 42, 'Hello, World', function(err, res) {
  if (err) {
    throw err;
  }
  console.log(res[0].fortytwo)
  console.log(res[0].hello)
});

However, these are all shorthands for something much more elegant. A database can have multiple Connections, those are created using db.connect().

var con = db.connect();

You can create multiple connections, each with their own transaction context.

Connection objects also contain shorthands to directly call run(), all() and each() with parameters and callbacks, respectively, for example:

con.all('SELECT 42 AS fortytwo', function(err, res) {
  if (err) {
    throw err;
  }
  console.log(res[0].fortytwo)
});

From connections, you can create prepared statements (and only that) using con.prepare():

var stmt = con.prepare('select ?::INTEGER as fortytwo');

To execute this statement, you can call for example all() on the stmt object:

stmt.all(42, function(err, res) {
  if (err) {
    throw err;
  }
  console.log(res[0].fortytwo)
});

You can also execute the prepared statement multiple times. This is for example useful to fill a table with data:

con.run('CREATE TABLE a (i INTEGER)');
var stmt = con.prepare('INSERT INTO a VALUES (?)');
for (var i = 0; i < 10; i++) {
  stmt.run(i);
}
stmt.finalize();
con.all('SELECT * FROM a', function(err, res) {
  if (err) {
    throw err;
  }
  console.log(res)
});

prepare() can also take a callback which gets the prepared statement as an argument:

var stmt = con.prepare('select ?::INTEGER as fortytwo', function(err, stmt) {
  stmt.all(42, function(err, res) {
    if (err) {
      throw err;
    }
    console.log(res[0].fortytwo)
  });
});

@hannes
Copy link
Member Author

hannes commented Nov 12, 2020

Fixes #661

@hannes hannes merged commit 295ce28 into master Nov 12, 2020
@hannes hannes deleted the nodejs branch November 12, 2020 15:06
@willium
Copy link

willium commented Nov 13, 2020

🥳

@willium
Copy link

willium commented Nov 13, 2020

@hannesmuehleisen I'm super excited to play with this! Can't seem to find the package on npm... am I too early?

@hannes
Copy link
Member Author

hannes commented Nov 13, 2020

Yes please try again tomorrow

@willium
Copy link

willium commented Nov 13, 2020

Got it! Looking forward to it :)

@willium
Copy link

willium commented Nov 13, 2020

I'll go ahead and add some TS types when we get a chance to test it too

@hannes
Copy link
Member Author

hannes commented Nov 14, 2020

Ok npm install duckdb should work now. Looking forward to feedback!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

Successfully merging this pull request may close these issues.

None yet

2 participants