Cours 05 : Bases de Données avec Go

1. database/sql

1.1 Connexion

import (
    "database/sql"
    _ "github.com/lib/pq" // PostgreSQL driver
)

type DB struct {
    *sql.DB
}

func NewDB(dsn string) (*DB, error) {
    db, err := sql.Open("postgres", dsn)
    if err != nil {
        return nil, fmt.Errorf("open db: %w", err)
    }

    // Configuration du pool
    db.SetMaxOpenConns(25)
    db.SetMaxIdleConns(5)
    db.SetConnMaxLifetime(5 * time.Minute)
    db.SetConnMaxIdleTime(1 * time.Minute)

    if err := db.Ping(); err != nil {
        return nil, fmt.Errorf("ping db: %w", err)
    }

    return &DB{db}, nil
}

1.2 CRUD basique

type User struct {
    ID        int       `db:"id"`
    Name      string    `db:"name"`
    Email     string    `db:"email"`
    CreatedAt time.Time `db:"created_at"`
}

// Create
func (db *DB) CreateUser(ctx context.Context, user *User) error {
    query := `INSERT INTO users (name, email) VALUES ($1, $2) RETURNING id, created_at`
    return db.QueryRowContext(ctx, query, user.Name, user.Email).
        Scan(&user.ID, &user.CreatedAt)
}

// Read
func (db *DB) GetUser(ctx context.Context, id int) (*User, error) {
    query := `SELECT id, name, email, created_at FROM users WHERE id = $1`
    
    var user User
    err := db.QueryRowContext(ctx, query, id).
        Scan(&user.ID, &user.Name, &user.Email, &user.CreatedAt)
    if err == sql.ErrNoRows {
        return nil, ErrNotFound
    }
    if err != nil {
        return nil, fmt.Errorf("get user: %w", err)
    }
    return &user, nil
}

// Update
func (db *DB) UpdateUser(ctx context.Context, user *User) error {
    query := `UPDATE users SET name = $1, email = $2 WHERE id = $3`
    result, err := db.ExecContext(ctx, query, user.Name, user.Email, user.ID)
    if err != nil {
        return fmt.Errorf("update user: %w", err)
    }
    
    rows, _ := result.RowsAffected()
    if rows == 0 {
        return ErrNotFound
    }
    return nil
}

// Delete
func (db *DB) DeleteUser(ctx context.Context, id int) error {
    query := `DELETE FROM users WHERE id = $1`
    result, err := db.ExecContext(ctx, query, id)
    if err != nil {
        return fmt.Errorf("delete user: %w", err)
    }
    
    rows, _ := result.RowsAffected()
    if rows == 0 {
        return ErrNotFound
    }
    return nil
}

1.3 Prepared Statements

type UserRepository struct {
    db          *sql.DB
    getStmt     *sql.Stmt
    createStmt  *sql.Stmt
}

func NewUserRepository(db *sql.DB) (*UserRepository, error) {
    ctx := context.Background()
    
    getStmt, err := db.PrepareContext(ctx,
        `SELECT id, name, email, created_at FROM users WHERE id = $1`)
    if err != nil {
        return nil, err
    }

    createStmt, err := db.PrepareContext(ctx,
        `INSERT INTO users (name, email) VALUES ($1, $2) RETURNING id, created_at`)
    if err != nil {
        return nil, err
    }

    return &UserRepository{db, getStmt, createStmt}, nil
}

func (r *UserRepository) GetUser(ctx context.Context, id int) (*User, error) {
    var user User
    err := r.getStmt.QueryRowContext(ctx, id).
        Scan(&user.ID, &user.Name, &user.Email, &user.CreatedAt)
    return &user, err
}

2. pgx (PostgreSQL Driver)

2.1 Connexion avec pgx

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

type PgxDB struct {
    pool *pgxpool.Pool
}

func NewPgxDB(ctx context.Context, dsn string) (*PgxDB, error) {
    config, err := pgxpool.ParseConfig(dsn)
    if err != nil {
        return nil, err
    }

    config.MaxConns = 25
    config.MinConns = 5
    config.MaxConnLifetime = 5 * time.Minute
    config.MaxConnIdleTime = 1 * time.Minute

    pool, err := pgxpool.NewWithConfig(ctx, config)
    if err != nil {
        return nil, err
    }

    return &PgxDB{pool}, nil
}

// pgx scan avancé
func (db *PgxDB) GetUser(ctx context.Context, id int) (*User, error) {
    query := `SELECT id, name, email, created_at FROM users WHERE id = $1`
    
    rows, err := db.pool.Query(ctx, query, id)
    if err != nil {
        return nil, err
    }
    defer rows.Close()

    user, err := pgx.CollectOneRow(rows, pgx.RowToStructByName[User])
    if err == pgx.ErrNoRows {
        return nil, ErrNotFound
    }
    return &user, err
}

2.2 Batch operations

