PostgreSQL catalogue provider

PostgreSQL schema-introspection adapter that builds a db.CatalogueProviderPort by reading information_schema and pg_catalog from a live database.

Overview

Catalogues are build-time artefacts. The Piko querier code generator needs the shape of your schema before it can produce typed Go query code. That shape covers tables, views, columns, indexes, enums, composite types, functions, and extensions. By default the generator builds the catalogue by replaying your migration files through the engine parser. This adapter offers an alternative path. It introspects a live PostgreSQL database directly and produces the same Catalogue structure from information_schema and pg_catalog queries.

The trade-off is direct. You introspect the live database, or you replay the checked-in migration history. Reach for introspection when migrations live elsewhere, for example Liquibase, Flyway, Atlas, or hand-written SQL applied by another team. It also fits when you want the generator to see the database as it stands right now. It fits a third case too, when extensions, custom types, and views are central to a schema the parser-replay path does not capture precisely. Reach for the default migration-replay path when migrations are the single source of truth, because that path makes generation reproducible from a checked-in *.up.sql history without a database connection.

This is not a runtime service. The introspection runs once during build-time code generation. Commit the generated .sql.go files, or regenerate them on every CI run. At runtime the database is a normal *sql.DB. There is no piko.With* registration. You wire the provider into a querier-service composition through the CatalogueProvider port, and no other generation step changes.

Requirements

  • A PostgreSQL connection that can read information_schema and pg_catalog. The default permissions on any role with CONNECT and table SELECT cover this.
  • A TypeNormaliser. Any db.EnginePort satisfies this interface, so pass the same engine you run at runtime, for example db_engine_postgres.NewPostgresEngine(). Introspected column types then normalise through the identical dialect logic the rest of the querier uses. There is no separate type-mapping step.

Configuration

import (
    "database/sql"
    "os"

    _ "github.com/jackc/pgx/v5/stdlib"

    "piko.sh/piko/wdk/db/db_catalogue_postgres"
    "piko.sh/piko/wdk/db/db_engine_postgres"
)

connection, err := sql.Open("pgx", os.Getenv("DATABASE_URL"))
if err != nil {
    return err
}
defer connection.Close()

engine := db_engine_postgres.NewPostgresEngine()
provider := db_catalogue_postgres.NewPgIntrospectionProvider(connection, engine)

The provider satisfies db.CatalogueProviderPort, the exact interface the default migration-replay provider returns. Its one public method is BuildCatalogue(ctx). The second return value is a []querier_dto.SourceError slice that this provider always returns as nil, so only the catalogue and the error need handling. Every per-table and per-view loop checks ctx.Err(), so a slow or hung introspection respects the generation timeout you set on the context.

catalogue, _, err := provider.BuildCatalogue(ctx)
if err != nil {
    return err
}

Bootstrap

The package does not register through piko.With*. The standard build command never invokes it. That path constructs the querier service with only an engine, emitter, and file reader, so it always uses migration-replay. To use introspection you call your own code-generation entry point, typically a cmd/generate/ binary or a go:generate script, and set the provider as the CatalogueProvider port.

The provider is a drop-in swap. It satisfies the same db.CatalogueProviderPort the default provider returns, so only the one CatalogueProvider field changes. db.NewQuerierService still requires a non-nil FileReader even when a catalogue provider overrides catalogue building, because the query SQL files are still read for analysis. Omitting it returns ErrMissingFileReaderPort at construction.

import (
    "piko.sh/piko/wdk/db"
    "piko.sh/piko/wdk/db/db_catalogue_postgres"
    "piko.sh/piko/wdk/db/db_emitter_pgx"
    "piko.sh/piko/wdk/db/db_engine_postgres"
)

engine := db_engine_postgres.NewPostgresEngine()
provider := db_catalogue_postgres.NewPgIntrospectionProvider(connection, engine)

service, err := db.NewQuerierService(db.QuerierPorts{
    Engine:            engine,
    Emitter:           db_emitter_pgx.NewPgxEmitter(),
    FileReader:        db.NewFSFileReader(queryFS),
    CatalogueProvider: provider,
})
if err != nil {
    return err
}

result, err := service.GenerateDatabase(ctx, "generated", &db.DatabaseConfig{
    QueryDirectory: "queries",
})
if err != nil {
    return err
}
// Write each result.Files entry to disk under your output directory.

To union the introspected schema with a migration-replay catalogue in one run, wrap both in db.NewCompositeCatalogueProvider and pass the result as CatalogueProvider. This is the pattern for tracking ad-hoc database changes alongside checked-in migrations.

See also

Sibling catalogues:

Postgres pieces:

  • PostgreSQL engine, runtime engine and dialect. Pair the same engine instance with this catalogue.
  • PGX Emitter, emit pgx-native query code for use with the introspected schema.

Framework docs:

External: