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!