PostgreSQL bilan bog‘lanish (`pgx` / `database/sql`)

Bu darsda Go dasturini PostgreSQL bazasiga ulaymiz va kitoblar ustida CRUD amallarini bajaramiz. Avval standart database/sql interfeysi, keyin pgxpool bilan ishlashni ko‘ramiz. Oxirida clean architecture darsidagi repository interfeysini PostgreSQL uchun amalga oshiramiz.

1. PostgreSQL’ni ishga tushirish

PostgreSQL’ni kompyuterga o‘rnatish shart emas. Uni Docker konteynerida ishga tushirish qulayroq:

docker run -d \
  --name books-db \
  -e POSTGRES_USER=app \
  -e POSTGRES_PASSWORD=secret \
  -e POSTGRES_DB=books \
  -p 5432:5432 \
  -v books-data:/var/lib/postgresql/data \
  postgres:17

Buyruq parametrlari:

--name books-db — konteyner nomi;
-e ... — foydalanuvchi, parol va baza nomi;
-p 5432:5432 — konteynerdagi 5432-portni kompyuterning 5432-portiga ulaydi;
-v books-data:... — ma’lumotlar books-data nomli Docker volume’da saqlanadi. Konteyner o‘chirilsa ham ular yo‘qolmaydi;
postgres:17 — PostgreSQL’ning 17-versiyasi. Rasmiy Docker Hub sahifasidan joriy versiyani tekshirib oling.

Baza tayyorligini tekshiramiz:

docker exec books-db pg_isready -U app

/var/run/postgresql:5432 - accepting connections

Jadval yaratish

psql — PostgreSQL’ning buyruq qatori klienti. U konteyner ichida tayyor turibdi:

docker exec -it books-db psql -U app -d books

books=# taklifi chiqqach, jadvalni yaratamiz:

CREATE TABLE books (
    id         BIGSERIAL PRIMARY KEY,
    title      TEXT        NOT NULL,
    author     TEXT        NOT NULL,
    year       INT         NOT NULL CHECK (year > 0),
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE (title, author)
);

\dt buyrug‘i jadvallar ro‘yxatini, \q esa psqldan chiqishni bildiradi.

Info

Real loyihada jadvallar qo‘lda emas, migratsiyalar orqali yaratiladi. Migratsiya — baza tuzilishidagi har bir o‘zgarishni tartib raqami bilan saqlaydigan SQL fayl. golang-migrate, goose yoki atlas kabi vositalar ularni ketma-ket qo‘llaydi. Shunda baza tuzilishi ham kod kabi Git’da saqlanadi.

2. Ulanish satri (DSN)

Go dasturi bazaga qanday ulanishni DSN (Data Source Name) — ulanish satridan biladi. PostgreSQL uchun u URL ko‘rinishida yoziladi:

postgres://app:secret@localhost:5432/books?sslmode=disable
└──┬───┘  └┬┘ └─┬──┘ └───┬───┘ └┬─┘ └─┬─┘ └──────┬──────┘
 sxema  user parol    host   port baza   parametrlar

sslmode=disable — lokal ishlab chiqish uchun shifrlashni o‘chiradi. Production’da baza boshqa serverda bo‘lsa, sslmode=verify-full ishlatiladi.

Warning

DSN ichida parol bor. Uni kodga yozmang va Git’ga qo‘shmang. Bu darsda soddalik uchun o‘zgaruvchidan o‘qiymiz. To‘g‘ri usul Config va .env darsida ko‘riladi.

export DATABASE_URL="postgres://app:secret@localhost:5432/books?sslmode=disable"

3. `database/sql` bilan ishlash

Loyihani tayyorlaymiz:

mkdir pgdemo && cd pgdemo
go mod init pgdemo
go get github.com/jackc/pgx/v5

pgx paketining ichida database/sql uchun drayver bor: github.com/jackc/pgx/v5/stdlib.

Ulanishni ochish

