Artemijs Pavlovs


Artemijs Pavlovs


Working with PostgreSQL in Go - raw notes

Artemijs's photo
·Nov 25, 2022·

3 min read

💡 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


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()

Package Reference:

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

jackc/pgx - PostgreSQL driver

  • 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 (
        _ ""
      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

Share this