DEV Community

loading...
Cover image for Connecting to postgres with standard sql package in golang

Connecting to postgres with standard sql package in golang

bitsmonkey profile image bitsmonkey Updated on ・2 min read

Quick steps connecting to postgres DB using the stand library with postgres driver,

Import the packages

import (
    "database/sql"
    _ "github.com/lib/pq" //postgres drivers for initialization
)
Enter fullscreen mode Exit fullscreen mode

A simple get method to run a select statement. In example we will the connection string from the environment variables.

var connStr = os.Getenv("CONNSTR")

//GetSomeDummyData ...
func (r *Repo) GetSomeDummyData() {

    db, _ := sql.Open("postgres", connStr)
    defer db.Close()

    id := "34940574"
    row := db.QueryRow("SELECT column1, column2 FROM dummytable where id=$1", id)

    rows, err := db.Query(query, qteNum, limit)
    defer rows.Close()
    if err != nil {
        log.Println(err)
    }

    var result []*model.DummyData

    for rows.Next() {
        d := &model.DummyData{}
        err = rows.Scan(&d.Prop1, &d.Prop2)

        if err != nil {
            log.Println(err)
            return nil, err
        }
        result = append(result, q)
    }
    return result, nil

}
Enter fullscreen mode Exit fullscreen mode

Now the connection string looks something like this in the environment variable

EXPORT CONNSTR="user=username password=password host=dbname.host.com port=5433 dbname=dbname connect_timeout=20 sslmode=disable"
Enter fullscreen mode Exit fullscreen mode

Lets now enable ssl mode with the ca cert in your project. In which case we need to set the path of the PEM file. Can be relative path of the absolute path. Postgres expects this in a environment variable PGSSLROOTCERT=pathof.pem and connection string will specify sslmode=verify-full

EXPORT PGSSLROOTCERT=pathof.pem
EXPORT CONNSTR="user=username password=password host=dbname.host.com port=5433 dbname=dbname connect_timeout=20 sslmode=verify-full"
Enter fullscreen mode Exit fullscreen mode

Photo by Ron Whitaker

Originally posted on Bitsmonkey

Discussion (0)

pic
Editor guide