Monday, 15 October 2012

ElSql - Library/DSL to manage SQL

For many years I've thought about writing a simple library for handling SQL and/or relational databases. At OpenGamma I got the chance, and today the ElSql library is launched (pronounced else-q-el).

ElSql

ElSql is not an ORM or anything similar. Instead, it is a simple tool for managing SQL external to the application. Plus a touch of DSL goodness.

The development model that ElSql is targetted at is Spring-based, using SqlParameterSource and JdbcTemplate. You need to be comfortable writing SQL, as well as writing the code to convert data objects to and from the form needed for the database.

So what does ElSql do?

Primarily, ElSql defines a DSL file format and two public classes - a manager and a configuration class. The aim of the DSL elements in the file format is to provide some basic tags to handle common difficulties encountered when building up SQL dynamically across a variety of databases. This external file-based approach has the major benefit of allowing DBAs to discuss the SQL more easily with the development team.

Here is an example elsql file:

 -- an example comment
 @NAME(SelectBlogs)
   @PAGING(:paging_offset,:paging_fetch)
     SELECT @INCLUDE(CommonFields)
     FROM blogs
     WHERE id = :id
       @AND(:date)
         date > :date
       @AND(:active)
         active = :active
     ORDER BY title, author
 @NAME(CommonFields)
   title, author, content

The aim is that at first glance it should be relatively clear what the file represents and how it works. And yes, that is significant whitespace indentation being used for defining the blocks.

The colon prefixes are variables, as defined by the Spring NamedParameterJdbcOperations.

The tags, starting with @, cover three key areas:

  • SQL LIKE vs = for wildcards
  • dynamic construction of WHERE/AND clauses where only some things are being searched for
  • paging of results

The second and third of these are shown in the example above. The @PAGING will add whatever code is necessary to perform paging on the configured database. The @AND block will only remain if the variable, such as :date exists.

The named block, @NAME, is how the application refers to the SQL:

  String sql = bundle.getSql("SelectBlogs", args);

where args is the SqlParameterSource that has to be created and passed to NamedParameterJdbcOperations anyway. (Passing in the arguments allows tags like @AND to work, by querying to see if the variable is present or not)

These three problems - wildcards, WHERE/AND and paging - have always been the three biggest issues faced when building SQL dynamically, and the tags neatly solve the problems. And of course there is now a great file we can use to talk to DBAs.

More details can be found in the user guide and configuration guide. Or look at a complete, yet simple, example.

Summary

ElSql is a library I've wanted to write for a long time and now it finally exists. Its not for everyone - you need to be able to write SQL for starters! The DSL elements are deliberately simple and limited, providing just enough to tackle the common cases hit when dynamically building SQL.

If it fits your needs, take it for a test run. See the project page on GitHub for more details, documentation and to fork it. Or download from maven com.opengamma/elsql. Feedback welcome!

8 comments:

  1. Cute idea but I favor something along the lines of http://www.jooq.org/ or http://www.querydsl.com/

    A Java-based DSL resulting in type-safe SQL expressions. I would love to see a third player in this space because both jooq and querydsl have their problems but it sounds like ElSql (nice name by the name!) fails to provide the compile-time type safety I'm looking for.

    ReplyDelete
    Replies
    1. I looked at projects like those you've highlighted (in fact probably at those projects), but they weren't quite what I was looking for. They do provide extra type safety and Java-ness, but at the expense of greater separation from the DBA (which matters to OpenGamma).

      Delete
    2. Yes, DBA like to operate on predictable SQL strings. With jOOQ or QueryDSL - being internal DSLs for Java - DBA have a less influence on executed SQL.

      How did you implement paging for SQL Server? Using window functions? Do you already have a roadmap with ideas you'd want to implement?

      With jOOQ, I'm currently evaluating how Eclipse Xtext or Scala Macros could come into play to bring even more SQL syntax closeness to the Java/Scala world. Both technologies look quite promising.

      Delete
    3. Paging in SQL server is by a window function (and as can be seen, the project/implementation is pretty simple). The most likely next step is another database, such as Oracle. I think the range of functionality supported by the tag is about right, but am happy to hear other views.

      jOOQ looks good, with a different set of goals to ElSql. Will be interesting to see what xtext might achieve.

      Delete
    4. Yes, I'm doing similar SQL Server paging in jOOQ. When moving to Oracle, you might want to read up on this interesting benchmark:

      http://www.inf.unideb.hu/~gabora/pagination/results.html

      Delete
  2. Hey Stephen,
    Great work on ElSql.

    I recently released my own little SQL library that has some SQL templates: https://github.com/agentgt/jirm/tree/master/jirm-core#sql-placeholder-parser

    The idea behind mine is to allow people to easily test their SQL with different values with out breaking the JDBC java code that uses it. Its not as powerful as your library but so far I really like it.


    ReplyDelete
  3. Replies
    1. Good to see another way of tackling the problem. Thanks for the link.

      Delete