We are working on the possibility to define SQL queries in a domain-specific language. The API will allow for type-safe SQL construction, database object referencing through the generated database schema classes like the following:

sql = DSL.query
    .Select(
        Orders.OrderId.As("id")
      , SqlExpression.Count(Orders.OrderId)
    )
    .From(Orders)
        .InnerJoin(Customers)
            .On(Orders.OrderId == Customers.CustomerId)
    .Where(Orders.OrderId.Greater(10))
        .And("1 < 2")
    .Having(Orders.OrderId.Count().Greater(5))
    .OrderBy(Orders.OrderId)
    .GetSQL;

So, when the database schema is changed, the programmer can re-generate the code of your database schema classes and get compilation errors in case invalid objects or fields are used in queries defined via DSL API.

Consider a situation when programmers have to define lots of SQL queries for reports, analytics and similar tasks saved as string resources. Changing the schema may lead to a case when some queries become invalid, and the only way to check for this is to run that report. If they are defined via such DSL API, the programmer will be notified about the problem at compile time.

Would you be interested in using such API in your projects?

We'll be grateful for your feedback.