ysql

README

ysql

Make composing string SQL for github.com/jackc/pgx/v4 a little easier

This introduces a couple of new features to the fantastic github.com/jackc/pgx/v4.

1. You can Scan() into a struct and it will match by field name (or struct tag ysql:"field_name"). There are some other cool packages that do this already (https://github.com/jmoiron/sqlx or https://github.com/georgysavva/scany). so this isn't really anything special and I'm probably not as optimized as those other packages are.

2. Some magical placeholder stuff!

Instead of just $1, $2, etc, you can use a field name:

select first_name from users where id = $id;

This will expand to the value in the struct for the field that matches that name. Another shortcut is for when you need field = $field, which is very common in selects and updates:

select first_name from users where $=id;

And my favorite shortcut is for inserts, which is a sort of meta-splat operator. $$field will be replaced by the name of the field, and $... will be replaced by a comma separated list of the field values.

insert into users ($$first_name, $$last_name) values ($...);

Example:

type User struct {
    ID int
    FirstName string `ysql:"first_name"`
    LastName string `ysql:"last_name"`
}

var user User
_, err := ysql.Exec(conn, ctx, `update users set first_name = $first_name, last_name = $last_name where id = $id;`, user);
// the exact same as:
_, err := ysql.Exec(conn, ctx, `update users set $=first_name, $=last_name where $=id;`, user);

// splat syntax:
_, err := ysql.Exec(conn, ctx, `insert into users ($$first_name, $$last_name) values ($...);`, user);

// reading a row: also traditional numbered placeholders still work as you'd expect
err := ysql.QueryRow(conn, ctx, `select first_name, last_name from users where id = $1;`, id).Scan(&user)

// reading many rows: 
rows, err := ysql.Query(conn, ctx, `select first_name from users where $=first_name;`, user)
if err != nil {
    return err
}
defer rows.Close()
for rows.Next() {
    var u User
    if err := rows.Scan(&u); err != nil {
        return err
    }
}
if err := rows.Err(); err != nil {
    return err
}

Documentation

Index

• Variables
• func Exec(h Handle, ctx context.Context, sql string, args ...interface{}) (pgconn.CommandTag, error)
• func Query(h Handle, ctx context.Context, sql string, args ...interface{}) (pgx.Rows, error)
• func QueryRow(h Handle, ctx context.Context, sql string, args ...interface{}) pgx.Row
• type Handle
• type Row
  • func (r *Row) Scan(dest ...interface{}) error
• type Rows
  • func (r *Rows) Close()
  • func (r *Rows) CommandTag() pgconn.CommandTag
  • func (r *Rows) Err() error
  • func (r *Rows) FieldDescriptions() []pgproto3.FieldDescription
  • func (r *Rows) Next() bool
  • func (r *Rows) RawValues() [][]byte
  • func (r *Rows) Scan(args ...interface{}) error
  • func (r *Rows) Values() ([]interface{}, error)

Variables

var ErrNoRows = pgx.ErrNoRows

Functions

func Exec

func Exec(h Handle, ctx context.Context, sql string, args ...interface{}) (pgconn.CommandTag, error)

func Query

func Query(h Handle, ctx context.Context, sql string, args ...interface{}) (pgx.Rows, error)

func QueryRow

func QueryRow(h Handle, ctx context.Context, sql string, args ...interface{}) pgx.Row

Types

type Handle

type Handle interface {
	Query(context.Context, string, ...interface{}) (pgx.Rows, error)
	QueryRow(context.Context, string, ...interface{}) pgx.Row
	Exec(context.Context, string, ...interface{}) (pgconn.CommandTag, error)
}

type Row

type Row Rows

mimic how pgx does QueryRow except we're not bothering with an interface

func (*Row) Scan

func (r *Row) Scan(dest ...interface{}) error

type Rows

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

func (*Rows) Close

func (r *Rows) Close()

func (*Rows) CommandTag

func (r *Rows) CommandTag() pgconn.CommandTag

func (*Rows) Err

func (r *Rows) Err() error

func (*Rows) FieldDescriptions

func (r *Rows) FieldDescriptions() []pgproto3.FieldDescription

func (*Rows) Next

func (r *Rows) Next() bool

func (*Rows) RawValues

func (r *Rows) RawValues() [][]byte

func (*Rows) Scan

func (r *Rows) Scan(args ...interface{}) error

func (*Rows) Values

func (r *Rows) Values() ([]interface{}, error)