Go and PostgreSQL: A Seamless Data Journey with Supabase

If you’ve worked with Psycopg in Python, you’ll find many similarities here. If not, don’t worry — I’ll guide you through the straightforward process of connecting to PostgreSQL in a few steps. Additionally, we’ll cover creating a table, inserting data, and fetching it into variables. Let’s get started!

Note

I will use Supabase, but feel free to choose any other method for connecting to Postgres that suits you best.

Create a project

Create a file named main.go and initialize a Go module by running go mod init.

go mod init main

Install dependencies

go get github.com/lib/pq

Connection

What’s going on here?

1. Credentials: The connection string for PostgreSQL is defined, specifying details such as the username, password, host, port, and database name.
2. Connection Setup: The program opens a connection to the PostgreSQL database using the sql.Open method. The defer statement ensures that the database connection is closed when the main() function exits.
3. Error Handling: The code checks for errors during the database connection setup. If an error occurs, the program logs the error and exits using log.Fatal().
4. Connection Verification: The code uses the Ping method to verify the database connection. If the verification fails, an error is logged and the program exits. The code up to this point establishes a connection to the PostgreSQL database and ensures that the connection is successfully established without errors.

package main

import (
 "database/sql"
 "log"
 _ "github.com/lib/pq"
)

func main() {
   connection := "postgres://postgres:KPAuwVRd@[email protected]:5432/postgres"
  
   db, err := sql.Open("postgres", connection)
  
   defer db.Close()
  
   if err != nil {
      log.Fatal(err)
   }
  
   if err = db.Ping(); err != nil {
      log.Fatal(err)
   }
}

Test

If everything works correctly we should be able to run this code without an error.

go run main.go

Create a table

To execute certain SQL queries, we require tables. Let’s create one using the Exec method.

Note that we need to pass a DB instance as a pointer.

func main() {
   // ... code

   createTodo(db)
}

func createTodo(db *sql.DB) {
    query := `
        CREATE TABLE todo (
         id SERIAL PRIMARY KEY,
         label TEXT NOT NULL,
         created_at TIMESTAMP DEFAULT NOW()
        );
        `
        
    _, err := db.Exec(query) 
        
    if err != nil {
        log.Fatal(err)
    }
}

After running the code, the expected outcome is the creation of a new table in the database.

Insert data into the table

func main() {
   // code ...
  
   // createTodo(db)
   todo := Todo{label: "Walk a dog"}
  
   insertTodo(db, todo)
}

type Todo struct {
   id         int
   label      string
   created_at string
}

func insertTodo(db *sql.DB, todo Todo) int {
   query := `
      INSERT INTO todo (label) VALUES ($1) RETURNING id;
   `
  
   var primaryKey int
   err := db.QueryRow(query, todo.label).Scan(&primaryKey)
  
   if err != nil {
      log.Fatal(err)
   }
  
   return primaryKey
}

Now, let’s try executing the code, and we should see the newly created row in the database.

Fetch a row

We can use our struct to map the data with the Scan method. If there is no error, we should see the printed data from the database.

func main() {
    // code ...

    query := "SELECT id, label, created_at FROM todo WHERE id = $1;"
    
    var todo Todo
    
    err = db.QueryRow(query, 1).Scan(&todo.id, &todo.label, &todo.created_at)
    
    if err != nil {
        log.Fatal(err)
    }
    
    fmt.Println(todo.id, todo.label, todo.created_at) // 1 Walk a dog 2023-12-07T15:39:07.819697Z
}

Thanks for reading my article!

If you enjoyed the read and want to be part of our growing community, hit the follow button, and let’s embark on a knowledge journey together.

Your feedback and comments are always welcome, so don’t hold back!

Stackademic

Thank you for reading until the end. Before you go:

• Please consider clapping and following the writer! 👏
• Follow us on Twitter(X), LinkedIn, and YouTube.
• Visit Stackademic.com to find out more about how we are democratizing free programming education around the world.