# Dynamic SQL Template with Golang

**TL;DR ... mas tqla!**

In software development, adaptability is key. As code evolves, so does the challenge of simplicity and readability. This issue invariably emerges when developing large-scale applications necessitating numerous diverse SQL statement variations.

Static SQL queries often fall short when you need some form of conditional query generation. Crafting dynamic queries in Golang requires mastery of SQL and the nuances of Golang.

The hurdles include preventing SQL injection, using parameterized queries, managing query complexity, optimizing performance, and maintaining code readability.

In this article, we will dive into strategies and libraries designed to tackle these issues and introduce a new library called [tqla](https://github.com/vauntdev/tqla) ( pronounced **tequila** ) that leverages Golang’s powerful [text/template](https://pkg.go.dev/text/template) package to generate dynamic conditional SQL statements securely with ease. If you are new to templating with Golang, I would suggest getting started by reading the docs and this [blog](https://www.digitalocean.com/community/tutorials/how-to-use-templates-in-go) from [Digital Ocean](https://www.digitalocean.com/).

All of the code for this article can be found on [GitHub](https://github.com/VauntDev/blogSnippets/tree/main/apps/todo).

## **Challenges of Building Dynamic SQL Query**

Many software applications require conditional SQL statements. This often becomes more apparent when developing an API that allows for some level of filtering or pagination.  As queries become more dynamic, challenges arise.

At a glance, a few challenges include:

* **Security:** It is important to ensure that your dynamic SQL queries are secure and that they do not expose sensitive data.
    
* **Performance:** Dynamic SQL queries can be slow, especially if they need to be split into multiple statements due to query builder limitations.
    
* **Maintainability:** It can be difficult to maintain dynamic SQL queries, especially as they become more complex due to business logic or conditional formatting constraints.
    

Let's walk through a real-world example that highlights these challenges.

## Todo Application

In a previous [article](https://blog.vaunt.dev/generic-http-handlers), we announced a generic, lightweight, HTTP handler framework that simplifies request/response marshaling. We built a very basic Todo Application that exposes an API that supports basic operations such as creating and retrieving todos.

In this post, we will focus primarily on our `todo` service, which is responsible for storing and retrieving todos from a data store. Specifically, we will extend our service to use a database. As we implement each database function, we will walk through how `tqla` can be used to avoid the challenges mentioned above.

### Database Creation

To keep things simple we will be storing todos in Sqlite. Creating the database in Golang can be done with the following code:

```go
db, err := sql.Open("sqlite3", “example.db”)
if err != nil {
    log.Fatal(err)
}
defer db.Close()
```

The above code creates a db connection to a local called `example.db`. Once the database has been created and the connection is established, we can set up our `todo` table.

Below is our `todo` table schema:

```sql
create table if not exists todos (
  id text primary key,
  title text not null,
  description text not null,
  completed boolean default 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP 
);
```

We can apply our schema by leveraging the previously created db connection as follows:

```go
if _, err := db.Exec(todoSchema); err != nil {
    log.Fatal(err)
}
```

Now that we have a database setup, we will implement our service that will store and retrieve todos.

### Service Definition

Our Todo service will have three functions, Add, Get, and List.

```go
type TodoService struct {
    db *sql.DB
}

func (ts *TodoService) Add(todos []pb.Todo) error { return nil}
func (ts TodoService) Get(id string) (pb.Todo, error) { return nil,nil }
func (ts TodoService) List(filter Filter) ([]*pb.Todo, error) { return nil,nil}
```

Let's start by implementing the `Add` function. Since we always have more things ***Todo*** than we have time for, our `Add` function accepts multiple Todos.

There are a number of ways we can implement this using our db driver.  Since we know there can be more than one to-do, we will want to attempt to add all eligible insert values to our statement.

Below are several implementations that use different techniques to build a database statement and argument slice.

### Standard Library SQL Statement

Before `tqla`, a solution that uses only the standard library might look something like this:

```go
valueStatement := make([]string, 0, len(todos))
args := make([]any, 0)
for _, t := range todos {
    valueStatement = append(valueStatement, "(?, ?, ?, ?, ?)")
    args = append(args, t.Id, t.Title, t.Description, t.Complete, t.CreatedAt)
}
stmt := fmt.Sprintf("INSERT INTO 'todos' ('id', 'title', 'description', 'completed', 'created_at') Values %s",
    strings.Join(valueStatement, ","))

_, err := db.Exec(stmt, args...)
```

In this example, we continue to append to the original statement and track arguments in order. However, this statement can get even more complex if you are using a database like Postgres, which requires placeholders to be `$number` instead of `?`.

I’ll spare everyone by excluding the function that I’m sure many of us have written that finds and replaces all `?` with `$number` placeholders...

While this example is simple, most people will likely feel the need for a library that can assist with building an SQL statement.

Up next, we will be using a simple statement builder to help offload some of the complexity in the above implementation.

### Squirrel Statement Builder

Many awesome SQL statement builders exist. One of our favorites is [squirrel](https://github.com/Masterminds/squirrel). If you haven’t checked it out, please do! Having familiarity with `squirrel` will help provide context on why we decided to build `tqla`.

Let’s rewrite the above example using `squirrel` as the query builder.

```go
psql := squirrel.StatementBuilder.PlaceholderFormat(squirrel.Dollar)

stmt := psql.Insert("todos").
    Columns("id", "title", "description", "completed", "created_at")

for _, t := range todos {
    stmt = stmt.Values(t.Id, t.Title, t.Description, t.Complete, t.CreatedAt)
}

insertStmt, insertArgs, err := stmt.ToSql()
if err != nil {
    return err
}

_, err := db.Exec(insertStmt, insertArgs...)
```

The above has several advantages compared to our first implementation. We no longer need to worry about tracking the statements and arguments.

Additionally, `squirrel` has also removed the need for placeholder formatting, meaning we no longer have to worry about what placeholder syntax our db requires.

That said, one big drawback, is we have lost the ability to see the majority of the query. Squirrel follows a builder pattern, which abstracts some of the common SQL syntax from us. This also means our SQL statement must fit into the builder functions Squirrel exposes.

Any familiar squirrel user knows that this can cause problems with nested selects, and complex joins.

As your applications grow, and you include limits, pagination, or other custom filters the amount of query sprawl grows with `squirrel`. Eventually, you are left with large functions with a limited understanding of what the final query could be without extensive review.

### Tqla Statement Builder

Let's try one last implementation, now using `tqla`.

```go
t, err := tqla.New(tqla.WithPlaceHolder(tqla.Dollar), tqla.WithFuncMap(exampleFuncs))
if err != nil {
    return err
}

insertStmt, insertArgs, err:= t.Compile(`{{ $length := sub ( len . ) 1 }}
INSERT INTO 'todos' ('id', 'title', 'description', 'completed', 'created_at') 
VALUES {{ range $i, $v := . }}
( {{$v.Id}}, {{$v.Title}}, {{$v.Description}}, {{ $v.Complete }}, {{ $v.CreatedAt }} ){{if lt $i $length}},{{else}};{{end -}}
{{end}}`, todos)

if err != nil {
    return err
}

_, err := db.Exec(insertStmt, insertArgs...)
```

The above example blends both of our previous solutions and provides more visibility and control into how the statement is built. `Tqla` still handles many of the messy details of query building, such as tracking arguments, ensuring safety, and driver interoperability. Most importantly, `tqla` extends Golang’s text templating by leveraging all of the powerful built-in capabilities of templating while preventing SQL injection.

We believe that `tqla` provides a more flexible and powerful query builder.

Our final `Add` implementation is as follows

```go
func (ts *TodoService) Add(todos []pb.Todo) error {
    // Init TQLA with the DB placeholder to use and additional functions that can
    // be called by the template.
    t, err := tqla.New(tqla.WithPlaceHolder(tqla.Dollar), tqla.WithFuncMap(exampleFuncs))
    if err != nil {
        return err
    }
    insertStmt, insertArgs, err := t.Compile(`{{ $length := sub ( len . ) 1 }}
        INSERT INTO 'todos' ('id', 'title', 'description', 'completed', 'created_at') 
        VALUES {{ range $i, $v := . }}
             ( {{$v.Id}}, {{$v.Title}}, {{$v.Description}}, {{ $v.Complete }}, {{ $v.CreatedAt }} ){{if lt $i $length}}, {{else}}; {{end -}}
        {{end}}`, todos)
    if err != nil {
        return err
    }
    
    if _, err := ts.db.Exec(insertStmt, insertArgs...); err != nil {
        return err
    }

    return nil
}
```

In case you're not convinced to give `tqla` a try, let's take a look at a few more examples by implementing our remaining functions.

### Additional Tqla Examples

Up next, we will implement our `Get` todo function. The purpose of this example is to show how even simple queries can leverage `tqla`.

```go

func (ts *TodoService) Get(id string) (*pb.Todo, error) {

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

	selectStmt, selectArgs, err := t.Compile(`
    select id,title, description, completed, created_at 
    from todos 
    where id= {{ . }}`, id)
	if err != nil {
		return nil, err
	}

	todo := &pb.Todo{}
	row := ts.db.QueryRow(selectStmt, selectArgs...)
	if err := row.Scan(&todo.Id, &todo.Title, &todo.Description, &todo.Complete, &todo.CreatedAt); err != nil {
		log.Println(err)
		return nil, err
	}

	return todo, nil
}
```

In this example, our function takes a todo ID which is supplied to our SQL template using `tqla`. The ID variable is replaced directly by `tqla` to create a final select statement that looks like this:

`select id, title, description, completed, created_at from todos where id=$1`

While `tqla` may not be needed in every SQL statement use case, our goal is to make it as low friction as possible so that it can grow with your application as query requirements change.

With the the `Get` function out of the way, all that is left is to implement our `List` function.

For our `List` function, we have a few more requirements. We need to support a `Limit` and optionally pagination by using a `Before` and `After` cursor. For pagination, we will use a base64 encoded string that is a combination of the `created_at` and `id` columns. We will use `Before` and `After` to represent the direction of the cursor for pagination.

```go
func (ts *TodoService) List(filter *Filter) ([]*pb.Todo, error) {

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

	selectStmt, selectArgs, err := t.Compile(`
	SELECT id, title, description, completed, created_at 
	FROM todos 
	{{ $page := ( parseCursor .After .Before .Limit ) }}
	{{ if $page.Forward }}
		WHERE (created_at,id) > ({{ $page.CreatedDttm }},{{ $page.Id }})
	{{ else }}
		WHERE (created_at,id) < ({{ $page.CreatedDttm }}, {{ $page.Id }})
	{{ end }}
	ORDER BY created_at
	LIMIT {{ $page.Limit }}
	`, filter)
	if err != nil {
		return nil, err
	}

	rows, err := ts.db.Query(selectStmt, selectArgs...)
	if err != nil {
		return nil, err
	}

	todos := make([]*pb.Todo, 0)
	for rows.Next() {
		todo := &pb.Todo{}
		if err := rows.Scan(&todo.Id, &todo.Title, &todo.Description, &todo.Complete, &todo.CreatedAt); err != nil {
			return nil, err
		}

		todos = append(todos, todo)
	}

	return todos, nil
}
```

The above example shows the full power of `tqla` by also leveraging a feature called custom functions.

### Custom Functions

The above examples showcase how `tqla` can be used in a number of use cases. Keep in mind that `tqla` is simply a query builder with the main focus of ensuring proper variable placeholder replacement.

That said, there are a few tricks that we have not covered yet. A unique and powerful feature of `tqla` is the ability to implement custom functions. Above, we created a function called `parseCursor`.

Here is how the parseCursor function is implemented:

```go
// tqla can be expanded through custom functions.
var exampleFuncs = template.FuncMap{
	// parseCursor creates a Pagination object from a base64 encoded cursor.
	// Uses after if before is empty other wise uses before. If both after and before are set
	// then the Pagination object returned in nil.
	"parseCursor": func(after string, before string, limit uint8) *Page {
		cursor := ""
		forward := false
		createdDttm := ""
		id := ""

		if len(after) > 0 && len(before) == 0 {
			forward = true
			cursor = after

		} else if len(before) > 0 && len(after) == 0 {
			forward = false
			cursor = before
		}

		if len(cursor) > 0 {
			data, err := base64.StdEncoding.DecodeString(cursor)
			if err != nil {
				return nil
			}
			values := strings.Split(string(data), "/")
			if len(values) == 2 {
				createdDttm = values[0]
				id = values[1]
			}
		}

		if limit > maxLimit {
			limit = maxLimit
		}

		t, _ := time.Parse("2006-01-02T15:04:05Z", createdDttm)
		return &Page{
			Forward:     forward,
			CreatedDttm: t,
			Id:          id,
			Limit:       limit,
		}
	},
}
```

These functions can then be registered with `tqla` through the options pattern.

Here is an example:

```go
t, err := tqla.New(tqla.WithFuncMap(exampleFuncs))
	if err != nil {
		return nil, err
	}
```

There are a myriad of use cases for custom functions and they are a unique feature that provides the flexibility to extend `tqla` for your specific use cases.

## Tqla at Vaunt

At Vaunt, we have begun replacing `squrriel` with `tqla`. So far we have been extremely happy with the flexibility with `tqla`.

Here is a recent use case

```go
data := struct {
		Provider   string
		ExternalId string
		Member     string
		UpdatedAt  time.Time
	}{
		Provider:   provider,
		ExternalId: externalId,
		Member:     member,
		UpdatedAt:  updatedAt,
	}

	templateFuncs := template.FuncMap{
		"toArray": func(val string) string {
			return "{" + val + "}"
		},
	}

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

	updateStmt, updateArgs, err := t.Compile(`
	update vaunt.entities 
		set members = array_append(members, {{ .Member }})
	where (members is null OR not members @> {{ toArray .Member }}) and provider = {{ .Provider }} and external_id = {{ .ExternalId }} and updated_at < {{ .UpdatedAt }};
    `, data)
	if err != nil {
		return err
	}

	_, err = crdb.conn.Exec(ctx, updateStmt, updateArgs...)
	if err != nil {
		return err
	}
```

The goal was to write a query that would append a value to a member array if the value was not already contained in the array.  The array `contains` function `'@>'` in Postgres is used to compare two arrays, as described [here](https://www.postgresql.org/docs/current/functions-array.html#FUNCTIONS-ARRAY).

The second argument needed to be an array containing the value we wanted to check.  Using `tqla`, we were able to do this by writing a custom template function that turned our string argument into a string array for comparison.

## **Contribute**

We believe strongly in open-source technology and developer-led growth. Vaunt is a tool that focuses on showcasing contributors and highlighting the contributions that they make.

There are a number of ways to contribute to `tqla`. You can get started by reviewing our [Contributing.md](https://github.com/VauntDev/tqla/blob/main/.github/CONTRIBUTING.md) doc and checking out any [open issues](https://github.com/VauntDev/tqla/issues).

To help thank `tqla` contributors, we have enabled several custom achievements that can be earned, powered by [Vaunt](https://vaunt.dev/). We believe in the tools we are building and of course, leverage them ourselves.

Go check out our `tqla`'s [community page](https://community.vaunt.dev/board/VauntDev/repository/tqla) to see the latest contributors who have earned achievements on `tqla`!

The following manifest outlines how achievements can be earned on `tqla`

```yaml
version: 0.0.1
achievements:
  - achievement:
      name: Mas Tqla!
      icon: https://raw.githubusercontent.com/VauntDev/tqla/main/.vaunt/mas_tqla.png
      description: uno mas, dba..., one more, mas tqla!   
      triggers:
        - trigger:
            actor: author
            action: star
            condition: starred = true
  - achievement: 
      name: Tqla Worm 
      icon: https://raw.githubusercontent.com/VauntDev/tqla/main/.vaunt/tqla_worm.png
      description: not all bugs are bad!    
      triggers:
        - trigger:
            actor: resolved_by
            action: issue
            condition: labels in ['bug'] & reason = COMPLETED
  - achievement: 
        name: Tqla Sunrise  
        icon: https://raw.githubusercontent.com/VauntDev/tqla/main/.vaunt/tqla_sunrise.png
        description: a shining example of your contribution to tqla!    
        triggers:
          - trigger:
              actor: author
              action: pull_request
              condition: merged = true
              operator: "|"         
          - trigger: 
              actor: reviewers
              action: pull_request
              condition: merged = true
```

You can check out the icons here. You can learn how to showcase any achievement you have earned with Vaunt by visiting and reviewing our [docs](https://docs.vaunt.dev/).

You can configure custom achievements on any GitHub repository by installing the Vaunt application through the [GitHub Marketplace](https://github.com/marketplace/vaunt-dev).

Go install it and vaunt your contributors!

## **Conclusion**

In conclusion, `tqla` is a powerful tool for building dynamic SQL queries. Leveraging Golang’s text templating `tqla` can help you overcome several challenges when working with dynamic queries.

In short, `Tqla` offers the following

* **Security:** You can use text templating to generate SQL queries that are safe and secure. `Tqla` prevents SQL injection by replacing template variables with database placeholders.
    
* **Performance:** Text templating can help you to generate efficient SQL queries. Some query builders are not flexible enough to allow for the most optimal query. `Tqla` stays out of your way when building complex queries while still ensuring proper database argument parsing.
    
* **Maintainability:**  `Tqla` allows you to consolidate your query logic into a single template. Other solutions require the separation of conditional logic from statement building.  `Tqla` templates make it easy to express complex filtering and sorting criteria in your SQL queries.
    

To stay in the loop on future development follow us on [**Twitter**](https://twitter.com/VauntDev) or join our [**Discord**](https://discord.com/invite/mn29Xkvry2)!

Don't hesitate to make feature requests.

If you want to see more examples of `tqla` feel free to reach out to us on our [**Discord**](https://discord.com/invite/mn29Xkvry2)or through [GitHub](https://github.com/VauntDev/tqla).

**mas tqla! - VauntDev.**