pgxwrapper

package module
v1.0.7 Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: Sep 19, 2023 License: MIT Imports: 8 Imported by: 0

README

pgxwrapper

Simple wrapper for PostgreSQL using pgx, sqlx and squirrel

Features:

  • Compatibility with squirrel
  • Transactions methods with squirrel
  • Background pinging DB connection with a ticker
  • Custom attempts to connect. If attempts is reach their maximum, db connection will close
  • Optional receive errors through error channel
  • Optional log errors

Dependencies:

  • github.com/jackc/pgx
  • github.com/jmoiron/sqlx
  • github.com/Masterminds/squirrel

Commands (PgDatabase interface):

Database
  1. GetDB() *sql.DB
  2. IsActive() bool
  3. Close() error
Without trasactions
  1. Get(ctx context.Context, dest interface{}, query string, args ...interface{}) error
  2. GetSq(ctx context.Context, dest interface{}, sqlizer sq.Sqlizer) error
  3. Select(ctx context.Context, dest interface{}, query string, args ...interface{}) error
  4. SelectSq(ctx context.Context, dest interface{}, sqlizer sq.Sqlizer) error
  5. SelectToMapSq(ctx context.Context, dest map[string]interface{}, sqlizer sq.Sqlizer) error
  6. Insert(ctx context.Context, query string, args ...interface{}) error
  7. InsertSq(ctx context.Context, sqlizer sq.Sqlizer) error
  8. Delete(ctx context.Context, query string, args ...interface{}) error
  9. DeleteSq(ctx context.Context, sqlizer sq.Sqlizer) error
  10. Update(ctx context.Context, query string, args ...interface{}) error
  11. UpdateSq(ctx context.Context, sqlizer sq.Sqlizer) error
  12. Exec(ctx context.Context, query string, args ...interface{}) error
  13. ExecSq(ctx context.Context, sqlizer sq.Sqlizer) error
With trasactions
  1. Tx(ctx context.Context, opts *sql.TxOptions) (*sqlx.Tx, error)
  2. GetTx(tx *sqlx.Tx, ctx context.Context, dest interface{}, query string, args ...interface{}) error
  3. GetSqTx(tx *sqlx.Tx, ctx context.Context, dest interface{}, sqlizer sq.Sqlizer) error
  4. SelectTx(tx *sqlx.Tx, ctx context.Context, dest interface{}, query string, args ...interface{}) error
  5. SelectSqTx(tx *sqlx.Tx, ctx context.Context, dest interface{}, sqlizer sq.Sqlizer) error
  6. SelectToMapSqTx(tx *sqlx.Tx, ctx context.Context, dest map[string]interface{}, sqlizer sq.Sqlizer) error
  7. InsertTx(tx *sqlx.Tx, ctx context.Context, query string, args ...interface{}) error
  8. InsertSqTx(tx *sqlx.Tx, ctx context.Context, sqlizer sq.Sqlizer) error
  9. DeleteTx(tx *sqlx.Tx, ctx context.Context, query string, args ...interface{}) error
  10. DeleteSqTx(tx *sqlx.Tx, ctx context.Context, sqlizer sq.Sqlizer) error
  11. UpdateTx(tx *sqlx.Tx, ctx context.Context, query string, args ...interface{}) error
  12. UpdateSqTx(tx *sqlx.Tx, ctx context.Context, sqlizer sq.Sqlizer) error
  13. ExecTx(tx *sqlx.Tx, ctx context.Context, query string, args ...interface{}) error
  14. ExecSqTx(tx *sqlx.Tx, ctx context.Context, sqlizer sq.Sqlizer) error

Options

  1. OptionDSN - dsn for connection, example: "host=localhost user=user dbname=db password=123 sslmode=disable"
  2. OptionTicker - stands for how often wrapper will check is connection active. Default is 5 sec
  3. OptionAttempts - attempts to connect to db. Default is two attempts
  4. OptionEnableLogs - enable logging errors and successful attempts to connect using default Golang logger
  5. OptionErrorChannel - channel that sends sqlx errors on connection attempts or ErrTooMuchAttempts

Example

package main

import "github.com/Planck1858/pgxwrapper"

// Optional error channel
errCh := make(chan error)