import (
    "database/sql"

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

db, err := sql.Open("pgx", os.Getenv("DATABASE_URL"))

_ bilan import drayverni faqat ro‘yxatdan o‘tkazish uchun kerak. Paketning init funksiyasi "pgx" nomli drayverni database/sqlga qo‘shadi.
sql.Open ulanish ochmaydi. U faqat DSN’ni tekshiradi va *sql.DB obyektini tayyorlaydi. Haqiqiy ulanish birinchi so‘rov paytida ochiladi.
Shuning uchun darhol db.PingContext(ctx) chaqirib, baza haqiqatan ham javob berayotganini tekshirish kerak.

`*sql.DB` — bitta ulanish emas, pul

*sql.DB bitta ulanish emas. U ulanishlar puli (connection pool): kerak paytda yangi ulanish ochadi, bo‘shaganlarini qayta ishlatadi. U goroutine’lar uchun xavfsiz, ya’ni bir vaqtda bir nechta goroutine undan foydalana oladi. Shu sababli sql.DB dastur boshida bir marta yaratiladi va butun dastur davomida ishlatiladi.

Pul sozlamalari:

db.SetMaxOpenConns(25)                  // bir vaqtda ochiq ulanishlar soni
db.SetMaxIdleConns(25)                  // kutib turgan ulanishlar soni
db.SetConnMaxLifetime(30 * time.Minute) // ulanishning maksimal umri
db.SetConnMaxIdleTime(5 * time.Minute)  // bo‘sh turgan ulanish qancha saqlanadi

SetMaxOpenConns chegarasiz qoldirilsa, yuklama oshganda ilova minglab ulanish ochib, bazani to‘ldirib qo‘yishi mumkin. PostgreSQL’da standart ulanishlar chegarasi 100 ta. Bu son barcha server nusxalari uchun umumiy.

Uch turdagi so‘rov

Metod	Qachon	Nima qaytaradi
`ExecContext`	`INSERT`, `UPDATE`, `DELETE` — natija qatorlari kerak emas	`sql.Result` (ta’sirlangan qatorlar soni)
`QueryRowContext`	Bitta qator kutilganda	`*sql.Row`
`QueryContext`	Bir nechta qator kutilganda	`*sql.Rows`

Barcha metodlarning Context qo‘shimchali shakli bor. Ularning ahamiyati keyingi darsda tushuntiriladi. Hozircha doim ...Context metodlaridan foydalanamiz.

To‘liq misol

main.go
package main

import (
    "context"
    "database/sql"
    "errors"
    "fmt"
    "log"
    "os"
    "time"

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

type Book struct {
    ID        int64
    Title     string
    Author    string
    Year      int
    CreatedAt time.Time
}

func main() {
    ctx := context.Background()

    db, err := sql.Open("pgx", os.Getenv("DATABASE_URL"))
    if err != nil {
        log.Fatalf("DSN noto‘g‘ri: %v", err)
    }
    defer db.Close()

    db.SetMaxOpenConns(25)
    db.SetMaxIdleConns(25)
    db.SetConnMaxLifetime(30 * time.Minute)

    pingCtx, cancel := context.WithTimeout(ctx, 5*time.Second)
    defer cancel()
    if err := db.PingContext(pingCtx); err != nil {
        log.Fatalf("bazaga ulanib bo‘lmadi: %v", err)
    }
    fmt.Println("Bazaga ulandik")

    // Tozalab olamiz, shunda misolni qayta-qayta ishga tushirish mumkin
    if _, err := db.ExecContext(ctx, `TRUNCATE books RESTART IDENTITY`); err != nil {
        log.Fatal(err)
    }

    // ---------- CREATE ----------
    var id int64
    err = db.QueryRowContext(ctx,
        `INSERT INTO books (title, author, year) VALUES ($1, $2, $3) RETURNING id`,
        "O‘tkan kunlar", "Abdulla Qodiriy", 1926,
    ).Scan(&id)
    if err != nil {
        log.Fatal(err)
    }
    fmt.Println("Yangi kitob ID:", id)

    db.ExecContext(ctx,
        `INSERT INTO books (title, author, year) VALUES ($1, $2, $3), ($4, $5, $6)`,
        "Kecha va kunduz", "Cho‘lpon", 1936,
        "Sarob", "Abdulla Qahhor", 1937,
    )

    // ---------- READ: bitta qator ----------
    var b Book
    err = db.QueryRowContext(ctx,
        `SELECT id, title, author, year, created_at FROM books WHERE id = $1`, id,
    ).Scan(&b.ID, &b.Title, &b.Author, &b.Year, &b.CreatedAt)
    if err != nil {
        log.Fatal(err)
    }
    fmt.Printf("Topildi: %s (%d)\n", b.Title, b.Year)

    // ---------- READ: topilmagan qator ----------
    err = db.QueryRowContext(ctx, `SELECT title FROM books WHERE id = $1`, 999).Scan(&b.Title)
    if errors.Is(err, sql.ErrNoRows) {
        fmt.Println("999-kitob topilmadi")
    }

    // ---------- READ: ko‘p qator ----------
    rows, err := db.QueryContext(ctx,
        `SELECT id, title, author, year, created_at FROM books WHERE year > $1 ORDER BY id`, 1930)
    if err != nil {
        log.Fatal(err)
    }
    defer rows.Close()

    var books []Book
    for rows.Next() {
        var b Book
        if err := rows.Scan(&b.ID, &b.Title, &b.Author, &b.Year, &b.CreatedAt); err != nil {
            log.Fatal(err)
        }
        books = append(books, b)
    }
    if err := rows.Err(); err != nil {
        log.Fatal(err)
    }
    fmt.Println("1930-yildan keyingi kitoblar:", len(books))
    for _, b := range books {
        fmt.Printf("  %d. %s — %s\n", b.ID, b.Title, b.Author)
    }

    // ---------- UPDATE ----------
    res, err := db.ExecContext(ctx, `UPDATE books SET year = $1 WHERE id = $2`, 1925, id)
    if err != nil {
        log.Fatal(err)
    }
    n, _ := res.RowsAffected()
    fmt.Println("Yangilangan qatorlar:", n)

    // ---------- DELETE ----------
    res, err = db.ExecContext(ctx, `DELETE FROM books WHERE id = $1`, 999)
    if err != nil {
        log.Fatal(err)
    }
    n, _ = res.RowsAffected()
    fmt.Println("O‘chirilgan qatorlar:", n)
}

go run main.go

Bazaga ulandik
Yangi kitob ID: 1
Topildi: O‘tkan kunlar (1926)
999-kitob topilmadi
1930-yildan keyingi kitoblar: 2
  2. Kecha va kunduz — Cho‘lpon
  3. Sarob — Abdulla Qahhor
Yangilangan qatorlar: 1
O‘chirilgan qatorlar: 0

Muhim qoidalar

Parametrlar $1, $2 orqali beriladi. Qiymatlar hech qachon SQL satriga fmt.Sprintf yoki + bilan qo‘shilmaydi. Bu SQL injection’dan himoyaning asosi. Mavzu alohida darsda batafsil ko‘riladi.

RETURNING id — PostgreSQL’ga xos imkoniyat. INSERT natijasida yaratilgan qatordagi qiymatlarni qaytaradi. Shu sababli bu yerda ExecContext emas, QueryRowContext ishlatildi.

sql.ErrNoRows — QueryRow hech qanday qator topmasa qaytadi. Bu xato emas, oddiy holat: API’da u 404 Not Foundga aylanadi.

rows.Close() majburiy. Qatorlar yopilmasa, ulanish pulga qaytmaydi. Bir necha yuz so‘rovdan keyin pul tugaydi va ilova "osilib" qoladi. defer rows.Close()ni QueryContextdan keyin, xato tekshirilgan zahoti yozing.

rows.Err() tekshiriladi. rows.Next() false qaytarganda sabab ikki xil bo‘lishi mumkin: qatorlar tugadi yoki o‘qish paytida xato yuz berdi. Farqni faqat rows.Err() ko‘rsatadi.

RowsAffected UPDATE yoki DELETE haqiqatan biror qatorga ta’sir qilganini bildiradi. 0 bo‘lsa, yozuv topilmagan. API’da bu ham 404ga aylanadi.

`NULL` qiymatlar

Ustun NULL bo‘lishi mumkin bo‘lsa, uni oddiy stringga o‘qib bo‘lmaydi. Buning uchun ko‘rsatkich yoki sql.Null* turlari ishlatiladi:

var description *string          // NULL → nil
var isbn sql.NullString          // NULL → isbn.Valid == false

row.Scan(&description, &isbn)

4. `pgxpool` bilan ishlash

pgx kutubxonasining o‘z interfeysi PostgreSQL uchun maxsus ishlab chiqilgan. U database/sqlga qaraganda biroz tezroq ishlaydi va PostgreSQL’ning massivlar, JSONB, COPY kabi imkoniyatlarini to‘liq qo‘llab-quvvatlaydi. Loyiha faqat PostgreSQL bilan ishlasa, pgxpool yaxshi tanlov.

main.go
package main

import (
    "context"
    "errors"
    "fmt"
    "log"
    "os"
    "time"

    "github.com/jackc/pgx/v5"
    "github.com/jackc/pgx/v5/pgxpool"
)

type Book struct {
    ID        int64     `db:"id"`
    Title     string    `db:"title"`
    Author    string    `db:"author"`
    Year      int       `db:"year"`
    CreatedAt time.Time `db:"created_at"`
}

func main() {
    ctx := context.Background()

    cfg, err := pgxpool.ParseConfig(os.Getenv("DATABASE_URL"))
    if err != nil {
        log.Fatalf("DSN noto‘g‘ri: %v", err)
    }
    cfg.MaxConns = 25
    cfg.MaxConnLifetime = 30 * time.Minute

    pool, err := pgxpool.NewWithConfig(ctx, cfg)
    if err != nil {
        log.Fatal(err)
    }
    defer pool.Close()

    if err := pool.Ping(ctx); err != nil {
        log.Fatalf("bazaga ulanib bo‘lmadi: %v", err)
    }

    // Bitta qator
    var title string
    err = pool.QueryRow(ctx, `SELECT title FROM books WHERE id = $1`, 1).Scan(&title)
    if errors.Is(err, pgx.ErrNoRows) {
        fmt.Println("topilmadi")
    } else if err != nil {
        log.Fatal(err)
    } else {
        fmt.Println("1-kitob:", title)
    }

    // Ko‘p qatorni to‘g‘ridan-to‘g‘ri struct'larga yig‘ish
    rows, err := pool.Query(ctx,
        `SELECT id, title, author, year, created_at FROM books ORDER BY id`)
    if err != nil {
        log.Fatal(err)
    }
    books, err := pgx.CollectRows(rows, pgx.RowToStructByName[Book])
    if err != nil {
        log.Fatal(err)
    }

    for _, b := range books {
        fmt.Printf("%d. %s (%d)\n", b.ID, b.Title, b.Year)
    }
}

1-kitob: O‘tkan kunlar
1. O‘tkan kunlar (1925)
2. Kecha va kunduz (1936)
3. Sarob (1937)

database/sqldan farqlari:

Topilmagan qator uchun sql.ErrNoRows emas, pgx.ErrNoRows qaytadi.
pgx.CollectRows va pgx.RowToStructByName qatorlarni ustun nomlari bo‘yicha struct’ga o‘zi joylaydi. db tagi ustun nomini ko‘rsatadi. rows.Close() ham avtomatik chaqiriladi.
Pul sozlamalari pgxpool.Config orqali beriladi. Ularni DSN ichida ham yozish mumkin: ?pool_max_conns=25.

5. PostgreSQL repository

Endi clean architecture darsidagi book.Repository interfeysini PostgreSQL uchun amalga oshiramiz. Interfeys o‘zgarmaydi:

type Repository interface {
    List(ctx context.Context) ([]Book, error)
    Get(ctx context.Context, id int) (Book, error)
    Create(ctx context.Context, b Book) (Book, error)
    ExistsByTitle(ctx context.Context, title, author string) (bool, error)
}

internal/storage/postgres.go
package storage

import (
    "context"
    "errors"
    "fmt"

    "github.com/jackc/pgx/v5"
    "github.com/jackc/pgx/v5/pgconn"
    "github.com/jackc/pgx/v5/pgxpool"

    "books-api/internal/book"
)

type PostgresBookRepo struct {
    pool *pgxpool.Pool
}

func NewPostgresBookRepo(pool *pgxpool.Pool) *PostgresBookRepo {
    return &PostgresBookRepo{pool: pool}
}

const bookColumns = `id, title, author, year, created_at`

func scanBook(row pgx.Row) (book.Book, error) {
    var b book.Book
    err := row.Scan(&b.ID, &b.Title, &b.Author, &b.Year, &b.CreatedAt)
    return b, err
}

func (r *PostgresBookRepo) List(ctx context.Context) ([]book.Book, error) {
    rows, err := r.pool.Query(ctx, `SELECT `+bookColumns+` FROM books ORDER BY id`)
    if err != nil {
        return nil, fmt.Errorf("kitoblar ro‘yxati: %w", err)
    }
    defer rows.Close()

    books := make([]book.Book, 0)
    for rows.Next() {
        b, err := scanBook(rows)
        if err != nil {
            return nil, fmt.Errorf("kitobni o‘qish: %w", err)
        }
        books = append(books, b)
    }
    return books, rows.Err()
}

func (r *PostgresBookRepo) Get(ctx context.Context, id int) (book.Book, error) {
    row := r.pool.QueryRow(ctx, `SELECT `+bookColumns+` FROM books WHERE id = $1`, id)

    b, err := scanBook(row)
    if errors.Is(err, pgx.ErrNoRows) {
        return book.Book{}, book.ErrNotFound
    }
    if err != nil {
        return book.Book{}, fmt.Errorf("kitob #%d: %w", id, err)
    }
    return b, nil
}

func (r *PostgresBookRepo) Create(ctx context.Context, b book.Book) (book.Book, error) {
    row := r.pool.QueryRow(ctx,
        `INSERT INTO books (title, author, year, created_at)
         VALUES ($1, $2, $3, $4)
         RETURNING `+bookColumns,
        b.Title, b.Author, b.Year, b.CreatedAt)

    created, err := scanBook(row)
    if err != nil {
        var pgErr *pgconn.PgError
        if errors.As(err, &pgErr) && pgErr.Code == "23505" { // unique_violation
            return book.Book{}, book.ErrDuplicate
        }
        return book.Book{}, fmt.Errorf("kitob yaratish: %w", err)
    }
    return created, nil
}

func (r *PostgresBookRepo) ExistsByTitle(ctx context.Context, title, author string) (bool, error) {
    var exists bool
    err := r.pool.QueryRow(ctx,
        `SELECT EXISTS (
             SELECT 1 FROM books WHERE lower(title) = lower($1) AND lower(author) = lower($2)
         )`, title, author).Scan(&exists)
    if err != nil {
        return false, fmt.Errorf("dublikatni tekshirish: %w", err)
    }
    return exists, nil
}

Repository’da e’tibor berishga arziydigan jihatlar:

Baza xatolari domen xatolariga aylantiriladi. pgx.ErrNoRows → book.ErrNotFound, PostgreSQL’ning 23505 (unique_violation) kodi → book.ErrDuplicate. Service va handler pgx haqida hech narsa bilmaydi.
ExistsByTitle bo‘lsa ham, UNIQUE cheklovi kerak. Ikki so‘rov bir vaqtda kelsa, ikkalasi ham "mavjud emas" javobini olib, keyin ikkalasi ham yozishga urinishi mumkin. Bunday holatda baza cheklovi ikkinchisini to‘xtatadi, repository esa xatoni ErrDuplicatega aylantiradi.
Xatolar kontekst bilan o‘raladi: fmt.Errorf("kitob #%d: %w", id, err). Logda xato qayerda yuz bergani ko‘rinadi, %w esa asl xatoni saqlab qoladi.

main.goda faqat repository yaratilishi o‘zgaradi:

pool, err := pgxpool.New(ctx, os.Getenv("DATABASE_URL"))
if err != nil {
    log.Fatal(err)
}
defer pool.Close()

repo := storage.NewPostgresBookRepo(pool)   // oldin: storage.NewMemoryBookRepo()
service := book.NewService(repo)
handler := httpapi.NewHandler(service)

Service, handler va ularning testlari o‘zgarmadi. Qatlamlarga ajratishning foydasi aynan shu yerda ko‘rinadi.

Xulosa

PostgreSQL’ni Docker’da bitta buyruq bilan ishga tushirish mumkin.
sql.Open ulanish ochmaydi. Ulanishni PingContext bilan tekshiring.
*sql.DB va *pgxpool.Pool — ulanishlar puli. Ular dastur boshida bir marta yaratiladi.
Qiymatlar doim $1, $2 parametrlari orqali uzatiladi.
rows.Close() va rows.Err() majburiy.
Topilmagan qator (ErrNoRows) va baza cheklovi xatolari repository’da domen xatolariga aylantiriladi.

Keyingi darsda context yordamida sekin so‘rovlarni bekor qilish, timeout qo‘yish va tranzaksiyalar bilan ishlashni o‘rganamiz.

PostgreSQL bilan bog‘lanish (pgx / database/sql)