In our daily routine at Springest, we run all sorts of monitoring queries directly on our database, independently of our actual application. To help us get things done we like to have up-to-date insight in the current status of everything we do.
These business intelligence queries vary from getting insights about conversions to checking how many users have verified their identity for a review with Facebook, to quickly showing whether any bills got paid recently. Of course we have built some internal tools for the most important things, but a few times a week someone who’s not a developer (in our case sales, marketing, or support) will come up with some question that requires an answer in the form of an SQL query.
|[Download||Fork||Watch this on Github…](https://github.com/Springest/ASQ)##No more “Hey man, could you just quickly run this query for me…”|
This caused a lot of (repeat) interactions between developers and a few other people that are handy with SQL, and those who are not so lucky… The point is that all those queries can take a lot of time to create, manage, run, and talk about.
That’s why we created ASQ. ASQ is a… database query management / bookmarking tool (let us know how you would describe it please ;)) that allows you to store your own queries. Once stored, anyone can run a query and get the results on the screen immediately. The user can then sort, export, and search within the query. You can also parameterize your queries so that the user can easily enter parameters to customize the results (for example with a date picker).
This is what it looks like:
The DB button on the left and the input box give you quick access to all queries on the databases that ASQ is configured to use. In the middle there are two maintanance buttons to add or edit a query, set arguments you’d wish to pass to the query, and make use of the export function.
##So what else is cool about ASQ?
*Easy to search for queries: users can use a dropdown with autocomplete ( screenshot) that filters on the name you’ve given the queries.
*Infinite scroll getting you more results after the first 100 results, so the interface is always snappy.
*Export to CSV , and we’d like to add export to Google Apps soon (but maybe someone else will get to it first? See below ;))
*SQL Syntax highlighting for your editing pleasure.
*Parameterize your query so that non-geek users can easily change certain variables like a string or a date (range). See the example below for a query including an argument.
*Autocomplete on those arguments to make it even easier to customize queries for the non-geeks without help. See screenshot.
*Search within results quickly filters your query results on every column. Here’s a simple query we’ve made for our support people to have a quick list of URLs for a certain client that can be chosen with autocomplete on a database field of the query maker’s choice:
WHERE i.name = ‘[STRING:Institute;autosuggest=institutes.name]’ AND t.status = ‘published’ As you see, we’ve hacked some HTML in there to make the result even more usable for the non-geek. It’s a bit dirty but we’ve found that doing it this way is easy and fast. After a few weeks of using and improving ASQ, we already have over a 100 queries in it. For us, it turns out to be a great alternative to phpMyAdmin and even Sequel Pro.
We’ve found that it’s a good idea to run ASQ on a mirror of your live database so there’s no chance of slowing down your app, while still being able to use queries that need realtime results.
##Feel free to use and improve ASQ
ASQ is released under the GPLv3 license. It was built on the Sinatra framework for Ruby, using MySQL for the database. You can clone it from Github, or fork it and play with it yourself. If you experience any issues, you can file an issue on Github.
And that’s basically it. Have fun querying away and please let us know what you think about it in the comments!
Want to work at the biggest learning source in the world?