MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for the ‘GO’ Category

Go Config & PostgreSQL

without comments

Finally, I got around to building a GO environment with PostgreSQL on Ubuntu. Next, I’ll need to sort out how to extend it to Oracle and MySQL. The only tedious part was figuring out where the current PostgreSQL ODBC driver was on GitHub.

The installation of GO has two options. You can install the version from the Linux Distro’s repo, or you can install the most current version from the GO website. The generic install on Ubuntu uses the following two commands:

sudo apt update
sudo apt install -y golang-go

This will install GO in the /usr/bin directory, which means you don’t need to add it manually to your $PATH environment variable in your .bashrc file.

Download from the GO website into your /home/username/Downloads directory and then run this command:

sudo tar -C /usr/local -xzf /home/student/Downloads/go1.24.0.linux-amd64.tar.gz

If you haven’t installed the distro’s repo of the GO programming environment, you should add this to your .bashrc file:

# Added to PATH for Go
export set PATH=$PATH:/usr/local/go/bin

However, add the following if you have installed the Linux distorts GO repo and prefer to use the most current release available (a which -a go command displays the go program files in your current $PATH variable and you can call the first one in the list without an absolute or fully qualified file name but the second requires a fully qualified file name):

# Added to PATH for Go
export set PATH=/usr/local/go/bin:$PATH

Now, you should decide where you want to put your GO programs. I chose to create a base .go directory and then project directories beneath that. Within each project directory you should create bin, pkg, and src subdirectories, as shown below:

mkdir .go .go/hello .go/hello/bin .go/hello/pkg .go/hello/src

You can write a “Hello World” program like the following in you ~.go/hello/src directory, which I’ve named hello-world.go for illustrative purposes:

// Declare package.
package main
 
// Import library.
import "fmt"
 
// Define main function using fmt library to print.
func main() {
    fmt.Println("Hello World!")
}

Next, you should create a makefile, like this in the same ~.go/hello/src directory. If you’re new to the rules for creating a makefile, there can be no leading or trailing white spaces, and you should use before commands:

.DEFAULT_GOAL := build
 
.PHONY: fmt vet build
fmt:
	go fmt ./...
 
vet: fmt
	go vet ./...
 
build: vet
	go build -o ../bin/hello hello-world.go

Before you run the make utility, you need to initialize the module with this syntax:

go mod init

It will create the go.mod file. You will see something like the following when you display the content of the go.mod file.

module hello-world.go
 
go 1.24.0

You have completed your project preparation. You run the make command to create the hello executable in the ~/.go/hello/bin directory.

Run the make utility:

make

You can run it from your current directory with this command:

~/.go/hello/bin/hello

It will return to console:

Hello World!

Having built the GO development environment and a rudimentary starting program, it’s time to learn how to write a GO program that queries the PostgreSQL database. The first step is finding the correct PostgreSQL library in GitHub.com. A number of web sites post examples using the pq library but that’s no longer correct.

You need to download the pgx ODBC driver with the following GO command:

go get github.com/jackc/pgx@latest

It will display the following to the console:

go: downloading github.com/jackc/pgx v3.6.2+incompatible
go: downloading github.com/pkg/errors v0.9.1
go: downloading golang.org/x/crypto v0.35.0
go: downloading golang.org/x/text v0.22.0
go: added github.com/jackc/pgx v3.6.2+incompatible
go: added github.com/pkg/errors v0.9.1
go: added golang.org/x/crypto v0.35.0
go: added golang.org/x/text v0.22.0

To use the standard database/sql approach you’ll also need to run this command:

go get github.com/jackc/pgx/v5/stdlib

It will display the following to the console:

go: downloading github.com/jackc/pgx/v5 v5.7.2
go: downloading github.com/jackc/pgpassfile v1.0.0
go: downloading github.com/jackc/pgservicefile v0.0.0-20240606120523-5a60cdf6a761
go: downloading github.com/jackc/puddle/v2 v2.2.2
go: downloading golang.org/x/sync v0.11.0
go: added github.com/jackc/pgpassfile v1.0.0
go: added github.com/jackc/pgservicefile v0.0.0-20240606120523-5a60cdf6a761
go: added github.com/jackc/pgx/v5 v5.7.2
go: added github.com/jackc/puddle/v2 v2.2.2

