I am working on my .NET open source project for generating SQL...

Initially, I wrote the project so it spit out exactly one SQL format... ugly. I started using this project in the real world and decided it would be nice if I could generate pretty SQL in debug mode and ugly SQL in release mode.

My first go involved a lot of if/else loops within my command generation code. It quickly got to be too much to handle. So, I started looking for another approach that would allow me to plug-in different formatting strategies. I also looked at other SQL formatting libraries but most were specific to one dialect.

Right now, I am playing with the idea of generating a tree of tokens, similar to an abstract syntax tree (AST) that a compiler works with. I was hoping to navigate the tree and generate text based on where I am in the tree. Now that I've built these trees, I'm not sure how to write the code to visit these nodes.

I feel like I'm going backwards. Instead of parsing SQL, generating an AST and spitting out SQL objects, I am allowing someone to build SQL objects, then generate an AST and spitting out SQL. When someone uses my library to build a SELECT statement, they specify the values to select, the tables to select from, filters, ordering, etc. But once I have generated an AST... I have less information than I originally started with! Only by asking "Is the next token the keyword FROM?" can I get a picture of where I'm at. I have to write a bunch of if/elif/else statements to figure out what comes next. At least when I had the SQL objects I could use polymorphism to avoid nasty branching.

So, perhaps I missed a day in compiler theory or perhaps it's because I'm trying to go backwards. But, for whatever reason, my next step is really unclear to me. I think the AST route was in the right direction even if it does mean losing information. I was able to write a really stupid formatter that puts spaces between every token for now. It's really when it comes to getting fancy that I'm overwhelmed.

If anyone has some good resources for going backwards, it would probably be really helpful.

  • 1
    Off topic: I looked at your site and all of your examples involved a lot more code than just typing out the SQL. Curious: what is the benefit of this library?
    – gahooa
    Commented Jan 30, 2013 at 3:16
  • So what does your AST actually look like? I'm unclear about your description of a "tree of tokens", because tokens don't normally appear in an AST. Commented Jan 30, 2013 at 4:10
  • @gahooa I've worked on a lot of projects where someone justified to themselves that it made sense to write a micro ORM. Usually, this involved using a DataTable schema or attributes and reflection. They often make the mistake of combining the logic for describing the schema and generating the SQL. The resulting SQL is usually full of minor bugs, like whitespace issues, SQL injection vulnerabilities and ambiguous column names. This libraries goal is to break out the common logic for generating SQL based on an existing schema. It's generic enough that someone can use it with their own ORM. Commented Jan 30, 2013 at 13:39
  • @GregHewgill My tree is made up of expressions and tokens. An expression can have other expressions and tokens in it. Each expression is typed (SelectCommand, Join, OrderByList, OrderBy). When I encounter an UPDATE command, I expect to see something like this: <UpdateCommand> => "UPDATE" <TableDeclaration> "SET" <SetterList> [ "WHERE" <Filter> ]. Here, UPDATE, SET and WHERE are keyword tokens and <UpdateCommand>, <TableDeclaration>, <SetterList> and <Filter> are other expressions. Commented Jan 30, 2013 at 13:45
  • See my SO answer on how to build a prettyprinter: stackoverflow.com/a/5834775/120163
    – Ira Baxter
    Commented May 19, 2016 at 8:22

2 Answers 2


I had a chance to look at your actual code. It looks like you're most of the way there but this "tree of tokens" concept is getting in the way. It turns out you probably don't need it at all (specifically, I'm referring to the stuff in the Expressions/ folder).

For example, in Join, you have a method called GetDeclarationExpression that returns an IExpressionItem that is a tree of tokens. What I'm suggesting is that you can simplify this and instead directly return a String which is the actual SQL generated from the join. In concrete terms, something like the following:

    String IJoinItem.GetDeclarationExpression(CommandOptions options)
        // [ "(" ] <Left> <Combiner> <Right> [ "ON" <Filter> ] [ ")" ]
        StringBuilder expression = new StringBuilder();
        if (WrapInParentheses ?? options.WrapJoinsInParentheses)
        expression.Append(" ")
        expression.Append(" ")
        expression.Append(" ")
        if (WrapInParentheses ?? options.WrapJoinsInParentheses)
        return expression.ToString();

Of course, all other methods used during the AST traversal would also need to be modified to return String instead of IExpressionItem as needed. Each method that is called during the tree traversal would be responsible for generating the actual SQL string, which will all eventually get concatenated together.

  • This is close to what I had originally. But it was intense changing the formatting strategy. Commented Jan 30, 2013 at 23:08
  • This gave me another idea that relates to your idea and @TMN's. I could create an abstract factory. Then I could create multiple classes for formatting one type of expression, based on the sub-classes that are returned by the factory. Then I don't need an AST anymore. The only draw back is that I might need to expose some internals of the builders to the formatters... Commented Jan 31, 2013 at 1:23

Have you considered having a separate formatter for each expression type? You could have a select-statement-formatter, a column-list-formatter, a from-clause-formatter, etc. I don't know specifically how your AST is structured, but you should be able to identify the various clauses and expressions and pass them to specific formatters.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.