// Open (with options)
dsn := "host=localhost user=user dbname=db password=password sslmode=disable"
db, err := pgxwrapper.Open(
    pgxwrapper.OptionDSN(dsn),               // standard postgresql DSN
    pgxwrapper.OptionEnableLogs(true))       // use standard Go's log for errors/warnings on connection
    pgxwrapper.OptionTicker(time.Second*10), // how often check db's connection (and reconnect). Default = 5 sec 
    pgxwrapper.OptionAttempts(10),           // attempts to connect to db. Default = 2
    pgxwrapper.OptionErrorChannel(errCh),    // optional error channel that sends errors on connection attempts
)
if err != nil {
	panic(err) // error will cause after first 2 attempts to connect to db or if options are invalid
}

// If you using errCh, then you NEED to receive them
go func() {
	<- errCh
	db.Close()
}()

// Fields should be public!
type user struct {
    Id        string   	`db:"id"`
    Name      string 	`db:"name"`
    CreatedAt time.Time `db:"created_at"`
}

ctx := context.Background()
userId := "eeb6dcb6-0a95-4b30-9b5b-a6e2d09d972b"

// Get
var getId string
err := db.Get(ctx, getId, fmt.Sprintf("SELECT id, name FROM users WHERE id = '%s';", userId))
errCheck(err)

// GetSq
rqGet := sq.Insert("users").
    Columns("id", "name", "created_at").
    Values(UUID.New().String(), "new_user", time.Now().UTC()).
    PlaceholderFormat(sq.Dollar) // For Postgres use only Dollar placeholder

err = db.GetSq(ctx, getId, rqGet)
errCheck(err)

// Select
users := make([]user, 0)
err = db.Select(ctx, &users, "SELECT * FROM users;")
errCheck(err)

// SelectSq
rqSelect := sq.Select("*").From("users")

users := make([]user, 0)
err = db.Select(ctx, &users, rqSelect)
errCheck(err)

// SelectToMapSq
rqSelect := sq.Select("*").From("users").Where(sq.Eq{"id":userId}).PlaceholderFormat(sq.Dollar)

mapUser := make(map[string]interface{})
err = db.SelectToMapSq(ctx, mapUser, rqSelect)
errCheck(err)

userId, ok := mapUser["id"]
if ok {
    userIdStr := userId.(string) 
	...
}

// Insert
err = db.Insert(ctx, "INSERT INTO users (id, name, created_at) VALUES ($1, $2, $3);",
	UUID.New().String(), "new_user1", time.Now().UTC())
errCheck(err)

// InsertSq
rqInsert := sq.Insert("users").
    Columns("id", "name", "created_at").
    Values(UUID.New().String(), "new_user2", time.Now().UTC()).
    PlaceholderFormat(sq.Dollar)

err = db.InserSq(ctx, rqInsert)
errCheck(err)

// Delete
err = db.Delete(ctx, "DELETE FROM users WHERE id = $1;", userId)
errCheck(err)

// DeleteSq
rqDelete := sq.Delete("users").Where(sq.Eq{"id":userId}).PlaceholderFormat(sq.Dollar)

err = db.DeleteSq(ctx, rqDelete)
errCheck(err)

// Update
err = db.Update(ctx, "UPDATE users SET name = $1 WHERE id = $2;", "new_name", userId)
errCheck(err)

// UpdateSq
sqUpdate := sq.Update("users").
    Set("name", "new_name").
    Where(sq.Eq{"id":userId}).
    PlaceholderFormat(sq.Dollar)

err = db.UpdateSq(ctx, sqUpdate)
errCheck(err)

// Exec
err = db.Exec(ctx, "UPDATE users SET name = $1 WHERE id = $2;", "new_name", userId)
errCheck(err)

// ExecSq
err = db.ExecSq(ctx, sqUpdate)
errCheck(err)

// Tx
tx, err := db.Tx(ctx, nil)
errCheck(err)

defer func(){
  if err != nil {
    tx.Rollback()
  } else {
    tx.Commit()
  }
}()

res, err := tx.ExecContext(ctx, "UPDATE users SET name = $1 WHERE id = $2;", "new_name", userId)
errCheck(err)

...

Documentation

Index

Constants

This section is empty.

Variables

View Source 
var (
	ErrDBIsNotActive   = errors.New("connect not active")
	ErrTooMuchAttempts = errors.New("database closed: too much attempts")
	ErrEmptyDSN        = errors.New("empty dsn")
	ErrInvalidAttempts = errors.New("invalid attempts number, must be > 0")
)

Functions

func OptionAttempts 

func OptionAttempts(a int) option

OptionAttempts attempts to connect to db. Default is two attempts

func OptionDSN 

func OptionDSN(dsn string) option

OptionDSN dsn for connection, example: "host=localhost user=user dbname=db password=123 sslmode=disable"

func OptionEnableLogs 

func OptionEnableLogs(el bool) option

