prequel

package module
v1.1.0 Latest Latest
Warning

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

 Go to latest Published: Nov 9, 2020 License: MIT Imports: 7 Imported by: 0

README

PostgreSQL query bulder and executor.

GoDoc Build Status

Requirements:

  • Go >= 1.9
  • PostgreSQL >= 9.5

Installation

go get -u syreclabs.com/go/prequel

prequel is a fast and lightweight PostgreSQL query bulder and runner which uses github.com/jmoiron/sqlx under the hood. See sqlx documentaion for struct/field mapping details.

Connecting to the database

Where possible, prequel uses thin wrappers around sqlx, so most connection related methods work the same way:

db, err := Connect(context.Background(), "postgres", "postgres://host/database")
if err != nil {
    // handle the error
}

One notable difference is that most prequel methods require context.Context.

SELECT

db.Select() allows querying slice of values:

b := builder.
    Select("first_name", "last_name", "email").
    From("users").
    Where("email = $1", "user@example.com")

var users []*User
if err := db.Select(ctx, b, &users); err != nil {
    return err
}

SELECT first_name, last_name, email FROM users WHERE (email = $1) [user@example.com] 320.096µs

Use db.Get() to get a single result:

b := builder.
    Select("first_name", "last_name", "email").
    From("users").
    Where("email = $1", "john@mail.net")

var user User
if err := db.Get(ctx, b, &user); err != nil {
    return err
}

Multiple Where() are joined with AND:

b := builder.
    Select("first_name", "last_name", "email", "created_at").
    From("users").
    Where("email = $1", "user@example.com").
    Where("first_name = $1", "First").
    Where("created_at < $1", time.Now().Add(10*time.Second))

SELECT first_name, last_name, email, created_at FROM users WHERE (email = $1) AND (first_name = $2) AND (created_at < $3) [user@example.com First 2018-07-05 21:19:47.710477716 -0500 -05 m=+10.013333066] 501.125µs

Slice parameters are rewritten so they can be used in IN:

b := builder.
    Select("first_name", "last_name", "email").
    From("users").
    Where("last_name IN ($1)", []string{"Last", "Doe", "Somebody", "Else"}).
    OrderBy("first_name DESC")

SELECT first_name, last_name, email FROM users WHERE (last_name IN ($1,$2,$3,$4)) ORDER BY first_name DESC [Last Doe Somebody Else] 266.623µs

UNIONs are supported too:

b := builder.
    Select("id", "first_name", "last_name", "email").
    From("users").
    Where("id = $1", 1).
    Union(false,
        builder.
            Select("id", "first_name", "last_name", "email").
            From("users").
            Where("id IN ($1)", []int64{1, 2})).
    Union(true,
        builder.
            Select("id", "first_name", "last_name", "email").
            From("users").
            Where("id IN ($1)", []int64{1, 2})).
    OrderBy("id")

SELECT id, first_name, last_name, email FROM users WHERE (id = $1) UNION SELECT id, first_name, last_name, email FROM users WHERE (id IN ($2,$3)) UNION ALL SELECT id, first_name, last_name, email FROM users WHERE (id IN ($4,$5)) ORDER BY id [1 1 2 1 2] 664.952µs

... as well as DISTINCT, GROUP BY, HAVING, ORDER BY, OFFSET, LIMIT and WITH queries (see builder godoc and builder/select_test.go for examples).

INSERT

Single row:

b := builder.
    Insert("users").
    Columns("first_name", "last_name", "email").
    Values("Jane", "Doe", "janedoe@mymail.com")

res, _ := db.Exec(ctx, b)

INSERT INTO users (first_name, last_name, email) VALUES ($1, $2, $3) [Jane Doe janedoe@mymail.com] 189.578µs

Multiple row inserts are supported too:

b := builder.
    Insert("users").
    Columns("first_name", "last_name", "email").
    Values("Jane", "Doe", "janie@notmail.me").
    Values("John", "Roe", "john@notmail.me").
    Values("Max", "Rockatansky", "maxrockatansky@notmail.me")

res, _ := db.Exec(ctx, b)

INSERT INTO users (first_name, last_name, email) VALUES ($1, $2, $3), ($4, $5, $6), ($7, $8, $9) [Jane Doe janie@notmail.me John Roe john@notmail.me Max Rockatansky maxrockatansky@notmail.me] 220.521µs

