Show HN: Django SQL Explorer | Hacker News Reader

Show HN: Django SQL Explorer

github.com

14 pointsnumlocked17 comments

This looks great numlocked! Excited to give it a whirl.

I spend a lot of time writing intranet CRUD apps and these kinds of tools can be really useful to give your users access to the raw data so they can export to csv & do their own analysis in Excel (or whatever their tool of choice).

Thanks a bunch for open sourcing it!

Glad you'll find it useful! One thing I've found really nice is the having the SQL and the query results right next to each other means that data is somewhat self-documenting. Even for consumers of the data who aren't fluent in SQL, most of the time they can pick out what fields and filters are being applied which cuts down on the back and forth of "wait, what is that field exactly" that you get when emailing CSV files back and forth.

This is pretty cool. It's kind of funny how after all the admin interfaces and fancy forms, people always end up wanting to just write their own custom SQL.

On the security front, the SQL blacklist definitely has to go. It's a false sense of security (ex: string concat + dynamic execution gets around it). The suggestion to use a read only user is a good one but even better is to use a read only database (ex: a Postgres replication slave).

Have you checked out JackDB? (http://www.jackdb.com/ full disclosure: I'm the founder) It's a full featured database client that runs entirely in your browser.

+1 to read only, fortunately databases are very good at enforcing this thing, unfortunately the users of databases generally less so.

RE: circumventing the blacklist, I think immediately of accessing a function with postgresql aka select my_destructive_function();

Yep, no disagreement there :)

The blacklist has no chance of defending against malicious users. Luckily (at the moment) we are using this purely internally and the blacklist is really just preventing people from shooting themselves in the foot. We're moving to a read-only user role shortly, and the suggestion to go with a read-only db is a great one.

Are you able to login as different database users?

I would love to try using this with Schemaverse (http://schemaverse.com) but most frameworks I have seen have trouble with multiple database roles/connections.

Yep - you sure can. I haven't released it to PyPi yet, but it's committed to master and tested. You just have to override this setting:

    EXPLORER_CONNECTION_NAME

And give it the name of the django connection you want to use. We are in the process of moving from Heroku Postgresql (which only supports a single DB user) to Amazon RDS Postgres specifically for this capability. It will ensure that users of SQL Explorer have read-only access (something that is currently enforced by a SQL blacklist - a risky approach)

Neat, thanks for the info! I will have to give this a try then.

Luckily, I don't need to care about read-only or blacklisted queries, the entire application is already built into the database layer.

Gotcha - in that case you'll want to turn off the blacklisting feature which you can do by overriding a setting. It's all documented in the readme :)

Unfortunately there is NOT currently a way via configuration to allow the general public to access queries - it's locked down the Django admins by default. But it's just a matter of removing some view decorators to change that. Maybe something for me to add in a future version...

Anyway, let me know if you need any help.

Looks interesting, been thinking of implementing one for our product (jvm based). I might consider a port, though not sure how much effort it would take.

Thanks!

Thanks! For what it's worth, this is heavily based on Stack Exchange's Data Explorer (in terms of functionality - not implementation). I'd estimate it took me 60 hours to build the Django version, to the point you see in github.

Hey there HN - over the last few weeks I wrote this Django app to help share data across our start-up (ePantry.com). It's our first significant open source contribution, and I hope some folks find it useful. I'd love to hear your feedback.

And I should mention this is heavily inspired by Stack Exchange's Data Explorer (which is credited in the readme file). We used an implementation of Data Explorer at my previous company (shout out to HN user moserware who came up with that idea) and it was a fantastic way to quickly share data and answer questions. We would wonder how a particular site metric was performing, and an engineer could quickly post a link to a query in hipchat - and we could talk about real data quickly instead of speculation.

will be taking it for a spin!

Another tool, different, but also great for sharing, you may enjoy: http://htsql.org/

I took a look at this before I undertook building SQL explorer, but it looked like overkill and I didn't want to learn a new query language and introduce a learning curve to every employee who might write queries. But it does look fairly full featured. Have you used it? Is it low-friction enough that people want to use it?

I have used it.

Adoption was good especially for simple queries, people were less focused on the language, and more focused on exploring/modifying examples.

For people who are already familiar with SQL and as you said, less interested in the query language, it may be almost immediately frustrating.

I can absolutely see circumstances where it would be overkill, I have never tried to use it as a performance critical tool, just as more an easy thing to slap on to ease access, two aspects that I have found useful on more than one occasion:

1. HTRAF toolkit is convenient for very basic visualization (depending upon the audience this can be very useful to have at hand quickly)

2. multiple output formats (json in particular) has been helpful in terms of quickly hacking together prototypes in the past

Wow this is going to save me so much time!!!!!!

Reply on news.ycombinator.com