OptionEnableLogs enable logging errors and successful attempts to connect using default Golang logger

func OptionErrorChannel added in v1.0.3 

func OptionErrorChannel(ch chan error) option

OptionErrorChannel channel that sends sqlx errors on connection attempts or ErrTooMuchAttempts

func OptionTicker 

func OptionTicker(d time.Duration) option

OptionTicker stands for how often wrapper will check is connection active

Types

type DB 

type DB struct {
	// contains filtered or unexported fields
}

DB database main struct

func Open 

func Open(opts ...option) (*DB, error)

Open create db entity and start connection to database. If connection is not established after attempts on ticker, it will close db connection. error will cause after two attempts to connect.

func (*DB) Close 

func (d *DB) Close() error

Close cancel database connection and cancel context

func (*DB) Delete 

func (d *DB) Delete(ctx context.Context, query string, args ...interface{}) error

Delete delete SQL command

func (*DB) DeleteSq 

func (d *DB) DeleteSq(ctx context.Context, sqlizer sq.Sqlizer) error

DeleteSq delete SQL command with Sqlizer

func (*DB) DeleteSqTx added in v1.0.6 

func (d *DB) DeleteSqTx(tx *sqlx.Tx, ctx context.Context, sqlizer sq.Sqlizer) error

DeleteSqTx delete SQL command with Sqlizer

func (*DB) DeleteTx added in v1.0.6 

func (d *DB) DeleteTx(tx *sqlx.Tx, ctx context.Context, query string, args ...interface{}) error

DeleteTx delete SQL command

func (*DB) Exec 

func (d *DB) Exec(ctx context.Context, query string, args ...interface{}) error

Exec execute SQL command

func (*DB) ExecSq 

func (d *DB) ExecSq(ctx context.Context, sqlizer sq.Sqlizer) error

ExecSq execute SQL command with Sqlizer

func (*DB) ExecSqTx added in v1.0.6 

func (d *DB) ExecSqTx(tx *sqlx.Tx, ctx context.Context, sqlizer sq.Sqlizer) error

ExecSqTx execute SQL command with Sqlizer

func (*DB) ExecTx added in v1.0.6 

func (d *DB) ExecTx(tx *sqlx.Tx, ctx context.Context, query string, args ...interface{}) error

ExecTx execute SQL command

func (*DB) Get 

func (d *DB) Get(ctx context.Context, dest interface{}, query string, args ...interface{}) error

Get get SQL command

func (*DB) GetDB 

func (d *DB) GetDB() *sql.DB

GetDB get sqlx database

func (*DB) GetSq 

func (d *DB) GetSq(ctx context.Context, dest interface{}, sqlizer sq.Sqlizer) error

GetSq get SQL command with Sqlizer

func (*DB) GetSqTx added in v1.0.6 

func (d *DB) GetSqTx(tx *sqlx.Tx, ctx context.Context, dest interface{}, sqlizer sq.Sqlizer) error

GetSqTx get SQL command with Sqlizer

func (*DB) GetTx added in v1.0.6 

func (d *DB) GetTx(tx *sqlx.Tx, ctx context.Context, dest interface{}, query string, args ...interface{}) error

GetTx get SQL command

func (*DB) Insert 

func (d *DB) Insert(ctx context.Context, query string, args ...interface{}) error

Insert insert SQL command

func (*DB) InsertSq 

func (d *DB) InsertSq(ctx context.Context, sqlizer sq.Sqlizer) error

InsertSq insert SQL command with Sqlizer

func (*DB) InsertSqTx added in v1.0.6 

func (d *DB) InsertSqTx(tx *sqlx.Tx, ctx context.Context, sqlizer sq.Sqlizer) error

InsertSqTx insert SQL command with Sqlizer

func (*DB) InsertTx added in v1.0.6 

func (d *DB) InsertTx(tx *sqlx.Tx, ctx context.Context, query string, args ...interface{}) error

InsertTx insert SQL command

func (*DB) IsActive 

func (d *DB) IsActive() bool

IsActive get connection status

func (*DB) Select 

func (d *DB) Select(ctx context.Context, dest interface{}, query string, args ...interface{}) error

Select select SQL command

func (*DB) SelectSq 

func (d *DB) SelectSq(ctx context.Context, dest interface{}, sqlizer sq.Sqlizer) error

SelectSq select SQL command with Sqlizer

func (*DB) SelectSqTx added in v1.0.6 

func (d *DB) SelectSqTx(tx *sqlx.Tx, ctx context.Context, dest interface{}, sqlizer sq.Sqlizer) error

