# Can new database connector handle "complex" SQL?

**URL:** <https://talk.observablehq.com/t/can-new-database-connector-handle-complex-sql/10797>\
**Category:** Help\
**Created:** [September 3, 2026, 5:23pm UTC](https://talk.observablehq.com/t/can-new-database-connector-handle-complex-sql/10797 "2026-09-03T17:23:43Z")\
**Posts on this page:** 12\
**Page:** 1

<div class="post-metadata">

**Author:** ![kpm-at-sonnys](https://avatars.discourse-cdn.com/v4/letter/k/8797f3/32.png) [@kpm-at-sonnys](https://talk.observablehq.com/u/kpm-at-sonnys)\
**Post date:** [September 3, 2026, 5:23pm UTC](https://talk.observablehq.com/t/can-new-database-connector-handle-complex-sql/10797/1 "2026-09-03T17:23:43Z")

</div>

I’m not seeing results for complex queries which do get results on “old.obser…”, for example something like:

```auto
const foo = display(await tsdb.sql`
WITH ram_lttb AS (
      SELECT
        panel_id,
        device_id,
        position,
        (unnest(lttb(time, ram, 60))).time AS time,
        (unnest(lttb(time, ram, 60))).value AS ram
      FROM data_table
      WHERE time > NOW() - INTERVAL '${hours} hours'
        AND gateway_id = '${gateway}'
      GROUP BY panel_id, device_id, position
    )
    SELECT
      r.panel_id,
      r.position AS "position_number",
      r.time,
      r.ram,
      s.ram_min
    FROM ram_lttb r
    JOIN state_table s
      ON s.time = r.time
     AND s.panel_id = r.panel_id
     AND s.device_id = r.device_id
    WHERE s.time > NOW() - INTERVAL '${hours} hours'
      AND s.gateway_id = '${gateway}'
    ORDER BY
      r.panel_id ASC,
      r.position ASC,
      r.time DESC
`)

```

---

<div class="post-metadata">

**Author:** ![kpm-at-sonnys](https://avatars.discourse-cdn.com/v4/letter/k/8797f3/32.png) [@kpm-at-sonnys](https://talk.observablehq.com/u/kpm-at-sonnys)\
**Post date:** [September 3, 2026, 5:57pm UTC](https://talk.observablehq.com/t/can-new-database-connector-handle-complex-sql/10797/2 "2026-09-03T17:57:35Z")

</div>

Tried using a plain sql cell, too. No joy.

---

<div class="post-metadata">

**Author:** ![tophtucker](https://yyz2.discourse-cdn.com/flex030/user_avatar/talk.observablehq.com/tophtucker/32/4781_2.png) [@tophtucker](https://talk.observablehq.com/u/tophtucker)\
**Post date:** [September 3, 2026, 5:57pm UTC](https://talk.observablehq.com/t/can-new-database-connector-handle-complex-sql/10797/3 "2026-09-03T17:57:36Z")

</div>

Hm. At a glance I see no reason why it shouldn’t work in New. It’s possible that it’s just loading slowly…? One difference is that Old streamed results, and New doesn’t, so for especially big slow queries you might get it all at once later…?

---

<div class="post-metadata">

**Author:** ![kpm-at-sonnys](https://avatars.discourse-cdn.com/v4/letter/k/8797f3/32.png) [@kpm-at-sonnys](https://talk.observablehq.com/u/kpm-at-sonnys)\
**Post date:** [September 3, 2026, 5:59pm UTC](https://talk.observablehq.com/t/can-new-database-connector-handle-complex-sql/10797/4 "2026-09-03T17:59:08Z")

</div>

It seems to “complete” but it’s empty. In “old” it gives me about 900 rows in about 1 second.

---

<div class="post-metadata">

**Author:** ![kpm-at-sonnys](https://avatars.discourse-cdn.com/v4/letter/k/8797f3/32.png) [@kpm-at-sonnys](https://talk.observablehq.com/u/kpm-at-sonnys)\
**Post date:** [September 3, 2026, 6:43pm UTC](https://talk.observablehq.com/t/can-new-database-connector-handle-complex-sql/10797/5 "2026-09-03T18:43:14Z")

</div>

BTW, it’s not really clear from my example, but I’m using Postgres+TimescaleDB

UPDATE: Is _seems_ as though timescale-specific things should be safely passed right through ADBC

---

<div class="post-metadata">

**Author:** ![tophtucker](https://yyz2.discourse-cdn.com/flex030/user_avatar/talk.observablehq.com/tophtucker/32/4781_2.png) [@tophtucker](https://talk.observablehq.com/u/tophtucker)\
**Post date:** [September 3, 2026, 9:02pm UTC](https://talk.observablehq.com/t/can-new-database-connector-handle-complex-sql/10797/6 "2026-09-03T21:02:03Z")

</div>

OK, upon closer inspection, the way you’re interpolating variables there won’t work with the `sql` tagged template literal. The exact solution varies a bit because different database drivers allow different sorts of parameterization.

In Postgres, for the gateway\_id string, you should do it without quotes around the parameterized value; for the interval, you have to multiply a string constant by the parameterized value:

```auto
DatabaseClient("postgres_db").sql`SELECT * FROM data_table
  WHERE time > NOW() - (INTERVAL '1 hour' * ${hours})
  AND gateway_id = ${gateway}`

```

In Snowflake, it’d be a little different, because it uses `CURRENT_TIMESTAMP` instead of `NOW` and doesn’t seem to allow that parameterized multiplication trick in `INTERVAL`s. But it has another function, `DATEADD`, which works:

```auto
DatabaseClient("snowflake_db").sql`SELECT * FROM data_table 
  WHERE time > DATEADD(hour, ${-hours}, CURRENT_TIMESTAMP())
  AND gateway_id = ${gateway}`

```

Or, you can get sneaky and hacky and “opt out” of how we safely handle interpolation:

```auto
DatabaseClient("snowflake_db").sql`SELECT * FROM DIM_USERS WHERE create_time > CURRENT_TIMESTAMP - INTERVAL ${sql([`'${hours} hours'`])} LIMIT 10`

```

We also just shipped backwards compatibility for the `query` method (if you have the 2018 standard library selected in the 📦 dependencies sidebar), which lets you do good old-fashioned injection-unsafe string-building, like you were doing before:

```auto
DatabaseClient("snowflake_db").query(`SELECT * FROM DIM_USERS WHERE create_time > CURRENT_TIMESTAMP - INTERVAL '${hours} hours' LIMIT 10`)

```

Does that fix it?

---

<div class="post-metadata">

**Author:** ![tophtucker](https://yyz2.discourse-cdn.com/flex030/user_avatar/talk.observablehq.com/tophtucker/32/4781_2.png) [@tophtucker](https://talk.observablehq.com/u/tophtucker)\
**Post date:** [September 3, 2026, 9:23pm UTC](https://talk.observablehq.com/t/can-new-database-connector-handle-complex-sql/10797/7 "2026-09-03T21:23:26Z")

</div>

For further background, which you may or may not need, but which might help someone else…

Both Old and New have a `db.sql` method. As of this morning’s backwards compatibility update, both Old and New also have a `db.query` method. (In New, it’s only available if the notebook is set to use the 2018 standard library.) They behave similarly for the simplest queries, but the differences become important with complexity.

With `query` you query the database like this:

```js
db.query("SELECT 1")

```

With `sql` you query the database like this:

```js
db.sql`SELECT 1`

```

So far, those two queries will behave identically.

With `query`, you are passing a plain old string to the database as the query, and it would behave the same however you constructed the string, _e.g._ with backticks:

```js
db.query(`SELECT 1`)

```

And it’d behave the same if you built the string by interpolating something into it, like this:

```js
db.query(`SELECT ${number}`)

```

That would still just run the query `SELECT 1` (assuming `number` is `1`).

But with `sql`, if you do this:

```js
db.sql`SELECT ${number}`

```

It is actually constructing a parameterized query string, `SELECT ?`, and then separately passing the parameter `1` directly to the database driver, which substitutes the `1` for the `?` to produce `SELECT 1`, and you get the same results.

The new way is safer, because if the value of your variable `number` somehow became something like `1; DROP TABLE table_name;`, the injection attack wouldn’t work.

And the new way can be more convenient, because you don’t have to put quotes around your strings or format your arrays to pass them in.

The problem is, you can’t put those question-mark parameters everywhere you might build up a string. They can only represent complete _values_.

With `query`, you could do something “crazy” like this:

```auto
db.query(`SELECT ${Math.random() > 0.5 ? "GREATEST" : "LEAST"}(1, 2)`)

```

Or even this:

```auto
db.query(`SEL${"ECT"} 1`)

```

But with `sql`, those are syntax errors. You can’t put `?`s there! It ends up being like `?(1, 2)` or `SEL? 1`, which indeed look like syntax errors. The question mark can only stand for a value.

There may be other database-specific limitations. For example, on Snowflake, it seems an `INTERVAL` cannot be dynamically constructed by a parameter. You certainly can’t do `INTERVAL '${hours} hours'`, because that string would become `'? hours'`, and that `?` would not be replaced, it would just be nonsense, an invalid interval, a syntax error. But it looks like you can’t even do `INTERVAL ${hoursString}`, where `hoursString` is `'6 hours'`, for whatever reason. Hence, the suggestion above to use `DATEADD` instead.

One “escape hatch” is that you can “cheat” and interpolate another sql fragment you’ve constructed by passing a raw string, like this:

```auto
db.sql`SELECT CURRENT_TIMESTAMP() - INTERVAL ${sql([`'${hours} hours'`])}`

```

That works because, behind the scenes, tagged template literals are just regular JavaScript functions with particular parameters, and nested `sql` fragments get flattened by the `sql` method. But that’s a hacky ugly confusing workaround that we wouldn’t really recommend. But maybe it helps elucidate what’s happening.

---

<div class="post-metadata">

**Author:** ![mbostock](https://yyz2.discourse-cdn.com/flex030/user_avatar/talk.observablehq.com/mbostock/32/9_2.png) [@mbostock](https://talk.observablehq.com/u/mbostock)\
**Post date:** [September 3, 2026, 9:30pm UTC](https://talk.observablehq.com/t/can-new-database-connector-handle-complex-sql/10797/8 "2026-09-03T21:30:59Z")

</div>

Great write-up @tophtucker!

You can shorten the last (unsafe interpolation) example even further if desired:

```javascript
db.sql`SELECT CURRENT_TIMESTAMP() - INTERVAL '${sql([hours])} hours'`

```

---

<div class="post-metadata">

**Author:** ![kpm-at-sonnys](https://avatars.discourse-cdn.com/v4/letter/k/8797f3/32.png) [@kpm-at-sonnys](https://talk.observablehq.com/u/kpm-at-sonnys)\
**Post date:** [September 4, 2026, 2:32pm UTC](https://talk.observablehq.com/t/can-new-database-connector-handle-complex-sql/10797/9 "2026-09-04T14:32:17Z")

</div>

Thanks @tophtucker and @mbostock… this is much closer to working.

Some data, though, comes in significantly differently:

- UUID string is now a Uint8Array(16)
- Portion of a different UUID string is now a Uint8Array(4)

There may be others… just the first I saw today which made my faceted plots become “infinitely” vertical. 🙂

---

<div class="post-metadata">

**Author:** ![mbostock](https://yyz2.discourse-cdn.com/flex030/user_avatar/talk.observablehq.com/mbostock/32/9_2.png) [@mbostock](https://talk.observablehq.com/u/mbostock)\
**Post date:** [September 4, 2026, 2:46pm UTC](https://talk.observablehq.com/t/can-new-database-connector-handle-complex-sql/10797/10 "2026-09-04T14:46:10Z")

</div>

Glad things are getting better and sorry again for the continued trouble.

I don’t think we’ll be able to change the behavior of the database driver to implicitly convert UUID to TEXT. You’ll likely need to add a `::TEXT` cast when you select a UUID in order to explicitly convert it to a string. For example:

```sql
SELECT gen_random_uuid()::text

```

Hope this helps.

---

<div class="post-metadata">

**Author:** ![kpm-at-sonnys](https://avatars.discourse-cdn.com/v4/letter/k/8797f3/32.png) [@kpm-at-sonnys](https://talk.observablehq.com/u/kpm-at-sonnys)\
**Post date:** [September 9, 2026, 7:59pm UTC](https://talk.observablehq.com/t/can-new-database-connector-handle-complex-sql/10797/11 "2026-09-09T19:59:40Z")

</div>

One more observation that may be helpful to others: If you have a query which returns nested data, the new db connectors here will flatten that into a JSON string. For example, if in Postgres I `SELECT jsonb_object_agg(name, value) AS metrics` then instead of getting a `metrics` object with properties, I get that object as though it’s been passed through `JSON.stringify()`.

To reconstitute, one needs to…

```auto
data.forEach(d=>{
  d.metrics = JSON.parse(d.metrics)
})

```

---

<div class="post-metadata">

**Author:** ![mbostock](https://yyz2.discourse-cdn.com/flex030/user_avatar/talk.observablehq.com/mbostock/32/9_2.png) [@mbostock](https://talk.observablehq.com/u/mbostock)\
**Post date:** [September 9, 2026, 9:27pm UTC](https://talk.observablehq.com/t/can-new-database-connector-handle-complex-sql/10797/12 "2026-09-09T21:27:27Z")

</div>

Yes, the Apache Arrow JavaScript implementation doesn’t yet support canonical extension types, as shown on their [implementation status dashboard](https://arrow.apache.org/docs/status.html). Interestingly, DuckDB-Wasm can return structured objects in rows, but I’d guess that’s using the struct data type?