Insert

Insert rows using the shared DB API and understand driver-specific insert behavior.

Use db.Insert when you want to execute an insert statement.

Insert is a semantic wrapper around Exec.

It does not generate SQL, inspect the statement, or behave like an ORM. It only makes application code easier to read.

result, err := db.Insert(ctx, conn, insertUserQuery, name, email, active)

Insert behavior differs between database engines.

The shared db API stays the same, but generated IDs, placeholders, and data modeling patterns are driver-specific.

Overview

Driver	Common pattern
MySQL	`db.Insert` and read `result.LastInsertId()`
Postgres	`INSERT ... RETURNING id` and read the ID with `db.Value`
Scylla	Generate IDs in application code and insert into query tables

Dialect Insert

Use dialect insert helpers when insert SQL is stored in db.DialectSQL.

This does not replace db.Insert.

Use db.Insert when you pass a direct SQL or CQL query string.

Use dialect helpers when you pass a db.DialectSQL value loaded from query models.

type Queries struct {
	InsertUser db.DialectSQL `json:"InsertUser"`
}

The correct helper depends on the active driver and on whether the insert statement returns a value.

Driver	Helper	SQL pattern	Result handling
MySQL	`db.InsertDialect`	`INSERT ... VALUES ...`	check `db.Result`
PostgreSQL	`db.InsertReturnDialect`	`INSERT ... RETURNING`	check `db.Result`
Scylla	`db.InsertDialect`	`INSERT ... VALUES ...`	ignore result

db.InsertDialect and db.InsertReturnDialect are not interchangeable.

Use db.InsertReturnDialect only when the selected query returns a value, such as PostgreSQL RETURNING.

MySQL Insert

MySQL commonly returns the generated auto-increment ID through LastInsertId.

const insertUserQuery = `
	INSERT INTO users (name, email, active)
	VALUES (?, ?, ?)
`

Use db.Insert:

func InsertUser(ctx context.Context, conn db.Conn, name string, email string, active bool) (db.Result, error) {
	return db.Insert(ctx, conn, insertUserQuery, name, email, active)
}

Usage:

result, err := InsertUser(ctx, conn, "Jane Doe", "jane.doe@example.com", true)
if err != nil {
	return err
}

fmt.Printf(
	"inserted user id=%d rows_affected=%d\n",
	result.LastInsertId(),
	result.RowsAffected(),
)

MySQL uses ? placeholders.

MySQL Insert Dialect

For MySQL, use db.InsertDialect.

The query uses ? placeholders.

--InsertUser
INSERT INTO users (name, email, active)
VALUES (?, ?, ?)

func InsertUser(ctx context.Context, conn db.Conn, queries Queries, name string, email string, active bool) (db.Result, error) {
	result, err := db.InsertDialect(ctx, conn, queries.InsertUser, name, email, active)
	if err != nil {
		return nil, err
	}

	if result.RowsAffected() != 1 {
		return nil, fmt.Errorf("expected 1 rows affected, got %d", result.RowsAffected())
	}

	return result, nil
}

The returned db.Result can be used to inspect the insert result.

result, err := InsertUser(ctx, conn, queries, "Jane Doe", "jane.doe@example.com", true)
if err != nil {
	log.Fatal(err)
}

fmt.Printf(
	"inserted user id=%d rows_affected=%d\n",
	result.LastInsertId(),
	result.RowsAffected(),
)

PostgreSQL Insert With RETURNING

PostgreSQL commonly uses RETURNING when you need the generated ID.

const insertUserQuery = `
	INSERT INTO users (name, email, active)
	VALUES ($1, $2, $3)
	RETURNING id
`

Use db.Value to read the returned ID.

func InsertUser(ctx context.Context, conn db.Conn, name string, email string, active bool) (db.Result, error) {
	return db.Insert(ctx, conn, insertUserQuery, name, email, active)
}

Usage:

result, err := InsertUser(ctx, conn, "Alice Doe", "alice.doe@example.com", true)

