Skip to content

Instantly share code, notes, and snippets.

@qbig

qbig/work-with-db.md

Created August 19, 2018 16:12
Show Gist options
  • Save qbig/620728bb7e4ffa08d591d2e88b6adc0e to your computer and use it in GitHub Desktop.
Save qbig/620728bb7e4ffa08d591d2e88b6adc0e to your computer and use it in GitHub Desktop.
Download ZIP
Working with Databases
Raw
work-with-db.md

Connecting the database

       package main

       import (
         "database/sql"
         "fmt"

         _ "github.com/lib/pq"
       )

       func main() {
         connStr := "postgres://postgres:postgres@
                     localhost:5432/example?sslmode=disable"
         db, err := sql.Open("postgres", connStr)
         if err != nil {
           panic(err)
         }
         defer db.Close()
         err = db.Ping()
         if err != nil {
           panic(err)
         }
         fmt.Println("Ping OK")
       }

Validating the connection

        package main
 
        import (
          "context"
          "database/sql"
          "fmt"
          "time"

          _ "github.com/lib/pq"
        )

        func main() {
          connStr := "postgres://postgres:postgres@
                      localhost:5432/example?sslmode=disable"
          db, err := sql.Open("postgres", connStr)
          if err != nil {
            panic(err)
          }
          defer db.Close()
          err = db.Ping()
          if err != nil {
            panic(err)
          }
          fmt.Println("Ping OK.")
          ctx, _ := context.WithTimeout(context.Background(),
                                        time.Nanosecond)
          err = db.PingContext(ctx)
          if err != nil {
            fmt.Println("Error: " + err.Error())
          }

          // Verify the connection is
          conn, err := db.Conn(context.Background())
          if err != nil {
            panic(err)
          }
          defer conn.Close()
          err = conn.PingContext(context.Background())
          if err != nil {
            panic(err)
          }
          fmt.Println("Connection Ping OK.")

        }

Executing statements

        DROP TABLE IF EXISTS post;
        CREATE TABLE post (
          ID serial,
          TITLE varchar(40),
          CONTENT varchar(255),
          CONSTRAINT pk_post PRIMARY KEY(ID)
        );
        SELECT * FROM post;
        package main

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

        const sel = "SELECT * FROM post;"
        const trunc = "TRUNCATE TABLE post;"
        const ins = "INSERT INTO post(ID,TITLE,CONTENT)
                     VALUES (1,'Title 1','Content 1'),
                     (2,'Title 2','Content 2') "

        func main() {
          db := createConnection()
          defer db.Close()

          _, err := db.Exec(trunc)
          if err != nil {
            panic(err)
          }
          fmt.Println("Table truncated.")
          r, err := db.Exec(ins)
          if err != nil {
            panic(err)
          }
          affected, err := r.RowsAffected()
          if err != nil {
            panic(err)
          }
          fmt.Printf("Inserted rows count: %d\n",
                     affected)

          rs, err := db.Query(sel)
          if err != nil {
            panic(err)
          }
          count := 0
          for rs.Next() {
            count++
          }
          fmt.Printf("Total of %d was selected.\n", count)
        }

        func createConnection() *sql.DB {
          connStr := "postgres://postgres:postgres@
                      localhost:5432/example?sslmode=disable"
          db, err := sql.Open("postgres", connStr)
          if err != nil {
            panic(err)
          }
          err = db.Ping()
          if err != nil {
            panic(err)
          }
          return db
        }

