[HN Gopher] Instant SQL for results as you type in DuckDB UI
___________________________________________________________________
Instant SQL for results as you type in DuckDB UI
Author : ryguyrg
Score : 254 points
Date : 2025-04-24 13:23 UTC (9 hours ago)
(HTM) web link (motherduck.com)
(TXT) w3m dump (motherduck.com)
| ryguyrg wrote:
| In DuckDB UI and MotherDuck.
|
| Awesome video of feature: https://youtu.be/aFDUlyeMBc8
|
| Disclaimer: I'm a co-founder at MotherDuck.
| rancar2 wrote:
| Thanks for sharing this update with the world and including it
| on the local ui too.
|
| Feature request: enable the tuning of when Instant SQL is run
| and displayed. The erroring out with flashing updates at nearly
| every keystoke while expanding on a query is distracting for me
| personally (my brain goes into troubleshooting vs thinking
| mode). I like the feature (so I will keep it on by default),
| but I'd like to have a few modes for it depending on my working
| context (specifically tuning of update frequency at separation
| characters [space, comma], end of statement
| [semicolon/newline], and injections [paste/autocomplete]).
| hamilton wrote:
| Great feedback! Thanks. We agree w/ the red errors. It's not
| helpful when it feels like your editor is screaming at you.
| theLiminator wrote:
| Curious if there has been any thought given to open sourcing
| the UI? Of course there's no obligation to though!
| hamilton wrote:
| We do have plans. It's a question of effort, not business /
| philosophy.
| rastignack wrote:
| It's good to know it. I live in a heavily regulated
| workplace and our data usage is constantly monitored.
|
| Good to know a totally offline tool is being considered.
|
| Thanks for the great tool BTW.
| strgcmc wrote:
| This is probably stupid, but at the hope of helping others
| through exposing my own ignorance -- I'm having trouble
| actually installing and running the preview... I've downloaded
| the preview release duckdb binary itself, then when I try to
| run "duckdb -ui", I'm getting this error:
|
| Extension Autoloading Error: An error occurred while trying to
| automatically install the required extension 'ui': Failed to
| download extension "ui" at URL "http://extensions.duckdb.org/00
| 69af20ab/osx_arm64/ui.duckdb_..." (HTTP 403) Extension "ui" is
| an existing extension.
|
| Is it looking to download the preview version of the extension,
| but getting blocked/unauthorized (hence the 403 forbidden
| response)? Or is there something about the auto-loading
| behavior that I'm supposed to disable maybe?
| carlineng wrote:
| 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!
| ryguyrg wrote:
| wohoo! glad you noticed that. Hamilton is amazing.
| wodenokoto wrote:
| Is that talk available online?
| carlineng wrote:
| Not yet, but I believe the DataCouncil staff recorded it and
| will post it to their YouTube channel sometime in the next
| few weeks: https://www.youtube.com/@DataCouncil/videos
| XCSme wrote:
| I hope this doesn't work with DELETE queries.
| ryguyrg wrote:
| ROFL
| codetrotter wrote:
| ROFL FROM jokes WHERE thats_a_new_one;
| falcor84 wrote:
| Maybe in the next version they could also implement support for
| DROP, with autocorrect for the nearest (not yet dropped) table
| name.
| clgeoio wrote:
| LLM powered queries that run in Agent mode so it can answer
| questions of your data before you know what to ask.
| XCSme wrote:
| That's actually not a bad idea, to have LLM autocomplete
| when you write queries, especially if you first add a
| comment at the top saying what you want to achieve:
|
| // Select all orders for users registered in last year, and
| compute average earnings per user
|
| SELECT ...
| krferriter wrote:
| DELETED 0 rows. Did you mean `where 1=1`? (click accept to
| re-run with new where clause)
| munk-a wrote:
| Or, for extra fun, it auto completes to DROP TRIGGER and just
| drops a single random trigger from your database. It'll help
| counter automation fears by ensuring your DBAs get to have a
| wonderful weekend on payroll where, very much in the easter
| spirit, they can hunt through the DB looking for the one
| thing that should be there but isn't!
| falcor84 wrote:
| Wow, that's perhaps the most nefarious version of chaos
| engineering I had ever heard of. Kudos!
| crmi wrote:
| Young bobby tables at it again
| worldsayshi wrote:
| Can't it just run inside a transaction that isn't committed?
| matsonj wrote:
| for clarity: Instant SQL won't automatically run queries that
| write or delete data or metadata. It only runs queries that
| read data.
| d0100 wrote:
| And this is a happy coincidence that json_serialize_sql
| doesn't work with anything but select queries
| ayhanfuat wrote:
| CTE inspection is amazing. I spend too much time doing that
| manually.
| hamilton wrote:
| Me too (author of the post here). In fact, I was watching a
| seasoned data engineer at MotherDuck show me how they would
| attempt to debug a regex in a CTE. As a longtime SQL user, I
| felt the pain immediately; haven't we all been there before?
| Instant SQL followed from that.
| RobinL wrote:
| Agree, definitely amazing feature. In the Python API you can
| get somewhere close with this kind of thing:
|
| input_data = duckdb.sql("SELECT * FROM read_parquet('...')")
|
| step_1 = duckdb.sql("SELECT ... FROM input_data JOIN ...")
|
| step_2 = duckdb.sql("SELECT ... FROM step_1")
|
| final = duckdb.sql("SELECT ... FROM step_2;")
| sannysanoff wrote:
| 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.
| datadrivenangel wrote:
| What is q-SQL?
| indeyets wrote:
| https://code.kx.com/q/basics/qsql/
| cess11 wrote:
| Maybe they're busy so it might be faster if you do it instead.
| makotech221 wrote:
| Delete From dbo.users w...
|
| (129304 rows affected)
| CurtHagenlocher wrote:
| The blog specifically says that they're getting the SQL AST so
| presumably they would not execute something like a DELETE.
| hamilton wrote:
| Correct. We only enable fast previews for SELECT statements,
| which is the actual hard problem. This said, at some point
| we're likely to also add support for previewing a CTAS before
| you actually run it.
| buremba wrote:
| I remember your demos of visualizing the CTEs of a huge
| query in the editor. I'm looking forward to trying it!
| makotech221 wrote:
| Cool. Now, there's this thing called a joke...
| wodenokoto wrote:
| 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.
| 1egg0myegg0 wrote:
| It is already available in the local DuckDB UI! Let us know
| what you think!
|
| -Customer software engineer at MotherDuck
| ukuina wrote:
| Does local DuckDB UI work without an internet connection?
| wodenokoto wrote:
| I'm pretty sure it doesn't. My understanding is it gets
| downloaded at startup and then runs offline.
|
| Kinda like regex101, draw.io or excalidraw.
| jephly wrote:
| (DuckDB UI developer here)
|
| It doesn't currently - the UI assets are loaded at runtime
| - but we do have an offline mode planned. See
| https://github.com/duckdb/duckdb-ui/issues/62.
| mritchie712 wrote:
| 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": [] }
| ] } } ]
| hamilton wrote:
| Indeed, we are! We worked with DuckDB Labs to add the
| query_location information, which we're also enriching with the
| tokenizer to draw a path through the AST to the cursor
| location. I've been wanting to do this since forever, and now
| that we have it, there's actually a long tail of inspection /
| debugging / enrichment features we can add to our SQL editor.
| krferriter wrote:
| This is a very cool feature. I don't know how useful it is or
| how I'd use it right now but I think I am going to get into
| some benchmarking and performance tweaking soon and this could
| be handy.
| RobinL wrote:
| Can you go the other way? (E.g. edit the above and turn it back
| into SQL string)
|
| I've used sqlglot to do this in the past, but doing it natively
| would be nice
| hamilton wrote:
| it can, but it doesn't format. You can even run the ast!
| hk1337 wrote:
| 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?
| hamilton wrote:
| You can use any variation of DuckDB valid syntax that you want!
| I prefer to put from first just because I think it's better,
| but Instant SQL works with traditional select __ from __
| queries.
| ltbarcly3 wrote:
| Yes it comes from a desire to impose intuition from other
| contexts onto something instead of building intuition with that
| thing.
|
| SQL is a declarative language. The ordering of the statements
| was carefully thought through.
|
| I will say it's harmless though, the clauses don't have any
| dependency in terms of meaning so it's fine to just allow them
| to be reordered in terms of the meaning of the query, but
| that's true of lots and lots of things in programming and just
| having a convention is usually better than allowing anything.
|
| For example, you could totally allow this to be legal:
| def for x in whatever: print(x)
| print_whatever(whatever):
|
| There's nothing ambiguous about it, but why? Like if you are
| used to seeing it one way it just makes it more confusing to
| read, and if you aren't used to seeing it the normal way you
| should at least somewhat master something before you try to
| improve it through cosmetic tweaks.
|
| I think you see this all the time, people try to impose their
| own comfort onto things for no actual improvement.
| whstl wrote:
| No, it comes from wanting to make autocompletion easier and
| to make variable scoping/method ordering make sense within
| LINQ. It is an actual improvement in this regard.
|
| LINQ popularized it and others followed. It does what it
| says.
|
| Btw: saying that "people try to impose their own comfort" is
| uncalled for.
| ltbarcly3 wrote:
| In that case you are just objectively incorrect, you can
| build a far, far more efficient autocomplete in the
| standard query order. I will guess something like half as
| many keystrokes to type the same select and from clauses.
| You are imagining a very niave autocomplete that can only
| guess columns after it knows the tables, but in reality you
| can guess most of the columns, including the first one, the
| tables, and the aliases. Names in dbs are incredibly
| sparse, and duplicate names don't make autocomplete less
| effective.
|
| If you are right about why they did it its even dumber than
| my reason, they are changing a language grammar to let them
| make a much worse solution to the same problem.
| ltbarcly3 wrote:
| This is such a bizarre feature.
| hamilton wrote:
| What about it is bizarre?
| pixl97 wrote:
| It's probably different for duckdb, but from something like
| Microsoft SQL tossing off these random queries at a database
| of any size could have some weird performance impacts. For
| example statistics on columns you don't want them on,
| unindexed queries with slow performance, temp tables being
| dumped out to disk, etc.
| hamilton wrote:
| I agree; one thing that is neat about Instant SQL is for
| many reasons, you can't do this with in any other DBMS. You
| really need DuckDB's specific architecture and ergonomics.
| thenaturalist wrote:
| On first glance possibly, on second glance not at all.
|
| First, repeat data analyst queries are a usage driver in SQL
| DBs. Think iterating the code and executing again.
|
| Another huge factor in the same vein is running dev pipelines
| with limited data to validate a change works when modelling
| complex data.
|
| This is currently a FE feature, but underneath lies effective
| caching.
|
| The underlying tech is driving down usage cost which is a big
| thing for data practitioners.
| potatohead24 wrote:
| It's neat but the CTE selection bit errors out more often than
| not & erroneously selects more than the current CTE
| hamilton wrote:
| Can you say more? Where does it error out? Sounds like a bug;
| if you could post an example query, I bet we can fix that.
| jpambrun wrote:
| 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..
| hamilton wrote:
| Definitely something we want too! (I'm the author / lead for
| the UI)
| RyanHamilton wrote:
| Local markdown file based sql notebooks:
| https://www.timestored.com/sqlnotebook Disclaimer: I'm the
| author
| crazygringo wrote:
| 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!
| Noumenon72 wrote:
| The article says it does subqueries:
|
| > Getting the AST is a big step forward, but we still need a
| way to take your cursor position in the editor and map it to a
| path through this AST. Otherwise, we can't know which part of
| the query you're interested in previewing. So we built some
| simple tools that pair DuckDB's parser with its tokenizer to
| enrich the parse tree, which we then use to pinpoint the start
| and end of all nodes, clauses, and select statements. This
| cursor-to-AST mapping enables us to show you a preview of
| exactly the SELECT statement you're working on, no matter where
| it appears in a complex query.
| hamilton wrote:
| You should read the post! This is what the feature does.
| geysersam wrote:
| > What would be helpful would be to be able to visualize
| intermediate results -- if my cursor is inside of a subquery,
| show me the results of that subquery.
|
| But that's exactly what they show in the blog post??
| jakozaur wrote:
| 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
| metadata wrote:
| Google SQL has it now:
|
| https://cloud.google.com/blog/products/data-analytics/simpli...
|
| It's pretty neat: FROM mydataset.Produce
| |> WHERE sales > 0 |> AGGREGATE SUM(sales) AS
| total_sales, COUNT(\*) AS num_sales GROUP BY item;
|
| Edit: formatting
| ryguyrg wrote:
| note that DuckDB allows that reverse ordering (FROM-first)
|
| FROM table SELECT foo, bar WHERE zoo="goo"
| viggity wrote:
| it makes intellisense/autocomplete work a hell of a lot
| easier. LINQ in dotnet does the same thing.
| crooked-v wrote:
| I suspect you'll like PRQL: https://github.com/PRQL/prql
| hamilton wrote:
| Obviously one advantage of SQL is everyone knows it. But
| conceptually, I agree. I think [1]Malloy is also doing some
| really fantastic work in this area.
|
| This is one of the reasons I'm excited about DuckDB's upcoming
| [2]PEG parser. If they can pull it off, we could have
| alternative dialects that run on DuckDB.
|
| [1] https://www.malloydata.dev/ [2]
| https://duckdb.org/2024/11/22/runtime-extensible-parsers.htm...
| wodenokoto wrote:
| I haven't tested but I believe there's a prql extension for
| duckdb
| tstack wrote:
| The PRQL[1] syntax is built around pipelines and works pretty
| well.
|
| I added a similar "get results as you type" feature to the
| SQLite integration in the Logfile Navigator (lnav)[2]. When
| entering PRQL queries, the preview will show the results for
| the current and previous stages of the pipeline. When you move
| the cursor around, the previews update accordingly. I was
| waiting years for something like PRQL to implement this since
| doing it with regular SQL requires more knowledge of the syntax
| and I didn't want to go down that path.
|
| [1] - https://prql-lang.org [2] -
| https://lnav.org/2024/03/29/prql-support.html
| mritchie712 wrote:
| there's a PRQL extension for duckdb:
|
| https://community-extensions.duckdb.org/extensions/prql.html
| RyanHamilton wrote:
| If you want to get started with prql check out qstudio
| https://www.timestored.com/qstudio/prql-ide it allows running
| prql easily against mysql postgresql duckdb etc
| Vaslo wrote:
| I moved from pandas and SQLite to polars and DuckDB. Such an
| improvement in these new tools.
| xdkyx wrote:
| Does it work as fast with more complicated queries with
| joins/havings and large tables?
| porridgeraisin wrote:
| This is just so good. I wish redash had this...
| jwilber wrote:
| 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.
| almosthere wrote:
| 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.
| motoboi wrote:
| 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).
| ergest wrote:
| There's an extension for that
| https://github.com/ywelsch/duckdb-psql
| Philpax wrote:
| Also https://github.com/ywelsch/duckdb-prql (by the same
| author!)
| gervwyk wrote:
| Nothing comes close to the power of mongodb aggression
| pipelines.. when used in production apps it reduces the amount
| of code significantly for us by doing data modeling as close as
| possible to the source
| hantusk wrote:
| CTEs go a long way towards left to right readability while
| keeping everything standard SQL.
| gitroom wrote:
| 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?
___________________________________________________________________
(page generated 2025-04-24 23:00 UTC)