HTSQL is a web technology that translates the language of the web (HTTP) to the language of databases (SQL)

HTSQL is a query language developed by Prometheus Research to allow SQL queries to be passed to a database via URLs. The HTSQL software delivers this capability in the form of a web application layer that can be installed, configured and managed by both small and large organizations. The goal of HTSQL is to simplify the data access layer between databases and applications, reducing cost and effort while providing business users with the access they need and IT people with the control they require.

HTSQL vs. Other Approaches

There are dozens of ways of solving the data access problem, from vendor-specific solutions to open source software and architectural approaches like service-oriented architecture. Your current solution may be working perfectly for you. On the other hand, you may need HTSQL if these questions sound familiar:

• Can you run another report for me?
• Can someone from your team help me develop this application?
• Can’t you just give me access to the database?

Traditional two-tier web applications, like PHP or Cold Fusion, embed database queries directly into page templates. This makes applications easy to develop but hard to maintain. Locating and changing the queries can be difficult, especially for someone other than the original developer. Three-tier applications like Microsoft .NET and Ruby on Rails, or applications built with web services, address this problem by creating a business object layer, but at the cost of additional complexity. And both approaches limit end users either to hard-coded queries or the available business objects. If the users’ needs change, the developer goes back to the drawing board, along with the DBA, the system architect, the configuration manager, and the system administrator.

HTSQL maps URLs directly onto SQL, returning the output in popular formats like HTML, XML, CSV, and JSON. In many cases, this eliminates the need to develop custom web applications. Users who are familiar with the data (but not necessarily with SQL) can serve themselves, retrieving the data they want, when they want, and in the format they want. For application developers, HTSQL provides a data access tier with the best features of HTTP, including REST, error handling, compression, and encryption. HTSQL queries are highly reusable and will stand the test of time. Your development language may change in five or ten years, but it’s very likely that HTTP will still be around.

Using HTTP for your data access layer has other advantages as well. As the world’s most popular Internet protocol, HTTP is well tolerated across networks and firewalls and can be managed by a wide range of best practices and tools. The goal of HTSQL is to increase data-centered productivity without placing new burdens on your IT staff or infrastructure.

Learn from our experience

Prometheus Research developed HTSQL to support data management projects at leading research universities. Research databases are large, diverse, and prone to frequent change, and researchers need to analyze data in ways that can’t always be predicted at the outset of the project. Rather than create dozens of reports and spend hundreds of hours maintaining ever-changing application screens, Prometheus innovated HTSQL to ensure that the data access layer would remain the same even if the database and applications around it changed.

It’s worked for our clients. For nine years, Prometheus has been delivering robust data management solutions, keeping up with the evolving needs of biomedical researchers. Those researchers have also benefited from creating and sharing HTSQL queries to go where their intellectual curiosity takes them. HTSQL has helped us meet our clients’ needs and build a successful business in the process, and we think it can do the same for other organizations.

A closer look at a query

What does an HTSQL query look like? Just like any other URL. Take this example of a simple SQL statement:

SELECT * from album
WHERE name LIKE ‘summertime’

In a database of album titles, this query would return on the albums whose titles contain the word “winter.” Now let’s look at the HTSQL query:

http://musicdemo.htsql.com/album?name~'summertime'

Using this simple syntax, it’s easy to guess how to change the query for a different database table, or a different title. If you were even a little familiar with the database, you might not need to look at the documentation at all. But if you did, you would see that HTSQL supports nearly all advanced SQL functionality, including joins, selectors, and filters, and can add, update and delete records as well as select them. If you can do it in SQL, you can probably do it in HTSQL.

Respecting your database

HTSQL does not override your database security schema. In most cases, no additional configuration of the database is required. Just as for any web application, your database administrator will need to pay special attention to the roles and permissions of HTSQL users to prevent unauthorized access. HTSQL supports all the security features of HTTP, including HTTPS.

HTSQL works best with databases that incorporate good database design principles, such as foreign key constraints and referential integrity. To try HTSQL with your database, contact us about a free trial installation.

Version Information

Currently available for PostgreSQL (8.3 or higher) and Oracle Database Client (10g or higher; Oracle 9i Client), with more databases to follow shortly. PostgreSQL version requires some python packages to be installed.