Golang Postgresql Setup

Tutorial: Accessing a relational database

(Note: go.dev/doc/tutorial/database-access 's Pgsql version)

Table of Contents

  • Prerequisites
  • Create a folder for your code
  • Set up a database
  • Find and import a database driver
  • Get a database handle and connect
  • Query for multiple rows
  • Query for a single row
  • Add data
  • Conclusion
  • Completed code

This tutorial introduces the basics of accessing a relational database with Go and the database/sql package in its standard library.

You’ll get the most out of this tutorial if you have a basic familiarity with Go and its tooling. If this is your first exposure to Go, please see Tutorial: Get started with Go for a quick introduction.

The database/sql package you’ll be using includes types and functions for connecting to databases, executing transactions, canceling an operation in progress, and more. For more details on using the package, see Accessing databases.

In this tutorial, you’ll create a database, then write code to access the database. Your example project will be a repository of data about vintage jazz records.

In this tutorial, you’ll progress through the following sections:

  • Create a folder for your code.
  • Set up a database.
  • Import the database driver.
  • Get a database handle and connect.
  • Query for multiple rows.
  • Query for a single row.
  • Add data.

Prerequisites

  • An installation of the PostgreSQL relational database management system (DBMS).
  • An installation of Go. For installation instructions, see Installing Go.
  • A tool to edit your code. Any text editor you have will work fine.
  • A command terminal. Go works well using any terminal on Linux and Mac, and on PowerShell or cmd in Windows.

Create a folder for your code

To begin, create a folder for the code you’ll write.

  1. Open a command prompt and change to your home directory.

On Linux or Mac:

$ cd

On Windows:

C:\> cd %HOMEPATH%

For the rest of the tutorial we will show a $ as the prompt. The commands we use will work on Windows too.

  1. From the command prompt, create a directory for your code called data-access.
$ mkdir data-access
$ cd data-access
  1. Create a module in which you can manage dependencies you will add during this tutorial.

Run the go mod init command, giving it your new code’s module path.

$ go mod init example/data-access
go: creating new go.mod: module example/data-access

This command creates a go.mod file in which dependencies you add will be listed for tracking. For more, be sure to see Managing dependencies.

Note: In actual development, you’d specify a module path that’s more specific to your own needs. For more, see Managing dependencies.

Next, you’ll create a database.

Set up a database

In this step, you’ll create the database you’ll be working with. You’ll use the CLI for the DBMS itself to create the database and table, as well as to add data.

You’ll be creating a database with data about vintage jazz recordings on vinyl.

The code here uses the PostgreSQL CLI, but most DBMSes have their own CLI with similar features.

  1. Open a new command prompt.

  2. At the command line, log into your DBMS, as in the following example for PostgreSQL.

$ psql -U postgres
Password for user postgres:
  1. At the PostgreSQL command prompt, create a database.
postgres=# CREATE DATABASE recordings;
  1. Change to the database you just created so you can add tables.
postgres=# \c recordings
You are now connected to database "recordings".

  1. In your text editor, in the data-access folder, create a file called create-tables.sql to hold SQL script for adding tables.

  2. Into the file, paste the following SQL code, then save the file.

DROP TABLE IF EXISTS album;

CREATE TABLE album (
  id         BIGSERIAL PRIMARY KEY,
  title      VARCHAR(128) NOT NULL,
  artist     VARCHAR(255) NOT NULL,
  price      NUMERIC(5,2) NOT NULL
);

INSERT INTO album
  (title, artist, price)
VALUES
  ('Blue Train', 'John Coltrane', 56.99),
  ('Giant Steps', 'John Coltrane', 63.99),
  ('Jeru', 'Gerry Mulligan', 17.99),
  ('Sarah Vaughan', 'Sarah Vaughan', 34.98);

In this SQL code, you:

  • Delete (drop) a table called album. Executing this command first makes it easier for you to re-run the script later if you want to start over with the table.
  • Create an album table with four columns: title, artist, and price. Each row’s id value is created automatically by the DBMS.
  • Add four rows with values.
  1. From the PostgreSQL command prompt, run the script you just created.

You’ll use the \i command in the following form:

recordings=# \i /path/to/create-tables.sql
  1. At your DBMS command prompt, use a SELECT statement to verify you’ve successfully created the table with data.
recordings=# SELECT * FROM album;

Example output:

 id |     title      |     artist      | price
----+----------------+-----------------+-------
  1 | Blue Train     | John Coltrane   | 56.99
  2 | Giant Steps    | John Coltrane   | 63.99
  3 | Jeru           | Gerry Mulligan  | 17.99
  4 | Sarah Vaughan  | Sarah Vaughan   | 34.98

Next, you’ll write some Go code to connect so you can query.

Find and import a database driver

Now that you’ve got a database with some data, get your Go code started.

Locate and import a database driver that will translate requests you make through functions in the database/sql package into requests the database understands.

  1. In your browser, visit the SQLDrivers wiki page to identify a driver you can use.

  2. Use the list on the page to identify the driver you’ll use. For accessing PostgreSQL in this tutorial, you’ll use pgx.

  3. Note the package name for the driver – here, github.com/jackc/pgx/v5/stdlib.

  4. Using your text editor, create a file in which to write your Go code and save the file as main.go in the data-access directory you created earlier.

  5. Into main.go, paste the following code to import the driver package.

package main

import _ "github.com/jackc/pgx/v5/stdlib"

In this code, you:

  • Add your code to a main package so you can execute it independently.
  • Import the PostgreSQL driver github.com/jackc/pgx/v5/stdlib.

With the driver imported, you’ll start writing code to access the database.

Get a database handle and connect

Now write some Go code that gives you database access with a database handle.

You’ll use a pointer to an sql.DB struct, which represents access to a specific database.

Write the code

Into main.go, beneath the import code you just added, paste the following Go code to create a database handle.

var db *sql.DB

func main() {
    // Capture connection properties.
    connStr := fmt.Sprintf(
        "host=localhost port=5432 user=%s password=%s dbname=recordings sslmode=disable",
        os.Getenv("DBUSER"),
        os.Getenv("DBPASS"),
    )

    // Get a database handle.
    var err error
    db, err = sql.Open("pgx", connStr)
    if err != nil {
        log.Fatal(err)
    }

    pingErr := db.Ping()
    if pingErr != nil {
        log.Fatal(pingErr)
    }
    fmt.Println("Connected!")
}

In this code, you:

  • Declare a db variable of type *sql.DB. This is your database handle.
  • Making db a global variable simplifies this example. In production, you’d avoid the global variable, such as by passing the variable to functions that need it or by wrapping it in a struct.
  • Use a PostgreSQL connection string to collect connection properties.
  • Call sql.Open to initialize the db variable.
  • Check for an error from sql.Open.
  • Call DB.Ping to confirm that connecting to the database works.
  • Check for an error from Ping, in case the connection failed.
  • Print a message if Ping connects successfully.

Near the top of the main.go file, just beneath the package declaration, import the packages you’ll need to support the code you’ve just written.

The top of the file should now look like this:

package main

import (
    "database/sql"
    "fmt"
    "log"
    "os"

    _ "github.com/jackc/pgx/v5/stdlib"
)

Save main.go.

Run the code

Begin tracking the PostgreSQL driver module as a dependency.

Use go get to add the github.com/jackc/pgx/v5 module as a dependency for your own module.

$ go get .

Go downloaded this dependency because you added it to the import declaration in the previous step.

From the command prompt, set the DBUSER and DBPASS environment variables for use by the Go program.

On Linux or Mac:

$ export DBUSER=username
$ export DBPASS=password

On Windows:

C:\Users\you\data-access> set DBUSER=username
C:\Users\you\data-access> set DBPASS=password

From the command line in the directory containing main.go, run the code.

$ go run .
Connected!

You can connect! Next, you’ll query for some data.

Query for multiple rows

In this section, you’ll use Go to execute an SQL query designed to return multiple rows.

For SQL statements that might return multiple rows, you use the Query method from the database/sql package, then loop through the rows it returns.

Write the code

Into main.go, immediately above func main, paste the following definition of an Album struct.

type Album struct {
    ID     int64
    Title  string
    Artist string
    Price  float32
}

Beneath func main, paste the following albumsByArtist function to query the database.

// albumsByArtist queries for albums that have the specified artist name.
func albumsByArtist(name string) ([]Album, error) {
    var albums []Album

    rows, err := db.Query("SELECT * FROM album WHERE artist = $1", name)
    if err != nil {
        return nil, fmt.Errorf("albumsByArtist %q: %v", name, err)
    }
    defer rows.Close()

    for rows.Next() {
        var alb Album
        if err := rows.Scan(&alb.ID, &alb.Title, &alb.Artist, &alb.Price); err != nil {
            return nil, fmt.Errorf("albumsByArtist %q: %v", name, err)
        }
        albums = append(albums, alb)
    }

    if err := rows.Err(); err != nil {
        return nil, fmt.Errorf("albumsByArtist %q: %v", name, err)
    }

    return albums, nil
}

Update your main function to call albumsByArtist.

albums, err := albumsByArtist("John Coltrane")
if err != nil {
    log.Fatal(err)
}
fmt.Printf("Albums found: %v\n", albums)

Run the code

$ go run .
Connected!
Albums found: [{1 Blue Train John Coltrane 56.99} {2 Giant Steps John Coltrane 63.99}]

Next, you’ll query for a single row.

Query for a single row

In this section, you’ll use Go to query for a single row in the database.

For SQL statements you know will return at most a single row, you can use QueryRow, which is simpler than using a Query loop.

Write the code

Beneath albumsByArtist, paste the following albumByID function.

// albumByID queries for the album with the specified ID.
func albumByID(id int64) (Album, error) {
    var alb Album

    row := db.QueryRow("SELECT * FROM album WHERE id = $1", id)
    if err := row.Scan(&alb.ID, &alb.Title, &alb.Artist, &alb.Price); err != nil {
        if err == sql.ErrNoRows {
            return alb, fmt.Errorf("albumsById %d: no such album", id)
        }
        return alb, fmt.Errorf("albumsById %d: %v", id, err)
    }
    return alb, nil
}

Update main to call albumByID.

// Hard-code ID 2 here to test the query.
alb, err := albumByID(2)
if err != nil {
    log.Fatal(err)
}
fmt.Printf("Album found: %v\n", alb)

Run the code

$ go run .
Connected!
Albums found: [{1 Blue Train John Coltrane 56.99} {2 Giant Steps John Coltrane 63.99}]
Album found: {2 Giant Steps John Coltrane 63.99}

Next, you’ll add an album to the database.

Add data

In this section, you’ll use Go to execute an SQL INSERT statement to add a new row to the database.

You’ve seen how to use Query and QueryRow with SQL statements that return data. To execute SQL statements that don’t return data, you use Exec.

Write the code

Beneath albumByID, paste the following addAlbum function to insert a new album in the database, then save the main.go.

// addAlbum adds the specified album to the database,
// returning the album ID of the new entry
func addAlbum(alb Album) (int64, error) {
    var id int64

    err := db.QueryRow(
        "INSERT INTO album (title, artist, price) VALUES ($1, $2, $3) RETURNING id",
        alb.Title,
        alb.Artist,
        alb.Price,
    ).Scan(&id)

    if err != nil {
        return 0, fmt.Errorf("addAlbum: %v", err)
    }

    return id, nil
}

Update main to call the new addAlbum function.

albID, err := addAlbum(Album{
    Title:  "The Modern Sound of Betty Carter",
    Artist: "Betty Carter",
    Price:  49.99,
})
if err != nil {
    log.Fatal(err)
}
fmt.Printf("ID of added album: %v\n", albID)

Run the code

$ go run .
Connected!
Albums found: [{1 Blue Train John Coltrane 56.99} {2 Giant Steps John Coltrane 63.99}]
Album found: {2 Giant Steps John Coltrane 63.99}
ID of added album: 5

Conclusion

Congratulations! You’ve just used Go to perform simple actions with a relational database.

Suggested next topics:

  • Take a look at the data access guide, which includes more information about the subjects only touched on here.
  • If you’re new to Go, you’ll find useful best practices described in Effective Go and How to write Go code.
  • The Go Tour is a great step-by-step introduction to Go fundamentals.

Completed code

package main

import (
    "database/sql"
    "fmt"
    "log"
    "os"

    _ "github.com/jackc/pgx/v5/stdlib"
)

var db *sql.DB

type Album struct {
    ID     int64
    Title  string
    Artist string
    Price  float32
}

func main() {
    connStr := fmt.Sprintf(
        "host=localhost port=5432 user=%s password=%s dbname=recordings sslmode=disable",
        os.Getenv("DBUSER"),
        os.Getenv("DBPASS"),
    )

    var err error
    db, err = sql.Open("pgx", connStr)
    if err != nil {
        log.Fatal(err)
    }

    if err := db.Ping(); err != nil {
        log.Fatal(err)
    }

    fmt.Println("Connected!")

    albums, err := albumsByArtist("John Coltrane")
    if err != nil {
        log.Fatal(err)
    }
    fmt.Printf("Albums found: %v\n", albums)

    alb, err := albumByID(2)
    if err != nil {
        log.Fatal(err)
    }
    fmt.Printf("Album found: %v\n", alb)

    albID, err := addAlbum(Album{
        Title:  "The Modern Sound of Betty Carter",
        Artist: "Betty Carter",
        Price:  49.99,
    })
    if err != nil {
        log.Fatal(err)
    }
    fmt.Printf("ID of added album: %v\n", albID)
}

func albumsByArtist(name string) ([]Album, error) {
    var albums []Album

    rows, err := db.Query(
        "SELECT * FROM album WHERE artist = $1",
        name,
    )
    if err != nil {
        return nil, fmt.Errorf("albumsByArtist %q: %v", name, err)
    }
    defer rows.Close()

    for rows.Next() {
        var alb Album
        if err := rows.Scan(
            &alb.ID,
            &alb.Title,
            &alb.Artist,
            &alb.Price,
        ); err != nil {
            return nil, fmt.Errorf("albumsByArtist %q: %v", name, err)
        }
        albums = append(albums, alb)
    }

    if err := rows.Err(); err != nil {
        return nil, fmt.Errorf("albumsByArtist %q: %v", name, err)
    }

    return albums, nil
}

func albumByID(id int64) (Album, error) {
    var alb Album

    row := db.QueryRow(
        "SELECT * FROM album WHERE id = $1",
        id,
    )

    if err := row.Scan(
        &alb.ID,
        &alb.Title,
        &alb.Artist,
        &alb.Price,
    ); err != nil {
        if err == sql.ErrNoRows {
            return alb, fmt.Errorf("albumsById %d: no such album", id)
        }
        return alb, fmt.Errorf("albumsById %d: %v", id, err)
    }

    return alb, nil
}

func addAlbum(alb Album) (int64, error) {
    var id int64

    err := db.QueryRow(
        "INSERT INTO album (title, artist, price) VALUES ($1, $2, $3) RETURNING id",
        alb.Title,
        alb.Artist,
        alb.Price,
    ).Scan(&id)

    if err != nil {
        return 0, fmt.Errorf("addAlbum: %v", err)
    }

    return id, nil
}