Instant SQL for results as you type in DuckDB UI

I just watched the author of this feature and blog post give a talk at the DataCouncil conference in Oakland, and it is obvious what a huge amount of craft, ingenuity, and care went into building it. Congratulations to Hamilton and the MotherDuck team for an awesome launch!

In DuckDB UI and MotherDuck.

Awesome video of feature: https://youtu.be/aFDUlyeMBc8

Disclaimer: I’m a co-founder at MotherDuck.

It would be even better if SQL had pipe syntax. SQL is amazing, but its ordering isn’t intuitive, and only CTEs provide a reliable way to preview intermediate results. With pipes, each step could clearly show intermediate outputs.

Example:

FROM orders |> WHERE order_date >= '2024-01-01' |> AGGREGATE SUM(order_amount) AS total_spent GROUP BY customer_id |> WHERE total_spent > 1000 |> INNER JOIN customers USING(customer_id) |> CALL ENRICH.APOLLO(EMAIL > customers.email) |> AGGREGATE COUNT(*) high_value_customer GROUP BY company.country

DuckDb is missing a killer feature by not having a pipe syntax like kusto or google's pipe query syntax.

Why is it a killer feature? First of all, LLMs complete text from left to right. That alone is a killer feature.

But for us meatboxes with less compute power, pipe syntax allow (much better) code completion.

Pipe syntax is delightful to work with and makes going back to SQL a real bummer moment (please insert meme of Kate Perry kissing the earth here).

If there are any DuckDB engineers here, I just want you to know that your tool has been incredible for my work in bioinformatics/biotech. It has the flexibility/simplicity that biological data (messy, changing constantly) requires.

I hope this doesn't work with DELETE queries.

I really like duckdb's notebooks for exploration and this feature makes them even more awesome, but the fact that I can't share, export or commit them into a git repo feels extremely limiting. It's neat-ish that it dodfoods and store them in a duckdb database. It even seems to stores historical versions, but I can't really do anything with it..

CTE inspection is amazing. I spend too much time doing that manually.

Cool tool, even cooler when paired with the manifold project for SQL[1], which has fantastic support for type-safe, native DuckDB syntax.

1. https://github.com/manifold-systems/manifold/blob/master/man...

it looks cool, but i wish i could just see the entire table that im about to query. i always start my queries with a quick `select * from table limit 10;` then go about adding the columns and joins

We are working on something similar over at yeet.

Except for system performance data.

You can checkout our sandbox at

https://yeet.cx/play

a fun function in duckdb (which I think they're using here) is `json_serialize_sql`. It returns a JSON AST of the SQL

    SELECT json_serialize_sql('SELECT 2');



    [
        {
            "json_serialize_sql('SELECT 2')": {
                "error": false,
                "statements": [
                    {
                        "node": {
                            "type": "SELECT_NODE",
                            "modifiers": [],
                            "cte_map": {
                                "map": []
                            },
                            "select_list": [
                                {
                                    "class": "CONSTANT",
                                    "type": "VALUE_CONSTANT",
                                    "alias": "",
                                    "query_location": 7,
                                    "value": {
                                        "type": {
                                            "id": "INTEGER",
                                            "type_info": null
                                        },
                                        "is_null": false,
                                        "value": 2
                                    }
                                }
                            ],
                            "from_table": {
                                "type": "EMPTY",
                                "alias": "",
                                "sample": null,
                                "query_location": 18446744073709551615
                            },
                            "where_clause": null,
                            "group_expressions": [],
                            "group_sets": [],
                            "aggregate_handling": "STANDARD_HANDLING",
                            "having": null,
                            "sample": null,
                            "qualify": null
                        },
                        "named_param_map": []
                    }
                ]
            }
        }
    ]

Does DuckDB UI support spatial visualizations ? Would be great to be able to use the UI with the spatial extensions.

First time seeing the from at the top of the query and I am not sure how I feel about it. It seems useful but I am so used to select...from.

I'm assuming it's more of a user preference like commas in front of the field instead of after field?

Edit: never mind, thanks for the replies! I had missed the part where it showed visualizing subqueries, which is what I wanted but didn't think it did. This looks very helpful indeed!

Wow, I used DuckDB in my last job, and have to say it was impressive for its speed. Now it's more useful than ever.

Will this be available in duckdb -ui ?

Is mother duck editor features available on-prem? My understanding is that mother duck is a data warehouse sass.

Does it work as fast with more complicated queries with joins/havings and large tables?

It's neat but the CTE selection bit errors out more often than not & erroneously selects more than the current CTE

At times I've done crude implementations of similar functionality, by basically just taking the current string on change and concatenating with " LIMIT 20" before passing it to the database API and then rerendering a table if the result is an associative array rather than an error message.

I think this would be better if it was combined with information about valid words in the cursor position, which would likely be a bit more involved but achievable through querying the schema and settling on a subset of SQL. It would help people that aren't already fluent in SQL to extract the data they want. Perhaps allow them to click the suggestions to add them to the query.

I've done partial implementations of this too, that query the schema for table or column names. It's very cheap even on large, complex schemas, so it's fine to just throw every change at the database and check what drops out. In practice I didn't get much out of either beyond the fun of hacking up an ephemeral tool, or I would probably have built some small product around it.

Amazing work. Motherduck and the duckdb ecosystem have done a great job of gathering talented engineers with great taste. Craftsmanship may be the word I’m looking for - I always look forward to their releases.

I spent the first two quarters of 2024 working on observability for a build-the-plane-as-you-fly-it style project. I can’t express how useful the cte preview would have been for debugging.

I moved from pandas and SQLite to polars and DuckDB. Such an improvement in these new tools.

honestly this kind of instant feedback wouldve saved me tons of headaches in the past - you think all these layers of tooling are making sql beginners pick it up faster or just overwhelming them?

This is just so good. I wish redash had this...

Delete From dbo.users w...

(129304 rows affected)

This is such a bizarre feature.

Please finally add q language with proper integration to your tables so that our precious q-SQL is available there. Stop reinventing the wheel, let's at least catch up to the previous generation (in terms of convenience). Make the final step.