xl

package module
v0.0.0-...-8da11cd Latest Latest
Warning

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

 Go to latest Published: Sep 19, 2020 License: MIT Imports: 8 Imported by: 2

README

xl 🐷

Build Status GoDoc Go Report Card codecov

SQL query builder for golang. Built on top of sqlx. Used by mört.

Pre-alpha software. Expect plenty of bugs and frequent breaking API changes.

TODO

  • Finish this TODO list.

Similar software

Usage

package main

import (
	"log"
	"time"

	_ "github.com/mattn/go-sqlite3"
	"github.com/tomyl/xl"
	"github.com/tomyl/xl/logger"
)

const schema = `
create table department (
	id integer primary key, 
	created_at timestamp not null,
	name text not null,
	city text not null
);

insert into department (id, created_at, name, city) values (1, current_timestamp, 'HR', 'Stockholm');
insert into department (id, created_at, name, city) values (2, current_timestamp, 'R&D', 'Hong Kong');

create table employee (
	id integer primary key, 
	created_at timestamp not null,
	department_id integer references department (id) not null,
	name text not null,
	salary integer not null
);

insert into employee (id, created_at, department_id, name, salary) values (1, current_timestamp, 1, 'Alice Örn', 12000);
insert into employee (id, created_at, department_id, name, salary) values (2, current_timestamp, 2, 'Bob Älv', 9000);
`

type Department struct {
	ID        int64     `db:"id"`
	CreatedAt time.Time `db:"created_at"`
	Name      string    `db:"name"`
	City      string    `db:"city"`
}

type Employee struct {
	ID           int64     `db:"id"`
	CreatedAt    time.Time `db:"created_at"`
	DepartmentID int64     `db:"department_id"`
	Name         string    `db:"name"`
	Salary       int64     `db:"salary"`
}

func main() {
	db, err := xl.Open("sqlite3", ":memory:")

	if err != nil {
		log.Fatalf("Failed to open database: %v", err)
	}

	xl.SetLogger(logger.Color)

	if err := xl.MultiExec(db, schema); err != nil {
		log.Fatalf("Failed to create schema: %v", err)
	}

	// Insert an employee
	var empId int64
	{
		q := xl.Insert("employee")
		q.SetRaw("created_at", "current_timestamp")
		q.Set("department_id", 1)
		q.Set("name", "Cecil Ål")
		q.Set("salary", 12345)
		id, err := q.ExecId(db)
		if err != nil {
			log.Fatalf("Failed to insert: %v", err)
		}
		empId = id
		log.Printf("Inserted employee %d", empId)
	}

	// Update employee
	{
		q := xl.Update("employee")
		q.Where("id=?", empId)
		q.Set("salary", 14000)
		if err := q.ExecOne(db); err != nil {
			log.Fatalf("Failed to update: %v", err)
		}
		log.Printf("Updated employee")
	}

	// Select all employees
	{
		var entries []Employee
		q := xl.Select("*").From("employee")
		if err := q.All(db, &entries); err != nil {
			log.Fatalf("Failed to select: %v", err)
		}
		log.Printf("Employees: %v", entries)
	}

	// Select employee with highest salary
	{
		var entry Employee
		q := xl.Select("*").From("employee")
		q.OrderBy("salary DESC")
		q.LimitOffset(1, 0)
		if err := q.First(db, &entry); err != nil {
			log.Fatalf("Failed to select: %v", err)
		}
		log.Printf("Employee: %v", entry)
	}

	// Select employee names from Stockholm department
	{
		var entries []string
		q := xl.Select("e.name")
		q.FromAs("employee", "e")
		q.FromAs("department", "d")
		q.Where("e.department_id=d.id")
		q.Where("d.city=?", "Stockholm")
		if err := q.All(db, &entries); err != nil {
			log.Fatalf("Failed to select: %v", err)
		}
		log.Printf("Employees: %v", entries)
	}

	// Select employees with inner join
	{
		var entries []struct {
			Department `db:"department"`
			Employee   `db:"employee"`
		}

		iq := xl.Select(`d.name "department.name"`)
		iq.FromAs("department", "d")
		iq.Where("d.city=?", "Stockholm")

		q := xl.Select(`e.name "employee.name"`)
		q.FromAs("employee", "e")
		q.InnerJoin(iq, "d.id=e.department_id")
		q.OrderBy("d.name, e.name")

		if err := q.All(db, &entries); err != nil {
			log.Fatalf("Failed to select: %v", err)
		}

		log.Printf("Employees: %v", entries)
	}

	// Same as above, just using SelectAlias instead of Select
	{
		var entries []struct {
			Department `db:"d"`
			Employee   `db:"e"`
		}

		iq := xl.SelectAlias("name")
		iq.FromAs("department", "d")
		iq.Where("d.city=?", "Stockholm")

		q := xl.SelectAlias("name")
		q.FromAs("employee", "e")
		q.InnerJoin(iq, "d.id=e.department_id")
		q.OrderBy("d.name, e.name")

		if err := q.All(db, &entries); err != nil {
			log.Fatalf("Failed to select: %v", err)
		}

		log.Printf("Employees: %v", entries)
	}

	// Delete employee
	{
		q := xl.Delete("employee")
		q.Where("id=?", empId)
		count, err := q.ExecCount(db)
		if err != nil {
			log.Fatalf("Failed to update: %v", err)
		}
		log.Printf("Deleted %d employees", count)
	}
}