func (db *PgxDB) BatchCreateUsers(ctx context.Context, users []User) error {
    batch := &pgx.Batch{}
    
    for _, user := range users {
        batch.Queue(
            `INSERT INTO users (name, email) VALUES ($1, $2)`,
            user.Name, user.Email,
        )
    }

    br := db.pool.SendBatch(ctx, batch)
    defer br.Close()

    for range users {
        _, err := br.Exec()
        if err != nil {
            return err
        }
    }
    return nil
}

3. sqlx

import "github.com/jmoiron/sqlx"

type SqlxDB struct {
    *sqlx.DB
}

func NewSqlxDB(dsn string) (*SqlxDB, error) {
    db, err := sqlx.Connect("postgres", dsn)
    if err != nil {
        return nil, err
    }
    return &SqlxDB{db}, nil
}

// StructScan
func (db *SqlxDB) GetUsers(ctx context.Context) ([]User, error) {
    var users []User
    err := db.SelectContext(ctx, &users, `SELECT * FROM users`)
    return users, err
}

// Named queries
func (db *SqlxDB) CreateUser(ctx context.Context, user *User) error {
    query := `INSERT INTO users (name, email) VALUES (:name, :email) RETURNING *`
    rows, err := db.NamedQueryContext(ctx, query, user)
    if err != nil {
        return err
    }
    if rows.Next() {
        rows.StructScan(user)
    }
    return nil
}

// In clause
func (db *SqlxDB) GetUsersByIDs(ctx context.Context, ids []int) ([]User, error) {
    query, args, err := sqlx.In(`SELECT * FROM users WHERE id IN (?)`, ids)
    if err != nil {
        return nil, err
    }
    query = db.Rebind(query)
    
    var users []User
    err = db.SelectContext(ctx, &users, query, args...)
    return users, err
}

4. sqlc (Code Generation)

4.1 Configuration

# sqlc.yaml
version: "2"
sql:
  - engine: "postgresql"
    queries: "queries.sql"
    schema: "schema.sql"
    gen:
      go:
        package: "db"
        out: "db"
        sql_package: "pgx/v5"

4.2 Définition des queries

-- schema.sql
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- queries.sql
-- name: GetUser :one
SELECT * FROM users WHERE id = $1;

-- name: ListUsers :many
SELECT * FROM users ORDER BY name;

-- name: CreateUser :one
INSERT INTO users (name, email) VALUES ($1, $2) RETURNING *;

-- name: UpdateUser :exec
UPDATE users SET name = $1, email = $2 WHERE id = $3;

-- name: DeleteUser :exec
DELETE FROM users WHERE id = $1;

4.3 Code généré

// db/users.sql.go (généré)
type User struct {
    ID        int64     `json:"id"`
    Name      string    `json:"name"`
    Email     string    `json:"email"`
    CreatedAt time.Time `json:"created_at"`
}

const getUser = `-- name: GetUser :one
SELECT id, name, email, created_at FROM users WHERE id = $1
`

func (q *Queries) GetUser(ctx context.Context, id int64) (User, error) {
    row := q.db.QueryRow(ctx, getUser, id)
    var i User
    err := row.Scan(&i.ID, &i.Name, &i.Email, &i.CreatedAt)
    return i, err
}

5. GORM (ORM)

5.1 Modèles et connexion

import "gorm.io/gorm"
import "gorm.io/driver/postgres"

type User struct {
    ID        uint           `gorm:"primarykey"`
    Name      string         `gorm:"size:255;not null"`
    Email     string         `gorm:"uniqueIndex;size:255"`
    Age       int            `gorm:"default:0"`
    Profile   Profile        `gorm:"foreignKey:UserID"`
    Orders    []Order        `gorm:"foreignKey:UserID"`
    CreatedAt time.Time
    UpdatedAt time.Time
    DeletedAt gorm.DeletedAt `gorm:"index"`
}

type Profile struct {
    ID     uint
    UserID uint
    Bio    string `gorm:"type:text"`
    Avatar string
}

type Order struct {
    ID       uint
    UserID   uint
    Product  string
    Amount   float64
    Status   string `gorm:"default:pending"`
}

func NewGORM(dsn string) (*gorm.DB, error) {
    db, err := gorm.Open(postgres.Open(dsn), &gorm.Config{
        Logger: logger.Default.LogMode(logger.Info),
        SkipDefaultTransaction: true,
        PrepareStmt: true,
    })
    if err != nil {
        return nil, err
    }

    // Auto Migrate
    db.AutoMigrate(&User{}, &Profile{}, &Order{})
    
    return db, nil
}

5.2 CRUD avec GORM

// Create
func createUser(db *gorm.DB, user *User) error {
    return db.Create(user).Error
}

// Batch create
func createUsers(db *gorm.DB, users []User) error {
    return db.CreateInBatches(users, 100).Error
}

// Read
func getUser(db *gorm.DB, id uint) (*User, error) {
    var user User
    err := db.Preload("Profile").Preload("Orders").First(&user, id).Error
    return &user, err
}

func listUsers(db *gorm.DB, page, size int) ([]User, int64, error) {
    var users []User
    var total int64
    
    db.Model(&User{}).Count(&total)
    
    offset := (page - 1) * size
    err := db.Offset(offset).Limit(size).Order("created_at desc").Find(&users).Error
    
    return users, total, err
}

