sqlinsert

README

sqlinsert

Generate SQL INSERT statement with bind parameters directly from a Go struct.

Features

• Define column names in struct tags.
• Struct values become bind arguments.
• Use SQL outputs and Args slice piecemeal. Or, use Insert()/InsertContext() with a sql.Conn, sql.DB, or sql.Tx to execute the INSERT statement directly, all in one blow.
• Works seamlessly with Go standard library database/sql package.
• Supports bind parameter token types of MySQL, PostgreSQL, Oracle, SingleStore (MemSQL), SQL Server (T-SQL), and their equivalents.
• Supports customized struct tags and token types.
• Supports Go 1.15 and later.
• 100% unit test coverage.

Example

Given

CREATE TABLE candy (
    id           CHAR(36) NOT NULL
    candy_name   VARCHAR(255) NOT NULL
    form_factor  VARCHAR(255) NOT NULL
    description  VARCHAR(255) NOT NULL
    manufacturer VARCHAR(255) NOT NULL
    weight_grams DECIMAL(9, 3) NOT NULL
    ts DATETIME  NOT NULL
)

type candyInsert struct {
	Id          string    `col:"id"`
	Name        string    `col:"candy_name"`
	FormFactor  string    `col:"form_factor"`
	Description string    `col:"description"`
	Mfr         string    `col:"manufacturer"`
	Weight      float64   `col:"weight_grams"`
	Timestamp   time.Time `col:"ts"`
}

var rec = candyInsert{
    Id:          `c0600afd-78a7-4a1a-87c5-1bc48cafd14e`,
    Name:        `Gougat`,
    FormFactor:  `Package`,
    Description: `tastes like gopher feed`,
    Mfr:         `Gouggle`,
    Weight:      1.16180,
    Timestamp:   time.Time{},
}

Before

stmt, _ := db.Prepare(`INSERT INTO candy 
    (id, candy_name, form_factor, description, manufacturer, weight_grams, ts) 
    VALUES ($1, $2, $3, $4, $5, $6, $7)`)
_, err := stmt.Exec(candyInsert.Id, candyInsert.Name, candyInsert.FormFactor, 
	candyInsert.Description, candyInsert.Mfr, candyInsert.Weight, candyInsert.Timestamp)

After

sqlinsert.UseTokenType = OrdinalNumberTokenType
ins := sqlinsert.NewInsert(`candy`, rec)
_, err := ins.Insert(db)

Documentation

Index

• Variables
• type Insert
  • func NewInsert(Table string, Record interface{}) *Insert
  • func (ins *Insert) Args() []interface{}
  • func (ins *Insert) Columns() string
  • func (ins *Insert) Insert(with InsertWith) (*sql.Stmt, error)
  • func (ins *Insert) InsertContext(ctx context.Context, with InsertWith) (*sql.Stmt, error)
  • func (ins *Insert) Params() string
  • func (ins *Insert) SQL() string
  • func (ins *Insert) Tokenize(tokenType TokenType) string
• type InsertWith
• type Inserter
• type TokenType

Variables

var UseStructTag = `col`

UseStructTag specifies the struct tag key for the column name. Default is `col`.

var UseTokenType = QuestionMarkTokenType

UseTokenType specifies the token type to use for values. Default is the question mark (`?`).

Types

type Insert

type Insert struct {
	Table  string
	Record interface{}
	// contains filtered or unexported fields
}

Insert models data used to produce a valid SQL INSERT statement with bind args. Table is the table name. Record is the struct with column-name tagged fields and the data to be inserted. Private fields recordType and recordValue are used internally with reflection and interfaces to field values.

func NewInsert

func NewInsert(Table string, Record interface{}) *Insert

NewInsert builds a new Insert using a given table-name (string) and record data (struct). It is recommended that new NewInsert be used to build every new INSERT; however, if only Insert.Tokenize is needed, a "manually" build Insert will support tokenization as long as Insert.Table and Insert.Record are valid.

func (*Insert) Args

func (ins *Insert) Args() []interface{}

Args returns the arguments to be bound in Insert() or the variadic Exec/ExecContext functions in database/sql.

func (*Insert) Columns

func (ins *Insert) Columns() string

Columns returns the comma-separated list of column names-as-tokens for the SQL INSERT statement.

func (*Insert) Insert

func (ins *Insert) Insert(with InsertWith) (*sql.Stmt, error)

Insert prepares and executes a SQL INSERT statement on a *sql.DB, *sql.Tx, or other Inserter-compatible interface to Prepare and Exec.

func (*Insert) InsertContext

func (ins *Insert) InsertContext(ctx context.Context, with InsertWith) (*sql.Stmt, error)

InsertContext prepares and executes a SQL INSERT statement on a *sql.DB, *sql.Tx, *sql.Conn, or other Inserter-compatible interface to PrepareContext and ExecContext.

func (*Insert) Params

func (ins *Insert) Params() string

Params returns the comma-separated list of bind param tokens for the SQL INSERT statement.

func (*Insert) SQL

func (ins *Insert) SQL() string

SQL returns the full parameterized SQL INSERT statement.

func (*Insert) Tokenize

func (ins *Insert) Tokenize(tokenType TokenType) string

Tokenize translates struct fields into the tokens of SQL column or value expressions.

type InsertWith

type InsertWith interface {
	Prepare(query string) (*sql.Stmt, error)
	PrepareContext(ctx context.Context, query string) (*sql.Stmt, error)
	Exec(query string, args ...interface{}) (sql.Result, error)
	ExecContext(ctx context.Context, query string, args ...interface{}) (sql.Result, error)
}

InsertWith models functionality needed to execute a SQL INSERT statement with database/sql via sql.DB or sql.Tx. Note: sql.Conn is also supported, however, for PrepareContext and ExecContext only.

type Inserter

type Inserter interface {
	Tokenize(tokenType TokenType) string
	Columns() string
	Params() string
	SQL() string
	Args() []interface{}
	Insert(with InsertWith) (*sql.Stmt, error)
	InsertContext(ctx context.Context, with InsertWith) (*sql.Stmt, error)
}

Inserter models functionality to produce a valid SQL INSERT statement with bind args.

type TokenType

type TokenType int

TokenType represents a type of token in a SQL INSERT statement, whether column or value expression.

const (

	// ColumnNameTokenType uses the column name from the struct tag specified by UseStructTag.
	// INSERT INTO tbl (foo, bar, ... baz)
	ColumnNameTokenType TokenType = 0

	// QuestionMarkTokenType uses question marks as value-tokens.
	// VALUES (?, ?, ... ?) -- MySQL, SingleStore
	QuestionMarkTokenType TokenType = 1

	// AtColumnNameTokenType uses @ followed by the column name from the struct tag specified by UseStructTag.
	// VALUES (@foo, @bar, ... @baz) -- MySQL, SingleStore
	AtColumnNameTokenType TokenType = 2

	// OrdinalNumberTokenType uses % plus the value of an ordered sequence of integers starting at 1.
	// %1, %2, ... %n -- Postgres
	OrdinalNumberTokenType TokenType = 3

	// ColonTokenType uses : followed by the column name from the struct tag specified by UseStructTag.
	// :foo, :bar, ... :baz -- Oracle
	ColonTokenType TokenType = 4
)