add DuckDB to dbfiddle - db<>fiddle

Anonymous 2538 replying to Jack Douglas

You are right.  
I think I managed to wrap everything into promises correctly without external packages. I updated gist in: https://gist.github.com/ttomasz/8f0ce83cf90830e7e372e8de2a64b170    
I also added installing/loading extensions.  
Unfortunately DuckDB's API is a little different than the packages you were using for sqlite and not everything is possible.   
Getting a list of columns from empty resultset is not easy so it's hard to differentiate if the query did not return anything e.g. it was DDL or if it returned zero rows.  
Maybe someone from DuckDB's team could improve my code, this is probably as far as my JavaScript skills go :)

Jack Douglas

do you know anything about the [duckdb-async](https://www.npmjs.com/package/duckdb-async) npm library? Perhaps I can use that

Jack Douglas replying to Anonymous 2538

unfortunately those ticks don't indicate anything good - just that the function (not the callback) returned without error. With the async SQLite library the actual result is returned at that point. I think the easiest way might be to wrap the callback into a promise and then use await: https://stackoverflow.com/a/51163205/12757754

Anonymous 2538

(replaced connection and paths of input/output files)

Anonymous 2538

I tested that script with full node image (only replaced connection): https://gist.github.com/ttomasz/8f0ce83cf90830e7e372e8de2a64b170  
it wrote ["✓","✓"] to output.json  
so I think the problem is how to install needed dependencies in alpine image (assuming that is a requirement?). Sadly that's not something that I know.

Jack Douglas

```js  
const fs = require('fs');

const db = require('/usr/local/lib/node_modules/better-sqlite3')(':memory:');

const batches = JSON.parse(fs.readFileSync('/batches.json', 'utf8'));
const result = [];

function backtick (m,s) {
  let longest = '```';
  let currentLength = 0;
  for(let i = 0; i < s.length; i++){
    if(s[i] === '`'){
      currentLength++;
      if(currentLength >= longest.length) longest += '`';
    } else {
      currentLength = 0;
    }
  }
  return `${longest} ${s}\n${m}\n${longest}`.replace(/^/gm, "> ") + '\n';
}

