Insert or update

On MySQL:

• ON DUPLICATE KEY UPDATE fires on any primary or unique key clash; there is no conflict target to name
• VALUES(column) is the value the INSERT tried to write, where SQLite and PostgreSQL use excluded.column; MySQL 8.0.19 and later prefer a row alias (VALUES (...) AS new), which MariaDB rejects
• No RETURNING: execrows counts 1 for an insert and 2 for an update, and a follow-up SELECT reads the row
• ON DUPLICATE KEY UPDATE item = item stands in for DO NOTHING, counting 0 rows when the row exists

An upsert writes a row whether or not it already exists: it inserts the row if it is new and updates the one that is there if it is not. Doing that in one statement avoids the race in "look first, then insert or update", where two callers can both see no row and both try to insert it. Every engine spells the upsert its own way, and Seagull reads each one in the engine's own syntax.

The example keeps a chip shop's stock. Each delivery adds to an item's quantity, creating the item the first time it arrives, and a second query adds an item only if it is not stocked yet.

The schema

item is the primary key. That uniqueness is what the upsert reacts to: a second row for the same item would clash with it.

db/migrations/0001_stock.up.sql

CREATE TABLE stock (
    item       VARCHAR(50) NOT NULL PRIMARY KEY,
    quantity   INT         NOT NULL,
    deliveries INT         NOT NULL DEFAULT 0
);

Insert or update

ON DUPLICATE KEY UPDATE fires on a clash with any primary or unique key; there is no conflict target to name. VALUES(quantity) is the value the INSERT tried to write, so quantity + VALUES(quantity) adds this delivery to what was already there.

db/queries/stock.sql

-- sgl.query(ReceiveDelivery, execrows)
INSERT INTO stock (item, quantity, deliveries) VALUES (?, ?, 1)
ON DUPLICATE KEY UPDATE
    quantity   = quantity + VALUES(quantity),
    deliveries = deliveries + 1;

MySQL 8.0.19 and later also accept a row alias (VALUES (?, ?, 1) AS new, then new.quantity) and deprecate VALUES(), but MariaDB accepts only VALUES(), so the example keeps to the form both understand.

MySQL has no RETURNING, and the example keeps to SQL that both engines run, so the command is execrows. The count it returns says which way the statement went: 1 for a row inserted and 2 for a row updated.

internal/store

type ReceiveDeliveryParams struct {
	Item     string
	Quantity int32
}

func (queries *Queries) ReceiveDelivery(ctx context.Context, params ReceiveDeliveryParams) (int64, error) {
	results, err := queries.writer.ExecContext(ctx, receiveDeliverySQL, params.Item, params.Quantity)
	if err != nil {
		return 0, err
	}
	return results.RowsAffected()
}

GetStock then reads the row as it ended up:

db/queries/stock.sql

-- sgl.query(GetStock, one)
SELECT item, quantity, deliveries FROM stock WHERE item = ?;

Insert only if new

There is no DO NOTHING here, so AddItem sets item = item on a clash. That changes nothing, so MySQL counts 0 rows, and execrows returns 1 for a new item and 0 for one already stocked.

db/queries/stock.sql

-- sgl.query(AddItem, execrows)
INSERT INTO stock (item, quantity) VALUES (?, 0)
ON DUPLICATE KEY UPDATE item = item;

INSERT IGNORE would skip the duplicate too, but it also turns other errors, such as a value too long for its column, into warnings. MySQL dialect features shows where it is the right tool.

Using it

The program books three deliveries, two of them cod, then tries to add peas and cod as new items.

example.go

package main

import (
	"context"
	"database/sql"
	"fmt"
	"io"

	"seagull.sh/seagull/examples/mysql/021_upserts/internal/store"
)