SelectSqTx select SQL command with Sqlizer

func (*DB) SelectToMapSq 

func (d *DB) SelectToMapSq(ctx context.Context, dest map[string]interface{}, sqlizer sq.Sqlizer) error

SelectToMapSq select SQL command with Sqlizer with mapper function

func (*DB) SelectToMapSqTx added in v1.0.6 

func (d *DB) SelectToMapSqTx(tx *sqlx.Tx, ctx context.Context, dest map[string]interface{}, sqlizer sq.Sqlizer) error

SelectToMapSqTx select SQL command with Sqlizer with mapper function

func (*DB) SelectTx added in v1.0.6 

func (d *DB) SelectTx(tx *sqlx.Tx, ctx context.Context, dest interface{}, query string, args ...interface{}) error

SelectTx select SQL command

func (*DB) Tx 

func (d *DB) Tx(ctx context.Context, opts *sql.TxOptions) (*sqlx.Tx, error)

Tx start transaction

func (*DB) Update 

func (d *DB) Update(ctx context.Context, query string, args ...interface{}) error

Update update SQL command

func (*DB) UpdateSq 

func (d *DB) UpdateSq(ctx context.Context, sqlizer sq.Sqlizer) error

UpdateSq update SQL command with Sqlizer

func (*DB) UpdateSqTx added in v1.0.6 

func (d *DB) UpdateSqTx(tx *sqlx.Tx, ctx context.Context, sqlizer sq.Sqlizer) error

UpdateSqTx update SQL command with Sqlizer

func (*DB) UpdateTx added in v1.0.6 

func (d *DB) UpdateTx(tx *sqlx.Tx, ctx context.Context, query string, args ...interface{}) error

UpdateTx update SQL command

type PgDatabase 

type PgDatabase interface {
	GetDB() *sql.DB
	IsActive() bool
	Close() error

	Get(ctx context.Context, dest interface{}, query string, args ...interface{}) error
	GetSq(ctx context.Context, dest interface{}, sqlizer sq.Sqlizer) error
	Select(ctx context.Context, dest interface{}, query string, args ...interface{}) error
	SelectSq(ctx context.Context, dest interface{}, sqlizer sq.Sqlizer) error
	SelectToMapSq(ctx context.Context, dest map[string]interface{}, sqlizer sq.Sqlizer) error
	Insert(ctx context.Context, query string, args ...interface{}) error
	InsertSq(ctx context.Context, sqlizer sq.Sqlizer) error
	Delete(ctx context.Context, query string, args ...interface{}) error
	DeleteSq(ctx context.Context, sqlizer sq.Sqlizer) error
	Update(ctx context.Context, query string, args ...interface{}) error
	UpdateSq(ctx context.Context, sqlizer sq.Sqlizer) error
	Exec(ctx context.Context, query string, args ...interface{}) error
	ExecSq(ctx context.Context, sqlizer sq.Sqlizer) error

	Tx(ctx context.Context, opts *sql.TxOptions) (*sqlx.Tx, error)
	GetTx(tx *sqlx.Tx, ctx context.Context, dest interface{}, query string, args ...interface{}) error
	GetSqTx(tx *sqlx.Tx, ctx context.Context, dest interface{}, sqlizer sq.Sqlizer) error
	SelectTx(tx *sqlx.Tx, ctx context.Context, dest interface{}, query string, args ...interface{}) error
	SelectSqTx(tx *sqlx.Tx, ctx context.Context, dest interface{}, sqlizer sq.Sqlizer) error
	SelectToMapSqTx(tx *sqlx.Tx, ctx context.Context, dest map[string]interface{}, sqlizer sq.Sqlizer) error
	InsertTx(tx *sqlx.Tx, ctx context.Context, query string, args ...interface{}) error
	InsertSqTx(tx *sqlx.Tx, ctx context.Context, sqlizer sq.Sqlizer) error
	DeleteTx(tx *sqlx.Tx, ctx context.Context, query string, args ...interface{}) error
	DeleteSqTx(tx *sqlx.Tx, ctx context.Context, sqlizer sq.Sqlizer) error
	UpdateTx(tx *sqlx.Tx, ctx context.Context, query string, args ...interface{}) error
	UpdateSqTx(tx *sqlx.Tx, ctx context.Context, sqlizer sq.Sqlizer) error
	ExecTx(tx *sqlx.Tx, ctx context.Context, query string, args ...interface{}) error
	ExecSqTx(tx *sqlx.Tx, ctx context.Context, sqlizer sq.Sqlizer) error
}

Source Files

View all Source files

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.