💡 This article is not intended as a representation of my writer skills. You can find articles from people that are far more wellspoken in the Best Text Material section . This is a summary from resources I used in form of raw notes.

Best Text Material:

[Medium] Artemiy Ryabinkov - How to work with Postgres in Go

Go database/sql tutorial

Gotchas:

sql.DB is not a database connection. It is an abstraction of the interface which performs such things as:

• It opens and closes connections to the actual underlying database, via the driver.

• It manages a pool of connections as needed, which may be a variety of things as mentioned.

• You generally shouldn’t use driver packages directly, instead, your code should only refer to types defined in database/sql

💡 This helps avoid making your code dependent on the driver, so that you can change the underlying driver (and thus the database you’re accessing) with minimal code changes. It also forces you to use the Go idioms instead of ad-hoc idioms that a particular driver author may have provided.

• sql.Open() does not establish any connections to the database, it prepares the database abstraction for later use ( read more here )
• sql.DB **object is designed to be long-lived. Create an instance and either move it around or make it available globally
• use db.Exec for queries that don’t return rows ( INSERT, UPDATE, DELETE etc. ) and db.Query for retrieving data from the database ( SELECT )
• Generally - prepare queries with placeholders and then execute them.

// boilerplate for a prepared SELECT query
q, err := db.Prepare("SELECT id, username from users where id = $1")
rows, err := q.Query(1)
if err != nil {
    log.Fatalf("failed to execute query: %v", err)
}
defer rows.Close()

• Use pgx.ConnPool to limit the connection pool limits. It reuses the information about OIDs. ( read more in the How to work with Postgres in Go: Connection pool limits and here )

Package Reference:

jmoiron/sqlx - super-set of Go’s standard database/sql package

jackc/pgx - PostgreSQL driver

Useful link combos:

• Choosing Between the pgx and database/sql Interfaces

  • Getting started with pgx through database/sql

    // boilerplate to connect to database using pgx driver and database/sql
    import (
      "database/sql"
      _ "github.com/jackc/pgx/v5/stdlib"
      "log"
    )
    
    func main() {
      psqlConStr := fmt.Sprintf("postgres://postgres:myAwesomePassword@localhost:5432/users")
      db, err := ConnectToPostgreSQLAndCheckConnection(psqlConStr)
      if err != nil {
          log.Fatalf("failed to connect to PostgreSQL: %v", err)
      }
      defer db.Close()
    }
    
    func ConnectToPostgreSQLAndCheckConnection(cs string) (*sql.DB, error) {
      db, err := sql.Open("pgx", cs)
      if err != nil {
          return nil, err
      }
    
      // check the database connection
      err = db.Ping()
      if err != nil {
          return nil, err
      }
    
      return db, nil
    }
    

  • Getting started with pgx