PostgreSQL Adapter for webtyp/storage + ddl

This repository implements the storage.Conn (from webtyp.com/storage) and ddl.Compiler (from webtyp.com/ddl) interfaces for PostgreSQL.

Why This Exists

This package allows using a unified database model across different layers without writing custom SQL statements for Postgres.

Without	With
Hand-written PostgreSQL per migration	ddl.Sync/CopyColumn/DropColumn and storage queries over one postgres.Open connection

See ARCHITECTURE and POSTGRES_SETUP for more details.

Before You Start

You need a PostgreSQL server and its DSN (Data Source Name). For instance: postgres://user:password@localhost:5432/dbname?sslmode=disable

Step-by-Step Guide

package main

import (
	"log"

	"webtyp.com/ddl"
	"webtyp.com/model"
	"webtyp.com/postgres"
)

// holiday is a minimal model; real ones are generated by ormc.
type holiday struct{ ID, Name string }

func (h *holiday) ModelName() string { return "holiday" }
func (h *holiday) Schema() []model.Field {
	return []model.Field{
		{Name: "id", Type: model.Text(), DB: &model.FieldDB{PK: true}},
		{Name: "name", Type: model.Text()},
	}
}
func (h *holiday) Pointers() []any                { return []any{&h.ID, &h.Name} }
func (h *holiday) IsNil() bool                    { return h == nil }
func (h *holiday) EncodeFields(model.FieldWriter) {}
func (h *holiday) DecodeFields(model.FieldReader) {}

func main() {
	conn, err := postgres.Open("postgres://postgres:postgres@localhost:5432/postgres?sslmode=disable")
	if err != nil {
		log.Fatal(err)
	}
	defer conn.Close()
	db := ddl.New(conn, conn.(ddl.Compiler))

	// Create the table, add new columns, keep a removed column that still holds data.
	if err := db.Sync(&holiday{}); err != nil {
		log.Fatal(err)
	}
	// A person decided: copy the old column into its replacement, then drop it.
	if err := db.CopyColumn(ddl.Removed{Table: "holiday", Column: "label", MovedTo: "name"}); err != nil {
		log.Fatal(err)
	}
	if err := db.DropColumn("holiday", "label"); err != nil {
		log.Fatal(err)
	}
}

Reading a backup

	snap, err := postgres.Backups(dsn).OpenBackup("migrate/pastdb/1793656810107289500-db.backup")
	if err != nil { return err }
	defer snap.Close()
	rows, err := snap.ReadTable("staff")

Needs pg_restore in PATH and a URL DSN with permission to CREATE DATABASE. The PostgreSQL client tools must not be newer than the server (ErrPgRestoreNewer says which to install).

Documentation Map

File	Answers
docs/ARCHITECTURE.md	How the package components are structured
docs/POSTGRES_SETUP.md	Postgres Setup & Troubleshooting
docs/PLAN.md	The delivery plan of this repository