SqlPad: Run SQL in your browser and chart the results | Hacker News Reader

SqlPad: Run SQL in your browser and chart the results

rickbergfalk.github.io

102 pointskissgyorgy30 comments

Hi Hacker News! So this is where the extra activity is from :) honored to be posted.

SqlPad was something I built to scratch my own itch and was sort of a self-challenge. I haven't done much with it lately, but a few users have contributed some additions back and have been helping with the project.

Just a heads up if you explore the code - I'm not a Node.js developer by trade and this is sort of a hobby for me as a SQL Server developer/DBA.

I'm flattered by the comments here. Really inspiring to get back to work on this.

Also wanted to add - If SqlPad isn't quite what you're looking for but want to visualize SQL results check out these other projects and solutions:

Metabase (http://www.metabase.com/)

Redash (https://github.com/getredash/redash)

Wagon (https://www.wagonhq.com/)

Periscope Data (https://www.periscopedata.com/)

Mode Analytics (https://modeanalytics.com/)

This looks really nice! I'm building something similar but as a desktop tool (using Qt). There really seems to be a dearth of desktop SQL tools for data analysts, as opposed to the myriad tools that support developers (Toad, Sequel Pro, Navicat). One thing I'm still trying to figure out is whether there is a market for this type of tool among business analysts/data scientists.

There is.

My view is this: By the time I teach power users (at large companies) how to use tools like Microstrategy, it becomes clear that it would have been quicker just to teach them SQL.

I think there's particular group you have to aim for:

The group that are at large companies, doing sophisticated analysis of data, who don't know how to code, but WANT to build their own reports. This is the sweet spot. Most of the market is more geared towards super simple drag and drop for users who are more interested in getting a canned report to their boss each Monday. Those aren't going to be a market for this.

That's the group I'm currently targeting (users who are fairly sophisticated in the types of analysis they can do with SQL, but can't code or only minimally in python/R). Do you think they would be more likely to go with a desktop solution than a hosted web solution? There's a number of startups in the space (Looker, Mode Analytics, Periscope, Chartio) but they are all externally hosted and so seem to target the types of small companies that already have their data in the cloud or are comfortable providing access to their internal databases.

By the way, would it be possible to ask you a few questions? My contact info is in my profile if you are open.

Yes, I'd definitely like to discuss with you.

Another vector might be to build a tool that allows programmers to build ad-hoc reports, and then hand it off to tech-savvy non-programmers. If the tool makes it easy to share/fork queries (I'm envisioning something like gist in an intranet), it would be a very gentle path for said non-programmers to ease into SQL.

Very nice. I've been building on something similar myself, but this looks like you came quite far.

Further to this - Does it have the ability to render pivot tables from two-dimensional results? Or cohort charts? These are visualisations that I find very useful.

Thanks troels! It doesn't but it's something we could keep in mind. Feel free to open an issue on GitHub to make the request. Do you have any examples of what you have in mind?

For a pivot, simply take a resultset that gives three columns and map the first to the X axis, the second to the Y and the third as values. It's very often that I find myself pulling that kind of data out in reports.

Making a general purpose cohort chart (https://blog.modeanalytics.com/cohort-analysis-helps-look-ah...) is probably a bit harder, since it requires a relatively complex query to generate the data. Would probably work best with a sort of macro or template system in place.

One thing I am missing with this and similar tools like re:dash is the ability to specify placeholders that get filled by information from a dropdown or date selector.

That simple feature opens up usage to non-developers who just need to view data from another time frame or filter by a certain country for example.

Dropdowns could be populated by a seperate sql query.

SQL charts straight from SQL queries with parameters driven from forms: http://www.sqldashboards.com/database/mysql-live-charting Skip to about 40 seconds.

Probably off topic, but: Do you have access to install node.js and access to the internet (to do the npm thing) where you'd like to run this kind of app (I'm assuming you keep your data in some kind of secure environment without outside internet access)?

Who has the clearance to install a runtime, npm, a bunch of dependencies (who knows what), for an SQL tool?

Maybe I just work some where that is more strict than most places? Or do people install this on their corporate machine (assuming they have admin rights??) and access the internal SQL server from there?

We have full admin rights on our computers in our company. The benefits outweigh the risks as long as the users are remotely competent.

The benefits outweigh the risks as long as the users are remotely competent.

...and you trust them to Do No Harm

You employ them, you give them access to your internal network, you give them laptops, keys, even free unlimited drinks(!). You already trust them, I suppose.

For an offline tool with GNUplot and LaTeX output, check out https://github.com/bingmann/sqlplot-tools/ -- its intended use case is experimental evaluation (you output "RESULT key=val key2=val2" lines and the tool creates the DB schema for you and imports the data, then you run SQL queries to plot), but you can just as well use it with existing tables. It's a pretty neat tool that I use a lot.

Our journey started very similar http://www.infocaptor.com

It took us 10 years though from this stage

BTW, SqlPad uses tauCharts library to draw charts http://www.taucharts.com

TauCharts has been an amazing library to work with. Its API fits so well with the mental model of how the SQL results map to visualizations. I can't recommend this charting library enough. It's like a mini tableau built on top of d3.

Is it possible to connect to a remote server? Didn't find anything in the docs.

I want to know this as well. I work on several different servers running SQL databases. I would like to install this on my dev machine and access my own local sql server database, the dev servers db and several others that I access via a VPN.

I've worked on something similar that allows specifying a hostname/port for the database (mysql/postgresql/mssql/h2..) http://www.sqldashboards.com/database/mysql-live-charting

Video demonstrates creating a full report with multiple chart types in 3 minutes.

If you're working with Postgres then check out https://github.com/sosedoff/pgweb for connecting to a remote server. It supports table data viewing as well as SQL queries, but doesn't provide the same chart capabilities.

Looks good! Could it be a free alternative for tools like Periscope (https://www.periscopedata.com/)?

Wow, had this set up and working in minutes on my local machine.

Needs some polish in a few spots but great work. Thanks.

Thanks! I'd love to hear your thoughts. feel free to send them to me via email or github issue

Looks very nice

So, phpMyAdmin basically?

it does charting now? and servers beside mysql?

Haven't really used it in years since I switched most of my servers to postgresql

No, not at all.

Reply on news.ycombinator.com