Querying MotherDuck does not work in Cube Core and Cube Cloud

[igorlukanin]

Describe the bug
If CUBEJS_DB_DUCKDB_MOTHERDUCK_TOKEN is specified for the duckdb driver so that it connects to MotherDuck, then any query would result in an error. In Cube Cloud, the query would run indefinitely. In Cube Core, a container will exit after an interval.

To Reproduce
Steps to reproduce the behavior:

1. Take the data model
2. Specify env vars
3. Run any query
4. See errors in the logs

cube-1  | E1106 13:48:04.302609835      40 ssl_utils.cc:593]                     load_file: UNKNOWN:Failed to load file {created_time:"2024-11-06T13:48:04.302598418+00:00", filename:"/usr/share/grpc/roots.pem", children:[UNKNOWN:No such file or directory {created_time:"2024-11-06T13:48:04.302597418+00:00", errno:2, os_error:"No such file or directory", syscall:"fopen"}]}
cube-1  | E1106 13:48:04.302628460      40 ssl_utils.cc:423]                     Could not get default pem root certs.
cube-1  | E1106 13:48:04.302630085      40 tls_security_connector.cc:461]        Update handshaker factory failed.
cube-1  | E1106 13:48:04.421059877     177 ssl_utils.cc:423]                     Could not get default pem root certs.
cube-1  | E1106 13:48:04.421074502     177 tls_security_connector.cc:461]        Update handshaker factory failed.

cube-1  | E1106 13:48:34.422520752     179 ssl_utils.cc:423]                     Could not get default pem root certs.
cube-1  | E1106 13:48:34.422562960     179 tls_security_connector.cc:461]        Update handshaker factory failed.
cube-1  | terminate called after throwing an instance of 'duckdb::InternalException'
cube-1  |   what():  {"exception_type":"INTERNAL","exception_message":"Attempted to dereference unique_ptr that is NULL!"}

Expected behavior
Querying MotherDuck just works.

Screenshots

Minimally reproducible Cube Schema

cubes:
  - name: test
    sql: SELECT 123 AS value

    measures:
      - name: count
        type: count

    dimensions:
      - name: value
        sql: value
        type: number

Setting CUBEJS_DB_SSL=true does not change anything.

CUBEJS_DEV_MODE=true
CUBEJS_API_SECRET=SECRET
# CUBEJS_DB_SSL=true

CUBEJS_DB_TYPE=duckdb
CUBEJS_DB_DUCKDB_MOTHERDUCK_TOKEN=<token>

Version:
v1.1.2, both in Cube Core and Cube Cloud

Additional context
Reported by at least two users in Slack: https://cube-js.slack.com/archives/C04NYBJP7RQ/p1723151261882389

[akatov]

I examined connecting to MotherDuck from Cube Cloud and also from the DuckDB CLI again - there is an additional authentication step after the initial connection with the token - I get prompted to enter a generated 8-character code into web UI before the connection can be established successfully.

I am also looking at the source code for the duckdb-driver and trying to run some of the commands in a node process.

From node I can connect to a local db using

db = new Database(localPath)

as well as to MotherDuck using

db = new Database('md:motherduck_token=' + token)

without needing the second authentication step, but I can only query using

db.all(QUERY, (err, res) => ...)

whereas the source for the duckdb-driver seems to first create a connection using

defaultConnection = db.connect()

and then use

defaultConnection.exec(QUERY, callback)

to run queries.

[igorlukanin]

Thanks you @akatov, this is very helpful! 🙌 Would you mind contributing a fix?