sqlm

package module
Version: v0.0.0-...-867c994 Latest Latest
Warning

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

Go to latest
Published: Oct 18, 2016 License: MIT Imports: 4 Imported by: 0

README

sqlm

A minimalist sql builder

Motivation

  • Used and tired of ORM. It adds so many layers of abstraction and you have to learn its paradigm to do something non-trivial.
  • Getting tired of fmt.Sprintf of SQL, you have to put a %s and then append some value, or put a ? and count the number of args.
  • Composable, easily composed by table name, columns and arguments which are defined at different places.

So here comes sqlm, which just formats sql and escapes the arguments with the same structure as raw sql.

// We should only retrieve columns asked by clients, so column list is passed in
// Table name defined some where else.
// The field name is defined near the model, so rename a field won't miss a single statement.
// Parameter of int, they don't need to be argumented as ?, caz no sql injection possible for int.
// Combine all of above, here comes a mess sql statement.
db.Query(fmt.Sprintf(
	`SELECT %s FROM %s 
 	WHERE %s = ? AND %s = %d AND %s = ?`, columns, table, fieldName1, fieldName2, fieldValue2, fieldName3), 
 	fieldValue1, fieldValue3);

vs

// Meets all the requirements and still looks sane.
sql, args := sqlm.Build(
	"SELECT", columns, "FROM", table,
	"WHERE", sqlm.And(
		sqlm.Exp(fieldName1, "=", sqlm.P(fieldValue)),
		sqlm.Exp(fieldName2, "=", fieldValue2),
		sqlm.Exp(fieldName3, "=", sqlm.P(fieldValue3)),
	),
)
db.Query(sql, args...)
  • sqlm.Build takes ...interface{} as input, and it concatenate them to build the sql statement.
  • sqlm.P marks that the passed in variable should generate a "?" in sql statement and its value append to the argument list.
  • A simple yet flexible F(ormat) method, which makes writing values list, where condition a piece of cake.
// generate some records to be inserted
recordsToBeInsert := []Record{.......}

// map each of record to sqlm.Expressions
expressions := []sqlm.Expression{} 
for _, record := range recordsToBeInsert {
    expressions = append(expressions, sqlm.F("(1, 2)", &record.field1, sqlm.P(&record.field2), &record.field3)) // (field1Value, field2Value, field3Value)
}

sqlm.Build(
	"INSERT INTO", table, sqlm.F("(1, 2)", columnList),
	"VALUES", sqlm.F("1,\n2", expressions),
)
  • Above example is complicated, it tries to bulk insert several dynamic generated records. So we first build value expressions for each record. sqlm.F("(1, 2)", fields...) will generate sql like (field1, P(field2), field3), and field2 will be replaced by a ?. Handy!
  • Then sqlm.F("1, \n2", expressions) will combine above generated expressions into
(field1, field2, field3),  // for record1
(field1, field2, field3),  // for record2
  • It has two parts, but still clear.

Some other Examples:

Select

sql, args := sqlm.Build(
		"SELECT abc, def FROM what",
		"WHERE abc =", 1,
		"AND bcd =", sqlm.P(2),
)

Yields:

SELECT abc, def FROM what WHERE abc = 1 AND bcd = ?
// args: [2]

Select with nested condition

	b := 23
	sql, args := sqlm.Build(
		"SELECT a, b, c, d FROM table",
		"WHERE",
		sqlm.And(
			sqlm.Or(
				sqlm.Exp("a = 3"),
				sqlm.Exp("b =", sqlm.P(b)),
			),
			sqlm.Exp("c", "=", sqlm.P(24)),
		),
	)

Yields:

SELECT a, b, c, d FROM table WHERE ((a = 3 OR b = ?) AND c = ?)

// args: [23, 24]

Docs

Expression

An interface which is the building block of this lib. Other operators either generate Expressions or Transform Expressions or Composite Expressions. You get the idea.

type Expression interface{
    ToSql() (string, []interface{})
}

sqlm.Exp

It wraps all its arguments into an Expression

sqlm.Exp("a", "=", "1")
sqlm.Exp(sqlm.Exp("a =", 1), "AND", sqlm.Exp("b = 2"))

sqlm.P

This indicates that it should be treated as dynamic parameter, so the ? generated and the value put into arguments

str := ReadFromRequest()
sql, arguments := sqlm.Exp("a =", sqlm.P(str)).ToSql()
// sql == "a = ?"
// arguments == [str]

sqlm.F

F stands for Format. The format defines how it composites all the expressions.

sqlm.F("(1,2)", "a = 1", "b = 2") // (a = 1,b = 2)
sqlm.F("1, 2", "a = 1", "b = 2") // a = 1, b = 2
sqlm.F("{prefix}1{sep}2{suffix}", "a = 1", "b = 2", "c = 3") // {prefix}a = 1{sep}b = 2{sep}c = 3{suffix}

sqlm.And

It is just an alias of

F("(1 AND 2)", expressions...)

sqlm.Or

F("(1 OR 2)", expressions...)

sqlm.G

Group, alias to

F("(1 2)"

Issues

  • Now when the parameter list is large, around 10000, it took around 10ms to build the sql and argument list.

WAIT, where is the struct?

Bind with struct is a nongoal for this lib. And also pls ignore the field_mapper.go. Will remove it in the future.

Some note

Bugs expected, just like any other OOS.

MIT LICENSE

Documentation

Index

Constants

This section is empty.

Variables

This section is empty.

Functions

func Build

func Build(expressions ...interface{}) (string, []interface{})

Types

type Expression

type Expression interface {
	ToSql() (string, []interface{})
}

Any thing can be converted to a sql and its arguments is an expression

func And

func And(components ...interface{}) Expression

func Exp

func Exp(components ...interface{}) Expression

Exp("SELECT", "a, b", "FROM", tableName) => "SELECT a, b FROM table". Use space to join all expressions

func F

func F(sepFormat string, components ...interface{}) Expression

sep format, like dateformatter, we use magic numbers to split (1,2) => prefix:( sep:, suffix:) 1,2 => prefix: sep:, suffix: If the sep has three letters, then the first is prefix, last is suffix and middle is the sep

func G

func G(components ...interface{}) Expression

func Not

func Not(exp interface{}) Expression

func Or

func Or(components ...interface{}) Expression

func P

func P(components ...interface{}) []Expression

Convert all components to Value expression E.g, 1 => 1

"what" => "?" args: "what"
Time => "?" args: "Time"

type Raw

type Raw struct {
	Sql       string
	Arguments []interface{}
}

Raw expression just a wrapper of a sql and relative arguments

func NewRaw

func NewRaw(sql string, arguments ...interface{}) Raw

func (Raw) ToSql

func (s Raw) ToSql() (string, []interface{})

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
t or T : Toggle theme light dark auto
y or Y : Canonical URL