if err != nil {
	log.Fatal(err)
}

fmt.Printf("inserted user id=%d rows_affected=%d\n", result.LastInsertId(), result.RowsAffected())

PostgreSQL uses numbered placeholders such as $1, $2, and $3.

PostgreSQL Insert Without RETURNING

If you do not need a generated value, you can also use db.Insert.

result, err := db.Insert(ctx, conn, `
	INSERT INTO audit_logs (message)
	VALUES ($1)
`, message)

if err != nil {
	return err
}

fmt.Println(result.RowsAffected())

For generated IDs, prefer RETURNING with db.Value.

PostgreSQL Insert Dialect

For PostgreSQL inserts that use RETURNING, use db.InsertReturnDialect.

The query uses numbered placeholders and must include RETURNING.

--InsertUser
INSERT INTO users (name, email, active)
VALUES ($1, $2, $3)
RETURNING id

func InsertUser(ctx context.Context, conn db.Conn, queries Queries, name string, email string, active bool) (db.Result, error) {
	result, err := db.InsertReturnDialect(ctx, conn, queries.InsertUser, name, email, active)
	if err != nil {
		return nil, err
	}

	if result.RowsAffected() != 1 {
		return nil, fmt.Errorf("expected 1 rows affected, got %d", result.RowsAffected())
	}

	return result, nil
}

Do not use db.InsertDialect for this PostgreSQL statement.

INSERT ... RETURNING returns data, so the PostgreSQL dialect helper is db.InsertReturnDialect.

Scylla Insert

Scylla data models are often query-driven.

A single logical entity may be written into multiple query tables.

For example:

const insertUserByIDQuery = `
	INSERT INTO users_by_id (id, email, name, active, created_at)
	VALUES (?, ?, ?, ?, ?)
`

const insertUserByEmailQuery = `
	INSERT INTO users_by_email (email, id, name, active, created_at)
	VALUES (?, ?, ?, ?, ?)
`

Generate the ID in application code and write both query tables.

func InsertUser(ctx context.Context, conn db.Conn, name string, email string, active bool) (string, error) {
	id := gocql.TimeUUID()
	createdAt := time.Now().UTC()

	if _, err := db.Insert(ctx, conn, insertUserByIDQuery, id, email, name, active, createdAt); err != nil {
		return "", err
	}

	if _, err := db.Insert(ctx, conn, insertUserByEmailQuery, email, id, name, active, createdAt); err != nil {
		return "", err
	}

	return id.String(), nil
}

Scylla uses ? placeholders.

This pattern makes reads efficient by writing data into the tables required by your queries.

Scylla Insert Dialect

For Scylla, use db.InsertDialect, but generate IDs in application code.

Scylla inserts do not return a db.Result that should be inspected.

--InsertUser
INSERT INTO users_by_id (id, email, name, active, created_at)
VALUES (?, ?, ?, ?, ?)

func InsertUser(ctx context.Context, conn db.Conn, queries Queries, email string, name string, active bool) (string, error) {
	id := gocql.TimeUUID()
	createdAt := time.Now().UTC()

	_, err := db.InsertDialect(ctx, conn, queries.InsertUser, id, email, name, active, createdAt)
	if err != nil {
		return "", err
	}

	return id.String(), nil
}

For Scylla, return the generated ID from application code.

Do not check RowsAffected() for Scylla inserts.

Scylla Batches

If multiple writes must be grouped, the Scylla driver also supports batches.

Use batches carefully and only when the data model requires grouped writes.

Batch behavior is documented in the Scylla Batches guide.

Insert vs Exec

Insert is equivalent to Exec.

result, err := db.Insert(ctx, conn, query, args...)

is equivalent to:

result, err := db.Exec(ctx, conn, query, args...)

Use Insert when you want the code to communicate insert intent.

Use Exec when the statement is generic.

Returning Values From Inserts

Use the API that matches your database behavior.