Change back to your home directory and run this command to build a new connect project:

mkdir .go/postgres .go/postgres/connect .go/postgres/connect/bin .go/postgres/connect/pkg .go/postgres/connect/src

Create postgres-connect.go program in the .go/postgres/connect/src directory:

package main
 
import (
	"database/sql"
	"fmt"
	"os"
 
	_ "github.com/jackc/pgx/v5/stdlib"
)
 
func main() {
	// Open connection and defer connection close.
	db, err := sql.Open("pgx", "host=localhost user=student password=student dbname=videodb sslmode=disable")
	if err != nil {
		fmt.Fprintf(os.Stderr, "Unable to connect to database: %v\n", err)
		os.Exit(1)
	}
	defer db.Close()
 
        // Declare an output variable for the query.
	var version string
	err = db.QueryRow("SELECT version()").Scan(&version)
	if err != nil {
		fmt.Fprintf(os.Stderr, "QueryRow failed: %v\n", err)
		os.Exit(1)
	}
 
	// Print database version.
	fmt.Println(version)
}

Next, you should create another makefile in the same ~.go/postgres/connect/src directory.

.DEFAULT_GOAL := build
 
.PHONY: fmt vet build
fmt:
	go fmt ./...
 
vet: fmt
	go vet ./...
 
build: vet
	go build -o ../bin/connect postgres-connect.go

Before you run the make utility, you need to initialize the go.mod module file and check for dependencies in it. That requires the following two commands:

go mod init

Which returns the following list of dependencies:

go: finding module for package github.com/jackc/pgx/v5/stdlib
go: found github.com/jackc/pgx/v5/stdlib in github.com/jackc/pgx/v5 v5.7.2

There are two ways to fix this but the easiest is to run this command:

go mod tidy

It adds the dependencies to the go.mod file. You will see something like the following when you display the content of the go.mod file.

module postgres-connect.go
 
go 1.24.0
 
require github.com/jackc/pgx/v5 v5.7.2
 
require (
	github.com/jackc/pgpassfile v1.0.0 // indirect
	github.com/jackc/pgservicefile v0.0.0-20240606120523-5a60cdf6a761 // indirect
	github.com/jackc/puddle/v2 v2.2.2 // indirect
	golang.org/x/crypto v0.31.0 // indirect
	golang.org/x/sync v0.10.0 // indirect
	golang.org/x/text v0.21.0 // indirect
)

You should also note that the go mod tidy command created new go.sum file for the dependencies, which should look like the following:

