Insert or update

On SQLite:

• ON CONFLICT (column) DO UPDATE SET ... = excluded.column, as in PostgreSQL
• RETURNING on the upsert gives back the row as it ended up
• DO NOTHING plus execrows tells the caller whether a row was inserted

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       TEXT    NOT NULL PRIMARY KEY,
    quantity   INTEGER NOT NULL,
    deliveries INTEGER NOT NULL DEFAULT 0
);

Insert or update

ON CONFLICT (item) names the conflict target, the unique column whose clash turns the insert into an update. Inside DO UPDATE, excluded is the row the INSERT tried to write, so excluded.quantity is the size of this delivery and stock.quantity is what was already there. RETURNING hands back the row as it ended up, whichever way the statement went, so the command is one.

db/queries/stock.sql

-- sgl.query(ReceiveDelivery, one)
INSERT INTO stock (item, quantity, deliveries) VALUES (?, ?, 1)
ON CONFLICT (item) DO UPDATE SET
    quantity   = stock.quantity + excluded.quantity,
    deliveries = stock.deliveries + 1
RETURNING item, quantity, deliveries;

The generated method takes the two values the INSERT binds and returns the stored row. excluded needs no parameters of its own, because it refers to values the INSERT already has.

internal/store

type ReceiveDeliveryParams struct {
	Item     string
	Quantity int64
}

type ReceiveDeliveryRow struct {
	Item       string `json:"item"`
	Quantity   int64  `json:"quantity"`
	Deliveries int64  `json:"deliveries"`
}

func (queries *Queries) ReceiveDelivery(ctx context.Context, params ReceiveDeliveryParams) (ReceiveDeliveryRow, error) {
	var row ReceiveDeliveryRow
	err := queries.writer.QueryRowContext(ctx, receiveDeliverySQL, params.Item, params.Quantity).Scan(&row.Item, &row.Quantity, &row.Deliveries)
	if err != nil {
		return ReceiveDeliveryRow{}, err
	}
	return row, nil
}

Insert only if new

AddItem uses DO NOTHING: when the item exists, the insert is skipped and nothing changes. Declared as execrows, the method returns 1 when the row went in and 0 when it was skipped, which is all the caller needs to know.

db/queries/stock.sql

-- sgl.query(AddItem, execrows)
INSERT INTO stock (item, quantity) VALUES (?, 0)
ON CONFLICT (item) DO NOTHING;

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/sqlite/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.
	// excluded.quantity is the value the INSERT tried to write, so the parameters stay plain
	// Item and Quantity fields.
	deliveries := []store.ReceiveDeliveryParams{
		{Item: "cod", Quantity: 10},
		{Item: "potatoes", Quantity: 40},
		{Item: "cod", Quantity: 5},
	}
	for _, delivery := range deliveries {
		row, err := queries.ReceiveDelivery(ctx, delivery)
		if err != nil {
			return fmt.Errorf("receiving %s: %w", delivery.Item, err)
		}
		fmt.Fprintf(output, "%-8s delivery %d: +%d, now %d in stock\n",
			row.Item, row.Deliveries, delivery.Quantity, row.Quantity)
	}

	// AddItem uses DO NOTHING, and execrows reports whether the row went in: 1 for a new
	// item, 0 when the item was already there and the insert was skipped.
	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
potatoes delivery 1: +40, now 40 in stock
cod      delivery 2: +5, now 15 in stock
added peas with no stock yet
cod is already stocked, nothing changed
cod      15
peas      0
potatoes 40

Try it

Give AddItem a RETURNING item clause and declare it one. When the item already exists, DO NOTHING skips the insert and RETURNING has no row to give back, so the method would return sql.ErrNoRows on a perfectly ordinary call. sgl vet warns about exactly that with Q046, and suggests optional: true, which returns (row, false, nil) instead, as in optional rows.

Across engines

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

	SQLite	PostgreSQL	MySQL	DuckDB
quantity parameter	int64	int64	int32	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.

CockroachDB and TimescaleDB run the PostgreSQL version unchanged.

MariaDB runs the MySQL version unchanged.

Compare the whole example side by side with PostgreSQL, MySQL 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/sqlite && go run ./021_upserts

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

cd seagull/examples/sqlite && go test ./021_upserts

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: sqlite
    migrations: db/migrations
    queries: db/queries
    go:
      package: store
      out: internal/store

db/migrations/0001_stock.up.sql