Need	Direct query helper	Dialect helper
MySQL generated ID	`db.Insert` + `result.LastInsertId()`	`db.InsertDialect`
PostgreSQL generated ID	`INSERT ... RETURNING id`	`db.InsertReturnDialect`
Scylla generated ID	generate ID in application code	`db.InsertDialect`
Insert without returned value	`db.Insert`	`db.InsertDialect`

Dialect Insert

For applications that support multiple drivers, keep insert SQL in db.DialectSQL.

Example:

type Queries struct {
	InsertUser db.DialectSQL `json:"InsertUser"`
}

Then select the correct query for the active driver:

q, err := db.Dialect(conn, queries.InsertUser, name, email, active)
if err != nil {
	return err
}

result, err := db.ExecQuery(ctx, conn, q)
if err != nil {
	return err
}

For PostgreSQL inserts that return an ID, use ValueQuery instead:

q, err := db.Dialect(conn, queries.InsertUser, name, email, active)
if err != nil {
	return 0, err
}

id, found, err := db.ValueQuery[int64](ctx, conn, q)
if err != nil {
	return 0, err
}

if !found {
	return 0, errors.New("insert did not return id")
}

return id, nil

Recommended Style

Use explicit SQL and choose the insert pattern based on the driver.

For MySQL:

result, err := db.Insert(ctx, conn, insertUserQuery, name, email, active)

For PostgreSQL when an ID is needed:

result, found, err := db.InsertValue(ctx, conn, insertUserQuery, name, email, active)

For Scylla:

id := gocql.TimeUUID()

_, err := db.Insert(ctx, conn, insertUserByIDQuery, id, email, name, active, createdAt)

Keep shared application code on db.Conn, but keep SQL behavior explicit.

Use db.Insert when you want to execute an insert statement.

Insert is a semantic wrapper around Exec.

It does not generate SQL, inspect the statement, or behave like an ORM. It only makes application code easier to read.

result, err := db.Insert(ctx, conn, insertUserQuery, name, email, active)

Insert behavior differs between database engines.

The shared db API stays the same, but generated IDs, placeholders, and data modeling patterns are driver-specific.

Overview

Driver	Common pattern
MySQL	`db.Insert` and read `result.LastInsertId()`
Postgres	`INSERT ... RETURNING id` and read the ID with `db.Value`
Scylla	Generate IDs in application code and insert into query tables

Dialect Insert

Use dialect insert helpers when insert SQL is stored in db.DialectSQL.

This does not replace db.Insert.

Use db.Insert when you pass a direct SQL or CQL query string.

Use dialect helpers when you pass a db.DialectSQL value loaded from query models.

type Queries struct {
	InsertUser db.DialectSQL `json:"InsertUser"`
}

The correct helper depends on the active driver and on whether the insert statement returns a value.

Driver	Helper	SQL pattern	Result handling
MySQL	`db.InsertDialect`	`INSERT ... VALUES ...`	check `db.Result`
PostgreSQL	`db.InsertReturnDialect`	`INSERT ... RETURNING`	check `db.Result`
Scylla	`db.InsertDialect`	`INSERT ... VALUES ...`	ignore result

db.InsertDialect and db.InsertReturnDialect are not interchangeable.

Use db.InsertReturnDialect only when the selected query returns a value, such as PostgreSQL RETURNING.

MySQL Insert

MySQL commonly returns the generated auto-increment ID through LastInsertId.

const insertUserQuery = `
	INSERT INTO users (name, email, active)
	VALUES (?, ?, ?)
`

Use db.Insert:

func InsertUser(ctx context.Context, conn db.Conn, name string, email string, active bool) (db.Result, error) {
	return db.Insert(ctx, conn, insertUserQuery, name, email, active)
}

Usage:

result, err := InsertUser(ctx, conn, "Jane Doe", "jane.doe@example.com", true)
if err != nil {
	return err
}

fmt.Printf(
	"inserted user id=%d rows_affected=%d\n",
	result.LastInsertId(),
	result.RowsAffected(),
)

MySQL uses ? placeholders.