// run books three deliveries into the stock table, two of them for the same item, then
// adds two items only if they are new, and prints the stock that results.
//
// Takes database (*sql.DB) which is a migrated database.
// Takes output (io.Writer) which receives each upsert's outcome and the final stock.
//
// Returns error when a query fails.
func run(ctx context.Context, database *sql.DB, output io.Writer) error {
	queries := store.New(database)

	// ReceiveDelivery inserts a new item, or adds to the quantity of one already in stock.
	// VALUES(quantity) is the value the INSERT tried to write, so the parameters stay plain
	// Item and Quantity fields. MySQL 8.0.19 and later also accept a row alias, VALUES (?,
	// ?, 1) AS new with new.quantity, and deprecate VALUES(); MariaDB accepts only VALUES(),
	// so the example keeps to it.
	//
	// MySQL has no RETURNING, so the method is execrows, and MySQL's count says what
	// happened: 1 for a row inserted, 2 for a row updated. GetStock then reads the row as it
	// ended up.
	deliveries := []store.ReceiveDeliveryParams{
		{Item: "cod", Quantity: 10},
		{Item: "potatoes", Quantity: 40},
		{Item: "cod", Quantity: 5},
	}
	for _, delivery := range deliveries {
		affected, err := queries.ReceiveDelivery(ctx, delivery)
		if err != nil {
			return fmt.Errorf("receiving %s: %w", delivery.Item, err)
		}
		row, err := queries.GetStock(ctx, delivery.Item)
		if err != nil {
			return fmt.Errorf("reading %s: %w", delivery.Item, err)
		}
		outcome := "inserted"
		if affected == 2 {
			outcome = "updated"
		}
		fmt.Fprintf(output, "%-8s delivery %d: +%d, now %d in stock (%s)\n",
			row.Item, row.Deliveries, delivery.Quantity, row.Quantity, outcome)
	}

	// AddItem sets item = item on a duplicate, which changes nothing, so MySQL counts 0
	// rows: execrows reports 1 for a new item and 0 when the item was already there. It is
	// MySQL's form of ON CONFLICT DO NOTHING; INSERT IGNORE would also hide errors such as a
	// value too long for its column.
	for _, item := range []string{"peas", "cod"} {
		added, err := queries.AddItem(ctx, item)
		if err != nil {
			return fmt.Errorf("adding %s: %w", item, err)
		}
		if added == 0 {
			fmt.Fprintf(output, "%s is already stocked, nothing changed\n", item)
			continue
		}
		fmt.Fprintf(output, "added %s with no stock yet\n", item)
	}

	stock, err := queries.ListStock(ctx)
	if err != nil {
		return fmt.Errorf("listing stock: %w", err)
	}
	for _, row := range stock {
		fmt.Fprintf(output, "%-8s %2d\n", row.Item, row.Quantity)
	}
	return nil
}

What it prints

The second cod delivery adds to the first rather than failing on the primary key, and adding cod again changes nothing.

cod      delivery 1: +10, now 10 in stock (inserted)
potatoes delivery 1: +40, now 40 in stock (inserted)
cod      delivery 2: +5, now 15 in stock (updated)
added peas with no stock yet
cod is already stocked, nothing changed
cod      15
peas      0
potatoes 40

Across engines

Only these Go types of ReceiveDelivery differ between engines; the rest are the same everywhere.

	MySQL	SQLite	PostgreSQL	DuckDB
quantity parameter	int32	int64	int64	int32
item column	·	string	string	string
quantity column	·	int64	int64	int32
deliveries column	·	int64	int64	int32

A · marks a parameter or column that version of the example does not have.

MariaDB runs the MySQL version unchanged.

CockroachDB and TimescaleDB run the PostgreSQL version unchanged.

Compare the whole example side by side with SQLite, PostgreSQL and DuckDB.

Where the example is missing:

• Not on ClickHouse. ClickHouse has no upsert statement; ReplacingMergeTree collapses rows instead.

Run it locally

From the directory you cloned the Seagull repository into:

cd seagull/examples/mysql && go run ./021_upserts

Its test runs it too, and checks what it prints against output.txt:

cd seagull/examples/mysql && SGL_EXAMPLE_DSN="$DATABASE_URL" go test ./021_upserts

