-- +goose Up
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE author
(
    id ...,
    name ...,
    created_at ...,
    updated_at ...
);

-- +goose StatementBegin
CREATE OR REPLACE FUNCTION update_author_timestamp() RETURNS TRIGGER AS
$$
BEGIN
    NEW.updated_at = now();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- +goose StatementEnd


CREATE OR REPLACE TRIGGER trigger_update_author_timestamp
    BEFORE UPDATE
    ON ...
    FOR EACH ROW
EXECUTE FUNCTION update_author_timestamp();


-- +goose Down
DROP TABLE ...;

-- +goose Up
CREATE INDEX ...;

-- +goose Down
DROP INDEX ...;

-- +goose Up
CREATE TABLE book
(
    id ...,
    name ...,
    created_at ...,
    updated_at ...
);

-- +goose StatementBegin
CREATE OR REPLACE FUNCTION update_book_timestamp() RETURNS TRIGGER AS
$$
BEGIN
    NEW.updated_at = now();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- +goose StatementEnd

CREATE OR REPLACE TRIGGER trigger_update_book_timestamp
    BEFORE UPDATE
    ON ...
    FOR EACH ROW
EXECUTE FUNCTION update_book_timestamp();

-- +goose Down
DROP TABLE ...;

-- +goose Up
CREATE INDEX  ...;

-- +goose Down
DROP INDEX ...;

-- +goose Up
CREATE TABLE author_book
(
    author_id ...,
    book_id ...,
    PRIMARY KEY (.. .)
);

-- +goose Down
DROP TABLE author_book;

"github.com/jackc/pgx/v5/pgxpool"
"github.com/jackc/pgx/v5/stdlib"
"github.com/pressly/goose/v3"
"github.com/project/library/config"

docker volumes
docker volume ls // если нужно удалить старый volume
docker volume rm ... // если нужно удалить старый volume
docker-compose up -d

docker ps -a // посмотреть контейнеры
docker stop / docker rm - для остановки и удаления контейнера

2025/03/06 15:03:14 OK   001_create_author_table.sql (5.89ms)
2025/03/06 15:03:14 OK   002_create_author_name_index.sql (8.83ms)
2025/03/06 15:03:14 OK   003_create_book_table.sql (9.78ms)
2025/03/06 15:03:14 OK   004_create_book_name_index.sql (2.51ms)
2025/03/06 15:03:14 OK   005_create_author_book_table.sql (3.28ms)
2025/03/06 15:03:14 OK   006_create_author_book_book_id_index.sql (2.99ms)
2025/03/06 15:03:14 goose: successfully migrated database to version: 6

type (
    Config struct {
        GRPC
        PG
    }

    GRPC struct {
        Port        string `env:"GRPC_PORT"`
        GatewayPort string `env:"GRPC_GATEWAY_PORT"`
    }

    PG struct {
        URL      string
        Host     string `env:"POSTGRES_HOST"`
        Port     string `env:"POSTGRES_PORT"`
        DB       string `env:"POSTGRES_DB"`
        User     string `env:"POSTGRES_USER"`
        Password string `env:"POSTGRES_PASSWORD"`
        MaxConn  string `env:"POSTGRES_MAX_CONN"`
    }
)

postgres://user:password@host:port/dbname?sslmode=disable&pool_max_conns=10

func (r *PostgresRepository) CreateBook(ctx context.Context, book entity.Book) (entity.Book, error) {
tx, err := r.db.Begin(ctx)
if err != nil {
return entity.Book{}, err
}
defer tx.Rollback(ctx)

    const queryBook = `INSERT INTO book (name) VALUES ($1) RETURNING id, created_at, updated_at`
    err = tx.QueryRow(ctx, queryBook, book.Name).Scan(&book.ID, &book.CreatedAt, &book.UpdatedAt)
    if err != nil {
        return entity.Book{}, err
    }

    const queryAuthorBooks = `INSERT INTO author_book (author_id, book_id) VALUES ($1, $2)`
    for _, authorID := range book.AuthorIDs {
        _, err := tx.Exec(ctx, queryAuthorBooks, authorID, book.ID)
        if err != nil {
            return entity.Book{}, err
        }
    }

    if err := tx.Commit(ctx); err != nil {
        return entity.Book{}, err
    }

    return book, nil
}

import "google/protobuf/timestamp.proto";

message Book {
  ...
  google.protobuf.Timestamp created_at = ...;
  google.protobuf.Timestamp updated_at = ...;
}

CREATE TYPE outbox_status as ENUM ('CREATED', 'IN_PROGRESS', 'SUCCESS');

CREATE TABLE outbox
(
    idempotency_key TEXT PRIMARY KEY,
    data            JSONB                   NOT NULL,
    status          outbox_status           NOT NULL,
    kind            INT                     NOT NULL,
    created_at      TIMESTAMP DEFAULT now() NOT NULL,
    updated_at      TIMESTAMP DEFAULT now() NOT NULL
);

type Transactor interface {
    WithTx(ctx context.Context, function func(ctx context.Context) error) error
}

func extractTx(ctx context.Context) (pgx.Tx, error) {}

func injectTx(ctx context.Context, pool *pgxpool.Pool) (context.Context, error, pgx.Tx) {}

func (l *libraryImpl) RegisterBook(ctx context.Context, name string, authorIDs []string) (*library.AddBookResponse, error) {
    var book entity.Book
    err := l.transactor.WithTx(ctx, func(ctx context.Context) error {
        book, txErr = l.booksRepository.CreateBook(ctx, entity.Book{
            Name:      name,
            AuthorIDs: authorIDs,
        })

        ...
        l.outboxRepository.SendMessage(ctx, idempotencyKey, repository.OutboxKindBook, serialized)
    })

    ...
}

type Outbox struct {
    Enabled         bool          `env:"OUTBOX_ENABLED"`
    Workers         int           `env:"OUTBOX_WORKERS"`
    BatchSize       int           `env:"OUTBOX_BATCH_SIZE"`
    WaitTimeMS      time.Duration `env:"OUTBOX_WAIT_TIME_MS"`
    InProgressTTLMS time.Duration `env:"OUTBOX_IN_PROGRESS_TTL_MS"`
    AuthorSendURL   string        `env:"OUTBOX_AUTHOR_SEND_URL"`
    BookSendURL     string        `env:"OUTBOX_BOOK_SEND_URL"`
}

make all

make test

make lint

make update