MySQL Insert Dialect

For MySQL, use db.InsertDialect.

The query uses ? placeholders.

--InsertUser
INSERT INTO users (name, email, active)
VALUES (?, ?, ?)

func InsertUser(ctx context.Context, conn db.Conn, queries Queries, name string, email string, active bool) (db.Result, error) {
	result, err := db.InsertDialect(ctx, conn, queries.InsertUser, name, email, active)
	if err != nil {
		return nil, err
	}

	if result.RowsAffected() != 1 {
		return nil, fmt.Errorf("expected 1 rows affected, got %d", result.RowsAffected())
	}

	return result, nil
}

The returned db.Result can be used to inspect the insert result.

result, err := InsertUser(ctx, conn, queries, "Jane Doe", "jane.doe@example.com", true)
if err != nil {
	log.Fatal(err)
}

fmt.Printf(
	"inserted user id=%d rows_affected=%d\n",
	result.LastInsertId(),
	result.RowsAffected(),
)

PostgreSQL Insert With RETURNING

PostgreSQL commonly uses RETURNING when you need the generated ID.

const insertUserQuery = `
	INSERT INTO users (name, email, active)
	VALUES ($1, $2, $3)
	RETURNING id
`

Use db.Value to read the returned ID.

func InsertUser(ctx context.Context, conn db.Conn, name string, email string, active bool) (db.Result, error) {
	return db.Insert(ctx, conn, insertUserQuery, name, email, active)
}

Usage:

result, err := InsertUser(ctx, conn, "Alice Doe", "alice.doe@example.com", true)

if err != nil {
	log.Fatal(err)
}

fmt.Printf("inserted user id=%d rows_affected=%d\n", result.LastInsertId(), result.RowsAffected())

PostgreSQL uses numbered placeholders such as $1, $2, and $3.

PostgreSQL Insert Without RETURNING

If you do not need a generated value, you can also use db.Insert.

result, err := db.Insert(ctx, conn, `
	INSERT INTO audit_logs (message)
	VALUES ($1)
`, message)

if err != nil {
	return err
}

fmt.Println(result.RowsAffected())

For generated IDs, prefer RETURNING with db.Value.

PostgreSQL Insert Dialect

For PostgreSQL inserts that use RETURNING, use db.InsertReturnDialect.

The query uses numbered placeholders and must include RETURNING.

--InsertUser
INSERT INTO users (name, email, active)
VALUES ($1, $2, $3)
RETURNING id

func InsertUser(ctx context.Context, conn db.Conn, queries Queries, name string, email string, active bool) (db.Result, error) {
	result, err := db.InsertReturnDialect(ctx, conn, queries.InsertUser, name, email, active)
	if err != nil {
		return nil, err
	}

	if result.RowsAffected() != 1 {
		return nil, fmt.Errorf("expected 1 rows affected, got %d", result.RowsAffected())
	}

	return result, nil
}

Do not use db.InsertDialect for this PostgreSQL statement.

INSERT ... RETURNING returns data, so the PostgreSQL dialect helper is db.InsertReturnDialect.

Scylla Insert

Scylla data models are often query-driven.

A single logical entity may be written into multiple query tables.

For example:

const insertUserByIDQuery = `
	INSERT INTO users_by_id (id, email, name, active, created_at)
	VALUES (?, ?, ?, ?, ?)
`

const insertUserByEmailQuery = `
	INSERT INTO users_by_email (email, id, name, active, created_at)
	VALUES (?, ?, ?, ?, ?)
`

Generate the ID in application code and write both query tables.

func InsertUser(ctx context.Context, conn db.Conn, name string, email string, active bool) (string, error) {
	id := gocql.TimeUUID()
	createdAt := time.Now().UTC()

	if _, err := db.Insert(ctx, conn, insertUserByIDQuery, id, email, name, active, createdAt); err != nil {
		return "", err
	}

	if _, err := db.Insert(ctx, conn, insertUserByEmailQuery, email, id, name, active, createdAt); err != nil {
		return "", err
	}

	return id.String(), nil
}

