Connecting to PostgreSQL Database from Go using pgxpool

Go is one of the hot programming language for web development(Backend). If you're new to backend development with Go, this guide will teach you how to connect your Go programs to a PostgreSQL database. You, will get a complete step by step guide to start from scratch.

PostgreSQL (often called "Postgres") is like a structured database that stores your data in an organized way. It's one of the most popular databases used by developers worldwide.

pgx and pgxpool

Prevously, we used pgx to connect to postgres database from Go. Now, we are using pgxpool because of various advantages over pgx. Some of the problems with pgx package and how pgxpool overcomes these problems are mentioned below.

pgx (Single Connection) Problems:

• Slow: Each database operation has to wait for the previous one to finish
• Inefficient: Creating new connections is expensive and time-consuming
• Limited: Can only handle one operation at a time
• Unreliable: If the connection breaks, your entire app stops working
• Single connection Example: One checkout lane at a grocery store (everyone waits in line)

pgxpool (Connection Pool) Benefits:

• Fast: Multiple operations can happen simultaneously
• Efficient: Reuses existing connections instead of creating new ones
• Scalable: Can handle many users at the same time
• Reliable: If one connection fails, others keep working
• Connection pool Example: Multiple checkout lanes in a grocery store (much faster, no waiting!)

Requirements

1. Go programming language installed - Download it here (we're using version 1.26.2)
2. PostgreSQL database installed
3. Basic Go knowledge - You should know what functions and imports are

Create Go Project

Now, we will create a new folder for our project. Open our terminal (command line for windows) and type these commands:

mkdir go_postgres          # This creates a new folder called "go_postgres"
cd go_postgres            # This moves you into that folder
go mod init go_postgres   # This tells Go "this is a new Go project"

Install Packages

We need two packagese to make our Go program work with PostgreSQL:

# This installs the PostgreSQL driver (the bridge between Go and PostgreSQL)
go get github.com/jackc/pgx/v5/pgxpool

# This helps us read from .env file to secure passwords
go get github.com/joho/godotenv

Important: Your Go version should be at least 1.18 to use pgx.

Set Up PostgreSQL Database

Now we need to create a place in PostgreSQL where our data will live. We'll create a database, a user, and a table with some sample data.

Open PostgreSQL

On your terminal, run these commands to access PostgreSQL:

sudo su postgres    # Switch to the postgres user

psql               # Open the PostgreSQL command line

You should now see something like postgres=# - this means you're inside PostgreSQL!

Create a Database

A database is like a container that holds all your tables. Let's create one:

CREATE DATABASE go_post_db;

Create a Database User

Just like your computer has user accounts, PostgreSQL has users too. Let's create one with password admin:

CREATE USER go_post_user WITH PASSWORD 'admin';

Give the User Permission

We need to give our user permission to do things in the database:

ALTER USER go_post_user with SUPERUSER;

Important Note: Making a user "SUPERUSER" gives them all permissions. This is fine for learning, but in real projects, you'd give more limited permissions for security.

Grant Database Access

Now let's give our user access to our specific database:

GRANT ALL PRIVILEGES ON DATABASE go_post_db TO go_post_user;

Gives our user permission to read, write, and modify data in our "gopostdb" database.

Switch to Our Database

Let's start using our new database. This command witches to our "gopostdb" database. You should see the prompt change to go_post_db=#

\c go_post_db; 

Create a Table

A table is like a spreadsheet where we store related information. Let's create a "User" table:

CREATE TABLE "users" (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    address VARCHAR(100)
);

• id SERIAL PRIMARY KEY - Each user gets a unique number automatically
• name VARCHAR(100) NOT NULL - User's name (required, up to 100 characters)
• email VARCHAR(100) NOT NULL UNIQUE - User's email (required and must be unique)
• address VARCHAR(100) - User's address (optional)

Add Some Sample Data

Let's add three sample users to our table:

INSERT INTO "users" (name, email, address) VALUES
('Sita Sharma', 'sita@email.com', 'Kathmandu'),
('Ram Thapa', 'ram@email.com', 'Pokhara'),
('Bikash Gurung', 'bikash@email.com', 'Lalitpur');

What this does: Adds three users to our table with their names, emails, and addresses.

Securing Database Password

Instead of putting your password directly in your code (which is unsafe), we'll store it in a special file.

Create a file called .env in your go_postgres folder and add:

DB_NAME=go_post_db
DB_USER=go_post_user
DB_PASSWORD=admin
DB_HOST=localhost
DB_PORT=5432

Explanation:

• DB_NAME - The name of your database
• DB_USER - Your database username
• DB_PASSWORD - Your database password
• DB_HOST - Database host, Where your database lives (localhost means on your computer)
• DB_PORT - Database port, PostgreSQL uses 5432 by default

Security Tip: If you're using Git, add .env to your .gitignore file so your password doesn't get shared accidentally!

Go Code to Connect to Database

Create a file called main.go in your go_postgres folder and add the following code in this file:

package main

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

    "github.com/joho/godotenv"
    "github.com/jackc/pgx/v5/pgxpool"
)