Needs a running MySQL server: set DATABASE_URL, or pass -dsn, to the address of an empty database.

The files

Every file of the example, as it is in the Seagull repository.

sgl.yaml

# Copyright 2026 PolitePixels Limited
#
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
#     http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.

# This project stands against fascism, authoritarianism, and all forms of
# oppression. We built this to empower people, not to enable those who would
# strip others of their rights and dignity.

version: 1

databases:
  - name: stock
    dialect: mysql
    migrations: db/migrations
    queries: db/queries
    go:
      package: store
      out: internal/store

db/migrations/0001_stock.up.sql

CREATE TABLE stock (
    item       VARCHAR(50) NOT NULL PRIMARY KEY,
    quantity   INT         NOT NULL,
    deliveries INT         NOT NULL DEFAULT 0
);

db/migrations/0001_stock.down.sql

DROP TABLE stock;

db/queries/stock.sql

-- sgl.query(ReceiveDelivery, execrows)
INSERT INTO stock (item, quantity, deliveries) VALUES (?, ?, 1)
ON DUPLICATE KEY UPDATE
    quantity   = quantity + VALUES(quantity),
    deliveries = deliveries + 1;

-- sgl.query(GetStock, one)
SELECT item, quantity, deliveries FROM stock WHERE item = ?;

-- sgl.query(AddItem, execrows)
INSERT INTO stock (item, quantity) VALUES (?, 0)
ON DUPLICATE KEY UPDATE item = item;

-- sgl.query(ListStock, many)
SELECT item, quantity, deliveries FROM stock ORDER BY item;

example.go

package main

import (
	"context"
	"database/sql"
	"fmt"
	"io"

	"seagull.sh/seagull/examples/mysql/021_upserts/internal/store"
)

// run books three deliveries into the stock table, two of them for the same item, then
// adds two items only if they are new, and prints the stock that results.
//
// Takes database (*sql.DB) which is a migrated database.
// Takes output (io.Writer) which receives each upsert's outcome and the final stock.
//
// Returns error when a query fails.
func run(ctx context.Context, database *sql.DB, output io.Writer) error {
	queries := store.New(database)

	// ReceiveDelivery inserts a new item, or adds to the quantity of one already in stock.
	// VALUES(quantity) is the value the INSERT tried to write, so the parameters stay plain
	// Item and Quantity fields. MySQL 8.0.19 and later also accept a row alias, VALUES (?,
	// ?, 1) AS new with new.quantity, and deprecate VALUES(); MariaDB accepts only VALUES(),
	// so the example keeps to it.
	//
	// MySQL has no RETURNING, so the method is execrows, and MySQL's count says what
	// happened: 1 for a row inserted, 2 for a row updated. GetStock then reads the row as it
	// ended up.
	deliveries := []store.ReceiveDeliveryParams{
		{Item: "cod", Quantity: 10},
		{Item: "potatoes", Quantity: 40},
		{Item: "cod", Quantity: 5},
	}
	for _, delivery := range deliveries {
		affected, err := queries.ReceiveDelivery(ctx, delivery)
		if err != nil {
			return fmt.Errorf("receiving %s: %w", delivery.Item, err)
		}
		row, err := queries.GetStock(ctx, delivery.Item)
		if err != nil {
			return fmt.Errorf("reading %s: %w", delivery.Item, err)
		}
		outcome := "inserted"
		if affected == 2 {
			outcome = "updated"
		}
		fmt.Fprintf(output, "%-8s delivery %d: +%d, now %d in stock (%s)\n",
			row.Item, row.Deliveries, delivery.Quantity, row.Quantity, outcome)
	}

	// AddItem sets item = item on a duplicate, which changes nothing, so MySQL counts 0
	// rows: execrows reports 1 for a new item and 0 when the item was already there. It is
	// MySQL's form of ON CONFLICT DO NOTHING; INSERT IGNORE would also hide errors such as a
	// value too long for its column.
	for _, item := range []string{"peas", "cod"} {
		added, err := queries.AddItem(ctx, item)
		if err != nil {
			return fmt.Errorf("adding %s: %w", item, err)
		}
		if added == 0 {
			fmt.Fprintf(output, "%s is already stocked, nothing changed\n", item)
			continue
		}
		fmt.Fprintf(output, "added %s with no stock yet\n", item)
	}

	stock, err := queries.ListStock(ctx)
	if err != nil {
		return fmt.Errorf("listing stock: %w", err)
	}
	for _, row := range stock {
		fmt.Fprintf(output, "%-8s %2d\n", row.Item, row.Quantity)
	}
	return nil
}