Operating with prepared statements

        DROP TABLE IF EXISTS post;
        CREATE TABLE post (
          ID serial,
          TITLE varchar(40),
          CONTENT varchar(255),
          CONSTRAINT pk_post PRIMARY KEY(ID)
        );
        SELECT * FROM post;
        package main

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

        const trunc = "TRUNCATE TABLE post;"
        const ins = "INSERT INTO post(ID,TITLE,CONTENT)
                     VALUES ($1,$2,$3)"

        var testTable = []struct {
          ID int
          Title string
          Content string
        }{
          {1, "Title One", "Content of title one"},
          {2, "Title Two", "Content of title two"},
          {3, "Title Three", "Content of title three"},
        }

        func main() {
          db := createConnection()
          defer db.Close()

          // Truncate table
          _, err := db.Exec(trunc)
          if err != nil {
            panic(err)
          }

          stm, err := db.Prepare(ins)
          if err != nil {
            panic(err)
          }

          inserted := int64(0)
          for _, val := range testTable {
            fmt.Printf("Inserting record ID: %d\n", val.ID)
            // Execute the prepared statement
            r, err := stm.Exec(val.ID, val.Title, val.Content)
            if err != nil {
              fmt.Printf("Cannot insert record ID : %d\n",
                         val.ID)
            }
            if affected, err := r.RowsAffected(); err == nil {
              inserted = inserted + affected
            }
          }

          fmt.Printf("Result: Inserted %d rows.\n", inserted)

        }

        func createConnection() *sql.DB {
          connStr := "postgres://postgres:postgres@
                      localhost:5432/example?sslmode=disable"
          db, err := sql.Open("postgres", connStr)
          if err != nil {
            panic(err)
          }
          err = db.Ping()
          if err != nil {
            panic(err)
          }
          return db
        }

Canceling the pending query

        DROP TABLE IF EXISTS post;
        CREATE TABLE post (
          ID serial,
          TITLE varchar(40),
          CONTENT varchar(255),
          CONSTRAINT pk_post PRIMARY KEY(ID)
        );
        SELECT * FROM post;
        INSERT INTO post(ID,TITLE,CONTENT) VALUES
                        (1,'Title One','Content One'),
                        (2,'Title Two','Content Two');
        package main

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

        const sel = "SELECT * FROM post p CROSS JOIN
           (SELECT 1 FROM generate_series(1,1000000)) tbl"

        func main() {
          db := createConnection()
          defer db.Close()

          ctx, canc := context.WithTimeout(context.Background(),
                                           20*time.Microsecond)
          rows, err := db.QueryContext(ctx, sel)
          canc() //cancel the query
          if err != nil {
            fmt.Println(err)
            return
          }
          defer rows.Close()
          count := 0
          for rows.Next() {
            if rows.Err() != nil {
              fmt.Println(rows.Err())
              continue
            }
            count++
          }

          fmt.Printf("%d rows returned\n", count)

        }

        func createConnection() *sql.DB {
          connStr := "postgres://postgres:postgres@
                      localhost:5432/example?sslmode=disable"
          db, err := sql.Open("postgres", connStr)
          if err != nil {
            panic(err)
          }
          err = db.Ping()
          if err != nil {
            panic(err)
          }
          return db
        }

Reading query result metadata

        DROP TABLE IF EXISTS post;
        CREATE TABLE post (
          ID serial,
          TITLE varchar(40),
          CONTENT varchar(255),
          CONSTRAINT pk_post PRIMARY KEY(ID)
        );
        SELECT * FROM post;
        INSERT INTO post(ID,TITLE,CONTENT) VALUES
                        (1,'Title One','Content One'),
                        (2,'Title Two','Content Two');
        package main

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

        const sel = "SELECT * FROM post p"

        func main() {

          db := createConnection()
          defer db.Close()

          rs, err := db.Query(sel)
          if err != nil {
            panic(err)
          }
          defer rs.Close()
          columns, err := rs.Columns()
          if err != nil {
            panic(err)
          }
          fmt.Printf("Selected columns: %v\n", columns)

          colTypes, err := rs.ColumnTypes()
          if err != nil {
            panic(err)
          }
          for _, col := range colTypes {
            fmt.Println()
            fmt.Printf("%+v\n", col)
          }

        }

        func createConnection() *sql.DB {
          connStr := "postgres://postgres:postgres@
                      localhost:5432/example?sslmode=disable"
          db, err := sql.Open("postgres", connStr)
          if err != nil {
            panic(err)
          }
          err = db.Ping()
          if err != nil {
            panic(err)
          }
          return db
        }