Documentation

Overview

Package xl provides convenience functions for building SQL queries.

Index

Examples

Constants

This section is empty.

Variables

This section is empty.

Functions

func MultiExec 

func MultiExec(e sqlx.Execer, query string) error

MultiExec executes a batch of SQL statements. Based on MultiExec from sqlx_test.go at github.com/jmoiron/sqlx.

func NextInt64 

func NextInt64(db Queryer, seq string) (int64, error)

func Placeholders 

func Placeholders(n int) string

func SetLogger 

func SetLogger(fn Logger)

SetLogger installs a global logger.

Types

type Context 

type Context interface {
	context.Context
	WithValue(key, value interface{}) TXContext
	DB() *DB
	Tx() *Tx
	Begin() (TXContext, error)
	Rollback() error
	Commit() error
}

type DB 

type DB struct {
	*sqlx.DB
}

A DB is a wrapper type around sqlx.DB that implements xl.Execer and xl.Queryer interfaces.

func Connect 

func Connect(driver, dsn string) (*DB, error)

Connect is same as Open except it verifies with a ping.

func NewDB 

func NewDB(db *sqlx.DB) *DB

NewDB wraps an sqlx.DB object.

func Open 

func Open(driver, dsn string) (*DB, error)

Open connects to a database.

func (*DB) Beginxl 

func (db *DB) Beginxl() (*Tx, error)

Beginxl starts a transaction.

func (*DB) Dialect 

func (db *DB) Dialect() Dialect

Dialect returns a Dialect based on this database connection.

type DeleteQuery 

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

func Delete 

func Delete(table string) *DeleteQuery

func (*DeleteQuery) Exec 

func (q *DeleteQuery) Exec(e Execer) (sql.Result, error)

func (*DeleteQuery) ExecCount 

func (q *DeleteQuery) ExecCount(e Execer) (int64, error)

func (*DeleteQuery) ExecErr 

func (q *DeleteQuery) ExecErr(e Execer) error

func (*DeleteQuery) ExecOne 

func (q *DeleteQuery) ExecOne(e Execer) error

func (*DeleteQuery) Statement 

func (q *DeleteQuery) Statement(d Dialect) (*Statement, error)

func (*DeleteQuery) Where 

func (q *DeleteQuery) Where(expr string, params ...interface{})

Where adds a WHERE clause. All WHERE clauses will be joined with AND. Note that Where doesn't surround the expression with parentheses. See SelectQuery doc for example.

type Dialect 

type Dialect struct {
	// sqlx bind type
	BindType int
}

A Dialect keep tracks of SQL dialect-specific settings.

type Execer 

type Execer interface {
	Dialect() Dialect
	Exec(query string, args ...interface{}) (sql.Result, error)
}

Execer can execute an SQL query and is also aware of which SQL dialect the database speaks.

type InsertQuery 

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

func Insert 

func Insert(table string) *InsertQuery

func (*InsertQuery) Exec 

func (q *InsertQuery) Exec(e Execer) (sql.Result, error)

func (*InsertQuery) ExecErr 

func (q *InsertQuery) ExecErr(e Execer) error

func (*InsertQuery) ExecId 

func (q *InsertQuery) ExecId(e Execer) (int64, error)

func (*InsertQuery) First 

func (q *InsertQuery) First(queryer Queryer, dest interface{}) error

func (*InsertQuery) Returning 

func (q *InsertQuery) Returning(expr string)

func (*InsertQuery) Set 

func (q *InsertQuery) Set(name string, param interface{})

func (*InsertQuery) SetRaw 

func (q *InsertQuery) SetRaw(name, rawvalue string)

func (*InsertQuery) Statement 

func (q *InsertQuery) Statement(d Dialect) (*Statement, error)

