Saturday, July 18, 2009

How can I get the compiler to check my SQL queries?

Over at Coding Horror, I found this piece of code:
var posts = 
(from p in DB.Posts
p.PostTypeId == PostTypeId.Question &&
p.DeletionDate == null &&
p.Score >= minscore
orderby p.LastActivityDate descending
select p).
This is C# code with a piece of infrastructure called LINQ to SQL. And I learn:
The big hook here is that this is code the compiler actually understands. You get code completion, compiler errors if you rename a database field or mistype the syntax, and so forth. Perhaps best of all, you get an honest to goodness post object as output!
Is there any open-source stack, possibly involving PostgreSQL, that can do that? And I don't mean ECPG, which can check the SQL syntax, but does not verify the existence and types of tables, columns, etc. at compile time.

Any ideas?


  1. In Python, Dejavu/GeniuSQL comes closest: the syntax is checked at "compile time" but the type checks are performed at runtime.

    You might look at GLORP which works similarly in Smalltalk.

  2. Well, there's my Java tool that I blogged about a couple of months ago, scroll a bit down to see code showing how I do it.

    It doesn't do code completion of SQL, nor anonymous SQL like in that example, my approach is equivalent to declaring SQL functions in PostgreSQL, but it does syntax and type checks at compile time. It also generates the types if you want it to.

    When working on it I've also figured out features in PostgreSQL that would be beneficial for a tool that uses this approach. I'm planning on getting that up on that uservoice thing -- when I find the time to clean up and organize my notes, grumble grumble...

  3. Hi is "linq to sql" for other DBs including pgSQL.

    Mono is a implementation of C# and the associated stack.

    Combining the two gives you a completely open source stack that will run on *nix.

    I have never used linq but 5min of googeling did the job.


  4. There is PG'OCaml for OCaml:

  5. Support for this really needs to be built into the language. Although I'm not usually a Micro$oft fan, I do have to admit that they have done a good job with Linq. You can use native C# code to write SQL-like queries against databases (as above), or against XML, or against any other provider that you can write.

    But the key is that the support for this was built into the language.

  6. We're currently working on PostgreSQL support in DbLinq, especially under Linux with Mono. It has to mature a bit, but we're already planning some advanced features like tsvector support.

    Feel free to partecipate at or at ##dblinq on freenode!