Maintainable SQL Query Building with Golang

Any application working with SQL queries can benefit from using a query builder to improve code readability, maintainability, and security. In fact, there are many different libraries that do just that in Golang. Here at Vaunt, we tried many different options before finally deciding to create one ourselves. Ultimately, we wanted something that was secure and provided variable replacement to prevent SQL injection while still being readable and able to have conditional statements. So we created a new library called tqla, released and announced late last year. You can read more about it in this article.

Before we built tqla, we mainly used Squirrel for our SQL query building logic—and we highly recommend it. We still use Squirrel in some areas but have gradually started replacing and implementing new query building logic with tqla. We have found many instances where tqla has improved our ability to maintain our code and fix problems we ran into when using other statement builders.

Real World Use Case

At Vaunt, we recently underwent a database migration from CockroachDB to TiDB. While CockroachDB was performant and reliable, we ultimately faced the decision of adding to our techstack to support an OLAP database. The need for this was to support our analytical workload on our open source community insight product. To keep our technology footprint small, we decided to move forward with TiDB and take advantage of the database’s HTAP architecture. 

CockroachDB is largely compatible with PostgreSQL, and we used the PostgreSQL syntax for many of our SQL queries. To switch to TiDB, we had to change a few of our tables and update queries to use MySQL syntax. In a few locations during the migration, we found that we were improperly using conditional query building statements and lacked the proper tests to catch that the statements were being generated incorrectly.

Demonstration

In Squirrel’s README, there is an example of how you can use conditional query building to update statements with optional filters:

if len(q) > 0 {
    users = users.Where("name LIKE ?", fmt.Sprint("%", q, "%"))
}

Here is a real, but simplified, example of how we updated one of our queries to conditionally join tables and add an optional filter:

psql := squirrel.StatementBuilder.PlaceholderFormat(squirrel.Question)

statementBuilder := psql.Select(`i.id`).
From("vaunt.installations i").
Where(`entity_name = ?`, name)

if len(provider) > 0 {
    statementBuilder.Where(`provider = ?`, provider)
}

if len(repo) > 0 {
    statementBuilder.Join(`repositories as r on JSON_CONTAINS(i.repositories, CONCAT('["', r.id, '"]'))`)
    statementBuilder.Where(`r.name = ?`, repo)
}

Can you spot the issue with the code? If not, don’t worry—it is something that slipped through our own code reviews as well until we ran our tests. 

The issue here is that we forgot to update the statement builder with the result of the builder functions. For example, the provider condition filter should instead read:

if len(provider) > 0 {
    statementBuilder = statementBuilder.Where(`provider = ?`, provider)
}

This is a relatively simple mistake to make and can easily be caught with sufficient test cases, but because it’s not technically invalid code, it can take a bit to realize what is happening immediately.

Another readability issue with this setup is that the conditional join is separated from the initial select statement. We could reorganize the builder to put each piece where it should go, but it would require multiple duplicate conditional statement checks and still suffer from some readability issues.

Using tqla

The above demonstration using Squirrel has since been rewritten, and the equivalent in tqla looks like this:

t, err := tqla.New(tqla.WithPlaceHolder(tqla.Question))
if err != nil {
    return nil, err
}

query, args, err := t.Compile(`
    SELECT i.id
    FROM vaunt.installations as i
    {{ if .Repo }}
    JOIN vaunt.repositories as r on JSON_CONTAINS(i.repositories, CONCAT('["', r.id, '"]'), '$')
    {{ end }}
    WHERE entity_name = {{ .Name}}
    {{ if .Provider }}
    AND i.provider = {{ .Provider }}
    {{ end }}
    {{ if .Repo }}
    AND r.name = {{ .Repo }}
    {{ end }}
    `, data)
if err != nil {
    return nil, err
}

As you can see, the template syntax for tqla makes incorporating conditional clauses very simple. Tqla automatically replaces the variables we are setting with our specified placeholders and provides the arguments we can use with our sql driver to execute the statement.

Similar to Squirrel, this statement building approach is easy to test, as we can create different sets of data objects to pass to the template builder and validate the output.

You can see that we are easily able to add conditional pieces of the query where they would fit in best. For example, here we have a conditional JOIN directly after the FROM statement—and although we still have multiple condition checks, it does not overly complicate the template.

Custom Functions

Another nice tqla feature helping to improve the maintainability of our sql builders is the ability to define custom functions we could use in the templates to abstract some transformation logic.

Here is an example of how we used a function to convert the Golang’s time.Time value into a sql.NullTime to allow us to do an insert with our data objects without needing to convert it beforehand:

funcs := template.FuncMap{
    "time": func(t time.Time) sql.NullTime {
        if t.IsZero() {
            return sql.NullTime{Valid: false}
        }
        return sql.NullTime{Time: t, Valid: true}
    },
}

t, err := tqla.New(tqla.WithPlaceHolder(tqla.Question), tqla.WithFuncMap(funcs))
if err != nil {
    return err
}

With this function defined in our tqla funcs map, we can now freely use it in our query templates by providing it a parameter from the data object that is a time.Time field. We can even call this function multiple times in the same template with different fields.

Here is a simplified example:

statement, args, err := t.Compile(`
    INSERT INTO events
        (name, created_at, merged_at, closed_at)
    VALUES ( 
        {{ .Name }},
        {{ time .CreatedAt }},
        {{ time .MergedAt }},
        {{ time .ClosedAt }}
    )`, eventData)

Conclusion

In conclusion, we believe that using tqla can help improve the maintainability of query building logic while offering some powerful utility for creating dynamic queries. The simplicity of the template structure allows for clean code readability and can make it faster to debug any potential errors.

We made tqla open source to share this library in hopes that it provides a good option for other users wanting a simple, maintainable, and secure way to build sql queries in many different types of applications.

If you are interested, please check out the repository and give it a star if it helps you in any way. Feel free to make any feature requests or bug reports!

We are always open to receiving feedback and contributions.

To stay in the loop on future development, follow us on X or join our Discord!

The above is the detailed content of Maintainable SQL Query Building with Golang. For more information, please follow other related articles on the PHP Chinese website!