CREATE TABLE stock (
    item       TEXT    NOT NULL PRIMARY KEY,
    quantity   INTEGER NOT NULL,
    deliveries INTEGER NOT NULL DEFAULT 0
);

db/migrations/0001_stock.down.sql

DROP TABLE stock;

db/queries/stock.sql

-- sgl.query(ReceiveDelivery, one)
INSERT INTO stock (item, quantity, deliveries) VALUES (?, ?, 1)
ON CONFLICT (item) DO UPDATE SET
    quantity   = stock.quantity + excluded.quantity,
    deliveries = stock.deliveries + 1
RETURNING item, quantity, deliveries;

-- sgl.query(AddItem, execrows)
INSERT INTO stock (item, quantity) VALUES (?, 0)
ON CONFLICT (item) DO NOTHING;

-- 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/sqlite/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.
	// excluded.quantity is the value the INSERT tried to write, so the parameters stay plain
	// Item and Quantity fields.
	deliveries := []store.ReceiveDeliveryParams{
		{Item: "cod", Quantity: 10},
		{Item: "potatoes", Quantity: 40},
		{Item: "cod", Quantity: 5},
	}
	for _, delivery := range deliveries {
		row, err := queries.ReceiveDelivery(ctx, delivery)
		if err != nil {
			return fmt.Errorf("receiving %s: %w", delivery.Item, err)
		}
		fmt.Fprintf(output, "%-8s delivery %d: +%d, now %d in stock\n",
			row.Item, row.Deliveries, delivery.Quantity, row.Quantity)
	}

	// AddItem uses DO NOTHING, and execrows reports whether the row went in: 1 for a new
	// item, 0 when the item was already there and the insert was skipped.
	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 SQLite database, then runs the example
// in example.go: deliveries are booked with INSERT ... ON CONFLICT DO UPDATE, and new
// items are added with ON CONFLICT DO NOTHING.
//
// Run it with `go run .`, or `go run . -db stock.db` to keep the database.
package main

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

	_ "modernc.org/sqlite"

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

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

func main() {
	path := flag.String("db", "", "SQLite database file; a temporary one when empty")
	flag.Parse()
	if err := execute(context.Background(), *path); err != nil {
		log.Fatal(err)
	}
}

// execute opens and migrates the database at path, or a temporary one when path is empty,
// and runs the example against it.
//
// Takes path (string) which is the database file, or empty.
//
// Returns error when the database cannot be prepared or the example fails.
func execute(ctx context.Context, path string) error {
	if path == "" {
		directory, err := os.MkdirTemp("", "seagull-example-")
		if err != nil {
			return err
		}
		defer func() { _ = os.RemoveAll(directory) }()
		path = filepath.Join(directory, "example.db")
	}
	database, err := sql.Open("sqlite", "file:"+path)
	if err != nil {
		return err
	}
	defer func() { _ = database.Close() }()

	migrator, err := migrate.NewMigrator(database, migrate_backend_sqlite.New(), 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   int64  `json:"quantity"`
	Deliveries int64  `json:"deliveries"`
}

internal/store/querier.go

// Code generated by sgl. DO NOT EDIT.

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 (?1, 0)
ON CONFLICT (item) DO NOTHING;`

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 listStockSQL = `SELECT item, quantity, deliveries FROM stock ORDER BY item;`

type ListStockRow struct {
	Item       string `json:"item"`
	Quantity   int64  `json:"quantity"`
	Deliveries int64  `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, ?2, 1)
ON CONFLICT (item) DO UPDATE SET
    quantity   = stock.quantity + excluded.quantity,
    deliveries = stock.deliveries + 1
RETURNING item, quantity, deliveries;`

type ReceiveDeliveryParams struct {
	Item     string
	Quantity int64
}

type ReceiveDeliveryRow struct {
	Item       string `json:"item"`
	Quantity   int64  `json:"quantity"`
	Deliveries int64  `json:"deliveries"`
}

func (queries *Queries) ReceiveDelivery(ctx context.Context, params ReceiveDeliveryParams) (ReceiveDeliveryRow, error) {
	var row ReceiveDeliveryRow
	err := queries.writer.QueryRowContext(ctx, receiveDeliverySQL, params.Item, params.Quantity).Scan(&row.Item, &row.Quantity, &row.Deliveries)
	if err != nil {
		return ReceiveDeliveryRow{}, err
	}
	return row, nil
}

output.txt

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