My project was a collaboration with my good friend Ejektaflex.

We are very happy to finally present: some kind of database explorer thing.

This is a tool that allows you to explore relational databases in a completely new way. I've always been frustrated by the experience of writing SQL queries - partially because of the syntax, but mostly because you can't actually see the data when you're writing them.

Databases are great at storing information and making that information accessible. The way we access that data shouldn't be so cumbersome.

Here's a video of our program in action:

The tool is designed to make the data visible at every step of the way. Every node is a valid query. You can click on any node to view the query at that point, and then use that information to build the next step of the query.

Node graphs like this are pretty good at dealing with data flow, but wire management is always a pain. To mitigate this, you can snap nodes together - this allows an entire group of nodes to be moved at once, without changing the results. This example is exactly the same as the previous image:

If you create a query you find useful, you can add a preview node to keep those results around. Previews can stand alone, or be snapped onto a node for quick reference.

This system allows you to build up an entire workspace of interesting queries. Queries that would normally be horribly complex are now trivial. And you can view the results of lots of queries at once - in the image at the top of this post, you can see that we have queried all of Bob Fawcett's films, and found both the longest and shortest films he starred in.

And at each step of the way, it generates reasonably nice SQL that you can copy into other tools:

Thoughts

We're both really happy with how this project turned out! Our theory was that a tool like this would allow us to make complicated queries far more easily than in SQL alone. And so far it seems we were right!

There are so many different ways we could expand on this in the future. We could allow you to save queries as a sort of "pseudo-view" and have a dedicated node for them. We could add charts and visualizations, so that there are more ways to view the data than just a table. We could integrate with the database's query planner and show info about query costs - in a way much easier to read than your typical EXPLAIN ANALYZE.

we could also add undo and redo lol

A more robust version of this tool would have to deal with slow databases. For the jam we just used SQLite, with the pretty small Sakila sample database. Real databases might take quite a while to perform any specific query. But, for every operation we perform, we should be able to instantaneously calculate the resulting schema, and possibly even generate fake data to use in previews. There are probably lots of ways you could make the process interactive even if some operations are slow.

Overall I think the project was a success, and I hope we can continue to work on this project in the future!

Download

The source code can be downloaded at https://github.com/bvisness/SQLJam/.

You will need a recent version of Go. Then just run go run main.go.

Also, shout out to Ramon Santamaria for making raylib. It was an absolute joy to use for the jam!

I don't work often with databases, and when I do I never remember the SQL syntax, so this would be nice to have available.

Do you think it would be hard to connect to a local or online MySQL server ? If only to fetch the tables information to be able to build requests, and only on demand actually query table data ?

It probably wouldn't be too difficult, but the real problem is that in its current state it can only make queries synchronously. Also I'm pretty sure there are bugs that get it stuck making a query every frame. 😛

But overall the database connection is the easy part. I think it would be relatively easy to make an interface that could work for many different database engines. The only reason we chose SQLite for the jam was because it required so little setup - overall, all the app needs to do right now is make SQL queries and get strings back.

Also, the app currently wedges a LIMIT 1000 onto every query to avoid problems with large joins. This might not really be a problem in practice, and the generated SQL to copy to other programs has no limit. So it would probably still work well for generating SQL for use in other tools.