INSERT

The Inserter builder constructs INSERT queries. Create one via Table.Insert(db) or dew.Insert[T](db, schema).

Insert with values

err := Users.Insert(db).
    Columns(Users.Name, Users.Email).
    Values("Alice", "alice@example.com").
    Exec()

Multiple rows

err := Users.Insert(db).
    Columns(Users.Name, Users.Email).
    Values("Alice", "alice@example.com").
    Values("Bob", "bob@example.com").
    Exec()

Insert with models

Pass struct pointers directly — columns are inferred from struct fields:

user := &User{Name: "Alice", Email: "alice@example.com"}

err := Users.Insert(db).
    Models(user).
    Exec()

Multiple models:

err := Users.Insert(db).
    Models(user1, user2, user3).
    Exec()

Do not use Columns() with Models() — columns are inferred from the struct. Mixing them returns an error.

Returning

PostgreSQL RETURNING clause:

sql, args, _ := Users.Insert(db).
    Columns(Users.Name).
    Values("Alice").
    Returning(Users.ID).
    ToSql()
// INSERT INTO users (name) VALUES ($1) RETURNING users.id

ScanWith + Returning

inserted, err := Users.Insert(db).
    Columns(Users.Name).
    Values("Alice").
    Returning(Users.ID, Users.Name).
    ScanWith(func(rows *sql.Rows) (*User, error) {
        var u User
        err := rows.Scan(&u.ID, &u.Name)
        return &u, err
    })

On conflict (upsert)

Do nothing

err := Users.Insert(db).
    Columns(Users.Name, Users.Email).
    Values("Alice", "alice@example.com").
    OnConflict(Users.Email).
    DoNothing().
    Exec()
// INSERT INTO users (name, email) VALUES ($1, $2) ON CONFLICT (email) DO NOTHING

Do update

err := Users.Insert(db).
    Columns(Users.Name, Users.Email).
    Values("Alice", "alice@example.com").
    OnConflict(Users.Email).
    SetUpdate(Users.Name, "Alice Updated").
    Exec()
// INSERT INTO users (name, email) VALUES ($1, $2)
// ON CONFLICT (email) DO UPDATE SET name = $3

Batch insert

Batch(size) splits large inserts into multiple statements, avoiding database parameter limits (e.g. PostgreSQL's 65535 limit):

err := Users.Insert(db).
    Models(users...).
    Batch(1000).
    Exec()
// Executes multiple INSERT statements, each with up to 1000 rows

Works with values too:

err := Users.Insert(db).
    Columns(Users.Name, Users.Email).
    Values("Alice", "alice@example.com").
    Values("Bob", "bob@example.com").
    Values("Carol", "carol@example.com").
    Batch(2).
    Exec()
// 2 statements: first with Alice+Bob, second with Carol

Batch with upsert

err := Users.Insert(db).
    Models(users...).
    OnConflict(Users.Email).
    DoNothing().
    Batch(1000).
    Exec()

Inspect batch queries

BatchQueries() returns the SQL and args for each chunk without executing:

queries, args, err := Users.Insert(db).
    Models(users...).
    Batch(1000).
    BatchQueries()
// queries[0] = "INSERT INTO users (...) VALUES ($1, ...), ($5, ...)"
// args[0]    = [...]

ToSql

sql, args, err := Users.Insert(db).
    Columns(Users.Name).
    Values("Alice").
    ToSql()

Context

err := Users.Insert(db).
    Columns(Users.Name).
    Values("Alice").
    Exec(ctx)

INSERT

On this page