PostgreSQL Query Builder for Go (GoLang)

PostgreSQL is a popular open-source relational database management system. It offers a wide range of features that are useful for managing and querying data. When working with a PostgreSQL database, it is common to use a query builder to simplify the process of constructing SQL queries. Go is a popular programming language that is commonly used for building web applications. In this article, we will discuss the benefits of using a PostgreSQL query builder for the Go programming language, especially for beginners.

Why Use a PostgreSQL Query Builder?

Constructing SQL queries manually can be challenging, especially for beginners. SQL syntax is complex and requires a lot of knowledge and practice to master. A query builder can simplify this process by providing an intuitive interface for constructing queries. A query builder allows you to construct complex queries with ease, without having to write complex SQL syntax manually.

The following are some of the benefits of using a PostgreSQL query builder:

1. Easy to Learn

A query builder provides an intuitive interface for constructing SQL queries, which makes it easy to learn. You don't need to have a deep understanding of SQL syntax to use a query builder. This makes it an excellent tool for beginners who are just getting started with database programming.

1. Saves Time and Effort

Constructing SQL queries manually can be time-consuming and error-prone. A query builder can save you a lot of time and effort by providing an interface for constructing queries quickly and easily. You can focus on the logic of your application rather than the details of SQL syntax.

1. Avoids Common SQL Errors

SQL syntax is complex, and it is easy to make mistakes when constructing queries manually. A query builder can help you avoid common SQL errors, such as syntax errors, typos, and logical errors. This can save you a lot of time and effort debugging your queries.

1. Provides Abstraction

A query builder provides a layer of abstraction between your application and the database. This means that you can change the underlying database without having to change your application code. This is especially useful when working with multiple databases or when migrating from one database to another.

1. Facilitates Code Reusability

A query builder allows you to construct queries programmatically. This means that you can reuse query logic across your application. This can help you write cleaner, more maintainable code.

PostgreSQL Query Builder for Go

Go is a popular programming language for building web applications. It offers excellent performance and is easy to learn. When working with a PostgreSQL database, it is common to use a query builder to simplify the process of constructing SQL queries.

There are several PostgreSQL query builders available for the Go programming language. One of the most popular ones is the Go-Postgresql-Query-Builder (pqb) full diclosure here, I wrote this one!. This library provides a simple and intuitive interface for constructing SQL queries. It is easy to learn and can save you a lot of time and effort when working with a PostgreSQL database.

This query builder aims to make complex queries for postgres easier to breakdown, put together, and read. The project is very much in its infancy, but is also in a very usable and functional state.

However, please feel free to test, fork, or submit PRs.

:-)

Install

go get github.com/SamuelBanksTech/Go-Postgresql-Query-Builder

Usage

Every example of usage would be unrealistic to show here, but once you become familiar, it becomes quite intuitive.

This query builder is best used with pgx by jackc but realistically this can be used with any postgres connection.

Basic Example

package main

import (
	"context"
	"fmt"
	"os"

	"github.com/SamuelBanksTech/Go-Postgresql-Query-Builder/pqb"
	"github.com/jackc/pgx/v4"
)

func main() {
	// urlExample := "postgres://username:password@localhost:5432/database_name"
	conn, err := pgx.Connect(context.Background(), os.Getenv("DATABASE_URL"))
	if err != nil {
		fmt.Fprintf(os.Stderr, "Unable to connect to database: %v\n", err)
		os.Exit(1)
	}
	defer conn.Close(context.Background())

	var qb pqb.Sqlbuilder

	pgQuery, queryArgs := qb.
		From(`myschema.widgets`).
		Select(`name`, `weight`).
		Where(`id`, `=`, `1`).
		Build()

	var name string
	var weight int64
	err = conn.QueryRow(context.Background(), pgQuery, queryArgs...).Scan(&name, &weight)
	if err != nil {
		fmt.Fprintf(os.Stderr, "QueryRow failed: %v\n", err)
		os.Exit(1)
	}

	fmt.Println(name, weight)
}

pgQuery Output:

SELECT "name", "weight" FROM "myschema"."widgets" WHERE "id" = $1

queryArgs Output:

[1]

Slightly More Advanced Example

package main

import (
	"context"
	"fmt"
	"os"

	"github.com/SamuelBanksTech/Go-Postgresql-Query-Builder/pqb"
	"github.com/jackc/pgx/v4"
)