main.go

// Command 021_upserts applies its migrations to a MySQL database, then runs the example
// in example.go: deliveries are booked with INSERT ... ON DUPLICATE KEY UPDATE, and new
// items are added only when they are not stocked yet.
//
// Run it with `go run . -dsn 'user:password@tcp(localhost:3306)/stock'`, or set
// DATABASE_URL instead of passing -dsn. Give it an empty database of its own, as the
// migrations create its tables and the example adds rows each time it runs.
package main

import (
	"context"
	"database/sql"
	"embed"
	"errors"
	"flag"
	"fmt"
	"log"
	"os"

	"github.com/go-sql-driver/mysql"

	"seagull.sh/seagull/sdk/migrate"
	"seagull.sh/seagull/sdk/migrate/migrate_backend_mysql"
)

// migrations holds the migration files, compiled into the binary.
//
//go:embed db/migrations
var migrations embed.FS

func main() {
	dsn := flag.String("dsn", os.Getenv("DATABASE_URL"), "MySQL data source name; defaults to $DATABASE_URL")
	flag.Parse()
	if err := execute(context.Background(), *dsn); err != nil {
		log.Fatal(err)
	}
}

// execute opens and migrates the database dsn names, and runs the example against it.
//
// Takes dsn (string) which is the database's data source name.
//
// Returns error when the database cannot be prepared or the example fails.
func execute(ctx context.Context, dsn string) error {
	if dsn == "" {
		return errors.New("set -dsn or DATABASE_URL to a MySQL data source name")
	}
	config, err := mysql.ParseDSN(dsn)
	if err != nil {
		return err
	}
	// Seagull maps DATETIME and TIMESTAMP columns to time.Time, which the driver returns
	// only when parseTime is set.
	config.ParseTime = true
	dsn = config.FormatDSN()

	database, err := sql.Open("mysql", dsn)
	if err != nil {
		return err
	}
	defer func() { _ = database.Close() }()

	migrator, err := migrate.NewMigrator(database, migrate_backend_mysql.New(migrate.WithDriverMultiStatements(config.MultiStatements)), migrations, "db/migrations")
	if err != nil {
		return err
	}
	if _, err := migrator.Up(ctx); err != nil {
		return fmt.Errorf("migrating: %w", err)
	}
	return run(ctx, database, os.Stdout)
}

internal/store/models.go

// Code generated by sgl. DO NOT EDIT.

package store

type Stock struct {
	Item       string `json:"item"`
	Quantity   int32  `json:"quantity"`
	Deliveries int32  `json:"deliveries"`
}

internal/store/querier.go

// Code generated by sgl. DO NOT EDIT.

// Package store reads DATETIME, DATE and TIMESTAMP columns into time.Time, so the DSN it connects with must set parseTime=true.
package store

import (
	"context"
	"database/sql"
)

type DBTX interface {
	ExecContext(ctx context.Context, query string, args ...any) (sql.Result, error)
	QueryContext(ctx context.Context, query string, args ...any) (*sql.Rows, error)
	QueryRowContext(ctx context.Context, query string, args ...any) *sql.Row
}

type dbtxWrapper interface {
	WrapDBTX(inner any) any
}

type Queries struct {
	reader  DBTX
	writer  DBTX
	wrapper dbtxWrapper
}

func New(db DBTX) *Queries {
	wrapper, _ := db.(dbtxWrapper)
	return &Queries{reader: db, writer: db, wrapper: wrapper}
}