Scylla uses ? placeholders.

This pattern makes reads efficient by writing data into the tables required by your queries.

Scylla Insert Dialect

For Scylla, use db.InsertDialect, but generate IDs in application code.

Scylla inserts do not return a db.Result that should be inspected.

--InsertUser
INSERT INTO users_by_id (id, email, name, active, created_at)
VALUES (?, ?, ?, ?, ?)

func InsertUser(ctx context.Context, conn db.Conn, queries Queries, email string, name string, active bool) (string, error) {
	id := gocql.TimeUUID()
	createdAt := time.Now().UTC()

	_, err := db.InsertDialect(ctx, conn, queries.InsertUser, id, email, name, active, createdAt)
	if err != nil {
		return "", err
	}

	return id.String(), nil
}

For Scylla, return the generated ID from application code.

Do not check RowsAffected() for Scylla inserts.

Scylla Batches

If multiple writes must be grouped, the Scylla driver also supports batches.

Use batches carefully and only when the data model requires grouped writes.

Batch behavior is documented in the Scylla Batches guide.

Insert vs Exec

Insert is equivalent to Exec.

result, err := db.Insert(ctx, conn, query, args...)

is equivalent to:

result, err := db.Exec(ctx, conn, query, args...)

Use Insert when you want the code to communicate insert intent.

Use Exec when the statement is generic.

Returning Values From Inserts

Use the API that matches your database behavior.

Need	Direct query helper	Dialect helper
MySQL generated ID	`db.Insert` + `result.LastInsertId()`	`db.InsertDialect`
PostgreSQL generated ID	`INSERT ... RETURNING id`	`db.InsertReturnDialect`
Scylla generated ID	generate ID in application code	`db.InsertDialect`
Insert without returned value	`db.Insert`	`db.InsertDialect`

Dialect Insert

For applications that support multiple drivers, keep insert SQL in db.DialectSQL.

Example:

type Queries struct {
	InsertUser db.DialectSQL `json:"InsertUser"`
}

Then select the correct query for the active driver:

q, err := db.Dialect(conn, queries.InsertUser, name, email, active)
if err != nil {
	return err
}

result, err := db.ExecQuery(ctx, conn, q)
if err != nil {
	return err
}

For PostgreSQL inserts that return an ID, use ValueQuery instead:

q, err := db.Dialect(conn, queries.InsertUser, name, email, active)
if err != nil {
	return 0, err
}

id, found, err := db.ValueQuery[int64](ctx, conn, q)
if err != nil {
	return 0, err
}

if !found {
	return 0, errors.New("insert did not return id")
}

return id, nil

Recommended Style

Use explicit SQL and choose the insert pattern based on the driver.

For MySQL:

result, err := db.Insert(ctx, conn, insertUserQuery, name, email, active)

For PostgreSQL when an ID is needed:

result, found, err := db.InsertValue(ctx, conn, insertUserQuery, name, email, active)

For Scylla:

id := gocql.TimeUUID()

_, err := db.Insert(ctx, conn, insertUserByIDQuery, id, email, name, active, createdAt)

Keep shared application code on db.Conn, but keep SQL behavior explicit.

Insert

Overview

Dialect Insert

MySQL Insert

MySQL Insert Dialect

PostgreSQL Insert With RETURNING

PostgreSQL Insert Without RETURNING

PostgreSQL Insert Dialect

Scylla Insert

Scylla Insert Dialect

Scylla Batches

Insert vs Exec

Returning Values From Inserts

Dialect Insert

Recommended Style

On this page

Insert

Overview

Dialect Insert

MySQL Insert

MySQL Insert Dialect

PostgreSQL Insert With RETURNING

PostgreSQL Insert Without RETURNING

PostgreSQL Insert Dialect

Scylla Insert

Scylla Insert Dialect

Scylla Batches

Insert vs Exec

Returning Values From Inserts

Dialect Insert

Recommended Style

On this page