DEV Community

Cover image for PostgreSQL with Go in 2021
Kushagra Mehta
Kushagra Mehta

Posted on

PostgreSQL with Go in 2021


If you’re a modern web developer, it is inevitable to ignore a database for long as it plays an important part in the application.

So in this post, I’ll be demonstrating how we can connect to a PostgreSQL database and perform basic SQL statements using Go.


You'll need Go version 1.16+ and PostgreSQL installed on your development machine.

In order to connect with PostgreSQL we need driver, So we'll use pgx as our driver.

Code time 🚀

Code v0.1 🌎


Let's Start with a simple "Hello World.!" code.

Let’s create a new main.go file. Within this, we’ll import a few packages and set up a simple connection to an already running local database. for this tutorial, I'm using postgres as username, 123 as password, localhost network address, :5432 default port, and test database.

You can change according to your setup.

Enter fullscreen mode Exit fullscreen mode

Now open main.go and write the following code.

package main

import (


func main() {

    // Open up our database connection.
    conn, _ := pgx.Connect(context.Background(), "postgres://postgres:123@localhost:5432/test")

    // defer the close till after the main function has finished
    // executing
    defer conn.Close(context.Background())
    var greeting string
    conn.QueryRow(context.Background(), "select 'Hello, world!'").Scan(&greeting)
Enter fullscreen mode Exit fullscreen mode

pgx.Connec() establishes a connection with a PostgreSQL server with a connection string, this will return pgx.Conn is a PostgreSQL connection handle.

conn.QueryRow() executes sql query on the database, After that we store the response of data using .Scan()

Code v1.0

So, now that we’ve successfully created a connection and build hello world with the database. Now let's start with a table and perform some queries over it.


Now we build a program where we can insert and fetch user data. We will understand various functions in the pgx package.

Creating Table into PostgreSQL

        ID          SERIAL   PRIMARY KEY,
Enter fullscreen mode Exit fullscreen mode

Table name is Users with ID and USERNAME columns.

Creating user struct

// User is the model present in the database
type User struct {
    ID       int    `json:"id"`
    UserName string `json:"username"`
Enter fullscreen mode Exit fullscreen mode

Inserting User into database

So if we want to store a user into the database.

//Creating temporary user object.
tmpUser := User{UserName: "Captain K"}
//Calling InsertUser Method
InsertUser(&tmpUser, conn)

func InsertUser(u *User, conn *pgx.Conn) {
    // Executing SQL query for insertion
    if _, err := conn.Exec(context.Background(), "INSERT INTO USERS(USERNAME) VALUES($1)", u.UserName); err != nil {
        // Handling error, if occur
        fmt.Println("Unable to insert due to: ", err)
    fmt.Println("Insertion Succesfull")
Enter fullscreen mode Exit fullscreen mode

Querying Multiple Rows

When we want to read all the users stored in the database.

func GetAllUsers(conn *pgx.Conn) {
    // Execute the query
    if rows, err := conn.Query(context.Background(), "SELECT * FROM USERS"); err != nil {
        fmt.Println("Unable to insert due to: ", err)
    } else {
        // carefully deferring Queries closing
        defer rows.Close()

        // Using tmp variable for reading
        var tmp User

        // Next prepares the next row for reading.
        for rows.Next() {
            // Scan reads the values from the current row into tmp
            rows.Scan(&tmp.ID, &tmp.UserName)
            fmt.Printf("%+v\n", tmp)
        if rows.Err() != nil {
            // if any error occurred while reading rows.
            fmt.Println("Error will reading user table: ", err)
Enter fullscreen mode Exit fullscreen mode

Querying a Single Row

Find a user using user's ID.

func GetAnUser(id int, conn *pgx.Conn) {
    // variable to store username
    var username string

    // Executing query for single row
    if err := conn.QueryRow(context.Background(), "SELECT USERNAME WHERE ID=$1", id).Scan(&username); err != nil {
        fmt.Println("Error occur while finding user: ", err)
    fmt.Printf("User with id=%v is %v\n", id, username)
Enter fullscreen mode Exit fullscreen mode

Conclusion 🎉

In this post, we managed to set up a connection to a PostgreSQL and then perform some simple queries to that database and marshal the returned responses into a struct. This should hopefully give you everything you need in order to take things further and build your own Go applications on top of PostgreSQL.

  • When to use db.Exec or db.Query ?
  • We can use pgx and pgxpool interchangeably but pgxpool is a concurrency-safe connection pool for pgx. It is not safe for concurrent usage. Using a connection pool to manage access to multiple database connections from multiple goroutines.

Recommended Reading: REST server with Go in 5 minutes

post banner

Top comments (3)

lomolo profile image
Edwin Moses • Edited

Btw, if you see this issue fixed by updating query for getting user by id to

select username from users where id = $1

birowo profile image
sonu_sharma profile image
sonu sharma

how to get column names ?