function escapeMarkdownCell (m){
  m ??= '';
  return m.replace(/[[*/\|`_<&]/g, String.raw`\$&`).replace(/^ +/gm,m=>m.replaceAll(' ','&numsp;')).replace(/\r?\n|\r/g,'<br>');
}

for (let batch of batches) {

  let markdown = '';

  try {

    const statement = db.prepare(batch);
    if(statement.reader){
      statement.raw(true);
      const columns = statement.columns();
      const colCount = columns.length;

      if(colCount) {
        markdown += '| ';
        for (let x = 0; x < colCount; x++) markdown += escapeMarkdownCell(columns[x].name) + ' | ';
        markdown = markdown.slice(0,-1) + '\n';

        markdown += '| ';
        for (let x = 0; x < colCount; x++) {
          const alignRight = false;
          markdown += ( alignRight ? '-' : ':' ) + ("-".repeat(columns[x].name.length)) + ( alignRight ? ':' : '-' ) + '|';
        }
        markdown += '\n';

        const data = statement.all();

        if(data.length) {
          for (let y = 0; y < data.length; y++) {
            markdown += '| ';
            for (let x = 0; x < colCount; x++) {
              const val = data[y][x];
              if(val === null){
                markdown += '*null*';
              } else if (val instanceof Buffer) {
                markdown += '`'+val.toString('hex')+'`';
              } else {
                markdown += escapeMarkdownCell(val.toString());
              }
              markdown += ' | ';
            }
            markdown = markdown.slice(0,-1) + '\n';
          }
        }
      }
    } else {
      db.exec(batch);
      markdown = '✓';
    }

  } catch(e) {
    markdown = backtick(JSON.stringify(e,null,2), 'error');
  }

  result.push(markdown);
}

fs.writeFileSync('/output.json', JSON.stringify(result));  
```

Jack Douglas replying to Anonymous 5732

each engine has a simple script like this one for SQLite:

Anonymous 5732 replying to Jack Douglas

I think I somehow got it very wrong, this it's not a Web-based fiddle, so the right fit it's not duckdb-wasm but the Node.js bindings. Will check with @Mause and come back on this. Do you have any example of what's required to integrate with db<>fiddle API?

Jack Douglas

thanks to both of you for your help on this - I'm happy to put the effort in to get this working

Jack Douglas

what is the "syncronous version of duckdb"?

Jack Douglas replying to Anonymous 5732

deasync is not a hard requirement, but writing synchronous code for db<>fiddle is much easier. async/await would also be ok but the node DuckDB API is callback based so I'd have to promisify it somehow

Anonymous 5732

hi! Carlo here, also duckdb dev, unsure how deasync works, unsure if it properly handles wait on message events. Is deasync an hard requirement? If so then you are using node, and then the syncronous version of duckdb would work better (but then probably why not the nodejs version?). Or if you are going to target the Web, probably you have to do without deasync in any case.

Anonymous 5830

Oblig chatgpt answer: The code you provided seems to be using the deasync and duckdb libraries for Node.js. From the code snippet, it appears that you are trying to create a synchronous version of the db.all() function by wrapping it with deasync.

The reason you might be experiencing a segmentation fault could be due to the interaction between the deasync library and the duckdb library. deasync has been known to cause issues with native bindings in some libraries, especially when it is used to synchronize asynchronous functions that were not designed to be used synchronously.

I would recommend avoiding the use of deasync and instead leveraging the asynchronous nature of Node.js. Instead of trying to force the asynchronous code to be synchronous, you can use async/await to achieve similar behavior. Here's an example of how you can modify your code to do that:const duckdb = require('/usr/local/lib/node_modules/duckdb');
const db = new duckdb.Database(':memory:');

async function main() {
  const res = await db.all('SELECT 42 AS fortytwo');
  console.log(res);
}

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

Mause

I would assume you would have to bind .all back to the database instance, no?

Jack Douglas

@Mause

Jack Douglas

```  
Segmentation fault (core dumped)

Jack Douglas

```sh  
cat <<"EOF" > fiddle.js
const deasync = require('/usr/local/lib/node_modules/deasync');
const duckdb = require('/usr/local/lib/node_modules/duckdb');
const db = new duckdb.Database(':memory:');
const all = deasync(db.all);

const res = all('SELECT 42 AS fortytwo');
console.log(res);
EOF

node /fiddle.js

Jack Douglas replying to Mause

I'm getting a segmentation fault when trying to wrap with deasync

Jack Douglas replying to Mause

thanks for the tip - I guess the issue is something similar as Alpine uses musl

Mause

Also, I'm one of the DuckDB developers 👋

Mause

Most of the issue people experience with running DuckDB is due to running with an os with an old glibc version, is that the issue you're experiencing?

Anonymous 2538

@Jack, re: [your answer](#a4041), I think there are two problems here. One is installing the package globally and the other is using alpine image.  
  
I tested a few combinations and even official node alpine images just seems to be incompatible.  
  
With this Dockerfile:  
```
FROM node:18
WORKDIR /usr/app
RUN npm install duckdb@0.7.1
ENTRYPOINT ["bash"]
```  
I can run the code successfully. You can then use require('duckdb') like in the docs examples instead of full path.

Jack Douglas replying to Anonymous 5533

give the proposal some stars then :)

Anonymous 5533

I've been hearing about DuckDB more and more recently. This would definitely be a great addition.

Anonymous 2538 replying to Jack Douglas

To be honest that's a pretty difficult question :)  
  
I tried to think what kind of metric would be useful here but I'm not sure what would that be.  
  
That being said here are some findings:   

Google trends says that it's still not very well known: https://trends.google.com/trends/explore?q=duckdb,sqlite,postgresql,clickhouse    

But on Github we can see that the project is very active: https://github.com/duckdb/duckdb    

And some companies do use it in production in some parts of their processing pipelines:  
https://medium.com/airbnb-engineering/how-airbnb-safeguards-changes-in-production-c83e94bfc52 (single mention in this article)  
  
And some companies are building products using it:    

https://www.techtarget.com/searchdatamanagement/news/252527260/MotherDuck-raises-475M-for-open-source-DuckDB-database   

https://www.boilingdata.com/  
  
I saw some claims that it's used by scientists and researches in their analyses (for example as pandas replacement) and I saw some people asking questions about processing e.g. genomics data in duckdb's discord but here I don't have any concrete sources to back this up.

Jack Douglas

@Anonymous, any idea how widely used DuckDB is?

2 Answers