Using Postgres With Golang

Hello!

Today we will look at how we can use golang to connect to the Postgres database and execute queries. To work with the database we will use github.com/jackc/pgx.

Let’s start with initializing the module and installing the driver for the database.

go mod init
go get github.com/jackc/pgx/v4

After that pgx will be available for use in code.

For the database, I am using docker, but it can be another type of installation. To do this, create a file docker-compose.yml with content:

version: '3'

services:
  Postgres:
    image: Postgres
    tty: true
    restart: always
    ports:
      - "5432:5432"
    environment:
      - POSTGRES_PASSWORD=postgres

And execute the command docker-compose up -d. Docker-compose and docker must be installed. The database is ready to work, and now we can start writing code.

Let’s start with the connection to the database. First, we create a configuration cfg which will have the address, port, user, password, and database to which we connect. Since my database works locally, I specify localhost as the host. When the configuration is ready, call pgx.Connect (cfg) to create a connection. After finishing work with the database connection it is necessary to close therefore we will add defer conn.Close() and we check for errors. Also to check that the database is responding we will run conn.Ping(context.Background())

package main

import (
    "context"
    "fmt"
    "log"
    "os"

    "github.com/jackc/pgx"
)

func main() {
    cfg := pgx.ConnConfig{
        Host:     "localhost",
        Port:     5432,
        User:     "postgres",
        Password: "postgres",
        Database: "postgres",
    }
    conn, err := pgx.Connect(cfg)
    if err != nil {
        fmt.Fprintf(os.Stderr, "Unable to connect to database: %v\n", err)
        log.Fatalf("Connect to database failed: %v\n", err)
    }
    defer conn.Close()

    err = conn.Ping(context.Background())
    if err != nil {
        log.Fatalf("Ping Failed: %v\n", err)
    }
}

We can start making inquiries. To begin, create a new table and write data to it. The Exec method is used to call requests that do not return anything.

    _, err = conn.Exec(`
        CREATE TABLE IF NOT EXISTS users(
            id SERIAL PRIMARY KEY,
            name TEXT,
            email TEXT UNIQUE NOT NULL
        );
    `)
    if err != nil {
        log.Fatalln(err)
    }

    name := "TestUser1"
    email := "testuser@gmail.com"
    _, err = conn.Exec(`
        INSERT INTO users(name, email)
        VALUES ($1, $2);
    `, name, email)
    if err != nil {
        log.Fatalln(err)
    }

The data has been created and we can try to read it. If one record is returned from the database, you can use the QueryRow method, if many, then Query. Also, in order to read the data you need the structure, we will have the structure name User which will have the same parameters as the fields in the table.

type User struct {
	Id    int
	Name  string
	Email string
}

And the request itself to get the data

	user := User{}
	err = conn.QueryRow("SELECT * FROM users WHERE name=$1;", name).Scan(&user.Id, &user.Name, &user.Email)
	if err != nil {
		fmt.Println(err)
		return
	}
	fmt.Println(user)

Pgx can also be used as a driver for the built-in sql package