SQL AST Builder For Go
Jun 24, 2014
I've been working on a library to bring arel-like functionality to Go.

We’ve been gradually adopting Go at work, and one of major hurdles in switching from Ruby is the lack of an expressive and easy to use database library. ActiveRecord really does sink its teeth in. You get used to your wheres and your includes. There are many Go ORMs today, but they all reimplement more or less the same basic things over and over – map a table to a struct, select a row by primary key – and constructing a complex search condition remains difficult or impossible in most. So I had a go myself.

I wanted this to be a query constructor before anything else. Think of arel and how all modeling is built on top of it. So, no migration capabilities (there’s already goose, and every other ORM has its own migrator baked in), and no emphasis on loading data at this point (again, this functionality is already well covered). Just focusing on building a query.

This is not the first attempt of its kind, xorm already does something conceptually similar, but I took it one step further, for better or worse.

It is here: bitbucket.org/ulfurinn/dbq. (There’s a github mirror as well.)

It’s all very early and raw and barely documented yet, but you can have a look at the test file to see how it’s supposed to be used. It’s already usable on a basic level – you can generate an SQL string and a list of placeholder bindings and use those with your established ORM to populate your models. This could also be used for building more complex modeling tools, proper associations and all, if there’s interest. Using it directly may get pretty verbose, just like raw arel.

Some quick examples:

1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16
  17
// conditions:
  e := q.Select().From("t").Where(Ident("a").Eq("meh")).Where(Ident("x").Eq(Bind("myValue")))
  sql, values := q.SQL(e, Args{"myValue": 42})
  // SELECT * FROM t WHERE (a = $1) AND (x = ($2)) [$1 = 'meh', $2 = 42]
  
  // subqueries:
  s1 := q.Select().From("t")
  q.Select().From(Alias(s1, "s"))
  // SELECT * FROM (SELECT * FROM t) AS s
  
  // flexible column list:
  q.Select(Ident("a"), "a1", Alias("b", "b_alias"), Alias(Literal(2).Plus(2), "c")).From("t")
  // SELECT a, a1, b AS b_alias, (2 + 2) AS c FROM t
  
  // join on:
  q.Select().From("t1", Join("t2", On(Ident("c1").Eq(Ident("c2")))))
  // SELECT * FROM t1 INNER JOIN t2 ON (c1 = c2)
  

The next steps are, in order of priority:

  1. Map more SQL constructs. This is very easy to do, almost mechanical.
  2. More high-level calls. Where() already tries to do something: you can give it manually constructed expressions or a map of column: value, and it will do the right thing. Go literals are mapped to SQL literal nodes where it makes sense, instead of using Literal() everywhere.
  3. Extend this to other dialects (I’ve hardly worked with anything other than Postgres for the last 10 years, so MySQL is completely foreign territory by now, and I’d rather let an expert code that part).
  4. Some validation, perhaps? Most things are of type Expression, and you could potentially combine them in ways that are not valid SQL. I’ll never be able to do a better job than an actual database engine, but some safety checks could be added.
  5. My own data population, eventually. There’s already a simple Into() call, but it’s very incomplete, more of a proof of concept, and existing ORMs do this part better.