Go и SQL: работа с базами данных

1package main
2
3import (
4    "database/sql"
5    "fmt"
6    "log"
7
8    _ "github.com/lib/pq"
9)
10
11func main() {
12    connStr := "user=postgres password=secret dbname=mydb sslmode=disable"
13    db, err := sql.Open("postgres", connStr)
14    if err != nil {
15        log.Fatal(err)
16    }
17    defer db.Close()
18
19    // Проверка соединения
20    if err := db.Ping(); err != nil {
21        log.Fatal(err)
22    }
23
24    fmt.Println("Connected to database")
25}

1func main() {
2    db, _ := sql.Open("postgres", connStr)
3
4    // Настройка пула соединений
5    db.SetMaxOpenConns(25)
6    db.SetMaxIdleConns(5)
7    db.SetConnMaxLifetime(5 * time.Minute)
8
9    // Использование
10    defer db.Close()
11}

1type User struct {
2    ID    int
3    Name  string
4    Email string
5}
6
7// Создание
8func CreateUser(db *sql.DB, name, email string) (int, error) {
9    var id int
10    err := db.QueryRow(
11        "INSERT INTO users(name, email) VALUES($1, $2) RETURNING id",
12        name, email,
13    ).Scan(&id)
14    return id, err
15}
16
17// Чтение
18func GetUser(db *sql.DB, id int) (*User, error) {
19    user := &User{}
20    err := db.QueryRow(
21        "SELECT id, name, email FROM users WHERE id = $1", id,
22    ).Scan(&user.ID, &user.Name, &user.Email)
23    return user, err
24}
25
26// Обновление
27func UpdateUser(db *sql.DB, id int, name string) error {
28    _, err := db.Exec(
29        "UPDATE users SET name = $1 WHERE id = $2", name, id,
30    )
31    return err
32}
33
34// Удаление
35func DeleteUser(db *sql.DB, id int) error {
36    _, err := db.Exec("DELETE FROM users WHERE id = $1", id)
37    return err
38}

1func GetAllUsers(db *sql.DB) ([]User, error) {
2    rows, err := db.Query("SELECT id, name, email FROM users")
3    if err != nil {
4        return nil, err
5    }
6    defer rows.Close()
7
8    var users []User
9    for rows.Next() {
10        var u User
11        if err := rows.Scan(&u.ID, &u.Name, &u.Email); err != nil {
12            return nil, err
13        }
14        users = append(users, u)
15    }
16
17    return users, rows.Err()
18}

1go get github.com/jmoiron/sqlx

1import "github.com/jmoiron/sqlx"
2
3func main() {
4    db, _ := sqlx.Connect("postgres", connStr)
5
6    // Автоматический маппинг
7    var users []User
8    db.Select(&users, "SELECT * FROM users WHERE active = $1", true)
9
10    // Одна строка
11    var user User
12    db.Get(&user, "SELECT * FROM users WHERE id = $1", 1)
13
14    // Named queries
15    query, args, _ := sqlx.In(
16        "SELECT * FROM users WHERE id IN (?)",
17        []int{1, 2, 3},
18    )
19    db.Select(&users, query, args...)
20}

1import (
2    "gorm.io/driver/postgres"
3    "gorm.io/gorm"
4)
5
6type User struct {
7    gorm.Model
8    Name  string
9    //gorm:"uniqueIndex"
10    Email string
11    Orders []Order
12}
13
14type Order struct {
15    gorm.Model
16    UserID uint
17    Amount float64
18}
19
20func main() {
21    dsn := "host=localhost user=postgres password=secret dbname=mydb port=5432 sslmode=disable"
22    db, _ := gorm.Open(postgres.Open(dsn), &gorm.Config{})
23
24    // Автоматическая миграция
25    db.AutoMigrate(&User{}, &Order{})
26}

1// Создание
2user := User{Name: "Alice", Email: "alice@test.com"}
3result := db.Create(&user)
4
5// Чтение
6var user User
7db.First(&user, 1)                    // По ID
8db.Where("name = ?", "Alice").First(&user) // По условию
9
10// Обновление
11db.Model(&user).Update("Name", "Bob")
12
13// Удаление
14db.Delete(&user, 1)

1// Создание с ассоциациями
2user := User{
3    Name:  "Alice",
4    Orders: []Order{
5        {Amount: 100.50},
6        {Amount: 200.75},
7    },
8}
9db.Create(&user)
10
11// Preload (жадная загрузка)
12var userWithOrders User
13db.Preload("Orders").First(&userWithOrders, 1)

1# Создание миграции
2migrate create -ext sql -dir db/migrations -seq create_users_table
3
4# Применение
5migrate -path db/migrations -database "postgres://localhost:5432/mydb?sslmode=disable" up
6
7# Откат
8migrate -path db/migrations -database "postgres://localhost:5432/mydb?sslmode=disable" down 1

1-- 000001_create_users_table.up.sql
2CREATE TABLE users (
3    id SERIAL PRIMARY KEY,
4    name VARCHAR(100) NOT NULL,
5    email VARCHAR(255) UNIQUE NOT NULL,
6    created_at TIMESTAMP DEFAULT NOW(),
7    updated_at TIMESTAMP DEFAULT NOW()
8);
9
10-- 000001_create_users_table.down.sql
11DROP TABLE IF EXISTS users;