

Show HN: Django SQL Explorer - numlocked
https://github.com/epantry/django-sql-explorer

======
randlet
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!

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

------
sehrope
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/](http://www.jackdb.com/)
_full disclosure: I 'm the founder_) It's a full featured database client that
runs entirely in your browser.

~~~
sendob
+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();

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

------
abstrct
Are you able to login as different database users?

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

~~~
numlocked
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)

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

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

------
Edmond
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!

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

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

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

~~~
sendob
will be taking it for a spin!

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

~~~
numlocked
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?

~~~
sendob
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

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

