Setting up Postgres locally #

You can skip this if you already have Postgres running.

Starting Postgres using Docker.

docker run \
--rm --name postgres \
-e POSTGRES_USER=postgres \
-e POSTGRES_PASSWORD=postgres \
-e POSTGRES_DB=postgres \
-p 5432:5432 \
-d postgres:latest

Install Postgres driver #

You will be using the inbuilt sql library in Go to make queries, but you still need a driver for the database in use.

Install the popular pq Go postgres driver.

go get github.com/lib/pq

You will not be using directly from this package directly, but the driver still needs to be loaded.

Load it by importing the package.

package main

import (
	_ "github.com/lib/pq"
)

func main() {
}
main.go

Connecting to database #

Now you will use the sql package in Go to connect the database.

package main

import (
	"database/sql"

	_ "github.com/lib/pq"
)

func main() {
	connectionStr := "postgres://postgres:postgres@localhost:5432/postgres?sslmode=disable"

	conn, err := sql.Open("postgres", connectionStr)
	if err != nil {
		panic(err)
	}

	conn.Close()
}
main.go

sql.Open accepts a driver name and a connection string.

Using DSN (Data Source Name) #

Instead of a connection string you can also use a DSN.

package main

import (
	"database/sql"

	_ "github.com/lib/pq"
)

func main() {
	connectionStr := "user=postgres password=postgres dbname=postgres port=5432 sslmode=disable"

	conn, err := sql.Open("postgres", connectionStr)
	if err != nil {
		panic(err)
	}

	conn.Close()
}
main.go

Running Queries #

Now you are all set, go ahead an start running some queries.

Here is a query that gets the postgres version.

package main

import (
	"database/sql"
	"fmt"

	_ "github.com/lib/pq"
)

func main() {
	connectionStr := "user=postgres password=postgres dbname=postgres sslmode=disable"

	conn, err := sql.Open("postgres", connectionStr)
	if err != nil {
		panic(err)
	}

	rows, err := conn.Query("SELECT version();")
	if err != nil {
		panic(err)
	}

	for rows.Next() {
		var version string
		rows.Scan(&version)
		fmt.Println(version)
	}

	rows.Close()

	conn.Close()
}
main.go

Output:

PostgreSQL 15.1 (Debian 15.1-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit