HTSQL

Hyper Text Structured Query Language (HTSQL) is a schema-driven URI-to-SQL query language that takes a request over HTTP, converts it to a SQL query, executes the query against a database, and returns the results in a format best suited for the user agent (CSV, HTML, etc.) The HTSQL language is implemented on "HTSQL servers," which use HTSQL to convert web requests into equivalent SQL, executes requests on a server-side database, and returns results in XML, HTML, CSV, JSON, or YAML formats. The current implementation as of April 2010 uses Python and works with PostgreSQL, MySQL, SQLite, Oracle, and Microsoft SQL Server databases.

HTSQL was prototyped and developed by Clark Evans (who proposed YAML in 2001) and implemented by Kirill Simonov, both of Prometheus Research.

Scope

HTSQL's intended audience is business users who are not SQL programmers. It seeks to simplify non-intuitive relational queries, such as aggregates and projections. The goal is not completeness in replicating SQL, but simplicity and intuitiveness. HTSQL allows users who are familiar with the data, but not SQL syntax, to directly access the database without going through an application.

Syntax

HTSQL uses a URI-based query syntax. Queries typically begin with a single table, optionally prefixed by a schema and followed by a filter expression to limit the number of rows that are returned.

Examples

Joins can be made across tables by referencing the column name preceded by the table name. This example requests the title of a degree program from the program table and the school name from the school table:

/program{school.name, title}

The result of this query from the HTSQL command-line system is:

program
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
school.name               | title
--------------------------+-----------------------------------
School of Art and Design  | Post Baccalaureate in Art History
School of Art and Design  | Bachelor of Arts in Art History
School of Art and Design  | Bachelor of Arts in Studio Art
School of Business        | Graduate Certificate in Accounting

...

Aggregate expressions can be used when there are many-to-one relationships in the database.

/school{name, count(program), count(department)}

The result shows the count (number) of programs and departments in each school:

school
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
name                     | count(program) | count(department)
-------------------------+----------------+------------------
School of Art and Design | 3              | 2
School of Business       | 5              | 3
College of Education     | 7              | 2
School of Engineering    | 8              | 4

Applications

HTSQL can be used for ad hoc database queries. Its developers consider it to be more concise and intuitive and therefore less error-prone than SQL for non-technical users.

HTSQL can also be used by "accidental programmers", such as data analysts and UI developers. Besides more readable, HTTP-based queries, HTSQL's developers claim it helps avoid common SQL mistakes like accidental joins.

Versions and licensing

HTSQL follows a dual license model. The software may be used free of charge with open source relational databases, including MySQL and PostgreSQL. Source code is available on the bitbucket site.

A commercial version of HTSQL, compatible with commercial databases like Oracle and Microsoft SQL Server, is also available.

Similar initiatives

• postgREST
• PostGraphile

External links

• HTSQL community website