func main() {
	// urlExample := "postgres://username:password@localhost:5432/database_name"
	conn, err := pgx.Connect(context.Background(), os.Getenv("DATABASE_URL"))
	if err != nil {
		fmt.Fprintf(os.Stderr, "Unable to connect to database: %v\n", err)
		os.Exit(1)
	}
	defer conn.Close(context.Background())

	var qb pqb.Sqlbuilder

	pgQuery, queryArgs := qb.
		From(`myschema.tasks`).
		LeftJoin(`myschema.users`, `users`, `myschema.tasks.user_id = users.id`).
		Where(`users.active`, `=`, `1`).
		Where(`myschema.tasks.completed`, `=`, `0`).
		Select(`myschema.tasks.task_details`, `users.name`, `users.email`).
		Build()


	rows, _ := conn.Query(context.Background(), pgQuery, queryArgs...)

	for rows.Next() {
		var taskData string
		var userName string
		var userEmail string
		
		err := rows.Scan(&taskData, &userName, &userEmail)
		if err != nil {
			log.Fatal(err)
		}
		fmt.Printf("%s - %s - %s\n", taskData, userName, userEmail)
	}
}

Even More Advanced Example Using Programmatic Query Clauses

package main

import (
	"context"
	"fmt"
	"os"
	"strings"

	"github.com/SamuelBanksTech/Go-Postgresql-Query-Builder/pqb"
	"github.com/jackc/pgx/v4"
)

type SearchFilters struct {
	IncludeAuthorDetails bool
	TitleSearch          string
	AuthorSearch         string
}

func main() {
	urlExample := "postgres://username:password@localhost:5432/database_name"
	conn, err := pgx.Connect(context.Background(), urlExample)
	if err != nil {
		fmt.Fprintf(os.Stderr, "Unable to connect to database: %v\n", err)
		os.Exit(1)
	}

	defer conn.Close(context.Background())

	filters := SearchFilters{
		IncludeAuthorDetails: true,
		TitleSearch:          "revenge gopher",
		AuthorSearch:         "",
	}

	pgQuery, queryArgs := filterQuery(filters)

	rows, _ := conn.Query(context.Background(), pgQuery, queryArgs...)

	for rows.Next() {
		var bookId int
		var bookTitle string

		if filters.IncludeAuthorDetails {
			var authorName string
			var authorEmail string

			err := rows.Scan(&bookId, &bookTitle, &authorName, &authorEmail)
			if err != nil {
				log.Fatal(err)
			}
			fmt.Printf("%d - %s - %s - %s\n", bookId, bookTitle, authorName, authorEmail)

		} else {
			err := rows.Scan(&bookId, &bookTitle)
			if err != nil {
				log.Fatal(err)
			}
			fmt.Printf("%d - %s\n", bookId, bookTitle)

		}
	}
}

func filterQuery(filters SearchFilters) (string, []interface{}) {

	var query pqb.Sqlbuilder

	query.
		From(`myschema.books`).
		Where(`myschema.books.deleted`, `=`, `0`).
		Select(`myschema.books.id`, `myschema.books.title`)

	if filters.IncludeAuthorDetails {
		query.
			LeftJoin(`myschema.authors`, `authors`, `myschema.books.author_id = authors.id`).
			Select(`authors.name`, `authors.email`)
	}

	if len(filters.TitleSearch) > 0 {
		titleSearchWords := strings.Fields(filters.TitleSearch)

		query.WhereStringMatchAny(`myschema.books.title`, titleSearchWords)
	}

	if len(filters.AuthorSearch) > 0 {
		authorSeachWords := strings.Fields(filters.AuthorSearch)

		if !filters.IncludeAuthorDetails {
			query.LeftJoin(`myschema.authors`, `authors`, `myschema.books.author_id = authors.id`)
		}

		query.WhereStringMatchAny(`authors.name`, authorSeachWords)
	}

	return query.Build()
}

Insert Example

package main

import (
	"context"
	"fmt"
	"os"

	"github.com/SamuelBanksTech/Go-Postgresql-Query-Builder/pqb"
	"github.com/jackc/pgx/v4"
)

type BookData struct {
	Title string
	Author string `pqb:"writer"` // notice the pqb field tag override
}

func main() {
	// urlExample := "postgres://username:password@localhost:5432/database_name"
	conn, err := pgx.Connect(context.Background(), os.Getenv("DATABASE_URL"))
	if err != nil {
		fmt.Fprintf(os.Stderr, "Unable to connect to database: %v\n", err)
		os.Exit(1)
	}
	defer conn.Close(context.Background())

	bd := BookData{
		Title:  "Revenge of the Gophers",
		Author: "Mr Cool Dev",
	}
	
	var qb pqb.Sqlbuilder
	pgQuery, err := qb.BuildInsert(`myschema.books`, bd, ``)
	if err != nil {
		log.Fatal(err)
    }

	_, err = conn.Exec(context.Background(), pgQuery)
	if err != nil {
		log.Fatal(err)
    }
}

Query Output:

INSERT INTO "myschema"."books" ("title", "writer") VALUES ('Revenge of the Gophers', 'Mr Cool Dev')