sqlinsert

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

Features

• Column names come from struct tags; struct values become bind args.
• Single-row and multi-row inserts from a struct, struct pointer, or slice of either.
• Take the SQL string and args and use them yourself, or run the INSERT directly with Exec()/ExecContext() on a sql.DB, sql.Tx, or sql.Conn.
• Bind-parameter formats for MySQL/MariaDB/SQLite/SingleStore (?), PostgreSQL/CockroachDB ($1), SQL Server (@p1), and named parameters (@name, :name).
• Configuration is per Insert value — safe for concurrent use, even with different databases in one process.
• Errors instead of panics, all matchable with errors.Is.
• Zero dependencies. Requires Go 1.18+ (test suite passes on every release from 1.18 through 1.27).

Install

go get github.com/zachvictor/sqlinsert/v2

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"`
}

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

Before

stmt, err := db.Prepare(`INSERT INTO candy
    (id, candy_name, form_factor, description, manufacturer, weight_grams, ts)
    VALUES (?, ?, ?, ?, ?, ?, ?)`)
if err != nil { ... }
result, err := stmt.Exec(rec.Id, rec.Name, rec.FormFactor,
    rec.Description, rec.Mfr, rec.Weight, rec.Timestamp)

After

ins := sqlinsert.Insert{Table: `candy`, Data: rec}
result, err := ins.Exec(db)

Exec returns the sql.Result, so LastInsertId() and RowsAffected() work as usual. A slice makes it a multi-row insert:

ins := sqlinsert.Insert{Table: `candy`, Data: []CandyInsert{rec1, rec2, rec3}}
result, err := ins.Exec(db)

Transactions

*sql.Tx satisfies the same interface as *sql.DB, so inserts join a transaction by passing the transaction:

tx, err := db.BeginTx(ctx, nil)
if err != nil { ... }
defer tx.Rollback()

if _, err = (sqlinsert.Insert{Table: `candy`, Data: candies}).ExecContext(ctx, tx); err != nil {
    return err
}
if _, err = (sqlinsert.Insert{Table: `wrappers`, Data: wrappers}).ExecContext(ctx, tx); err != nil {
    return err
}
return tx.Commit()

Inspecting the SQL and args

Question-mark tokens are the default; set TokenType per Insert for other dialects:

ins := sqlinsert.Insert{
    Table:     `candy`,
    Data:      []CandyInsert{rec1, rec2},
    TokenType: sqlinsert.OrdinalNumberTokenType, // PostgreSQL
}
query, err := ins.SQL()
// INSERT INTO candy (id,candy_name,...,ts) VALUES ($1,$2,...,$7),($8,$9,...,$14)
args, err := ins.Args()
// []any{"c0600afd-...", "Gougat", ..., "Nussnicht", ...}
result, err := db.Exec(query, args...)

Every piece is available on its own: Columns() for the column list, Params() for the token rows, Args() for the bind args. That is also the route to anything Exec doesn't do — SQL functions in the VALUES clause, RETURNING via QueryRow, or preparing a statement once and executing it many times with your own sql.Stmt.

Token types

TokenType	VALUES tokens	Multi-row	For
QuestionMarkTokenType	?,?, ... ?	yes	MySQL, MariaDB, SQLite, SingleStore
OrdinalNumberTokenType	$1,$2, ... $n	yes	PostgreSQL, CockroachDB
AtOrdinalTokenType	@p1,@p2, ... @pn	yes	SQL Server (T-SQL)
AtColumnNameTokenType	@foo,@bar	no	named parameters
ColonTokenType	:foo,:bar	no	Oracle

QuestionMarkTokenType is the zero value, so it applies when TokenType is unset. Ordinal tokens number continuously across the rows of a multi-row insert. The named-parameter token types repeat the same name in every row, so a multi-row Insert with one of them returns an error.

Field mapping

• Exported fields with a col tag become columns and bind args, in field order.
• Unexported fields and untagged fields are skipped; col:"-" skips explicitly.
• A col tag on an unexported field is an error — the tag states an intent the package cannot honor.
• Untagged embedded structs are flattened into their parent; a tagged embedded struct is a single column.
• Read a different tag key with StructTag: sqlinsert.Insert{Table: "t", Data: rec, StructTag: "db"}

Errors

Everything sqlinsert rejects — nil or non-struct data, empty slices, bad tags, a named-parameter token type on a multi-row insert — wraps ErrInvalidInsert:

if _, err := ins.Exec(db); errors.Is(err, sqlinsert.ErrInvalidInsert) {
    // a bug on the caller's side, not a database failure
}

Errors from the database pass through unwrapped.

Caveats

• Identifiers are trusted input. The table name and tag-derived column names are interpolated into the SQL verbatim (identifiers cannot be bind parameters). Never build them from user input. Field values are always bind args, never interpolated.
• Batch limits. Drivers cap bind parameters per statement (PostgreSQL 65,535; SQL Server 2,100). Chunk large slices accordingly.

Design

sqlinsert is a helper for database/sql, not an ORM. It maps struct tags to the column list and struct values to the bind args — the two mechanical, error-prone parts of writing an INSERT — and hides nothing else. SQL's INSERT is already simple and direct; database/sql already absorbs the vagaries of drivers and bind parameters; Go structs already carry ordered, typed, tagged fields. This package just connects them.

Migrating from v1

v1	v2
import ".../sqlinsert"	import ".../sqlinsert/v2"
sqlinsert.UseTokenType = X (global)	Insert{TokenType: X} (per Insert)
sqlinsert.UseStructTag = "db" (global)	Insert{StructTag: "db"} (per Insert)
ins.Insert(db) → closed *sql.Stmt	ins.Exec(db) → sql.Result
ins.InsertContext(ctx, db)	ins.ExecContext(ctx, db) (works with sql.Conn)
ins.SQL() → string	ins.SQL() → (string, error)
Tokenize(...), ColumnNameTokenType	removed (use Columns())
panics on bad input	errors wrapping ErrInvalidInsert

Behavior fixes to be aware of: multi-row ordinal parameters now number continuously (($1,$2),($3,$4) — v1 emitted invalid repeated ($1,$2),($1,$2)), and multi-row named-token inserts now return an error instead of silently generating driver-dependent SQL.