OnConflictDoNothing() can be used to control PostgreSQL ON CONFLICT behaviour:

TODO

UPDATE and DELETE

UPDATE and DELETE are straightforward:

b := builder.
    Update("users").
    Set("last_name = $1", "Another").
    Where("email = $1", "user@example.com")

res, _ := db.Exec(ctx, b)

UPDATE users SET last_name = $1 WHERE (email = $2) [Another user@example.com] 158.102µs

b := builder.
    Delete("users").
    Where("email = $1", "user@example.com")

res, _ := db.Exec(ctx, b)

DELETE FROM users WHERE (email = $1) [user@example.com] 190.161µs

Upsert

Upsert is implemented using PostgreSQL ON CONFLICT clause:

b := builder.
    Upsert("users", "(email)").
    Columns("first_name", "last_name", "email").
    Values("Simple", "Last", "user@example.com")

res, _ := db.Exec(ctx, b)

INSERT INTO users (first_name, last_name, email) VALUES ($1, $2, $3) ON CONFLICT (email) DO UPDATE SET first_name = EXCLUDED.first_name, last_name = EXCLUDED.last_name, email = EXCLUDED.email [Simple Last user@example.com] 271.08µs

target arg accepts conditions and parameters:

b := builder.
    Upsert("users", "(email) WHERE email != $1", "janie@notmail.me").
    Columns("first_name", "last_name", "email").
    Values("Complex", "Last", "user@example.com")

res, _ := db.Exec(ctx, b)

INSERT INTO users (first_name, last_name, email) VALUES ($1, $2, $3) ON CONFLICT (email) WHERE email != $4 DO NOTHING [Wax Rockatansky maxrockatansky@notmail.me janie@notmail.me] 193.868µs

and upsert-ing multiple rows is also supported:

b := builder.
    Upsert("users", "(email)").
    Columns("first_name", "last_name", "email").
    Values("Jane", "Doe", "janie@notmail.me").
    Values("John", "Roe", "john@notmail.me").
    Values("Max", "Rockatansky", "user@example.com")

res, _ := db.Exec(ctx, b)

INSERT INTO users (first_name, last_name, email) VALUES ($1, $2, $3), ($4, $5, $6), ($7, $8, $9) ON CONFLICT (email) DO UPDATE SET first_name = EXCLUDED.first_name, last_name = EXCLUDED.last_name, email = EXCLUDED.email [Jane Doe janie@notmail.me John Roe john@notmail.me Max Rockatansky user@example.com] 199.644µs

Insect

b := builder.Insect("users").
    Columns("first_name", "last_name", "email").
    Values(user.FirstName, user.LastName, user.Email).
    Where("email = $1", user.Email).
    Returning("*")

var users []*User
_ := db.Select(ctx, b, &users)

WITH sel AS (SELECT * FROM users WHERE (email = $1)), ins AS (INSERT INTO users (first_name, last_name, email) SELECT $2, $3, $4 WHERE (NOT EXISTS(SELECT * FROM sel)) RETURNING *) SELECT * FROM ins UNION ALL SELECT * FROM sel [user@example.com First Last user@example.com] 410.672µs

Executing raw SQL

Use builder.SQL() to get just parameter handling and IN args rewriting:

b := builder.SQL("SELECT id, name FROM table1 WHERE id = $1 AND $2", 1, true)

var user User
_ := db.Get(ctx, b, &user)

Alternatively, various *Raw methods (SelectRaw, GetRaw, ExecRaw) allow executing queiries directly with sqlx.

Documentation

Overview

Package prequel provides PostgreSQL query bulder and executor.

Index

Constants

This section is empty.

Variables

This section is empty.

Functions

func SetLogLevel 

func SetLogLevel(lvl int)

func SetLogger 

func SetLogger(logger Logger)

SetLogger allows changing logging adapter used by prequel.

Types

type Beginner 

type Beginner interface {
	Begin(ctx context.Context) (*Tx, error)
	MustBegin(ctx context.Context) *Tx
	BeginTx(ctx context.Context, opts *sql.TxOptions) (*Tx, error)
	MustBeginTx(ctx context.Context, opts *sql.TxOptions) *Tx
}

