Schema from a live database
On this page
On SQLite:
- schema_source_sqlite reads PRAGMA table_xinfo and index lists; the dialect normalises the declared types, so INTEGER PRIMARY KEY is bigint, as the rowid, and other INTEGER columns integer, all mapping to int64
- schema_from: file:club.db replaces migrations in sgl.yaml; the path is resolved against the directory that holds sgl.yaml, and sgl refuses a file that does not exist
- Input.Migrations is nil, so the queries come from Input.Queries
Seagull usually learns your schema by replaying your migration files. Not every project has them: the database may predate any migrations, or be built by hand, or by another tool's migration system. For those, a schema source reads the schema from the live database instead, and the queries are checked and typed against what is really there. This example generates code for a bird-watching club whose SQLite database has no migrations at all.
The database
The club's tables exist only in the database. legacy/schema.sql records how they were made:
legacy/schema.sql
CREATE TABLE members (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
email TEXT UNIQUE,
joined_on TEXT NOT NULL DEFAULT (date('now'))
);
CREATE TABLE sightings (
id INTEGER PRIMARY KEY,
member_id INTEGER NOT NULL REFERENCES members (id),
species TEXT NOT NULL,
count INTEGER NOT NULL DEFAULT 1,
seen_at TEXT NOT NULL
);
main.go opens the database you name with -db, or builds a temporary one from that file when you name none, and passes the open *sql.DB to run.
The queries
Two queries, one joining both tables and one aggregating:
db/queries/club.sql
-- sgl.query(ListSightings, many)
SELECT sightings.species, sightings.count, members.name AS member, members.email
FROM sightings
JOIN members ON members.id = sightings.member_id
ORDER BY sightings.seen_at;
-- sgl.query(BirdsBySpecies, many)
SELECT species, sum(count) AS birds FROM sightings GROUP BY species ORDER BY species;
The program
example.go
package main
import (
"context"
"database/sql"
"fmt"
"io"
"io/fs"
"maps"
"slices"
"strings"
"seagull.sh/seagull"
"seagull.sh/seagull/sdk/dialect/dialect_engine_sqlite"
"seagull.sh/seagull/sdk/schema/schema_source_sqlite"
)
// run generates the store package against the schema of a live SQLite database rather
// than migration files: the schema source reads the tables through PRAGMA, and the
// queries are analysed against what it finds.
//
// Takes database (*sql.DB) which is the live database.
// Takes queries (fs.FS) which holds db/queries.
// Takes output (io.Writer) which receives the schema and the generated code.
//
// Returns error when the database cannot be read or generation fails.
func run(ctx context.Context, database *sql.DB, queries fs.FS, output io.Writer) error {
dialect := dialect_engine_sqlite.New()
generator, err := seagull.New(dialect, seagull.WithSchemaSource(schema_source_sqlite.New(database, dialect)))
if err != nil {
return err
}
input := seagull.Input{
Migrations: nil,
Queries: queries,
Package: "store",
MigrationDir: "",
QueryDir: "db/queries",
}
catalogue, _, err := generator.Catalogue(ctx, input)
if err != nil {
return fmt.Errorf("reading the schema: %w", err)
}
schema := catalogue.Schemas[catalogue.DefaultSchema]
for _, name := range slices.Sorted(maps.Keys(schema.Tables)) {
var columns []string
table := schema.Tables[name]
for index := range table.Columns {
columns = append(columns, table.Columns[index].Name+" "+table.Columns[index].SQLType.EngineName)
}
fmt.Fprintf(output, "table %s: %s\n", name, strings.Join(columns, ", "))
}
result, err := generator.Generate(ctx, input)
if err != nil {
return err
}
if err := result.Err(); err != nil {
return err
}
for _, file := range result.Files {
if file.Name == "models.go" || file.Name == "club.sql.go" {
fmt.Fprintf(output, "\n%s:\n%s", file.Name, declarations(string(file.Content)))
}
}
return nil
}
// declarations keeps the type declarations and method signatures of a generated file,
// which say what the schema source taught the generator.
//
// Takes source (string) which is the generated Go source.
//
// Returns string which holds the struct types and the func lines.
func declarations(source string) string {
var kept strings.Builder
inType := false
for line := range strings.Lines(source) {
switch {
case strings.HasPrefix(line, "type ") && strings.HasSuffix(line, "{\n"):
inType = true
kept.WriteString(line)
case inType:
kept.WriteString(line)
inType = line != "}\n"
case strings.HasPrefix(line, "func (queries"):
kept.WriteString(strings.TrimSuffix(line, " {\n") + "\n")
}
}
return kept.String()
}
The schema source. schema_source_sqlite.New(database, dialect), from seagull.sh/seagull/sdk/schema/schema_source_sqlite, reads the tables, columns and indexes through SQLite's PRAGMA statements. It takes the dialect as well as the connection because the dialect normalises the declared types, so the columns come out with the same types a migration would have given them. seagull.WithSchemaSource hands the source to the generator, in place of replaying migrations.
The input. Migrations is nil and MigrationDir empty, since there are none. The queries come from Queries instead, which with migrations could have been left nil.
Catalogue and Generate both build the catalogue through the source. The program prints the tables it found, then generates and prints only the type declarations and method signatures of two generated files, which is where what the database taught the generator shows.
Any type with a BuildCatalogue(ctx) method can be a schema source, and sdk/schema/schema_source_postgres does the same for the PostgreSQL family. The generator may change the catalogue a source returns, so a source that caches one must return a fresh copy each time.
What it prints
table members: id bigint, name text, email text, joined_on text
table sightings: id bigint, member_id integer, species text, count integer, seen_at text
models.go:
type Members struct {
ID int64 `json:"id"`
Name string `json:"name"`
Email *string `json:"email"`
JoinedOn string `json:"joined_on"`
}
type Sightings struct {
ID int64 `json:"id"`
MemberID int64 `json:"member_id"`
Species string `json:"species"`
Count int64 `json:"count"`
SeenAt string `json:"seen_at"`
}
club.sql.go:
type BirdsBySpeciesRow struct {
Species string `json:"species"`
Birds *int64 `json:"birds"`
}
func (queries *Queries) BirdsBySpecies(ctx context.Context) ([]BirdsBySpeciesRow, error)
type ListSightingsRow struct {
Species string `json:"species"`
Count int64 `json:"count"`
Member string `json:"member"`
Email *string `json:"email"`
}
func (queries *Queries) ListSightings(ctx context.Context) ([]ListSightingsRow, error)
The tables and their types come from the database. models.go has a struct for each table, as it would with migrations. email is UNIQUE but not NOT NULL, so it is a *string there and in ListSightingsRow. In BirdsBySpecies, sum(count) is an *int64: SQL's SUM gives NULL when it adds up no rows, so Seagull treats it as nullable.
With sgl
sgl.yaml says the same thing to sgl, with schema_from in place of migrations:
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: club
dialect: sqlite
schema_from: file:club.db
queries: db/queries
go:
package: store
out: internal/store
The DSN's path is resolved from the directory sgl runs in, and sgl opens the database with its own driver, so every sgl generate and sgl vet now needs the database to be there. That is the trade: no migration files to keep, but a build that depends on a database. Where you can, keep the schema in migrations and use schema diff to check a live database against them.
Try it
Build club.db beside sgl.yaml with sqlite3 club.db < legacy/schema.sql, then run sgl generate. Then add a column to the database, sqlite3 club.db "ALTER TABLE members ADD COLUMN phone TEXT", and generate again: Members gains a Phone *string field, with no migration written.
Run it locally
From the directory you cloned the Seagull repository into:
cd seagull/examples/embedding && go run ./050_schema_from_live_database
Its test runs it too, and checks what it prints against output.txt:
cd seagull/examples/embedding && go test ./050_schema_from_live_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: club
dialect: sqlite
schema_from: file:club.db
queries: db/queries
go:
package: store
out: internal/store
db/queries/club.sql
-- sgl.query(ListSightings, many)
SELECT sightings.species, sightings.count, members.name AS member, members.email
FROM sightings
JOIN members ON members.id = sightings.member_id
ORDER BY sightings.seen_at;
-- sgl.query(BirdsBySpecies, many)
SELECT species, sum(count) AS birds FROM sightings GROUP BY species ORDER BY species;
example.go
package main
import (
"context"
"database/sql"
"fmt"
"io"
"io/fs"
"maps"
"slices"
"strings"
"seagull.sh/seagull"
"seagull.sh/seagull/sdk/dialect/dialect_engine_sqlite"
"seagull.sh/seagull/sdk/schema/schema_source_sqlite"
)
// run generates the store package against the schema of a live SQLite database rather
// than migration files: the schema source reads the tables through PRAGMA, and the
// queries are analysed against what it finds.
//
// Takes database (*sql.DB) which is the live database.
// Takes queries (fs.FS) which holds db/queries.
// Takes output (io.Writer) which receives the schema and the generated code.
//
// Returns error when the database cannot be read or generation fails.
func run(ctx context.Context, database *sql.DB, queries fs.FS, output io.Writer) error {
dialect := dialect_engine_sqlite.New()
generator, err := seagull.New(dialect, seagull.WithSchemaSource(schema_source_sqlite.New(database, dialect)))
if err != nil {
return err
}
input := seagull.Input{
Migrations: nil,
Queries: queries,
Package: "store",
MigrationDir: "",
QueryDir: "db/queries",
}
catalogue, _, err := generator.Catalogue(ctx, input)
if err != nil {
return fmt.Errorf("reading the schema: %w", err)
}
schema := catalogue.Schemas[catalogue.DefaultSchema]
for _, name := range slices.Sorted(maps.Keys(schema.Tables)) {
var columns []string
table := schema.Tables[name]
for index := range table.Columns {
columns = append(columns, table.Columns[index].Name+" "+table.Columns[index].SQLType.EngineName)
}
fmt.Fprintf(output, "table %s: %s\n", name, strings.Join(columns, ", "))
}
result, err := generator.Generate(ctx, input)
if err != nil {
return err
}
if err := result.Err(); err != nil {
return err
}
for _, file := range result.Files {
if file.Name == "models.go" || file.Name == "club.sql.go" {
fmt.Fprintf(output, "\n%s:\n%s", file.Name, declarations(string(file.Content)))
}
}
return nil
}
// declarations keeps the type declarations and method signatures of a generated file,
// which say what the schema source taught the generator.
//
// Takes source (string) which is the generated Go source.
//
// Returns string which holds the struct types and the func lines.
func declarations(source string) string {
var kept strings.Builder
inType := false
for line := range strings.Lines(source) {
switch {
case strings.HasPrefix(line, "type ") && strings.HasSuffix(line, "{\n"):
inType = true
kept.WriteString(line)
case inType:
kept.WriteString(line)
inType = line != "}\n"
case strings.HasPrefix(line, "func (queries"):
kept.WriteString(strings.TrimSuffix(line, " {\n") + "\n")
}
}
return kept.String()
}
legacy/schema.sql
CREATE TABLE members (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
email TEXT UNIQUE,
joined_on TEXT NOT NULL DEFAULT (date('now'))
);
CREATE TABLE sightings (
id INTEGER PRIMARY KEY,
member_id INTEGER NOT NULL REFERENCES members (id),
species TEXT NOT NULL,
count INTEGER NOT NULL DEFAULT 1,
seen_at TEXT NOT NULL
);
main.go
// Command 050_schema_from_live_database generates this example's store package against a
// live SQLite database instead of migration files, as a project with an existing database
// and no migrations would.
//
// Run it with `go run . -db club.db` against a database of your own, or with `go run .`
// to build one from legacy/schema.sql in a temporary directory first.
package main
import (
"context"
"database/sql"
_ "embed"
"flag"
"log"
"os"
"path/filepath"
_ "modernc.org/sqlite"
)
// legacySchema is the schema of the club's existing database, which predates any
// migration files.
//
//go:embed legacy/schema.sql
var legacySchema string
func main() {
path := flag.String("db", "", "SQLite database to read the schema from; a new one from legacy/schema.sql when empty")
flag.Parse()
if err := execute(context.Background(), *path); err != nil {
log.Fatal(err)
}
}
// execute opens the database at path, or builds a temporary one from the legacy schema
// 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 opened or the example fails.
func execute(ctx context.Context, path string) error {
create := path == ""
if create {
directory, err := os.MkdirTemp("", "seagull-example-")
if err != nil {
return err
}
defer func() { _ = os.RemoveAll(directory) }()
path = filepath.Join(directory, "club.db")
}
database, err := sql.Open("sqlite", "file:"+path)
if err != nil {
return err
}
defer func() { _ = database.Close() }()
if create {
if _, err := database.ExecContext(ctx, legacySchema); err != nil {
return err
}
}
return run(ctx, database, os.DirFS("."), os.Stdout)
}
output.txt
table members: id bigint, name text, email text, joined_on text
table sightings: id bigint, member_id integer, species text, count integer, seen_at text
models.go:
type Members struct {
ID int64 `json:"id"`
Name string `json:"name"`
Email *string `json:"email"`
JoinedOn string `json:"joined_on"`
}
type Sightings struct {
ID int64 `json:"id"`
MemberID int64 `json:"member_id"`
Species string `json:"species"`
Count int64 `json:"count"`
SeenAt string `json:"seen_at"`
}
club.sql.go:
type BirdsBySpeciesRow struct {
Species string `json:"species"`
Birds *int64 `json:"birds"`
}
func (queries *Queries) BirdsBySpecies(ctx context.Context) ([]BirdsBySpeciesRow, error)
type ListSightingsRow struct {
Species string `json:"species"`
Count int64 `json:"count"`
Member string `json:"member"`
Email *string `json:"email"`
}
func (queries *Queries) ListSightings(ctx context.Context) ([]ListSightingsRow, error)