ysql

README

ysql

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

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

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 ...any) (pgconn.CommandTag, error)
• func Query(h Handle, ctx context.Context, sql string, args ...any) (pgx.Rows, error)
• func QueryRow(h Handle, ctx context.Context, sql string, args ...any) 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) Conn() *pgx.Conn
  • func (r *Rows) Err() error
  • func (r *Rows) FieldDescriptions() []pgconn.FieldDescription
  • func (r *Rows) Next() bool
  • func (r *Rows) RawValues() [][]byte
  • func (r *Rows) Scan(args ...any) 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 ...any) (pgconn.CommandTag, error)

func Query

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

func QueryRow

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

Types

type Handle

type Handle interface {
	Query(context.Context, string, ...any) (pgx.Rows, error)
	QueryRow(context.Context, string, ...any) pgx.Row
	Exec(context.Context, string, ...any) (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) Conn

func (r *Rows) Conn() *pgx.Conn

func (*Rows) Err

func (r *Rows) Err() error

func (*Rows) FieldDescriptions

func (r *Rows) FieldDescriptions() []pgconn.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 ...any) error

func (*Rows) Values

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