Beginner is an interface used by Begin and BeginTx (and their Must* variants).

type Conn 

type Conn struct {
	Conn *sqlx.Conn
}

Conn is a wrapper around sqlx.Conn which supports builder.Builder.

func (*Conn) Begin 

func (conn *Conn) Begin(ctx context.Context) (*Tx, error)

Begin starts a new transaction using this connection.

func (*Conn) BeginTx 

func (conn *Conn) BeginTx(ctx context.Context, opts *sql.TxOptions) (*Tx, error)

BeginTx starts a new transaction using this connection.

func (*Conn) Close 

func (conn *Conn) Close() error

Close returns this connection to the connection pool.

func (*Conn) Exec 

func (conn *Conn) Exec(ctx context.Context, b builder.Builder) (sql.Result, error)

Exec using this connection.

func (*Conn) ExecRaw 

func (conn *Conn) ExecRaw(ctx context.Context, sql string, params ...interface{}) (sql.Result, error)

func (*Conn) Get 

func (conn *Conn) Get(ctx context.Context, b builder.Builder, dest interface{}) error

Get using this connection.

func (*Conn) GetRaw 

func (conn *Conn) GetRaw(ctx context.Context, dest interface{}, sql string, params ...interface{}) error

func (*Conn) MustBegin 

func (conn *Conn) MustBegin(ctx context.Context) *Tx

MustBegin starts a new transaction using this DB. This method will panic on error.

func (*Conn) MustBeginTx 

func (conn *Conn) MustBeginTx(ctx context.Context, opts *sql.TxOptions) *Tx

MustBeginTx starts a new transaction using this DB. This method will panic on error.

func (*Conn) MustExec 

func (conn *Conn) MustExec(ctx context.Context, b builder.Builder) sql.Result

MustExec using this connection. This method will panic on error.

func (*Conn) MustExecRaw 

func (conn *Conn) MustExecRaw(ctx context.Context, sql string, params ...interface{}) sql.Result

func (*Conn) Select 

func (conn *Conn) Select(ctx context.Context, b builder.Builder, dest interface{}) error

Select using this connection.

func (*Conn) SelectRaw 

func (conn *Conn) SelectRaw(ctx context.Context, dest interface{}, sql string, params ...interface{}) error

type DB 

type DB struct {
	DB *sqlx.DB
}

DB is a wrapper around sqlx.DB which supports builder.Builder.

func Connect 

func Connect(ctx context.Context, driverName, dataSourceName string) (*DB, error)

Connect is a wrapper for sqlx.Connect that returns *prequel.DB.

func MustConnect 

func MustConnect(ctx context.Context, driverName, dataSourceName string) *DB

MustConnect is a wrapper for sqlx.MustConnect that returns *prequel.DB. This method will panic on error.

func MustOpen 

func MustOpen(driverName, dataSourceName string) *DB

MustOpen is a wrapper for sqlx.MustOpen that returns *prequel.DB. This method will panic on error.

func NewDB 

func NewDB(db *sql.DB, driverName string) *DB

NewDB is a wrapper for sqlx.NewDb that returns *prequel.DB.

func Open 

func Open(driverName, dataSourceName string) (*DB, error)

Open is a wrapper for sqlx.Open that returns *prequel.DB.

func (*DB) Begin 

func (db *DB) Begin(ctx context.Context) (*Tx, error)

Begin starts a new transaction using this DB.

func (*DB) BeginTx 

func (db *DB) BeginTx(ctx context.Context, opts *sql.TxOptions) (*Tx, error)

BeginTx starts a new transaction using this DB.

func (*DB) Conn 

func (db *DB) Conn(ctx context.Context) (*Conn, error)

Conn returns a single connection using this DB. Conn will block until either a connection is returned or ctx is canceled. Queries run on the same Conn will be run in the same database session. Every Conn must be returned to the database pool after use by calling Conn.Close.

func (*DB) Exec 

func (db *DB) Exec(ctx context.Context, b builder.Builder) (sql.Result, error)

Exec using this DB.

func (*DB) ExecRaw 

func (db *DB) ExecRaw(ctx context.Context, sql string, params ...interface{}) (sql.Result, error)

func (*DB) Get 

func (db *DB) Get(ctx context.Context, b builder.Builder, dest interface{}) error

Get using this DB.

func (*DB) GetRaw 

func (db *DB) GetRaw(ctx context.Context, dest interface{}, sql string, params ...interface{}) error

func (*DB) MustBegin 

func (db *DB) MustBegin(ctx context.Context) *Tx

MustBegin starts a new transaction using this DB. This method will panic on error.

func (*DB) MustBeginTx 

func (db *DB) MustBeginTx(ctx context.Context, opts *sql.TxOptions) *Tx

MustBeginTx starts a new transaction using this DB. This method will panic on error.

func (*DB) MustConn 

func (db *DB) MustConn(ctx context.Context) *Conn

Conn returns a single connection using this DB and panic on error. Conn will block until either a connection is returned or ctx is canceled. Queries run on the same Conn will be run in the same database session. Every Conn must be returned to the database pool after use by calling Conn.Close.

func (*DB) MustExec 

func (db *DB) MustExec(ctx context.Context, b builder.Builder) sql.Result

MustExec using this DB. This method will panic on error.

func (*DB) MustExecRaw 

func (db *DB) MustExecRaw(ctx context.Context, sql string, params ...interface{}) sql.Result

func (*DB) Select 

func (db *DB) Select(ctx context.Context, b builder.Builder, dest interface{}) error

Select using this DB.

func (*DB) SelectRaw 

func (db *DB) SelectRaw(ctx context.Context, dest interface{}, sql string, params ...interface{}) error

type Execer 

type Execer interface {
	Exec(ctx context.Context, b builder.Builder) (sql.Result, error)
	ExecRaw(ctx context.Context, query string, params ...interface{}) (sql.Result, error)
	MustExec(ctx context.Context, b builder.Builder) sql.Result
	MustExecRaw(ctx context.Context, query string, params ...interface{}) sql.Result
}

Execer is an interface used by Exec and MustExec.

type Logger 

type Logger interface {
	Printf(format string, v ...interface{})
	SetLevel(lvl int)
}

Logger is a logging interface user by prequel.

type Queryer 

type Queryer interface {
	Select(ctx context.Context, b builder.Builder, dest interface{}) error
	SelectRaw(ctx context.Context, dest interface{}, q string, params ...interface{}) error
	Get(ctx context.Context, b builder.Builder, dest interface{}) error
	GetRaw(ctx context.Context, dest interface{}, q string, params ...interface{}) error
}

Queryer is an interface used by Select and Get

type Runner 

type Runner interface {
	Queryer
	Execer
}

Runner is an interface used by both Queryer and Execer.

type Tx 

type Tx struct {
	Tx *sqlx.Tx
}

Tx is a wrapper around sqlx.Tx which supports builder.Builder.

func (*Tx) Commit 

func (tx *Tx) Commit() error

Commit this transaction.

func (*Tx) Exec 

func (tx *Tx) Exec(ctx context.Context, b builder.Builder) (sql.Result, error)

Exec using this transaction.

func (*Tx) ExecRaw 

func (tx *Tx) ExecRaw(ctx context.Context, sql string, params ...interface{}) (sql.Result, error)

func (*Tx) Get 

func (tx *Tx) Get(ctx context.Context, b builder.Builder, dest interface{}) error

Get using this transaction.

func (*Tx) GetRaw 

func (tx *Tx) GetRaw(ctx context.Context, dest interface{}, sql string, params ...interface{}) error

func (*Tx) MustExec 

func (tx *Tx) MustExec(ctx context.Context, b builder.Builder) sql.Result

Must Exec using this transaction and panic on error.

func (*Tx) MustExecRaw 

func (tx *Tx) MustExecRaw(ctx context.Context, sql string, params ...interface{}) sql.Result

func (*Tx) Rollback 

func (tx *Tx) Rollback() error

Rollback this transaction.

func (*Tx) Select 

func (tx *Tx) Select(ctx context.Context, b builder.Builder, dest interface{}) error

Select using this transaction.

func (*Tx) SelectRaw 

func (tx *Tx) SelectRaw(ctx context.Context, dest interface{}, sql string, params ...interface{}) error

Source Files

View all Source files

Directories

Path Synopsis

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.