func NewWithReplica(writer DBTX, reader DBTX) *Queries {
	wrapper, _ := writer.(dbtxWrapper)
	return &Queries{reader: reader, writer: writer, wrapper: wrapper}
}

func (queries *Queries) WithTx(transaction *sql.Tx) *Queries {
	if queries == nil {
		return &Queries{reader: transaction, writer: transaction, wrapper: nil}
	}
	db := queries.wrapDBTX(transaction)
	return &Queries{reader: db, writer: db, wrapper: queries.wrapper}
}

func (queries *Queries) wrapDBTX(db DBTX) DBTX {
	if queries.wrapper == nil {
		return db
	}
	wrapped, ok := queries.wrapper.WrapDBTX(db).(DBTX)
	if !ok {
		return db
	}
	return wrapped
}

func (queries *Queries) RunInTx(ctx context.Context, db *sql.DB, fn func(*Queries) error) error {
	transaction, err := db.BeginTx(ctx, nil)
	if err != nil {
		return err
	}
	defer transaction.Rollback()
	if err = fn(queries.WithTx(transaction)); err != nil {
		return err
	}
	return transaction.Commit()
}

internal/store/stock.sql.go

// Code generated by sgl. DO NOT EDIT.

package store

import "context"

const addItemSQL = `INSERT INTO stock (item, quantity) VALUES (?, 0)
ON DUPLICATE KEY UPDATE item = item;`

func (queries *Queries) AddItem(ctx context.Context, item string) (int64, error) {
	results, err := queries.writer.ExecContext(ctx, addItemSQL, item)
	if err != nil {
		return 0, err
	}
	return results.RowsAffected()
}

const getStockSQL = `SELECT item, quantity, deliveries FROM stock WHERE item = ?;`

type GetStockRow struct {
	Item       string `json:"item"`
	Quantity   int32  `json:"quantity"`
	Deliveries int32  `json:"deliveries"`
}

func (queries *Queries) GetStock(ctx context.Context, item string) (GetStockRow, error) {
	var row GetStockRow
	err := queries.reader.QueryRowContext(ctx, getStockSQL, item).Scan(&row.Item, &row.Quantity, &row.Deliveries)
	if err != nil {
		return GetStockRow{}, err
	}
	return row, nil
}

const listStockSQL = `SELECT item, quantity, deliveries FROM stock ORDER BY item;`

type ListStockRow struct {
	Item       string `json:"item"`
	Quantity   int32  `json:"quantity"`
	Deliveries int32  `json:"deliveries"`
}

func (queries *Queries) ListStock(ctx context.Context) ([]ListStockRow, error) {
	rows, err := queries.reader.QueryContext(ctx, listStockSQL)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var results []ListStockRow
	if rows.Next() {
		var row ListStockRow
		scanArgs := []any{&row.Item, &row.Quantity, &row.Deliveries}
		for more := true; more; more = rows.Next() {
			row = ListStockRow{}
			if err := rows.Scan(scanArgs...); err != nil {
				return nil, err
			}
			results = append(results, row)
		}
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return results, nil
}

const receiveDeliverySQL = `INSERT INTO stock (item, quantity, deliveries) VALUES (?, ?, 1)
ON DUPLICATE KEY UPDATE
    quantity   = quantity + VALUES(quantity),
    deliveries = deliveries + 1;`

type ReceiveDeliveryParams struct {
	Item     string
	Quantity int32
}

func (queries *Queries) ReceiveDelivery(ctx context.Context, params ReceiveDeliveryParams) (int64, error) {
	results, err := queries.writer.ExecContext(ctx, receiveDeliverySQL, params.Item, params.Quantity)
	if err != nil {
		return 0, err
	}
	return results.RowsAffected()
}

output.txt

cod      delivery 1: +10, now 10 in stock (inserted)
potatoes delivery 1: +40, now 40 in stock (inserted)
cod      delivery 2: +5, now 15 in stock (updated)
added peas with no stock yet
cod is already stocked, nothing changed
cod      15
peas      0
potatoes 40