func main() {
    // Load our secret database information from the .env file
    err := godotenv.Load()
    if err != nil {
        log.Fatal("Error loading .env file")
    }

    // Build the connection string (like a phone number for our database)
    connStr := fmt.Sprintf("postgres://%s:%s@%s:%s/%s",
        os.Getenv("DB_USER"),     // Username
        os.Getenv("DB_PASSWORD"), // Password
        os.Getenv("DB_HOST"),     // Where the database is
        os.Getenv("DB_PORT"),     // Port number
        os.Getenv("DB_NAME"),     // Database name
    )

    // Create a connection pool (multiple connections managed smartly)
    pool, err := pgxpool.New(context.Background(), connStr)
    if err != nil {
        log.Fatalf("Unable to create connection pool: %v\n", err)
    }
    // Make sure we close all connections when we're done
    // defer will be called when the program exists
    defer pool.Close()

    fmt.Println("Connected to PostgreSQL with connection pool!")

    // Test the connection by asking PostgreSQL to say hello
    var greeting string
    err = pool.QueryRow(context.Background(), "SELECT 'Hello PGSQL'").Scan(&greeting)
    if err != nil {
        log.Fatal(err)
    }
    fmt.Println(greeting)

    // Test the pool health
    err = pool.Ping(context.Background())
    if err != nil {
        log.Fatal("Failed to ping database:", err)
    }
    fmt.Println("Database connection pool is healthy!")
}

Code Explanation:

1. Import packages - We bring in the tools we need
2. Load environment variables - We read our database info from the .env file
3. Build connection string - We create the "address" for our database
4. Create connection pool - We establish a pool of connections
5. Test the pool - We run a simple query and ping to make sure everything works

Run Code

Open terminal OR command prompt and run:

go run main.go

If everything is set up correctly, you should see:

Connected to PostgreSQL with connection pool!
Hello from PostgreSQL!
Database connection pool is healthy!

Congratulations! You've successfully connected Go to PostgreSQL using a connection pool!

Create Table and Insert Data

After creating connection to database, we can create tables and add data directly from Go. Add the following code in main.go file:

 /* Previous Code Block */


// create new table profiles 
_, err = pool.Exec(context.Background(), `
    CREATE TABLE IF NOT EXISTS profiles (
        id SERIAL PRIMARY KEY,
        name TEXT NOT NULL
    )
`)
if err != nil {
    log.Fatal(err)
}

// Add a new user to the table
_, err = pool.Exec(context.Background(), `INSERT INTO profiles (name) VALUES ($1)`, "Prashish")
if err != nil {
    log.Fatal(err)
}

fmt.Println("Inserted a new user!")

Code Explanation:

• Creates a new table called "profiles" if it doesn't exist
• Adds a user named "Prashish" to the table using the connection pool
• The $1 is a placeholder that gets replaced with "Prashish" safely
• Notice we use pool.Exec instead of conn.Exec - the pool handles everything for us!

Verify Table Creation and Data Insertion

After running the code after adding the new table profiles and inserting a data into that table, you can open up the terminal and select the database we created earlier go_post_db and the select * from profiles command inside our database.

sudo su postgres
psql
\c go_post_db
select * from profiles;

If you have correctly created the table profiles and added data into this table, then you can see this data when you do select * from profiles.

Final Code

Our final code in main.go:

package main

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

    "github.com/jackc/pgx/v5/pgxpool"
    "github.com/joho/godotenv"
)

func main() {
    // Load our secret database information from the .env file
    err := godotenv.Load()
    if err != nil {
        log.Fatal("Error loading .env file")
    }

    // Build the connection string (like a phone number for our database)
    connStr := fmt.Sprintf("postgres://%s:%s@%s:%s/%s",
        os.Getenv("DB_USER"),     // Username
        os.Getenv("DB_PASSWORD"), // Password
        os.Getenv("DB_HOST"),     // Where the database is
        os.Getenv("DB_PORT"),     // Port number
        os.Getenv("DB_NAME"),     // Database name
    )

    // Create a connection pool (multiple connections managed smartly)
    pool, err := pgxpool.New(context.Background(), connStr)
    if err != nil {
        log.Fatalf("Unable to create connection pool: %v\n", err)
    }
    // Make sure we close all connections when we're done
    defer pool.Close()

    fmt.Println("Connected to PostgreSQL with connection pool!")

    // Test the connection by asking PostgreSQL to say hello
    var greeting string
    err = pool.QueryRow(context.Background(), "SELECT 'Hello PGSQL'").Scan(&greeting)
    if err != nil {
        log.Fatal(err)
    }
    fmt.Println(greeting)

    // Test the pool health
    err = pool.Ping(context.Background())
    if err != nil {
        log.Fatal("Failed to ping database:", err)
    }
    fmt.Println("Database connection pool is healthy!")

    // create table "profiles" if it doesn't exist
    _, err = pool.Exec(context.Background(), `
    CREATE TABLE IF NOT EXISTS profiles (
        id SERIAL PRIMARY KEY,
        name TEXT NOT NULL
    )
    `)
    if err != nil {
        log.Fatal(err)
    }

    // insert a data into profiles table
    _, err = pool.Exec(context.Background(), `INSERT INTO profiles (name) VALUES ($1)`, "Prashish")
    if err != nil {
        log.Fatal(err)
    }
}

Every expert was once a beginner. Take your time, experiment, and don't be afraid to make mistakes - that's how you learn!