sqlc-demo/main.go

package main

import (
	"context"
	"encoding/json"
	"errors"
	"flag"
	"fmt"
	"log"
	"os"
	"sqlc-demo/internal"
	"sqlc-demo/migrations"
	"time"

	"github.com/google/uuid"
	"github.com/jackc/pgx/v5"
	"github.com/jackc/pgx/v5/pgtype"
	"github.com/jackc/pgx/v5/pgxpool"
	_ "github.com/jackc/pgx/v5/stdlib"
	"github.com/pressly/goose/v3"
)

const envPostgresConnString = "POSTGRES_CONNECTION_STRING"

func main() {
	var demoName string
	flag.StringVar(&demoName, "demo", "listVersions", "one of: listVersions, listOpenServers, createVersionAndServers, countServersByVersionCode, deleteServer")

	flag.Parse()

	if err := runMigrations(); err != nil {
		log.Fatal(err)
	}

	pool, err := newPgxPool()
	if err != nil {
		log.Fatal(err)
	}
	defer pool.Close()

	queries := internal.New(pool)

	if err = (demo{queries: queries, pool: pool}).run(demoName); err != nil {
		log.Fatal(err)
	}
}

func runMigrations() error {
	goose.SetLogger(goose.NopLogger())
	goose.SetBaseFS(migrations.Migrations)
	_ = goose.SetDialect("postgres")

	db, err := goose.OpenDBWithDriver("pgx", os.Getenv(envPostgresConnString))
	if err != nil {
		return err
	}
	defer func() {
		_ = db.Close()
	}()
	db.SetMaxOpenConns(1)

	ctx, cancel := context.WithTimeout(context.Background(), 10*time.Second)
	defer cancel()

	return goose.UpContext(ctx, db, ".")
}

func newPgxPool() (*pgxpool.Pool, error) {
	pool, err := pgxpool.New(context.Background(), os.Getenv(envPostgresConnString))
	if err != nil {
		return nil, err
	}

	pingCtx, cancel := context.WithTimeout(context.Background(), time.Second)
	defer cancel()
	if err = pool.Ping(pingCtx); err != nil {
		return nil, fmt.Errorf("couldn't ping db: %w", err)
	}

	return pool, nil
}

type demo struct {
	pool    *pgxpool.Pool
	queries *internal.Queries
}

func (d demo) run(name string) error {
	switch name {
	case "listVersions":
		return d.listVersions()
	case "listOpenServers":
		return d.listOpenServers()
	case "createVersionAndServers":
		return d.createVersionAndServers()
	case "countServersByVersionCode":
		return d.countServersByVersionCode()
	case "deleteServer":
		return d.deleteServer()
	default:
		return errors.New("unknown demo")
	}
}

func (d demo) listVersions() error {
	versions, err := d.queries.ListVersions(context.Background())
	if err != nil {
		return fmt.Errorf("couldn't list versions: %w", err)
	}

	for _, v := range versions {
		printVersion(v)
	}

	return nil
}

func (d demo) listOpenServers() error {
	servers, err := d.queries.ListServers(context.Background(), internal.ListServersParams{
		Open: pgtype.Bool{
			Bool:  true,
			Valid: true,
		},
		Special:   pgtype.Bool{},
		Key:       nil,
		Sort:      []string{"KEY_ASC", "OPEN_ASC"},
		PagOffset: 0,
		PagLimit:  10,
	})
	if err != nil {
		return fmt.Errorf("couldn't list versions: %w", err)
	}

	for _, s := range servers {
		printServer(s.Server, s.Version)
	}

	return nil
}

func (d demo) createVersionAndServers() error {
	ctx := context.Background()

	// https://docs.sqlc.dev/en/latest/howto/transactions.html
	tx, err := d.pool.BeginTx(ctx, pgx.TxOptions{})
	if err != nil {
		return fmt.Errorf("couldn't start transaction: %w", err)
	}
	defer func() {
		_ = tx.Rollback(ctx)
	}()

	queries := d.queries.WithTx(tx)

	v, err := queries.CreateVersion(ctx, internal.CreateVersionParams{
		Code:     uuid.NewString()[:6],
		Name:     uuid.NewString(),
		Host:     uuid.NewString(),
		Timezone: time.UTC.String(),
	})
	if err != nil {
		return fmt.Errorf("couldn't create version: %w", err)
	}

	configBytes, _ := json.Marshal(internal.ServerConfig{})
	buildingInfoBytes, _ := json.Marshal(internal.BuildingInfo{})
	unitInfoBytes, _ := json.Marshal(internal.UnitInfo{})

	servers, err := queries.CreateOrUpdateServers(ctx, internal.CreateOrUpdateServersParams{
		Key:          []string{uuid.NewString()[:10], uuid.NewString()[:10]},
		Url:          []string{uuid.NewString(), uuid.NewString()},
		Open:         []bool{true, true},
		VersionCode:  []string{v.Code, v.Code},
		Config:       [][]byte{configBytes, configBytes},
		BuildingInfo: [][]byte{buildingInfoBytes, buildingInfoBytes},
		UnitInfo:     [][]byte{unitInfoBytes, unitInfoBytes},
	})
	if err != nil {
		return fmt.Errorf("couldn't create servers: %w", err)
	}

	printVersion(v)

	for _, s := range servers {
		printServer(s, v)
	}

	return tx.Commit(ctx)
}

func (d demo) countServersByVersionCode() error {
	rows, err := d.queries.CountServersByVersionCode(context.Background())
	if err != nil {
		return err
	}

	for _, r := range rows {
		fmt.Printf("Version: %s\nCount: %d\n", r.VersionCode, r.Cnt)
	}

	return nil
}

func (d demo) deleteServer() error {
	s, err := d.queries.DeleteServer(context.Background())
	if err != nil {
		return fmt.Errorf("couldn't delete server: %w", err)
	}

	v, err := d.queries.GetVersion(context.Background(), s.VersionCode)
	if err != nil {
		return err
	}

	printServer(s, v)

	return nil
}

func printServer(s internal.Server, v internal.Version) {
	fmt.Printf(
		"---%s---\nOpen: %v\nConfig: %v\nBuildingInfo: %v\nUnit info: %v\nCreated at: %s\nVersion: %s (%s)\n",
		s.Key,
		s.Open.Bool,
		s.Config,
		s.BuildingInfo,
		s.UnitInfo,
		s.CreatedAt.Time,
		v.Name,
		v.Code,
	)
}

func printVersion(v internal.Version) {
	fmt.Printf("---%s---\nCode: %s\nHost: %s\nTimezone: %s\n", v.Name, v.Code, v.Host, v.Timezone)
}