Your first query

The same on TimescaleDB. TimescaleDB runs this example unchanged: the SQL and the Go that sgl generates are identical to the PostgreSQL version. Only dialect: in sgl.yaml changes.

Seagull reads two things: the migrations that build your schema, and the queries you write against it. From them it generates a Go package with a method for every query, typed from the schema. This first example is a tasks list: one table, and three queries to add a task, complete it and list them all.

The schema

Migrations are plain SQL files, applied in order. Seagull replays them to learn every table and column, including which columns can be NULL.

db/migrations/0001_init.up.sql

CREATE TABLE tasks (
    id         BIGSERIAL   PRIMARY KEY,
    title      TEXT        NOT NULL,
    completed  BOOLEAN     NOT NULL DEFAULT false,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

COMMENT ON TABLE tasks IS 'Jobs to do around the harbour.';
COMMENT ON COLUMN tasks.completed IS 'Whether the job is done.';

The queries

Each query starts with an sgl.query header giving the method's name and its command: what the method returns. ListTasks returns every row, so it is many.

db/queries/tasks.sql

-- sgl.query(ListTasks, many)
SELECT id, title, completed, created_at FROM tasks ORDER BY id;

-- CompleteTask marks a task done and returns how many tasks it changed.

Adding a task uses RETURNING, so the new row comes straight back and the command is one:

db/queries/tasks.sql

-- sgl.query(CreateTask, one)
INSERT INTO tasks (title) VALUES ($1) RETURNING id, title, completed, created_at;

-- ListTasks returns every task, oldest first.

Completing a task is execrows, which returns how many rows changed, so the program can tell whether the task existed:

db/queries/tasks.sql

-- sgl.query(CompleteTask, execrows)
UPDATE tasks SET completed = true WHERE id = $1;

What sgl generates

sgl generate writes the package named in sgl.yaml. Every column of the query becomes a field of a row struct, typed from the schema: a column that can be NULL would be a pointer, and these cannot be, so none are.

internal/store

type ListTasksRow struct {
	ID        int64     `json:"id"`
	Title     string    `json:"title"`
	Completed bool      `json:"completed"`
	CreatedAt time.Time `json:"created_at"`
}

// ListTasks returns every task, oldest first.
func (queries *Queries) ListTasks(ctx context.Context) ([]ListTasksRow, error) {
	rows, err := queries.reader.QueryContext(ctx, listTasksSQL)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var results []ListTasksRow
	if rows.Next() {
		var row ListTasksRow
		scanArgs := []any{&row.ID, &row.Title, &row.Completed, &row.CreatedAt}
		for more := true; more; more = rows.Next() {
			row = ListTasksRow{}
			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
}

Using it

The program opens the database, runs the migrations and calls the generated methods. There is no SQL in it and nothing to scan by hand: the methods take and return Go values.

example.go

package main

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

	"seagull.sh/seagull/examples/postgres/001_first_query/internal/store"
)

// run adds two tasks, completes the first and lists them all, through the methods sgl
// generated from db/queries/tasks.sql. The id column is BIGSERIAL, so ID is int64, and
// created_at is TIMESTAMPTZ, so CreatedAt is a time.Time; the example leaves it out of
// the output, as it changes on every run.
//
// sgl.yaml sets go.doc_comments, so the comment above each query in tasks.sql becomes its
// method's doc comment, and the migration's COMMENT ON TABLE and COMMENT ON COLUMN become
// the doc comments of the Tasks model and its Completed field: open internal/store to see
// them, or hover over queries.CreateTask in an editor.
//
// Takes database (*sql.DB) which is a migrated database, opened with pgx's database/sql
// driver.
// Takes output (io.Writer) which receives the task list.
//
// Returns error when a query fails.
func run(ctx context.Context, database *sql.DB, output io.Writer) error {
	queries := store.New(database)

	feed, err := queries.CreateTask(ctx, "Feed the seagulls")
	if err != nil {
		return fmt.Errorf("creating a task: %w", err)
	}
	if _, err := queries.CreateTask(ctx, "Count the chips"); err != nil {
		return fmt.Errorf("creating a task: %w", err)
	}

	completed, err := queries.CompleteTask(ctx, feed.ID)
	if err != nil {
		return fmt.Errorf("completing task %d: %w", feed.ID, err)
	}
	fmt.Fprintf(output, "completed %d task\n", completed)

	tasks, err := queries.ListTasks(ctx)
	if err != nil {
		return fmt.Errorf("listing tasks: %w", err)
	}
	for _, task := range tasks {
		fmt.Fprintf(output, "%d\t%-18s completed=%t\n", task.ID, task.Title, task.Completed)
	}
	return nil
}

What it prints

completed 1 task
1	Feed the seagulls  completed=true
2	Count the chips    completed=false

Try it

Change a column's type in the migration, or ask for a column that does not exist, and run sgl generate again: the Go changes with it, or sgl vet says what is wrong, with a code you can look up in the diagnostics.

Across engines

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

	PostgreSQL	SQLite	CockroachDB	MySQL	MariaDB	DuckDB	ClickHouse
id column	int64	int64	int64	·	int64	int64	·
title column	string	string	string	·	string	string	·
completed column	bool	int64	bool	·	int32	bool	·
created_at column	time.Time	string	time.Time	·	time.Time	time.Time	·
id parameter	·	·	·	·	·	·	uint64

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

TimescaleDB runs the PostgreSQL version unchanged.

Compare the whole example side by side with SQLite, CockroachDB, MySQL, MariaDB, DuckDB and ClickHouse.

Run it locally

From the directory you cloned the Seagull repository into:

cd seagull/examples/postgres && go run ./001_first_query

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

cd seagull/examples/postgres && SGL_EXAMPLE_ENGINE=timescaledb SGL_EXAMPLE_DSN="$DATABASE_URL" go test ./001_first_query

Needs a running TimescaleDB 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: tasks
    dialect: postgres
    migrations: db/migrations
    queries: db/queries
    go:
      package: store
      out: internal/store
      driver: database/sql
      doc_comments: true
    migrate:
      dsn: ${env:DATABASE_URL}

db/migrations/0001_init.up.sql

CREATE TABLE tasks (
    id         BIGSERIAL   PRIMARY KEY,
    title      TEXT        NOT NULL,
    completed  BOOLEAN     NOT NULL DEFAULT false,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

COMMENT ON TABLE tasks IS 'Jobs to do around the harbour.';
COMMENT ON COLUMN tasks.completed IS 'Whether the job is done.';

db/migrations/0001_init.down.sql

DROP TABLE tasks;

db/queries/tasks.sql

-- CreateTask adds a task and returns it with the id and creation time the database
-- gave it.
-- sgl.query(CreateTask, one)
INSERT INTO tasks (title) VALUES ($1) RETURNING id, title, completed, created_at;

-- ListTasks returns every task, oldest first.
-- sgl.query(ListTasks, many)
SELECT id, title, completed, created_at FROM tasks ORDER BY id;

-- CompleteTask marks a task done and returns how many tasks it changed.
-- sgl.query(CompleteTask, execrows)
UPDATE tasks SET completed = true WHERE id = $1;

example.go

package main

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

	"seagull.sh/seagull/examples/postgres/001_first_query/internal/store"
)

// run adds two tasks, completes the first and lists them all, through the methods sgl
// generated from db/queries/tasks.sql. The id column is BIGSERIAL, so ID is int64, and
// created_at is TIMESTAMPTZ, so CreatedAt is a time.Time; the example leaves it out of
// the output, as it changes on every run.
//
// sgl.yaml sets go.doc_comments, so the comment above each query in tasks.sql becomes its
// method's doc comment, and the migration's COMMENT ON TABLE and COMMENT ON COLUMN become
// the doc comments of the Tasks model and its Completed field: open internal/store to see
// them, or hover over queries.CreateTask in an editor.
//
// Takes database (*sql.DB) which is a migrated database, opened with pgx's database/sql
// driver.
// Takes output (io.Writer) which receives the task list.
//
// Returns error when a query fails.
func run(ctx context.Context, database *sql.DB, output io.Writer) error {
	queries := store.New(database)

	feed, err := queries.CreateTask(ctx, "Feed the seagulls")
	if err != nil {
		return fmt.Errorf("creating a task: %w", err)
	}
	if _, err := queries.CreateTask(ctx, "Count the chips"); err != nil {
		return fmt.Errorf("creating a task: %w", err)
	}

	completed, err := queries.CompleteTask(ctx, feed.ID)
	if err != nil {
		return fmt.Errorf("completing task %d: %w", feed.ID, err)
	}
	fmt.Fprintf(output, "completed %d task\n", completed)

	tasks, err := queries.ListTasks(ctx)
	if err != nil {
		return fmt.Errorf("listing tasks: %w", err)
	}
	for _, task := range tasks {
		fmt.Fprintf(output, "%d\t%-18s completed=%t\n", task.ID, task.Title, task.Completed)
	}
	return nil
}

main.go

// Command 001_first_query applies its migrations to a PostgreSQL database, then runs the
// example in example.go: two tasks are added, one is completed, and both are listed.
//
// Run it with `go run . -dsn postgres://user:password@localhost:5432/example`, 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/jackc/pgx/v5/stdlib"

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

// 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"), "PostgreSQL connection string; defaults to $DATABASE_URL")
	flag.Parse()
	if err := execute(context.Background(), *dsn); err != nil {
		log.Fatal(err)
	}
}

// execute opens and migrates the database at dsn, and runs the example against it.
//
// Takes dsn (string) which is the PostgreSQL connection string.
//
// 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 PostgreSQL connection string")
	}
	database, err := sql.Open("pgx", dsn)
	if err != nil {
		return err
	}
	defer func() { _ = database.Close() }()

	migrator, err := migrate.NewMigrator(database, migrate_backend_postgres.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

import "time"

// Jobs to do around the harbour.
type Tasks struct {
	ID    int64  `json:"id"`
	Title string `json:"title"`
	// Whether the job is done.
	Completed bool      `json:"completed"`
	CreatedAt time.Time `json:"created_at"`
}

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/tasks.sql.go

// Code generated by sgl. DO NOT EDIT.

package store

import (
	"context"
	"time"
)

const completeTaskSQL = `-- CompleteTask marks a task done and returns how many tasks it changed.
UPDATE tasks SET completed = true WHERE id = $1;`

// CompleteTask marks a task done and returns how many tasks it changed.
func (queries *Queries) CompleteTask(ctx context.Context, id int64) (int64, error) {
	results, err := queries.writer.ExecContext(ctx, completeTaskSQL, id)
	if err != nil {
		return 0, err
	}
	return results.RowsAffected()
}

const createTaskSQL = `-- CreateTask adds a task and returns it with the id and creation time the database
-- gave it.
INSERT INTO tasks (title) VALUES ($1) RETURNING id, title, completed, created_at;`

type CreateTaskRow struct {
	ID        int64     `json:"id"`
	Title     string    `json:"title"`
	Completed bool      `json:"completed"`
	CreatedAt time.Time `json:"created_at"`
}

// CreateTask adds a task and returns it with the id and creation time the database
// gave it.
func (queries *Queries) CreateTask(ctx context.Context, title string) (CreateTaskRow, error) {
	var row CreateTaskRow
	err := queries.writer.QueryRowContext(ctx, createTaskSQL, title).Scan(&row.ID, &row.Title, &row.Completed, &row.CreatedAt)
	if err != nil {
		return CreateTaskRow{}, err
	}
	return row, nil
}

const listTasksSQL = `-- ListTasks returns every task, oldest first.
SELECT id, title, completed, created_at FROM tasks ORDER BY id;`

type ListTasksRow struct {
	ID        int64     `json:"id"`
	Title     string    `json:"title"`
	Completed bool      `json:"completed"`
	CreatedAt time.Time `json:"created_at"`
}

// ListTasks returns every task, oldest first.
func (queries *Queries) ListTasks(ctx context.Context) ([]ListTasksRow, error) {
	rows, err := queries.reader.QueryContext(ctx, listTasksSQL)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var results []ListTasksRow
	if rows.Next() {
		var row ListTasksRow
		scanArgs := []any{&row.ID, &row.Title, &row.Completed, &row.CreatedAt}
		for more := true; more; more = rows.Next() {
			row = ListTasksRow{}
			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
}

output.txt

completed 1 task
1	Feed the seagulls  completed=true
2	Count the chips    completed=false