An Introduction to Go Ast - Generating SQL Queries Based on Structs
Tags: go, sql, programming, tutorial
Published: 2023-03-01 03:48:32

The Go standard library includes an 'ast' package, which provides a set of tools for working with the abstract syntax tree (AST). The AST represents the structure of a Go program in a tree-like data structure and can be used to analyze, modify, or generate code programmatically. In this post, we investigate the possibility of analyzing structs and generating a SQL query based on them.

## Goal

Our goal involves generating a SQL query based on a given struct with specific formatting requirements. The struct name should be pluralized and lowercase, while the field names should be in snake_case and also lowercase. 

For example, given the following struct 
```go
type User struct {
	ID             int
	Name           string
	InvitationCode string
}
```

The generated SQL query should be an INSERT statement into a table named users with columns id, name, and invitation_code. The values to be inserted should be represented using placeholders in the query, such as :id, :name, and :invitation_code.


## Formatting Requirements 

Let's create some helper functions to normalize the structs and field names. 
The first requirement we look at is to transform UpperCamelCase into snake_case

We can do this by simply iterating over the string and anytime we see a uppercase character and the previous character is lower case we insert a underscore `_`

```go
func SnakeCase(s string) string {
	var str strings.Builder
	var prev rune
	for i, r := range s {
		// check if we should insert a underscore
		if i > 0 && unicode.IsUpper(r) && unicode.IsLower(prev) {
			str.WriteRune('_')
		}
		// lower case all characters
		str.WriteRune(unicode.ToLower(r))
		prev = r
	}
	return str.String()
}
```

Next up we want to pluralize the string,
this could be done in similar fashion to this
```go
func Pluralize(s string) string {
	if strings.HasSuffix(s, "y") {
		return strings.TrimSuffix(s, "y") + "ies"
	}
	return s + "s"
}
```

As a little helper function lets add a `NormalizeTableName()` function which combines the above into one

```go
// normalize the struct name to lowercase, pluralize it and apply snakeCase
// for example, User -> users, ReviewPost -> review_posts
func normalizeTableName(name string) string {
	return pluralize(snakeCase(name))
}
```




With those helper functions out of the way let's have a look on how to parse and analyse the file. 
To do this we can use the `go/ast` package, the ast package provides us with a set of tools for working with the abstract syntax tree. 

## File Parsing
The Go standard library comes with a handy package to parse Go source code files and generate a AST based on it.

```go
func ExtractStructs(filePath string) []QueryBuilder {
	// create a file set, this keeps track of file positions for error messages and other diagnostic output
	fset := token.NewFileSet()
	// parse the whole file including errors
	parsedAST, err := parser.ParseFile(fset, filePath, nil, parser.AllErrors)
}
```

After parsing the file and checking for potential errors we use the `go/ast` package to inspect the abstract syntax tree.

`Inspect` traverses an AST in depth first order. If the function returns true, `Inspect` is called recursively.
```go
ast.Inspect(parsedAST, func(n ast.Node) bool {
   return true
})
```
In our case we are only interested if our node of type `ast.TypeSpec` and `ast.StructType`.
`TypeSpec` is needed to get the actual struct name and `ast.StructType` let's us iterate over the struct fields

```go
ast.Inspect(parsedAST, func(n ast.Node) bool {
	// try to convert n to ast.TypeSpec
	if typeSpec, isTypeSpec := n.(*ast.TypeSpec); isTypeSpec {
		s, isStructType := typeSpec.Type.(*ast.StructType)
		// check if conversion was successful
		if !isStructType {
			return true
		}
		// get the struct name
		structName := normalizeTableName(typeSpec.Name.Name)
		// get Fields helper function
		fields := getFields(s)
	}
	return true
})
```
As you've probably noticed there is another helper function `getFields`
The implementation looks like this. 
```go
func getFields(s *ast.StructType) []string {
	fields := make([]string, len(s.Fields.List))
	for i, field := range s.Fields.List {
		if len(field.Names) == 0 {
			continue
		}
		fields[i] = SnakeCase(field.Names[0].Name)
	}
	return fields
}
```

With this function we got all we need. We are able to extract the struct name and the field names additionally apply snake_case and pluralization respectively. 

## Query Generation 

As we already extracted all the needed data we can introduce a simple function to generate the needed queries. To keep this nice and clean we use another struct which defines the needed data for a query and a struct method to return a insert query.

```go
// define the QueryBuilder struct
type QueryBuilder struct {
	TableName string
	Fields    []string
}
// Generate a insert query
func (q QueryBuilder) InsertQuery() string {
	return fmt.Sprintf("INSERT INTO %s (%s) VALUES (:%s)", strings.ToLower(q.TableName), strings.Join(q.Fields, ", "), strings.Join(q.Fields, ", :"))
}
// add more implementation for the needed queries
```

and at last let's update the `ExtractStructs` function and combine everything

```go
func ExtractStructs(filePath string) []QueryBuilder {
	fset := token.NewFileSet()
	parsedAST, err := parser.ParseFile(fset, filePath, nil, parser.AllErrors)
	if err != nil {
		fmt.Println(err)
		return nil
	}

	// Find all struct declarations
	var structs []QueryBuilder
	ast.Inspect(parsedAST, func(n ast.Node) bool {
		// try to convert n to ast.TypeSpec
		if typeSpec, isTypeSpec := n.(*ast.TypeSpec); isTypeSpec {
			s, isStructType := typeSpec.Type.(*ast.StructType)
			// check if conversion was successful
			if !isStructType {
				return true
			}
			// get the struct name
			structName := typeSpec.Name.Name
			// get Fields helper function
			fields := getFields(s)
			structs = append(structs, QueryBuilder{TableName: normalizeTableName(structName), Fields: fields})
		}
		return true
	})
	return structs
}
``` 


You can find the full code example [here](https://github.com/trapajim/struct-query-generator)