wohoo! glad you noticed that. Hamilton is amazing.

Is that talk available online?

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]).

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/0069af20ab/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?

Curious if there has been any thought given to open sourcing the UI? Of course there's no obligation to though!

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

While I would certainly agree with you that putting the FROM clause first would be a significant improvement to SQL and was a genuine design mistake, this otherwise feels more like just wanting SQL to be less declarative and more imperative. Wanting it to be more like LINQ and less like relational algebra.

That, I think, is most developers' real sticking point with SQL. It's not object-relational impedance mismatch between their application and the data store, it's imperative-declarative impedance mismatch between their preferred or demonstrated talent. They are used to thinking about problems in exactly one way, so when they struggle to adapt to a different way of thinking about the problems they assume their familiarity is what's more correct.

I think this is why the same developers insist that XML/HTML is "just a markup language." Feeding a document into an executable to produce output isn't really significantly different than feeding imperative language into a compiler. The only real difference is that one is Turing complete, but Turning completeness is not a requirement of programming languages.

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...

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;

I suspect you'll like PRQL: https://github.com/PRQL/prql

I haven’t tested but I believe there’s a prql extension for duckdb

that syntax is horrendous.

There’s an extension for that https://github.com/ywelsch/duckdb-psql

CTEs go a long way towards left to right readability while keeping everything standard SQL.

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

Maybe in the next version they could also implement support for DROP, with autocorrect for the nearest (not yet dropped) table name.

for clarity: Instant SQL won't automatically run queries that write or delete data or metadata. It only runs queries that read data.

Can't it just run inside a transaction that isn't committed?

Young bobby tables at it again

ROFL

You can try marimo notebooks, which are stored as pure Python and support SQL cells through duckdb. (I’m one of its authors.)

https://github.com/marimo-team/marimo

Local markdown file based sql notebooks: https://www.timestored.com/sqlnotebook Disclaimer: I'm the author

Definitely something we want too! (I'm the author / lead for the UI)

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.

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;")

`from my_table`

will do the same!

We are working on how to make it easy to switch from instant sql -> run query -> instant sql

We support spatial calculations in the UI, but not spatial visualizations just yet. Thanks for the feedback!

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.

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.

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

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.

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):

I think you see this all the time, people try to impose their own comfort onto things for no actual improvement.

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.

> 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??

You should read the post! This is what the feature does.

It is already available in the local DuckDB UI! Let us know what you think!

-Customer software engineer at MotherDuck

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.

Check out livedocs.com, we built a notebook around Polars and DuckDB (disclaimer: I'm the founder)

The blog specifically says that they're getting the SQL AST so presumably they would not execute something like a DELETE.

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.

What about it is bizarre?

What is q-SQL?

Maybe they're busy so it might be faster if you do it instead.