// Update
func updateUser(db *gorm.DB, user *User) error {
    return db.Model(user).Updates(User{
        Name: user.Name,
        Age:  user.Age,
    }).Error
}

// Delete (soft)
func deleteUser(db *gorm.DB, id uint) error {
    return db.Delete(&User{}, id).Error
}

5.3 Hooks

type User struct {
    gorm.Model
    Password string
}

func (u *User) BeforeCreate(tx *gorm.DB) error {
    hashed, err := bcrypt.GenerateFromPassword([]byte(u.Password), bcrypt.DefaultCost)
    if err != nil {
        return err
    }
    u.Password = string(hashed)
    return nil
}

func (u *User) AfterCreate(tx *gorm.DB) error {
    return tx.Create(&Profile{UserID: u.ID}).Error
}

6. Redis (go-redis)

6.1 Connexion et opérations

import "github.com/redis/go-redis/v9"

type Cache struct {
    client *redis.Client
}

func NewCache(addr, password string, db int) *Cache {
    return &Cache{
        client: redis.NewClient(&redis.Options{
            Addr:         addr,
            Password:     password,
            DB:           db,
            PoolSize:     10,
            MinIdleConns: 5,
        }),
    }
}

func (c *Cache) SetUser(ctx context.Context, user *User, ttl time.Duration) error {
    data, _ := json.Marshal(user)
    return c.client.Set(ctx, "user:"+strconv.Itoa(user.ID), data, ttl).Err()
}

func (c *Cache) GetUser(ctx context.Context, id int) (*User, error) {
    data, err := c.client.Get(ctx, "user:"+strconv.Itoa(id)).Bytes()
    if err == redis.Nil {
        return nil, nil
    }
    if err != nil {
        return nil, err
    }
    var user User
    json.Unmarshal(data, &user)
    return &user, nil
}

6.2 Patterns Redis

// Cache-aside pattern
func getUserWithCache(ctx context.Context, db *DB, cache *Cache, id int) (*User, error) {
    // 1. Essayer le cache
    user, err := cache.GetUser(ctx, id)
    if err != nil {
        return nil, err
    }
    if user != nil {
        return user, nil // Cache hit
    }

    // 2. Cache miss → DB
    user, err = db.GetUser(ctx, id)
    if err != nil {
        return nil, err
    }

    // 3. Mettre en cache
    cache.SetUser(ctx, user, 5*time.Minute)
    
    return user, nil
}

// Distributed lock
func (c *Cache) AcquireLock(ctx context.Context, key string, ttl time.Duration) (bool, error) {
    return c.client.SetNX(ctx, "lock:"+key, "1", ttl).Result()
}

func (c *Cache) ReleaseLock(ctx context.Context, key string) error {
    return c.client.Del(ctx, "lock:"+key).Err()
}

7. Migrations

7.1 golang-migrate

import (
    "github.com/golang-migrate/migrate/v4"
    _ "github.com/golang-migrate/migrate/v4/database/postgres"
    _ "github.com/golang-migrate/migrate/v4/source/file"
)

func RunMigrations(dsn, migrationsPath string) error {
    m, err := migrate.New(
        "file://"+migrationsPath,
        dsn,
    )
    if err != nil {
        return err
    }

    if err := m.Up(); err != nil && err != migrate.ErrNoChange {
        return err
    }
    return nil
}

7.2 Migration files

-- migrations/000001_create_users.up.sql
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- migrations/000001_create_users.down.sql
DROP TABLE IF EXISTS users;

8. Transactions

func (db *DB) TransferMoney(ctx context.Context, fromID, toID int, amount float64) error {
    tx, err := db.BeginTx(ctx, &sql.TxOptions{
        Isolation: sql.LevelSerializable,
    })
    if err != nil {
        return err
    }
    defer tx.Rollback() // safe: no-op si déjà commité

    // Débit
    result, err := tx.ExecContext(ctx,
        `UPDATE accounts SET balance = balance - $1 WHERE id = $2 AND balance >= $1`,
        amount, fromID)
    if err != nil {
        return err
    }
    rows, _ := result.RowsAffected()
    if rows == 0 {
        return fmt.Errorf("insufficient funds")
    }

    // Crédit
    _, err = tx.ExecContext(ctx,
        `UPDATE accounts SET balance = balance + $1 WHERE id = $2`,
        amount, toID)
    if err != nil {
        return err
    }

    return tx.Commit()
}

Résumé

• database/sql : interface standard, drivers (pgx, lib/pq)
• pgx : driver PostgreSQL performant, batch, pgxpool
• sqlx : extensions (StructScan, Named, In)
• sqlc : code generation type-safe
• GORM : ORM complet (relations, hooks, preloading)
• Redis : go-redis, cache-aside, locks
• Migrations : golang-migrate, goose
• Transactions : isolation levels, rollback
• Query builders : Squirrel, goqu
• Best practices : N+1, prepared statements, connection pooling