Custom functions
On this page
On SQLite:
- modernc.org/sqlite registers Go functions for every new connection with RegisterDeterministicScalarFunction
- undeclared, slugify(title) has an unknown type and is nullable; declared with null_on_null on a NOT NULL argument it is a non-null text, so the field is a string
- a parameter compared with a function's result is not typed from it, so FindArticleBySlug declares type: text
- undeclared, slugify draws warning Q005, unknown function, at each query that calls it
SQLite lets a program add its own SQL functions: you write a Go function, register it with the driver, and every query on that connection can call it. Seagull analyses your SQL without a database, though, so it has never heard of such a function and cannot tell what it returns. Declaring the function closes that gap. You give its argument types, its return type and how it treats NULL, and Seagull types every query that calls it. The same goes for functions a loadable extension adds.
This example adds slugify, which turns an article's title into a URL slug, and calls it from two queries.
The queries
One query selects the slug as a column, and one finds an article by it:
db/queries/articles.sql
-- sgl.query(ListArticleSlugs, many)
SELECT id, title, slugify(title) AS slug FROM articles ORDER BY id;
db/queries/articles.sql
-- sgl.query(FindArticleBySlug, one)
-- :slug as sgl.param(slug, type: text)
SELECT id, title FROM articles WHERE slugify(title) = :slug;
The :slug parameter declares type: text. Seagull types a parameter from what it is compared with, but it does not take the type from a function's result, so without type: text the method would take slug any.
Declaring the function
For sgl, the declaration goes under custom_functions in sgl.yaml:
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: articles
dialect: sqlite
migrations: db/migrations
queries: db/queries
custom_functions:
- name: slugify
args: [text]
returns: text
nullable: null_on_null
go:
package: store
out: internal/store
args lists the argument types and returns the result type. nullable: null_on_null says that a NULL argument gives a NULL result and any other argument gives a value. title is NOT NULL, so Seagull knows the slug is never NULL either. The other choices are never_null and called_on_null.
A program that embeds Seagull passes the same declaration to seagull.WithCustomFunctions as a seagull.CustomFunction, which is what example.go does below.
What sgl generates
The slug is a plain string:
internal/store
type ListArticleSlugsRow struct {
ID int64 `json:"id"`
Title string `json:"title"`
Slug string `json:"slug"`
}
func (queries *Queries) ListArticleSlugs(ctx context.Context) ([]ListArticleSlugsRow, error) {
rows, err := queries.reader.QueryContext(ctx, listArticleSlugsSQL)
if err != nil {
return nil, err
}
defer rows.Close()
var results []ListArticleSlugsRow
if rows.Next() {
var row ListArticleSlugsRow
scanArgs := []any{&row.ID, &row.Title, &row.Slug}
for more := true; more; more = rows.Next() {
row = ListArticleSlugsRow{}
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
}
Without the declaration, Seagull warns that the function is unknown, and the field becomes Slug any: it cannot know the type, and it has to assume the value might be NULL.
Registering the function
Seagull only needs to know the function's shape; the database has to be able to run it. main.go registers it with modernc.org/sqlite in an init function, so it is in place before any connection opens:
main.go
// Command 048_custom_functions registers slugify, a Go function, with the SQLite driver,
// then runs the example in example.go: Seagull types the queries that call slugify once
// it is declared, and the generated methods run it inside SQLite.
//
// Run it with `go run .`, or `go run . -db articles.db` to keep the database.
package main
import (
"context"
"database/sql"
"database/sql/driver"
"embed"
"flag"
"fmt"
"log"
"os"
"path/filepath"
sqlitedriver "modernc.org/sqlite"
"seagull.sh/seagull/sdk/migrate"
"seagull.sh/seagull/sdk/migrate/migrate_backend_sqlite"
)
// project holds the migration and query files, compiled into the binary.
//
//go:embed db
var project embed.FS
// init registers slugify with the driver. Every connection opened afterwards can call it,
// so it runs before main and before any test opens a database.
func init() {
sqlitedriver.MustRegisterDeterministicScalarFunction("slugify", 1, callSlugify)
}
// callSlugify adapts slugify to the driver's calling convention: NULL gives NULL, and
// text gives its slug.
//
// Takes arguments ([]driver.Value) which holds the one SQL argument.
//
// Returns driver.Value which is the slug, or nil for NULL.
// Returns error when the argument is not text.
func callSlugify(_ *sqlitedriver.FunctionContext, arguments []driver.Value) (driver.Value, error) {
switch value := arguments[0].(type) {
case nil:
return nil, nil
case string:
return slugify(value), nil
default:
return nil, fmt.Errorf("slugify takes text, not %T", value)
}
}
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(), project, "db/migrations")
if err != nil {
return err
}
if _, err := migrator.Up(ctx); err != nil {
return fmt.Errorf("migrating: %w", err)
}
return run(ctx, database, project, os.Stdout)
}
MustRegisterDeterministicScalarFunction registers slugify for every new connection, taking one argument. Deterministic tells SQLite that the same input always gives the same output. callSlugify adapts the Go function to the driver's calling convention: NULL in gives NULL out, which is exactly what the declaration promised, and anything but text is an error.
The program
run vets the queries twice, first with no functions declared and then with slugify, and prints what Seagull made of the slug each time. Then it calls the generated methods, whose SQL runs slugify inside SQLite:
example.go
package main
import (
"context"
"database/sql"
"fmt"
"io"
"io/fs"
"strings"
"unicode"
"seagull.sh/seagull"
"seagull.sh/seagull/examples/embedding/048_custom_functions/internal/store"
"seagull.sh/seagull/sdk/dialect/dialect_engine_sqlite"
)
// slugifyFunction declares slugify to Seagull, as custom_functions in sgl.yaml does for
// sgl: one text argument, a text result, and NULL in gives NULL out.
var slugifyFunction = seagull.CustomFunction{
Name: "slugify",
ReturnType: "text",
Nullable: "null_on_null",
Arguments: []string{"text"},
MinArguments: 0,
IsAggregate: false,
IsVariadic: false,
}
// slugify turns a title into a URL slug: lower-case letters and digits, with one hyphen
// between words. SQLite calls it through the function main.go registers with the driver.
//
// Takes title (string) which is the text to convert.
//
// Returns string which is the slug.
func slugify(title string) string {
words := strings.FieldsFunc(strings.ToLower(title), func(r rune) bool {
return !unicode.IsLetter(r) && !unicode.IsDigit(r)
})
return strings.Join(words, "-")
}
// run vets the queries without and with the declaration of slugify, then calls the
// generated methods, whose SQL runs slugify inside SQLite.
//
// Takes database (*sql.DB) which is a migrated database with slugify registered.
// Takes project (fs.FS) which holds db/migrations and db/queries.
// Takes output (io.Writer) which receives the results.
//
// Returns error when vetting or a query fails.
func run(ctx context.Context, database *sql.DB, project fs.FS, output io.Writer) error {
if err := vet(ctx, project, output); err != nil {
return err
}
if err := vet(ctx, project, output, slugifyFunction); err != nil {
return err
}
queries := store.New(database)
for _, title := range []string{"Gulls of the North Sea", "Why Chips? A Field Guide"} {
if _, err := queries.CreateArticle(ctx, title); err != nil {
return fmt.Errorf("creating %q: %w", title, err)
}
}
articles, err := queries.ListArticleSlugs(ctx)
if err != nil {
return fmt.Errorf("listing slugs: %w", err)
}
for _, article := range articles {
fmt.Fprintf(output, "%d\t%-28s %s\n", article.ID, article.Title, article.Slug)
}
found, err := queries.FindArticleBySlug(ctx, "why-chips-a-field-guide")
if err != nil {
return fmt.Errorf("finding by slug: %w", err)
}
fmt.Fprintf(output, "why-chips-a-field-guide is article %d\n", found.ID)
return nil
}
// vet analyses the queries with the given functions declared, and prints the diagnostics
// and the type Seagull inferred for the slug column.
//
// Takes project (fs.FS) which holds db/migrations and db/queries.
// Takes output (io.Writer) which receives the report.
// Takes functions (...seagull.CustomFunction) which are the functions to declare.
//
// Returns error when the queries cannot be analysed.
func vet(ctx context.Context, project fs.FS, output io.Writer, functions ...seagull.CustomFunction) error {
generator, err := seagull.New(dialect_engine_sqlite.New(), seagull.WithCustomFunctions(functions...))
if err != nil {
return err
}
report, err := generator.Vet(ctx, seagull.Input{
Migrations: project,
Queries: nil,
Package: "",
MigrationDir: "db/migrations",
QueryDir: "db/queries",
})
if err != nil {
return err
}
fmt.Fprintf(output, "custom functions declared: %d\n", len(functions))
for _, diagnostic := range report.Diagnostics {
fmt.Fprintf(output, " %s:%d: %s[%s]: %s\n", diagnostic.Filename, diagnostic.Line,
diagnostic.Severity, diagnostic.Code, diagnostic.Message)
}
for _, query := range report.Queries {
for index := range query.OutputColumns {
column := &query.OutputColumns[index]
if column.Name != "slug" {
continue
}
sqlType := column.SQLType.EngineName
if sqlType == "" {
sqlType = "unknown"
}
fmt.Fprintf(output, " %s.slug: type %s, nullable %t\n", query.Name, sqlType, column.Nullable)
}
}
return nil
}
What it prints
custom functions declared: 0
articles.sql:5: warning[Q005]: unknown function "slugify"
articles.sql:9: warning[Q005]: unknown function "slugify"
ListArticleSlugs.slug: type unknown, nullable true
custom functions declared: 1
ListArticleSlugs.slug: type text, nullable false
1 Gulls of the North Sea gulls-of-the-north-sea
2 Why Chips? A Field Guide why-chips-a-field-guide
why-chips-a-field-guide is article 2
Undeclared, the slug's type is unknown and it is nullable, and vet reports the unknown function. The warning carries code Q004, the code for a type that cannot be inferred, with the unknown-function code Q005 at the start of its message. Declared, the slug is a non-null text. The two articles then come back with slugs SQLite computed by calling the Go function, and FindArticleBySlug finds the second by its slug.
Try it
Change nullable to called_on_null in sgl.yaml and run sgl generate: Seagull can no longer promise a value, so Slug becomes a *string.
Run it locally
From the directory you cloned the Seagull repository into:
cd seagull/examples/embedding && go run ./048_custom_functions
Its test runs it too, and checks what it prints against output.txt:
cd seagull/examples/embedding && go test ./048_custom_functions
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: articles
dialect: sqlite
migrations: db/migrations
queries: db/queries
custom_functions:
- name: slugify
args: [text]
returns: text
nullable: null_on_null
go:
package: store
out: internal/store
db/migrations/0001_init.up.sql
CREATE TABLE articles (
id INTEGER PRIMARY KEY,
title TEXT NOT NULL
);
db/migrations/0001_init.down.sql
DROP TABLE articles;
db/queries/articles.sql
-- sgl.query(CreateArticle, one)
INSERT INTO articles (title) VALUES (?) RETURNING id, title;
-- sgl.query(ListArticleSlugs, many)
SELECT id, title, slugify(title) AS slug FROM articles ORDER BY id;
-- sgl.query(FindArticleBySlug, one)
-- :slug as sgl.param(slug, type: text)
SELECT id, title FROM articles WHERE slugify(title) = :slug;
example.go
package main
import (
"context"
"database/sql"
"fmt"
"io"
"io/fs"
"strings"
"unicode"
"seagull.sh/seagull"
"seagull.sh/seagull/examples/embedding/048_custom_functions/internal/store"
"seagull.sh/seagull/sdk/dialect/dialect_engine_sqlite"
)
// slugifyFunction declares slugify to Seagull, as custom_functions in sgl.yaml does for
// sgl: one text argument, a text result, and NULL in gives NULL out.
var slugifyFunction = seagull.CustomFunction{
Name: "slugify",
ReturnType: "text",
Nullable: "null_on_null",
Arguments: []string{"text"},
MinArguments: 0,
IsAggregate: false,
IsVariadic: false,
}
// slugify turns a title into a URL slug: lower-case letters and digits, with one hyphen
// between words. SQLite calls it through the function main.go registers with the driver.
//
// Takes title (string) which is the text to convert.
//
// Returns string which is the slug.
func slugify(title string) string {
words := strings.FieldsFunc(strings.ToLower(title), func(r rune) bool {
return !unicode.IsLetter(r) && !unicode.IsDigit(r)
})
return strings.Join(words, "-")
}
// run vets the queries without and with the declaration of slugify, then calls the
// generated methods, whose SQL runs slugify inside SQLite.
//
// Takes database (*sql.DB) which is a migrated database with slugify registered.
// Takes project (fs.FS) which holds db/migrations and db/queries.
// Takes output (io.Writer) which receives the results.
//
// Returns error when vetting or a query fails.
func run(ctx context.Context, database *sql.DB, project fs.FS, output io.Writer) error {
if err := vet(ctx, project, output); err != nil {
return err
}
if err := vet(ctx, project, output, slugifyFunction); err != nil {
return err
}
queries := store.New(database)
for _, title := range []string{"Gulls of the North Sea", "Why Chips? A Field Guide"} {
if _, err := queries.CreateArticle(ctx, title); err != nil {
return fmt.Errorf("creating %q: %w", title, err)
}
}
articles, err := queries.ListArticleSlugs(ctx)
if err != nil {
return fmt.Errorf("listing slugs: %w", err)
}
for _, article := range articles {
fmt.Fprintf(output, "%d\t%-28s %s\n", article.ID, article.Title, article.Slug)
}
found, err := queries.FindArticleBySlug(ctx, "why-chips-a-field-guide")
if err != nil {
return fmt.Errorf("finding by slug: %w", err)
}
fmt.Fprintf(output, "why-chips-a-field-guide is article %d\n", found.ID)
return nil
}
// vet analyses the queries with the given functions declared, and prints the diagnostics
// and the type Seagull inferred for the slug column.
//
// Takes project (fs.FS) which holds db/migrations and db/queries.
// Takes output (io.Writer) which receives the report.
// Takes functions (...seagull.CustomFunction) which are the functions to declare.
//
// Returns error when the queries cannot be analysed.
func vet(ctx context.Context, project fs.FS, output io.Writer, functions ...seagull.CustomFunction) error {
generator, err := seagull.New(dialect_engine_sqlite.New(), seagull.WithCustomFunctions(functions...))
if err != nil {
return err
}
report, err := generator.Vet(ctx, seagull.Input{
Migrations: project,
Queries: nil,
Package: "",
MigrationDir: "db/migrations",
QueryDir: "db/queries",
})
if err != nil {
return err
}
fmt.Fprintf(output, "custom functions declared: %d\n", len(functions))
for _, diagnostic := range report.Diagnostics {
fmt.Fprintf(output, " %s:%d: %s[%s]: %s\n", diagnostic.Filename, diagnostic.Line,
diagnostic.Severity, diagnostic.Code, diagnostic.Message)
}
for _, query := range report.Queries {
for index := range query.OutputColumns {
column := &query.OutputColumns[index]
if column.Name != "slug" {
continue
}
sqlType := column.SQLType.EngineName
if sqlType == "" {
sqlType = "unknown"
}
fmt.Fprintf(output, " %s.slug: type %s, nullable %t\n", query.Name, sqlType, column.Nullable)
}
}
return nil
}
main.go
// Command 048_custom_functions registers slugify, a Go function, with the SQLite driver,
// then runs the example in example.go: Seagull types the queries that call slugify once
// it is declared, and the generated methods run it inside SQLite.
//
// Run it with `go run .`, or `go run . -db articles.db` to keep the database.
package main
import (
"context"
"database/sql"
"database/sql/driver"
"embed"
"flag"
"fmt"
"log"
"os"
"path/filepath"
sqlitedriver "modernc.org/sqlite"
"seagull.sh/seagull/sdk/migrate"
"seagull.sh/seagull/sdk/migrate/migrate_backend_sqlite"
)
// project holds the migration and query files, compiled into the binary.
//
//go:embed db
var project embed.FS
// init registers slugify with the driver. Every connection opened afterwards can call it,
// so it runs before main and before any test opens a database.
func init() {
sqlitedriver.MustRegisterDeterministicScalarFunction("slugify", 1, callSlugify)
}
// callSlugify adapts slugify to the driver's calling convention: NULL gives NULL, and
// text gives its slug.
//
// Takes arguments ([]driver.Value) which holds the one SQL argument.
//
// Returns driver.Value which is the slug, or nil for NULL.
// Returns error when the argument is not text.
func callSlugify(_ *sqlitedriver.FunctionContext, arguments []driver.Value) (driver.Value, error) {
switch value := arguments[0].(type) {
case nil:
return nil, nil
case string:
return slugify(value), nil
default:
return nil, fmt.Errorf("slugify takes text, not %T", value)
}
}
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(), project, "db/migrations")
if err != nil {
return err
}
if _, err := migrator.Up(ctx); err != nil {
return fmt.Errorf("migrating: %w", err)
}
return run(ctx, database, project, os.Stdout)
}
internal/store/articles.sql.go
// Code generated by sgl. DO NOT EDIT.
package store
import "context"
const createArticleSQL = `INSERT INTO articles (title) VALUES (?1) RETURNING id, title;`
type CreateArticleRow struct {
ID int64 `json:"id"`
Title string `json:"title"`
}
func (queries *Queries) CreateArticle(ctx context.Context, title string) (CreateArticleRow, error) {
var row CreateArticleRow
err := queries.writer.QueryRowContext(ctx, createArticleSQL, title).Scan(&row.ID, &row.Title)
if err != nil {
return CreateArticleRow{}, err
}
return row, nil
}
const findArticleBySlugSQL = `SELECT id, title FROM articles WHERE slugify(title) = ?1;`
type FindArticleBySlugRow struct {
ID int64 `json:"id"`
Title string `json:"title"`
}
func (queries *Queries) FindArticleBySlug(ctx context.Context, slug string) (FindArticleBySlugRow, error) {
var row FindArticleBySlugRow
err := queries.reader.QueryRowContext(ctx, findArticleBySlugSQL, slug).Scan(&row.ID, &row.Title)
if err != nil {
return FindArticleBySlugRow{}, err
}
return row, nil
}
const listArticleSlugsSQL = `SELECT id, title, slugify(title) AS slug FROM articles ORDER BY id;`
type ListArticleSlugsRow struct {
ID int64 `json:"id"`
Title string `json:"title"`
Slug string `json:"slug"`
}
func (queries *Queries) ListArticleSlugs(ctx context.Context) ([]ListArticleSlugsRow, error) {
rows, err := queries.reader.QueryContext(ctx, listArticleSlugsSQL)
if err != nil {
return nil, err
}
defer rows.Close()
var results []ListArticleSlugsRow
if rows.Next() {
var row ListArticleSlugsRow
scanArgs := []any{&row.ID, &row.Title, &row.Slug}
for more := true; more; more = rows.Next() {
row = ListArticleSlugsRow{}
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
}
internal/store/models.go
// Code generated by sgl. DO NOT EDIT.
package store
type Articles struct {
ID int64 `json:"id"`
Title string `json:"title"`
}
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()
}
output.txt
custom functions declared: 0
articles.sql:5: warning[Q005]: unknown function "slugify"
articles.sql:9: warning[Q005]: unknown function "slugify"
ListArticleSlugs.slug: type unknown, nullable true
custom functions declared: 1
ListArticleSlugs.slug: type text, nullable false
1 Gulls of the North Sea gulls-of-the-north-sea
2 Why Chips? A Field Guide why-chips-a-field-guide
why-chips-a-field-guide is article 2