pgq

pgq - PostgreSql helpers

• Generating PostgreSQL queries using templates
• A simple wrapper around sql queries from a package github.com/jackc/pgx
• Managing nested transactions

Example of usage:

CREATE TABLE table1
(
    id bigserial NOT NULL,
    c_int bigint,
    c_double double precision,
    c_bool boolean,
    c_date date,
    c_time time without time zone,    
    c_timestamp timestamp without time zone,
    c_timestamp_zone timestamp with time zone,
    c_text text,
    c_bytes bytea,
    CONSTRAINT table_pkey PRIMARY KEY (id)
);
ALTER TABLE table1 ADD CONSTRAINT table_pkey PRIMARY KEY (id);

package main

import (
    "context"
    "fmt"
    "io/ioutil"
    "time"

    "github.com/jackc/pgx/v4"
    "github.com/n-r-w/pgq"
)

func main() {
    // ******************************** Sql Binder ********************************
    templateSql := `
    DELETE FROM public.table1;
    INSERT INTO public.table1(
    c_int, c_double, c_bool, c_date, c_time, c_timestamp, c_timestamp_zone, c_text, c_bytes)
    VALUES (:c_int, :c_double, :c_bool, :c_date, :c_time, :c_timestamp, :c_timestamp_zone, :c_text, :c_bytes);`

    binds := map[string]interface{}{
        "c_int":            123,
        "c_double":         123.456,
        "c_bool":           true,
        "c_date":           time.Now(),
        "c_time":           time.Duration(time.Hour*13 + time.Minute*41 + time.Second*2), // 13:41:02
        "c_timestamp":      time.Now(),
        "c_timestamp_zone": time.Now(),
        "c_text":           "test text",
        "c_bytes":          []byte("qwerty"),
    }

    binder := pgq.NewSqlBinder(templateSql, "")
    for variable, value := range binds {
        binder.Bind(variable, value)
    }

    sql, err := binder.Sql()
    if err != nil {
        panic(err)
    }
    fmt.Println(sql)
    /*
               INSERT INTO public.table1(
               c_int, c_double, c_bool, c_date, c_time, c_timestamp, c_timestamp_zone, c_text, c_bytes)
               VALUES (123, 123.456, TRUE, '2022-06-01 13:14:52.439 +0300', '13:41:2', '2022-06-01 13:14:52.439 +0300',
                          '2022-06-01 13:14:52.439 +0300', E'test text', E'\\x717765727479')
    */

    // ******************************** Sql Query Wrapper ********************************
    conn, err := pgx.Connect(context.Background(),
        "host=localhost user=postgres password=1 port=5433 dbname=demo")
    if err != nil {
        panic(err)
    }
    defer conn.Close(context.Background())

    query := pgq.NewQuery(conn, context.Background())

    err = query.ExecBind(templateSql, binds, "")
    if err != nil {
        panic(err)
    }

    err = query.Select("select * from table1")
    if err != nil {
        panic(err)
    }

    for query.Next() {
        fmt.Println("\n==========================================================================================================")
        fmt.Printf("int: %v, double: %v, bool: %v, date: %v, time: %v,  timestamp: %v, timestamp zone: %v, text: %v, bytes: %v",
            query.Value("c_int"), query.Value("c_double"), query.Value("c_bool"),
            query.Value("c_date"), query.Value("c_time"), query.Value("c_timestamp"),
            query.Value("c_timestamp_zone"), query.Value("c_text"), query.Value("c_bytes"))
        fmt.Println("\n----------------------------------------------------------------------------------------------------------")
        fmt.Printf("int: %v, double: %v, bool: %v, date: %v, time: %v, duration: %v,  timestamp: %v, timestamp zone: %v, text: %v, bytes: %v, bytes as string: %v",
            query.Int("c_int"), query.Float32("c_double"), query.Bool("c_bool"),
            query.Time("c_date"), query.Time("c_time"), query.Duration("c_time"), query.Time("c_timestamp"),
            query.Time("c_timestamp_zone"), query.String("c_text"), query.Bytes("c_bytes"), query.String("c_bytes"))
    }
    /*
        ==========================================================================================================
        int: 123, double: 123.456, bool: true, date: 2022-06-01 00:00:00 +0000 UTC, time: 49262000000,
            timestamp: 2022-06-01 13:14:52.439 +0000 UTC, timestamp zone: 2022-06-01 13:14:52.439 +0300 MSK,
            text: test text, bytes: [113 119 101 114 116 121]
        ----------------------------------------------------------------------------------------------------------
        int: 123, double: 123.456, bool: true, date: 2022-06-01 00:00:00 +0000 UTC, time: 0001-01-01 13:41:02 +0000 UTC,
            duration: 13h41m2s,  timestamp: 2022-06-01 13:14:52.439 +0000 UTC, timestamp zone: 2022-06-01 13:14:52.439 +0300 MSK,
            text: test text, bytes: [113 119 101 114 116 121], bytes as string: qwerty
    */

    // Large Objects
    fmt.Println("\n==========================================================================================================")
    fmt.Println("Large object processing...")

    data, err := ioutil.ReadFile("largefile.MP4")
    if err != nil {
        panic(err)
    }

    // save new
    oid, err := query.SaveLargeObject(nil, 0, data)
    if err != nil {
        panic(err)
    }
    fmt.Println("Large object created...")
    // load
    testData, err := query.LoadLargeObject(nil, oid)
    if err != nil {
        panic(err)
    }
    fmt.Println("Large object loaded...")
    // update
    oid, err = query.SaveLargeObject(nil, oid, data)
    if err != nil {
        panic(err)
    }
    fmt.Println("Large object updated...")

    fmt.Println("file size:", len(testData))
    // remove
    err = query.RemoveLargeObject(nil, oid)
    if err != nil {
        panic(err)
    }
    fmt.Println("Large object removed...")
    fmt.Println("Large object processing done")

    query.Exec("delete from table1")

    // ============== Nested transaction management
    nested := pgq.NewTxNested(conn, context.Background())
    _, err = nested.Begin()
    if err != nil {
        panic(err)
    }

    err = query.ExecBind(templateSql, binds, "")
    if err != nil {
        panic(err)
    }

    nested.Begin() // no new transactions, just increments the counter

    err = query.ExecBind(templateSql, binds, "")
    if err != nil {
        panic(err)
    }

    nested.Commit() // there is no commit, just decreases the counter
    // nested.Rollback() will roll back all changes

    err = nested.Commit() // commit is executed here
    if err != nil {
        panic(err)
    }
}