Skip to Content
Ma'lumotlar BazasiPostgreSQLCRUD

CRUD

CRUD — Create, Read, Update, Delete, ya’ni yaratish, o’qish, yangilash va o’chirish. pgx’da bu to’rt amal uchta metodga tayanadi:

  • Exec — natija qatorlarini qaytarmaydigan so’rovlar (INSERT, UPDATE, DELETE).
  • QueryRow — aynan bitta qator kutilganda.
  • Query — ko’p qator qaytaradigan SELECT.

Misollar uchun jadval:

CREATE TABLE products (
    id    bigserial PRIMARY KEY,
    name  text NOT NULL,
    price int  NOT NULL
);

$1 — parametrli so’rovlar (SQL injection’dan himoya)

Foydalanuvchi kiritgan qiymatni SQL satriga hech qachon yopishtirmang. pgx’da qiymatlar $1, $2 … kabi placeholder’lar orqali alohida uzatiladi — baza ularni kod deb emas, oddiy ma’lumot deb qabul qiladi.

insert.go
package main
 
import (
    "context"
    "fmt"
    "log"
 
    "github.com/jackc/pgx/v5/pgxpool"
)
 
func main() {
    ctx := context.Background()
    pool, err := pgxpool.New(ctx, "postgres://gopher:secret@localhost:5432/shop")
    if err != nil {
        log.Fatal(err)
    }
    defer pool.Close()
 
    // TO'G'RI: qiymat $1 orqali uzatiladi.
    tag, err := pool.Exec(ctx,
        "INSERT INTO products (name, price) VALUES ($1, $2)",
        "Klaviatura", 250000)
    if err != nil {
        log.Fatal(err)
    }
    fmt.Println("qo'shildi:", tag.RowsAffected(), "qator")
}
$ go run insert.go
qo'shildi: 1 qator

Exec pgconn.CommandTag qaytaradi; RowsAffected() esa nechta qator o’zgarganini aytadi.

INSERT ... RETURNING — yangi ID ni olish

PostgreSQL’da INSERT’dan keyin baza generatsiya qilgan id’ni darhol qaytarib olsa bo’ladi — buning uchun alohida SELECT yozish shart emas. Bitta qator qaytgani uchun QueryRow ishlatamiz.

returning.go
package main
 
import (
    "context"
    "fmt"
    "log"
 
    "github.com/jackc/pgx/v5/pgxpool"
)
 
func main() {
    ctx := context.Background()
    pool, err := pgxpool.New(ctx, "postgres://gopher:secret@localhost:5432/shop")
    if err != nil {
        log.Fatal(err)
    }
    defer pool.Close()
 
    var id int64
    err = pool.QueryRow(ctx,
        "INSERT INTO products (name, price) VALUES ($1, $2) RETURNING id",
        "Sichqoncha", 120000).Scan(&id)
    if err != nil {
        log.Fatal(err)
    }
    fmt.Println("yangi mahsulot ID:", id)
}
$ go run returning.go
yangi mahsulot ID: 2

Bitta qator o’qish va pgx.ErrNoRows

QueryRow(...).Scan(...) bitta qatorni o’qib, o’zgaruvchilarga joylaydi. Agar qator topilmasa, Scan pgx.ErrNoRows qaytaradi — buni “topilmadi” holati sifatida alohida ushlab olish kerak, aks holda oddiy xato bilan aralashib ketadi.

queryrow.go
package main
 
import (
    "context"
    "errors"
    "fmt"
    "log"
 
    "github.com/jackc/pgx/v5"
    "github.com/jackc/pgx/v5/pgxpool"
)
 
func main() {
    ctx := context.Background()
    pool, err := pgxpool.New(ctx, "postgres://gopher:secret@localhost:5432/shop")
    if err != nil {
        log.Fatal(err)
    }
    defer pool.Close()
 
    var name string
    var price int
    err = pool.QueryRow(ctx,
        "SELECT name, price FROM products WHERE id = $1", 999).
        Scan(&name, &price)
 
    switch {
    case errors.Is(err, pgx.ErrNoRows):
        fmt.Println("mahsulot topilmadi")
    case err != nil:
        log.Fatal(err)
    default:
        fmt.Printf("%s — %d so'm\n", name, price)
    }
}
$ go run queryrow.go
mahsulot topilmadi

“Topilmadi”ni tekshirishning to’g’ri yo’li — errors.Is(err, pgx.ErrNoRows).

Ko’p qator o’qish: Query va rows.Next

Bir nechta qator qaytganda Query ishlatiladi. Natijani rows.Next() sikli bilan aylanib chiqamiz, har qadamda Scan qilamiz. defer rows.Close() — majburiy, aks holda ulanish havzaga qaytmaydi.

query.go
package main
 
import (
    "context"
    "fmt"
    "log"
 
    "github.com/jackc/pgx/v5/pgxpool"
)
 
func main() {
    ctx := context.Background()
    pool, err := pgxpool.New(ctx, "postgres://gopher:secret@localhost:5432/shop")
    if err != nil {
        log.Fatal(err)
    }
    defer pool.Close()
 
    rows, err := pool.Query(ctx,
        "SELECT id, name, price FROM products ORDER BY id")
    if err != nil {
        log.Fatal(err)
    }
    defer rows.Close()
 
    for rows.Next() {
        var id int64
        var name string
        var price int
        if err := rows.Scan(&id, &name, &price); err != nil {
            log.Fatal(err)
        }
        fmt.Printf("#%d %s — %d so'm\n", id, name, price)
    }
    if err := rows.Err(); err != nil { // sikl davomidagi xatoni tekshiramiz
        log.Fatal(err)
    }
}
$ go run query.go
#1 Klaviatura — 250000 so'm
#2 Sichqoncha — 120000 so'm

Sikldan keyin rows.Err()’ni tekshirishni unutmang — Next xatolik yuz berganda ham false qaytaradi.

Update va Delete

Ikkalasi ham Exec orqali bajariladi, RowsAffected() esa nechta qatorga tegilganini aytadi — bu “haqiqatan biror qator o’zgardimi?” degan savolga javob beradi.

update.go
package main
 
import (
    "context"
    "fmt"
    "log"
 
    "github.com/jackc/pgx/v5/pgxpool"
)
 
func main() {
    ctx := context.Background()
    pool, err := pgxpool.New(ctx, "postgres://gopher:secret@localhost:5432/shop")
    if err != nil {
        log.Fatal(err)
    }
    defer pool.Close()
 
    tag, err := pool.Exec(ctx,
        "UPDATE products SET price = $1 WHERE id = $2", 199000, 2)
    if err != nil {
        log.Fatal(err)
    }
    fmt.Println("yangilandi:", tag.RowsAffected())
 
    tag, err = pool.Exec(ctx, "DELETE FROM products WHERE id = $1", 1)
    if err != nil {
        log.Fatal(err)
    }
    fmt.Println("o'chirildi:", tag.RowsAffected())
}
$ go run update.go
yangilandi: 1
o'chirildi: 1

Manba / batafsil: pkg.go.dev/github.com/jackc/pgx/v5 

Bir nechta o’zgarishni “yo hammasi, yo hech biri” tarzida bajarish kerak bo’lsa — Transaksiyalar.

Last updated on
UlanishTransaksiyalar