Building SQL statements with a little tqla…
Table of contents
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 ( pronounced tequila ) that leverages Golang’s powerful 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 from Digital Ocean.
All of the code for this article can be found on GitHub.
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, 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:
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:
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:
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.
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 serval 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:
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. 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.
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.
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
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.
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.
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:
// 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:
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
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.
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 doc and checking out any open issues.
To help thank tqla contributors, we have enabled several custom achievements that can be earned, powered by Vaunt. We believe in the tools we are building and of course, leverage them ourselves.
Go check out our tqla's community page to see the latest contributors who have earned achievements on tqla!
The following manifest outlines how achievements can be earned on tqla
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.
You can configure custom achievements on any GitHub repository by installing the Vaunt application through the GitHub Marketplace.
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.
Tqlaprevents 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.
Tqlastays out of your way when building complex queries while still ensuring proper database argument parsing.Maintainability:
Tqlaallows you to consolidate your query logic into a single template. Other solutions require the separation of conditional logic from statement building.Tqlatemplates 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 or join our Discord!
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 or through GitHub.
mas tqla! - VauntDev.
Top comments (0)