BlocklySQL: A new block-based editor for SQL | Hacker News Reader

BlocklySQL: A new block-based editor for SQL

dbinf.informatik.uni-wuerzburg.de

128 pointsleeuw0138 comments

We have been using something like this based on Blockly in production for over seven years now to build dashboards. GIF demo: https://www.dialogic.nl/wp-content/uploads/2018/02/demo1.gif

Our users (researchers) had no problems learning the concepts of querying but didn't want to be bothered learning SQL syntax - hence we built this (also, our system does quite a bit of magic behind the scenes to allow mixing e.g. CSV data with data from a database, and also allows for things not easily done in SQL, e.g. pivot tables, so it's not just SQL).

As someone who works in BI I am mighty impressed! I don't think I have seen a tool pack as much power in a fairly easy to follow manner. My 8 year older daughter picked up Scratch[0] after the briefest of intros. You application reminds of Scratch.

[0]https://scratch.mit.edu

Probably it's made with the same technology used to build Scratch, namely Blocky (https://github.com/google/blockly)

yeah I recognize the UI from Scratch. The puzzle pieces representing logic and state look the same. It's cool to see it being used elsewhere.

Is that coffee exemple from the GIF above a production screenshot? It looks very demo-i

Yes, the demo was definitely made using the production system! (Although usually our ‘puzzles’ are quite a bit more complicated than this simple coffee example. Although more often than you expect, simply publishing a small CSV with a bit of filtering is sufficient!)

Correct!

Is this gif using BlockySQL? Doesn't look like it though, since there are many more options available. And it looks more powerful than BlocklySQL.

Is the source available?

What is the Deutsch Limit?

A term made up by Fred Lakin describing a comment Peter Deutsch made at a VL talk by Scott Kim and Warren Robinett about a visual machine language they had invented.

Deutsch said something like:

> "Well, this is all fine and well, but the problem with visual programming languages is that you can't have more than 50 visual primitives on the screen at the same time. How are you going to write an operating system?"

This points out the obvious density advantage of text. This barrier has become known as the "Deutsch Limit," stated as:

> The problem with visual programming is that you can't have more than 50 visual primitives on the screen at the same time.

...

This is clearly a problem with visual representations. However, it is not immediately clear to me that a similar limit does not also exist in textual languages.

When textually programming I frequently use an Emacs window with about 50 lines of text on my 19" monitor. Anyone older than about 35 complains that they cannot read the text because the font is too small. I use a lot of whitespace in my programs, so we might assume that the 50 lines in the editor contain 40 meaningful lines. Most common programming styles dictate limiting the number of "primitives" or statements to one or two per line, giving my textual screen at most 80 primitives.

http://www.faqs.org/faqs/visual-lang/faq

I think there is, but not necessarily in number of symbols. One story this brings to mind is that this is partly the reason why K code is so terse; Arthur Whitney hates scrolling to read source code.

I get the feeling that a successful visual language really requires the ability to group multiple primitives into a single visual element (building a chip). I just get the feeling that a lot of visual languages are looking at the implementation of instruction and not programming in the large.

I like the idea and the look of the whole thing but I couldn't figure out how to easily

SELECT * from customers

so... I'm kind of bummed out. The blocks seemed to not stick together. They'd just "island" and sit by themselves. I finally managed to push them together for a "SELECT FROM customers" combination of blocks. But there was a "clause" thing stuck on the screen.

I felt like this should be a really quick thing to do.

After all that I then got:

Ungültige Abfrage: Unknown table 'web1312.customers'

so the table I selected didn't even exist. Possibly.

Sadness.

(Maybe its a firefox thing? Can I blame firefox?)

The * is hiding under "Tables & Attributes"; choose the yellow box with * in it, then snap it to the end of the SELECT block, then snap the SELECT block on top of a FROM block which has a yellow "customers" block snapped into it. Voila, the "Show SQLCode" button now emits "select * from customers" (FF Beta, macOS)

I got there too after some puzzling. There's not even a distinction between attributes and table names to guide you. I got there by thinking "what would the correct syntax be". Kind of ironic.

Well, I spent 5 minutes laboriously building a query for the whole thing to hang on me when I reached the ORDER BY clause.

I actually don't see how this is better than a cheat sheet with SQL keywords and autocompletion of tables/fields.

I feel exactly the same way, but I guess people really don't want to learn super basic SQL? A solid autocompletion with automatic inventory of tables, foreign keys and indexes makes it easy enough I think. But I never succesfully converted a business person. Either they were ok to use SQL before, or they will keep wanting to click on buttons.

Suggestion: Make the block palette sections collapsable, so one see more than just one category. The way it is, it is a lot of unnecessary clicking.

Paper: https://www.researchgate.net/publication/336754811_BlocklySQ...

And the GitHub repo is here: https://github.com/nicolaipoehner/blocklysql

Very cool. I think it might benefit from some kind of context. If you could select an open piece, it could restrict the available pieces to what's valid at that point.

This looks really cool.

Anyone know of a tool that can parse a complex sql and generate a table diagram showing the various joins etc.,?

I don't think this is exactly what you described, but you may find it valuable: it's a tool that produces a visualization of a postgres query plan https://tatiyants.com/pev/#/plans/new (click "create sample plan" in top right and then "submit" to see an example)

SSMS has built-in support for generating database diagrams.

Beyond that, look for ERD generation tools.

SQLeo[0] is a Java desktop client. You can use it to build SQL statements visually or paste in a SQL statement and get a diagram. Its been around for a long time, don't be fooled by the dated look of the screenshot.

[0]https://sqleo.sourceforge.io/

Exactly what i was looking for. Thank you.

http://revj.sourceforge.net/example.html

Just tried this out, works like a charm.

Thanks so much for this. Been looking for a similar tool for a few months now.

IntelliJ IDEA Ultimate comes with a diagram functionality based on yFiles, which is quite good and I've used it several times to visualize the current database schema.

Yeah it's pretty OK, it's actually one of the least polished areas of the IDE, I'm amazed no one has devoted serious resources to a tool that can properly reverse engineer a schema and infer relationships giving you a list of "I think X and Y are related, are they?" questions to solve it.

I've inherited so many "interesting" schema's over the years and it feels like archaeology understanding the creators intent.

I used to use a tool called FlySpeed SQL Query that had a great quick SQL builder using a UI. This reminds me of it a lot (though it wasn't block based). Great concept!

This might be useful to teach beginners in our company the basics of SQL. We already use a rather "playful" approach to teach Java to them.

This was their use-case. They build it to help students learn SQL.

Completely freezes up permanently after placing a single block for me on Chrome latest, Windows.

I get a blank screen on top where I assume a diagram should be in FF 85.0b3 (64-bit)

That's weird, it works for me just fine on this exact version. Try disabling Enhanced Tracking Protection and any JS/ad blocking extensions (it works for me regardless of all that, but anyway).

Just turned off ublock, https everywhere, decentraleyes and DDG privacy essentials and still see blank space.

Interestingly enough, in chrome it's also blank, but there are some squares with some green and gray in the bottom right of the viewing window/frame.

Maybe it's a platform issue.

Um, nope. Couldn't figure out how to use the JOINs and gave up.

I failled to stick a 'select * from table' together. Could not find the star? Unsure.

Took me far too long, but this is the answer: https://imgur.com/iBfOmtO

You have to use the cog icon on the FROM to add more bits to the FROM.

Reply on news.ycombinator.com