github.com/davecgh/go-spew v1.1.0/go.mod h1:J7Y8YcW2NihsgmVo/mv3lAwl/skON4iLHjSsI+c5H38=
github.com/davecgh/go-spew v1.1.1 h1:vj9j/u1bqnvCEfJOwUhtlOARqs3+rkHYY13jYWTU97c=
github.com/davecgh/go-spew v1.1.1/go.mod h1:J7Y8YcW2NihsgmVo/mv3lAwl/skON4iLHjSsI+c5H38=
github.com/jackc/pgpassfile v1.0.0 h1:/6Hmqy13Ss2zCq62VdNG8tM1wchn8zjSGOBJ6icpsIM=
github.com/jackc/pgpassfile v1.0.0/go.mod h1:CEx0iS5ambNFdcRtxPj5JhEz+xB6uRky5eyVu/W2HEg=
github.com/jackc/pgservicefile v0.0.0-20240606120523-5a60cdf6a761 h1:iCEnooe7UlwOQYpKFhBabPMi4aNAfoODPEFNiAnClxo=
github.com/jackc/pgservicefile v0.0.0-20240606120523-5a60cdf6a761/go.mod h1:5TJZWKEWniPve33vlWYSoGYefn3gLQRzjfDlhSJ9ZKM=
github.com/jackc/pgx/v5 v5.7.2 h1:mLoDLV6sonKlvjIEsV56SkWNCnuNv531l94GaIzO+XI=
github.com/jackc/pgx/v5 v5.7.2/go.mod h1:ncY89UGWxg82EykZUwSpUKEfccBGGYq1xjrOpsbsfGQ=
github.com/jackc/puddle/v2 v2.2.2 h1:PR8nw+E/1w0GLuRFSmiioY6UooMp6KJv0/61nB7icHo=
github.com/jackc/puddle/v2 v2.2.2/go.mod h1:vriiEXHvEE654aYKXXjOvZM39qJ0q+azkZFrfEOc3H4=
github.com/pmezard/go-difflib v1.0.0 h1:4DBwDE0NGyQoBHbLQYPwSUPoCMWR5BEzIk/f1lZbAQM=
github.com/pmezard/go-difflib v1.0.0/go.mod h1:iKH77koFhYxTK1pcRnkKkqfTogsbg7gZNVY4sRDYZ/4=
github.com/stretchr/objx v0.1.0/go.mod h1:HFkY916IF+rwdDfMAkV7OtwuqBVzrE8GR6GFx+wExME=
github.com/stretchr/testify v1.3.0/go.mod h1:M5WIy9Dh21IEIfnGCwXGc5bZfKNJtfHm1UVUgZn+9EI=
github.com/stretchr/testify v1.7.0/go.mod h1:6Fq8oRcR53rry900zMqJjRRixrwX3KX962/h/Wwjteg=
github.com/stretchr/testify v1.8.1 h1:w7B6lhMri9wdJUVmEZPGGhZzrYTPvgJArz7wNPgYKsk=
github.com/stretchr/testify v1.8.1/go.mod h1:w2LPCIKwWwSfY2zedu0+kehJoqGctiVI29o6fzry7u4=
golang.org/x/crypto v0.31.0 h1:ihbySMvVjLAeSH1IbfcRTkD/iNscyz8rGzjF/E5hV6U=
golang.org/x/crypto v0.31.0/go.mod h1:kDsLvtWBEx7MV9tJOj9bnXsPbxwJQ6csT/x4KIN4Ssk=
golang.org/x/sync v0.10.0 h1:3NQrjDixjgGwUOCaF8w2+VYHv0Ve/vGYSbdkTa98gmQ=
golang.org/x/sync v0.10.0/go.mod h1:Czt+wKu1gCyEFDUtn0jG5QVvpJ6rzVqr5aXyt9drQfk=
golang.org/x/text v0.21.0 h1:zyQAAkrwaneQ066sspRyJaG9VNi/YJ1NfzcGB3hZ/qo=
golang.org/x/text v0.21.0/go.mod h1:4IBbMaMmOPCJ8SecivzSH54+73PCFmPWxNTLm+vZkEQ=
gopkg.in/check.v1 v0.0.0-20161208181325-20d25e280405/go.mod h1:Co6ibVJAznAaIkqp8huTwlJQCZ016jof/cbN4VW5Yz0=
gopkg.in/yaml.v3 v3.0.0-20200313102051-9f266ea9e77c/go.mod h1:K4uyk7z7BCEPqu6E+C64Yfv1cQ7kz7rIZviUmN+EgEM=
gopkg.in/yaml.v3 v3.0.1 h1:fxVm/GzAzEWqLHuvctI91KS9hhNmmWOoWu0XTYJS7CA=
gopkg.in/yaml.v3 v3.0.1/go.mod h1:K4uyk7z7BCEPqu6E+C64Yfv1cQ7kz7rIZviUmN+EgEM=

Run the make utility:

make

You can now run make to create the connect executable, which you can call with the following command:

~/.go/postgres/connect/bin/connect

It will return to console:

PostgreSQL 14.17 (Ubuntu 14.17-0ubuntu0.22.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit

Written by maclochlainn

March 4th, 2025 at 11:04 pm

Posted in GO,GOLANG,PostgreSQL,PostgreSQL 14,Ubuntu

Tagged with ,