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." )
}
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
}
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
}
Very helpful. Thank you @qbig!