Retrieving data from query result

       DROP TABLE IF EXISTS post;
       CREATE TABLE post (
         ID serial,
         TITLE varchar(40),
         CONTENT varchar(255),
         CONSTRAINT pk_post PRIMARY KEY(ID)
       );
       SELECT * FROM post;
       INSERT INTO post(ID,TITLE,CONTENT) VALUES
                       (1,'Title One','Content One'),
                       (2,NULL,'Content Two');
        package main

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

        const sel = `SELECT title,content FROM post;
        SELECT 1234 NUM; `
 
        const selOne = "SELECT title,content FROM post
                        WHERE ID = $1;"

        type Post struct {
          Name sql.NullString
          Text sql.NullString
        }

        func main() {
          db := createConnection()
          defer db.Close()

          rs, err := db.Query(sel)
          if err != nil {
            panic(err)
          }
          defer rs.Close()

          posts := []Post{}
          for rs.Next() {
            if rs.Err() != nil {
              panic(rs.Err())
            }
            p := Post{}
            if err := rs.Scan(&p.Name, &p.Text); err != nil {
              panic(err)
            }
            posts = append(posts, p)
          }

          var num int
          if rs.NextResultSet() {
            for rs.Next() {
              if rs.Err() != nil {
                panic(rs.Err())
              }
              rs.Scan(&num)
            }
          }

          fmt.Printf("Retrieved posts: %+v\n", posts)
          fmt.Printf("Retrieved number: %d\n", num)

          row := db.QueryRow(selOne, 100)
          or := Post{}
          if err := row.Scan(&or.Name, &or.Text); err != nil {
            fmt.Printf("Error: %s\n", err.Error())
            return
          }

          fmt.Printf("Retrieved one post: %+v\n", or)

        }

        func createConnection() *sql.DB {
          connStr := "postgres://postgres:postgres@
                      localhost:5432/example?sslmode=disable"
          db, err := sql.Open("postgres", connStr)
          if err != nil {
            panic(err)
          }
          err = db.Ping()
          if err != nil {
            panic(err)
          }
          return db
        }

Parsing query result into map

        DROP TABLE IF EXISTS post;
        CREATE TABLE post (
          ID serial,
          TITLE varchar(40),
          CONTENT varchar(255),
          CONSTRAINT pk_post PRIMARY KEY(ID)
        );
        SELECT * FROM post;
        INSERT INTO post(ID,TITLE,CONTENT) VALUES 
                        (1,NULL,'Content One'),
                        (2,'Title Two','Content Two');
        package main

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

        const selOne = "SELECT id,title,content FROM post
                        WHERE ID = $1;"

        func main() {
          db := createConnection()
          defer db.Close()

          rows, err := db.Query(selOne, 1)
          if err != nil {
            panic(err)
          }
          cols, _ := rows.Columns()
          for rows.Next() {
            m := parseWithRawBytes(rows, cols)
            fmt.Println(m)
            m = parseToMap(rows, cols)
            fmt.Println(m)
          }
        }

        func parseWithRawBytes(rows *sql.Rows, cols []string)
                               map[string]interface{} {
          vals := make([]sql.RawBytes, len(cols))
          scanArgs := make([]interface{}, len(vals))
          for i := range vals {
            scanArgs[i] = &vals[i]
          }
          if err := rows.Scan(scanArgs...); err != nil {
            panic(err)
          }
          m := make(map[string]interface{})
          for i, col := range vals {
            if col == nil {
              m[cols[i]] = nil
            } else {
              m[cols[i]] = string(col)
            }
          }
          return m
        }

        func parseToMap(rows *sql.Rows, cols []string)
                        map[string]interface{} {
          values := make([]interface{}, len(cols))
          pointers := make([]interface{}, len(cols))
          for i := range values {
            pointers[i] = &values[i]
          }

          if err := rows.Scan(pointers...); err != nil {
            panic(err)
          }

          m := make(map[string]interface{})
          for i, colName := range cols {
            if values[i] == nil {
              m[colName] = nil
            } else {
              m[colName] = values[i]
            }
          }
          return m
        }

        func createConnection() *sql.DB {
          connStr := "postgres://postgres:postgres@
                      localhost:5432/example?sslmode=disable"
          db, err := sql.Open("postgres", connStr)
          if err != nil {
            panic(err)
          }
          err = db.Ping()
          if err != nil {
            panic(err)
          }
          return db
        }

