024: PostgreSQL

A Piko project using PostgreSQL as its database, with type-safe queries generated from .sql files and a read replica configured through the pgx database/sql driver.

What this demonstrates

  • The WithDatabase bootstrap option with the PostgreSQL driver.
  • Analytics queries using CTEs and window functions, with read-replica configuration via db.Replica.
  • Running migrations (and seeds) inline at startup in main(), via db.NewMigrationService / migrator.Up, before piko.New boots the app.

Project structure

src/
  cmd/
    main/main.go        Bootstrap with WithDatabase("analytics", ...).
    generator/main.go   Per-project generator entry point.
  db/
    queries/*.sql       Annotated SQL files for the querier generator.
    migrations/*.sql    Schema migrations.
    seeds/              Seed data.
    generated/          Type-safe querier output.
  pages/
    index.pk            Dashboard.
    products.pk         Products listing.
    orders.pk           Orders listing.
  actions/
    products/create.go
    orders/create.go

How to run this example

From the Piko repository root (Docker must be running, as the example starts a PostgreSQL container via testcontainers-go):

cd examples/scenarios/024_database_postgres/src/
go mod tidy
go run ./cmd/generator/main.go all
air

See also