type Logger 

type Logger func(query string, params []interface{}, d time.Duration, rows int64, err error)

A Logger functions logs executed statements.

type NamedValue 

type NamedValue interface {
	Name() string
}

type Queryer 

type Queryer interface {
	Dialect() Dialect
	Query(query string, args ...interface{}) (*sql.Rows, error)
	Queryx(query string, args ...interface{}) (*sqlx.Rows, error)
	QueryRowx(query string, args ...interface{}) *sqlx.Row
}

Execer can execute an SQL query and fetch fetch the ros and is also aware of which SQL dialect the database speaks.

type SelectQuery 

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

func From 

func From(table string) *SelectQuery

func FromAs 

func FromAs(table, alias string) *SelectQuery

func NewSelect 

func NewSelect() *SelectQuery

func Select 

func Select(expr string, params ...interface{}) *SelectQuery

func SelectAlias 

func SelectAlias(cols ...string) *SelectQuery

func (*SelectQuery) All 

func (q *SelectQuery) All(queryer Queryer, dest interface{}) error

func (*SelectQuery) Clone 

func (q *SelectQuery) Clone() *SelectQuery

func (*SelectQuery) Column 

func (q *SelectQuery) Column(expr string, params ...interface{})

func (*SelectQuery) Columns 

func (q *SelectQuery) Columns(exprs ...string)

func (*SelectQuery) ColumnsAlias 

func (q *SelectQuery) ColumnsAlias(columns ...string)

func (*SelectQuery) Distinct 

func (q *SelectQuery) Distinct()

func (*SelectQuery) First 

func (q *SelectQuery) First(queryer Queryer, dest interface{}) error

func (*SelectQuery) From 

func (q *SelectQuery) From(table string) *SelectQuery

func (*SelectQuery) FromAs 

func (q *SelectQuery) FromAs(table, alias string) *SelectQuery

func (*SelectQuery) FromLateralSubselectAs 

func (q *SelectQuery) FromLateralSubselectAs(sq *SelectQuery, alias string)

func (*SelectQuery) FromSubselect 

func (q *SelectQuery) FromSubselect(sq *SelectQuery)

func (*SelectQuery) FromSubselectAs 

func (q *SelectQuery) FromSubselectAs(sq *SelectQuery, alias string)

func (*SelectQuery) GroupBy 

func (q *SelectQuery) GroupBy(expr string)

func (*SelectQuery) InnerJoin 

func (q *SelectQuery) InnerJoin(jq *SelectQuery, cond string, params ...interface{})

func (*SelectQuery) Join 

func (q *SelectQuery) Join(jq *SelectQuery, joinType, cond string, params ...interface{})

func (*SelectQuery) LeftJoin 

func (q *SelectQuery) LeftJoin(jq *SelectQuery, cond string, params ...interface{})

func (*SelectQuery) LimitOffset 

func (q *SelectQuery) LimitOffset(limit, offset int64) *SelectQuery

func (*SelectQuery) OrderBy 

func (q *SelectQuery) OrderBy(expr string, params ...interface{})

func (*SelectQuery) Queryx 

func (q *SelectQuery) Queryx(queryer Queryer) (*sqlx.Rows, error)

func (*SelectQuery) Statement 

func (q *SelectQuery) Statement(d Dialect) (*Statement, error)

func (*SelectQuery) Total 

func (q *SelectQuery) Total(queryer Queryer) (int, error)

Count runs this query without LIMIT/OFFSET and returns the COUNT.

func (*SelectQuery) Where 

func (q *SelectQuery) Where(expr string, params ...interface{})

Where adds a WHERE clause. All WHERE clauses will be joined with AND. Note that Where doesn't surround the expression with parentheses.

Example 
package main

import (
	"fmt"

	"github.com/tomyl/xl"
)

func main() {
	q := xl.Select("salary").From("employee")
	q.Where("name=?", "Alice Örn")
	q.Where("(city='Hong Kong' OR city='Stockholm')")
	st, _ := q.Statement(xl.Dialect{})
	fmt.Println(st.SQL)

}

Output:

SELECT salary FROM employee WHERE name=? AND (city='Hong Kong' OR city='Stockholm')

type Statement 

type Statement struct {
	SQL    string
	Params []interface{}
}

A Statement represents a complied SQL statement and its parameters. Bind type is undefined, i.e. the statement is must use correct bind type already.

func New 

func New(query string, params ...interface{}) *Statement

Build Statement from pre-compiled or hand-written SQL.

func (*Statement) All 

func (s *Statement) All(q Queryer, dest interface{}) error

Pass compiled SQL and parameters to sqlx.Select.

func (*Statement) Exec 

func (s *Statement) Exec(e Execer) (sql.Result, error)

Executed compiled SQL statement.

func (*Statement) ExecCount 

func (s *Statement) ExecCount(e Execer) (int64, error)

Execute compiled statement and return affected rows.

func (*Statement) ExecOne 

func (s *Statement) ExecOne(e Execer) error

Execute compiled statement and return error if affected rows is not exactly 1.

func (*Statement) First 

func (s *Statement) First(q Queryer, dest interface{}) error

Pass compiled SQL and parameters to sqlx.Get.

func (*Statement) QueryRowx 

func (s *Statement) QueryRowx(q Queryer) *sqlx.Row

Pass compiled SQL and parameters to sqlx.QueryRowx.

func (*Statement) Queryx 

func (s *Statement) Queryx(q Queryer) (*sqlx.Rows, error)

type Statementer 

type Statementer interface {
	Statement(Dialect) (*Statement, error)
}

type TXContext 

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

func WithDB 

func WithDB(ctx context.Context, db *DB) TXContext

func (TXContext) Begin 

func (c TXContext) Begin() (TXContext, error)

func (TXContext) Commit 

func (c TXContext) Commit() error

func (TXContext) DB 

func (c TXContext) DB() *DB

func (TXContext) Deadline 

func (c TXContext) Deadline() (time.Time, bool)

func (TXContext) Done 

func (c TXContext) Done() <-chan struct{}

func (TXContext) Err 

func (c TXContext) Err() error

func (TXContext) Rollback 

func (c TXContext) Rollback() error

func (TXContext) Tx 

func (c TXContext) Tx() *Tx

func (TXContext) Value 

func (c TXContext) Value(key interface{}) interface{}

func (TXContext) WithValue 

func (c TXContext) WithValue(key, value interface{}) TXContext

type Tx 

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

Wrapper type around sqlx.Tx that implements xl.Execer and xl.Queryer interfaces.

func (*Tx) Beginxl 

func (tx *Tx) Beginxl() (*Tx, error)

func (*Tx) Commit 

func (tx *Tx) Commit() error

func (*Tx) Dialect 

func (tx *Tx) Dialect() Dialect

func (*Tx) Exec 

func (tx *Tx) Exec(query string, args ...interface{}) (sql.Result, error)

func (*Tx) Query 

func (tx *Tx) Query(query string, args ...interface{}) (*sql.Rows, error)

func (*Tx) QueryRowx 

func (tx *Tx) QueryRowx(query string, args ...interface{}) *sqlx.Row

func (*Tx) Queryx 

func (tx *Tx) Queryx(query string, args ...interface{}) (*sqlx.Rows, error)

func (*Tx) Rollback 

func (tx *Tx) Rollback() error

type UpdateQuery 

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

func Update 

func Update(table string) *UpdateQuery

func (*UpdateQuery) Exec 

func (q *UpdateQuery) Exec(e Execer) (sql.Result, error)

func (*UpdateQuery) ExecCount 

func (q *UpdateQuery) ExecCount(e Execer) (int64, error)

func (*UpdateQuery) ExecErr 

func (q *UpdateQuery) ExecErr(e Execer) error

func (*UpdateQuery) ExecOne 

func (q *UpdateQuery) ExecOne(e Execer) error

func (*UpdateQuery) First 

func (q *UpdateQuery) First(queryer Queryer, dest interface{}) error

func (*UpdateQuery) Returning 

func (q *UpdateQuery) Returning(expr string)

func (*UpdateQuery) Set 

func (q *UpdateQuery) Set(name string, param interface{})

Set sets column to provided parameter. 

q.Set("title", userTitle)

func (*UpdateQuery) SetNull 

func (q *UpdateQuery) SetNull(name string)

SetNull is a shorthand for SetRaw(col, "NULL"). 

q.SetNull("error")

func (*UpdateQuery) SetRaw 

func (q *UpdateQuery) SetRaw(name, rawvalue string)

SetRaw sets column to provided SQL expression. The value will not be escaped in any way. Use Set() for values provided by untrusted sources. 

q.SetRaw("updated_at", "current_timestamp")

func (*UpdateQuery) Statement 

func (q *UpdateQuery) Statement(d Dialect) (*Statement, error)

func (*UpdateQuery) Where 

func (q *UpdateQuery) Where(expr string, params ...interface{})

Where adds a WHERE clause. All WHERE clauses will be joined with AND. Note that Where doesn't surround the expression with parentheses. See SelectQuery doc for example.

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.