Connect to Postgresql with GO

Start Postgre with Docker

docker-compose.yml

version: "3"
services:
  postgres:
    image: postgres
    container_name: postgres
    ports:
      - 5432:5432
    environment:
      - POSTGRES_USER=root
      - POSTGRES_PASSWORD=password
    tty: true
    restart: always
    user: root
    volumes:
      - ./init:/docker-entrypoint-initdb.d
      - /etc/localtime:/etc/localtime:ro
  pgweb:
    image: sosedoff/pgweb
    container_name: pgweb
    ports: 
      - "8081:8081"
    environment:
      - DATABASE_URL=postgres://root:password@postgres:5432/testdb?sslmode=disable
    links: 
      - postgres:postgres
    restart: always
    depends_on:
      - postgres

Start-up

docker-compose up -d

Connect from Go

main.go

package main

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

const (
    // Initialize connection constants.
    HOST     = "127.0.0.1"
    DATABASE = "testdb"
    USER     = "root"
    PASSWORD = "password"
)

func checkError(err error) {
    if err != nil {
        panic(err)
    }
}

func main() {
    // Initialize connection string.
    var connectionString string = fmt.Sprintf("host=%s user=%s password=%s dbname=%s sslmode=disable", HOST, USER, PASSWORD, DATABASE)

    // Initialize connection object.
    db, err := sql.Open("postgres", connectionString)
    checkError(err)

    err = db.Ping()
    checkError(err)
    fmt.Println("Successfully created connection to database")

    // Drop previous table of same name if one exists.
    _, err = db.Exec("DROP TABLE IF EXISTS inventory;")
    checkError(err)
    fmt.Println("Finished dropping table (if existed)")

    // Create table.
    _, err = db.Exec("CREATE TABLE inventory (id serial PRIMARY KEY, name VARCHAR(50), quantity INTEGER);")
    checkError(err)
    fmt.Println("Finished creating table")

    // Insert some data into table.
    sql_statement := "INSERT INTO inventory (name, quantity) VALUES ($1, $2);"
    _, err = db.Exec(sql_statement, "banana", 150)
    checkError(err)
    _, err = db.Exec(sql_statement, "orange", 154)
    checkError(err)
    _, err = db.Exec(sql_statement, "apple", 100)
    checkError(err)
    fmt.Println("Inserted 3 rows of data")
}

go run main.go

Connect with psql

Connect from shell

psql -h localhost -U root -d postgres -p 5432

DB list display

\l

Connect to the target DB

\c testdb

Check the current DB

select current_database();

Check if data is included

testdb=# select * from inventory;
 id |  name  | quantity 
----+--------+----------
  1 | banana |      150
  2 | orange |      154
  3 | apple  |      100

reference

https://qiita.com/hiro9/items/e6e41ec822a7077c3568 https://docs.microsoft.com/ja-jp/azure/postgresql/connect-go