Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Instant SQL for results as you type in DuckDB UI

motherduck.com

Post reply on HN
Print view View original

101–110 of 124 posts

Re: Instant SQL for results as you type in DuckDB UI

#101

Post by whstl »

ltbarcly3 wrote: post #80
whstl wrote: post #41

Earlier quoted context omitted.

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.

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 a…

An autocomplete that shows only the column names of the desired table BEFORE the from clause is typed by the user would require a time machine.

Sure you can do something that is close enough, but the LINQ authors were looking for precision in the autocompletion and for the LINQ query to have the same ordering as expression syntax.

The goals of this syntax are very precise and people seem to like it. Once again: calling it dumb is uncalled for.

Re: Instant SQL for results as you type in DuckDB UI

#102

Post by cess11 »

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.

Re: Instant SQL for results as you type in DuckDB UI

#105

Post by ako »

XCSme wrote: post #16
clgeoio wrote: post #11

Earlier quoted context omitted.

LLM powered queries that run in Agent mode so it can answer questions of your data before you know what to ask.

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

That already works in windsurf, I’ve created unit tests in go, where I just wrote a short comment in the unit test what data to query and windsurf would autocomplete with the full sql.

Re: Instant SQL for results as you type in DuckDB UI

#106

Post by XCSme »

ako wrote: post #105
XCSme wrote: post #16

Earlier quoted context omitted.

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

That already works in windsurf, I’ve created unit tests in go, where I just wrote a short comment in the unit test what data to query and windsurf would autocomplete with the full sql.

I mean, all LLMs do this already, but I never saw LLM autocomplete in a db tool (e.g. phpmyamdin, MongoDB Compass, etc).

Re: Instant SQL for results as you type in DuckDB UI

#107

Post by acdanger »

1egg0myegg0 wrote: post #99
acdanger wrote: post #97

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

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

Just emphasizing that the ability to display a map with geo data on it would be a killer feature for me and for many others I work with! Hope it lands on the roadmap.

Re: Instant SQL for results as you type in DuckDB UI

#108

Post by ltbarcly3 »

pests wrote: post #100
ltbarcly3 wrote: post #80

Earlier quoted context omitted.

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 a…

I don’t want to type any column names. When you start with FROM the only autocomplete suggestions available are the columns from the specific table, not the entire database. How many columns do I need to type before you can single down a single table? What if you have multiple tables with the same column names?

This is extremely easy to check. It depends on the schema.

If your tables have very heterogeneous column names, like 1 column will identify any table on average. There will be some duplicates but the median columns will be one or two, but generally you can even complete those after a few characters.

If your database has very homogenous column names you don't need to identify a single table for autocomplete to be very precise, unless there is no correlation between column name co occurence within tables. However if there is no correlation you are back to very low number of columns to identify the table.

Re: Instant SQL for results as you type in DuckDB UI

#109

Post by ltbarcly3 »

whstl wrote: post #101
ltbarcly3 wrote: post #80

Earlier quoted context omitted.

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 a…

An autocomplete that shows only the column names of the desired table BEFORE the from clause is typed by the user would require a time machine. Sure you can do something that is close enough, but the LINQ authors were looking for precision in the autocompletion and for the LINQ query to have the same ordering as expression syntax. The goals of this syntax are very precise and people seem to like it. Once again: calli…

So you want it to work this way, regardless of how well autocomplete works? Sounds like its about your personal comfort to make it work like another system you are more familiar with, which is exactly what I suggested.

It doesn't require a time machine, just a basic understanding of statistics or probability.

Re: Instant SQL for results as you type in DuckDB UI

#110

Post by NDizzle »

jakozaur wrote: post #43

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(custome…

This is the stuff nightmares are made out of. Keep that style of coding out of any project I’m involved in, please.
Post reply on HN