Sleepless nights. Long weekends. Adrenaline-fueled commits. These are just some of the things we've experienced while working on our latest module, Query UI.
What is QUI?
Query UI (QUI) is Bonify's latest module to be released on Drupal.org. In basic terms, the module provides a graphical user interface to run SELECT queries on your database.
Why Should I Care?
QUI is designed to solve a pretty specific problem: getting information out of the database in different formats. We built QUI as a report generation tool; a simple and easy way to build queries and display data in a variety of popular formats. QUI provides an interface that anyone familiar with SQL queries will instantly recognize. You have the ability to SELECT data, JOIN on other tables, choose and reorder columns, use expressions, GROUP BY, ORDER BY, WHERE and HAVING conditions, and more.
QUI provides some nice features to go along with your queries, such as the ability to serve the data as a callback, limit access by user role, and change the output format of the data. Of course, all QUI Reports are Drupal entities, so they are easily accessible and editable in code. They can also be exported, imported and maintained in code, so keeping Reports in sync across different environments is a breeze.
Sounds Like Views
We know that people will compare QUI to Views. However, QUI has a very different purpose in life than Views. QUI is built from the ground up as a data retrieval tool. It gets data and displays that data in different formats. Some of the formatters that come with QUI include JSON, Table, CSV, and pipe delimited. That data can be accessed (like an API), at a specific URL for that report, or downloaded and imported into Excel.
Views' purpose in life is to display Drupal entities with field formatters, using entity display modes. For example, Views is great for creating a list of blog articles for presentation to your visitors. If, however, you want to simply display a table of information that pulls from multiple database tables with specific columns and specific conditions, Views is overly cumbersome and complex (some might say bloated).
If you are not convinced that QUI will be better for displaying reports or extracting data from Drupal than Views, then it's probably not the right solution to your problem. If you've fought with Views to get data out of your database for reporting, API development or other administrative purposes, QUI would have been a better fit.
Packed With Features
QUI is packed with features, both in the admin interface, and behind the scenes in code. With QUI, you can export and import reports from different environments. With QUI, you can query data from another database. With QUI, you can retrieve a simple query object, modify it, run the query and use the results with a few lines of code. With QUI, you can quickly provide API callbacks for retrieving specific data from your site, or from any database you can access with your settings.php configuration. With QUI, you can quickly view and download reports in a few clicks. With QUI you can migrate data in combination with the Migrate module without having to write the queries by hand. With QUI you can fetch Report data via the command-line using its Drush integration. The possibilities seem endless...
With Great Power...
QUI isn't for everyone. It's designed for the person who understands SQL queries and recognizes that they are responsible for the data they expose with the queries they create with it. QUI doesn't have any bumpers or training wheels. If you want to make a report accessible to anonymous users with raw user data, that's your business. Only give QUI access to trusted user roles, and use your judgment when creating reports!
Have questions? Let us know your thoughts, give us feedback, submit feature requests, and help us make QUI better. We're hoping to simplify Drupal reporting and integrate QUI with other popular modules.
We are currently working on integrating QUI with the Migrate module, allowing you to migrate data by building your SQL queries with QUI!
Stay tuned for additional blog articles about using QUI in code, creating your own data formatters, and building your migrations with QUI.