sqlinsert

README

sqlinsert

Generate a 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.
• 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 custom struct tags and token types.
• Supports Go 1.8 to 1.19.
• Test coverage: 100% files, 97.5% statements. Tested on Go 1.15, 1.17, and 1.18.

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 (?, ?, ?, ?, ?, ?, ?)`)
_, err := stmt.Exec(candyInsert.Id, candyInsert.Name, candyInsert.FormFactor,
	candyInsert.Description, candyInsert.Mfr, candyInsert.Weight, candyInsert.Timestamp)

After

ins := sqlinsert.Insert{`candy`, &rec}
_, err := ins.Insert(db)

This is not an ORM

Hide nothing

Unlike ORMs, sqlinsert does not create an abstraction layer over SQL relations, nor does it restructure SQL functions. The aim is to keep it simple and hide nothing. sqlinsert is fundamentally a helper for database/sql. It simply maps struct fields to INSERT elements:

• struct tags => SQL columns and tokens string => Prepare query string
• struct values => bind args []interface{} => Exec args ...interface{} (Go 1.18+ args ...any)

Use only what you need

All aspects of SQL INSERT remain in your control:

• I just want the column names for my SQL. Insert.Columns()
• I just want the parameter-tokens for my SQL. Insert.Params()
• I just want the bind args for my Exec() call. Insert.Args()
• I just want a Prepare-Exec wrapper. Insert.Insert()

This is a helper

Let SQL be great

SQL’s INSERT is already as close to functionally pure as possible. Why would we change that? Its simplicity and directness are its power.

Let database/sql be great

Some database vendors support collection types for bind parameters, some don’t. Some database drivers support slices for bind args, some don’t. The complexity of this reality is met admirably by database/sql with the necessary amount of flexibility and abstraction: flexibility in open-ended SQL; abstraction in the variadic args ...interface{} for bind args. In this way, database/sql respects INSERT’s power, hiding nothing even as it tolerates the vagaries of bind-parameter handling among database vendors and drivers.

Let Go be great

Go structs support ordered fields, strong types, and field metadata via tags and reflection. In these respects, the Go struct can encapsulate the information of a SQL INSERT-row perfectly and completely. sqlinsert uses these features of Go structs to makes your SQL INSERT experience more Go-idiomatic.

Limitations of the Prepare-Exec wrappers

Insert.Insert and Insert.InsertContext are for simple binding only. In the spirit of “hide nothing,” these do not support SQL operations in the VALUES clause. If you require, say—

INSERT INTO foo (bar, baz, oof) VALUES (some_function(?), REPLACE(?, 'oink', 'moo'), ? + ?);

—then you can use sqlinsert.Insert methods piecemeal. For example, use Insert.Columns to build the column list for Prepare and Insert.Args to marshal the args for Exec/ExecContext.

Documentation

Index

• Variables
• func Tokenize(recordType reflect.Type, tokenType TokenType) string
• type 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
• 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 (`?`).

Functions

func Tokenize

func Tokenize(recordType reflect.Type, tokenType TokenType) string

Tokenize translates struct fields into the tokens of SQL column or value expressions as a comma-separated list enclosed in parentheses.

Types

type Insert

type Insert struct {
	Table string
	Data  interface{}
}

Insert models data used to produce a valid SQL INSERT statement with bind args. Table is the table name. Data is either a struct with column-name tagged fields and the data to be inserted or a slice struct (struct ptr works too). Private recordType and recordValue fields are used with reflection to get struct tags for Insert.Columns, Insert.Params, and Insert.SQL and to retrieve values for Insert.Args.

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. Multi Row Insert: Insert.Data is a slice; first item in slice is

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.

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
)