StateDB: The first few days, Part B - Connecting to PostgreSQL

Part A covered the Go basics I was learning during my first few days of StateDB. This part is about getting from those basics to a database query.

The examples build on the DatabaseConfig struct and environment variables from Part A. Before I could connect, though, there were a couple more things I needed to understand.

What the heck is a pointer?

I also had to learn what the heck a pointer was.

A pointer holds the memory address of a value. Here’s a small example:

host := "localhost"
hostPointer := &host

fmt.Println(*hostPointer) // localhost

*hostPointer = "postgres"
fmt.Println(host) // postgres

&host gets the address of host. hostPointer holds that address, and *hostPointer accesses the value there. Assigning through the pointer changes the original host variable.

Where this actually showed up in StateDB was the signature of my connection helper:

func connectDatabase(ctx context.Context, config DatabaseConfig) (*pgxpool.Pool, error)

The function takes a context and a DatabaseConfig, then returns two values: a pointer to a pgxpool.Pool and an error. The * in *pgxpool.Pool is part of the return type. It means “pointer to a pool,” rather than returning the pool struct itself by value.

When I call it:

db, err := connectDatabase(ctx, config)

db holds the returned pointer, and err holds the error. After checking the error, I can call methods through that pointer, like db.Ping(ctx) and db.Close().

That helped connect the two uses of *: in a type like *pgxpool.Pool, it describes a pointer; in an expression like *hostPointer, it accesses the value the pointer points to.

I’m still not sure I completely understand, but I think I’m getting there.

And what is context?

That function signature had another thing I needed to understand: ctx context.Context. Then there was context.Background(). Similar names, different jobs.

context is a standard-library package, brought in with import "context". context.Context is an interface type defined in that package. It lets functions share cancellation signals, deadlines, and request scoped values.

In ctx context.Context, ctx is just the parameter name, and context.Context is its type. It gives the function a way to receive that information from its caller.

To get a starting context, I can write this inside a function:

ctx := context.Background()

context.Background() returns an empty context with no deadline, cancellation, or values.

That ctx can be passed to connectDatabase(ctx, config) and then to database operations like db.Ping(ctx). A context lets operations that support it respond to cancellation.

Getting to PostgreSQL

Then there was figuring out how to import pgxpool, the connection-pool package for the pgx PostgreSQL driver.

For pgx v5, the dependency can be added with:

go get github.com/jackc/pgx/v5/pgxpool

And imported into a Go file with:

import "github.com/jackc/pgx/v5/pgxpool"

A connection pool manages reusable database connections. Importing it is only the beginning; I still had to figure out how to use it to query PostgreSQL.

Actually connecting

Then came putting those pieces together to connect to the database:

fmt.Println("Connecting to StateDB")

db, err := connectDatabase(ctx, config)
if err != nil {
	log.Fatal(err)
}
defer db.Close()

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

fmt.Println("Connected to StateDB")

This is a snippet from inside a function, using the ctx and config values set up earlier. connectDatabase is a helper in the project, not a built-in Go function.

The call returns two values: db and err. I check the error before trying to use db. Then db.Ping(ctx) checks that the database can actually be reached. The final message only prints if both checks succeed.

defer db.Close() schedules cleanup for when the surrounding function returns. One detail with this example: log.Fatal exits the program immediately, so if the ping fails, the deferred close won’t run. Returning an error from this function would allow that cleanup to run before the caller handles it.

There’s a lot packed into a small amount of code: configuration, multiple return values, error checks, cleanup, and finally checking the connection.

Running a simple query

After connecting, I had to learn how to actually run a simple query:

rows, err := db.Query(
	ctx,
	`
	select id, name, status, created_at
	from test_items
	`,
)
if err != nil {
	log.Fatal(err)
}
defer rows.Close()

The backticks let the SQL span multiple lines inside a Go string, and ctx is the context for the operation. This query selects id, name, status, and created_at from every row in test_items.

db.Query returns a result set and an error. I check the error first, then use defer rows.Close() to schedule closing the results when the surrounding function returns.

Then I needed to loop through the results, read all four columns into variables, and print them:

for rows.Next() {
	var id int
	var name string
	var status string
	var created_at time.Time

	if err := rows.Scan(&id, &name, &status, &created_at); err != nil {
		log.Fatal(err)
	}

	fmt.Printf("ID: %d, Name: %s, Status: %s, Created At: %v\n", id, name, status, created_at)
}

if err := rows.Err(); err != nil {
	log.Fatal(err)
}

rows.Next() advances to the next row. rows.Scan reads its columns in order: id, name, status, and created_at. Each destination needs & so pgx gets a pointer and can fill in the variable.

created_at uses time.Time to hold the timestamp, so this example also needs import "time".

The print statement uses %d for the integer ID, %s for the name and status strings, and %v for the timestamp’s default representation. The four values follow the same order as their placeholders. That brought pointers and formatted output into the same small loop.

The final rows.Err() check catches errors that stop iteration; rows.Next() returning false can mean either the end of the results or an error. As with the connection example, log.Fatal exits without running deferred cleanup.

What I accomplished

By this point, I was making sense of pointers and contexts, connecting through pgxpool, and reading query results into Go variables. The syntax from Part A was starting to fit together in code that talked to PostgreSQL.

Baby steps.

Part C continues with more queries, starting with inserting a row.