Handling transactions

        DROP TABLE IF EXISTS post;
        CREATE TABLE post (
          ID serial,
          TITLE varchar(40),
          CONTENT varchar(255),
          CONSTRAINT pk_post PRIMARY KEY(ID)
        );
        SELECT * FROM post;
        INSERT INTO post(ID,TITLE,CONTENT) VALUES
                        (1,'Title One','Content One'),
                        (2,NULL,'Content Two');
        package main

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

        const selOne = "SELECT id,title,content FROM post
                        WHERE ID = $1;"
        const insert = "INSERT INTO post(ID,TITLE,CONTENT)
                VALUES (4,'Transaction Title','Transaction Content');"

        type Post struct {
          ID int
          Title string
          Content string
        }

        func main() {
          db := createConnection()
          defer db.Close()

          tx, err := db.Begin()
          if err != nil {
            panic(err)
          }
          _, err = tx.Exec(insert)
          if err != nil {
            panic(err)
          }
          p := Post{}
          // Query in other session/transaction
          if err := db.QueryRow(selOne, 4).Scan(&p.ID,
                &p.Title, &p.Content); err != nil {
            fmt.Println("Got error for db.Query:" + err.Error())
          }
          fmt.Println(p)
          // Query within transaction
          if err := tx.QueryRow(selOne, 4).Scan(&p.ID,
                 &p.Title, &p.Content); err != nil {
            fmt.Println("Got error for db.Query:" + err.Error())
          }
          fmt.Println(p)
          // After commit or rollback the
          // transaction need to recreated.
          tx.Rollback()

        }

        func createConnection() *sql.DB {
          connStr := "postgres://postgres:postgres@
                      localhost:5432/example?sslmode=disable"
          db, err := sql.Open("postgres", connStr)
          if err != nil {
            panic(err)
          }
          err = db.Ping()
          if err != nil {
            panic(err)
          }
          return db
        }

Executing stored procedures and functions

        CREATE OR REPLACE FUNCTION format_name
        (firstname Text,lastname Text,age INT) RETURNS 
        VARCHAR AS $$
        BEGIN
          RETURN trim(firstname) ||' '||trim(lastname) ||' ('||age||')';
        END;
        $$ LANGUAGE plpgsql;
        package main

        import (
          "database/sql"
          "fmt"

          _ "github.com/go-sql-driver/mysql"
          _ "github.com/lib/pq"
        )

        const call = "select * from format_name($1,$2,$3)"

        const callMySQL = "CALL simpleproc(?)"

        type Result struct {
          Name string
          Category int
        }

        func main() {
          db := createConnection()
          defer db.Close()
          r := Result{}

          if err := db.QueryRow(call, "John", "Doe",
                    32).Scan(&r.Name); err != nil {
            panic(err)
          }
          fmt.Printf("Result is: %+v\n", r)
        }

        func createConnection() *sql.DB {
          connStr := "postgres://postgres:postgres@localhost:5432
                      /example?sslmode=disable"
          db, err := sql.Open("postgres", connStr)
          if err != nil {
            panic(err)
          }
          err = db.Ping()
          if err != nil {
            panic(err)
          }
          return db
        }
@edwinakatosh
Copy link

Very helpful. Thank you @qbig!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment