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

Directories

Path Synopsis