Cloudflare D1 Quick Guide

Connection Setup

import (
    _ "github.com/peterheb/cfd1"    // D1 driver
    _ "github.com/mattn/go-sqlite3" // Local testing
)

db, err := database.New("cfd1", "https://api.cloudflare.com/client/v4/accounts/<ACCOUNT_ID>/d1/database/<DATABASE_ID>")

Connection pool settings: - SetMaxOpenConns(25) - Max concurrent connections - SetMaxIdleConns(5) - Idle pool size - SetConnMaxLifetime(5 * time.Minute) - Reuse time - SetConnMaxIdleTime(2 * time.Minute) - Idle timeout

---

Repository Pattern

Structure

type Repository struct {
    db *sql.DB
}

func NewRepository(db *sql.DB) *Repository {
    return &Repository{db: db}
}

CREATE

func (r *Repository) Create(ctx context.Context, req *CreateRequest) (*Model, error) {
    now := time.Now()
    result, err := r.db.ExecContext(ctx,
        `INSERT INTO table (col1, col2, created_at, updated_at) VALUES (?, ?, ?, ?)`,
        req.Col1, req.Col2, now, now)
    if err != nil {
        return nil, fmt.Errorf("failed to create: %w", err)
    }

    id, _ := result.LastInsertId()
    return r.GetByID(ctx, id)
}

READ

func (r *Repository) GetByID(ctx context.Context, id int64) (*Model, error) {
    rows, err := r.db.QueryContext(ctx,
        `SELECT id, col1, col2, created_at, updated_at FROM table WHERE id = ?`, id)
    if err != nil {
        return nil, fmt.Errorf("failed to query: %w", err)
    }
    defer rows.Close()

    if !rows.Next() {
        return nil, fmt.Errorf("not found")
    }

    model := &Model{}
    err = scanModel(rows, &model.ID, &model.Col1, &model.Col2, &model.CreatedAt, &model.UpdatedAt)
    return model, err
}

UPDATE

func (r *Repository) Update(ctx context.Context, id int64, req *UpdateRequest) (*Model, error) {
    result, err := r.db.ExecContext(ctx,
        `UPDATE table SET col1 = COALESCE(NULLIF(?, ''), col1), updated_at = ? WHERE id = ?`,
        req.Col1, time.Now(), id)
    if err != nil {
        return nil, fmt.Errorf("failed to update: %w", err)
    }

    if rows, _ := result.RowsAffected(); rows == 0 {
        return nil, fmt.Errorf("not found")
    }

    return r.GetByID(ctx, id)
}

DELETE

func (r *Repository) Delete(ctx context.Context, id int64) error {
    result, err := r.db.ExecContext(ctx, `DELETE FROM table WHERE id = ?`, id)
    if err != nil {
        return fmt.Errorf("failed to delete: %w", err)
    }

    if rows, _ := result.RowsAffected(); rows == 0 {
        return fmt.Errorf("not found")
    }
    return nil
}

---

Critical: D1 Type Handling

D1 returns different types than SQLite: - Integers → float64 (not int64) - Timestamps → string (not time.Time)

Scanner Function

func scanModel(scanner interface{Scan(dest ...interface{}) error},
               id *int64, name *string, createdAt, updatedAt *time.Time) error {
    var idFloat float64  // Must use float64 for D1
    var createdAtNT, updatedAtNT NullableTime  // Custom type for timestamps

    err := scanner.Scan(&idFloat, name, &createdAtNT, &updatedAtNT)
    if err != nil {
        return err
    }

    *id = int64(idFloat)  // Convert to int64

    if createdAtNT.Valid {
        *createdAt = createdAtNT.Time
    }
    if updatedAtNT.Valid {
        *updatedAt = updatedAtNT.Time
    }

    return nil
}

NullableTime Type

type NullableTime struct {
    Time  time.Time
    Valid bool
}

func (nt *NullableTime) Scan(value interface{}) error {
    if value == nil {
        nt.Valid = false
        return nil
    }

    nt.Valid = true

    switch v := value.(type) {
    case time.Time:
        nt.Time = v
    case string:
        formats := []string{
            time.RFC3339,
            "2006-01-02 15:04:05",
            "2006-01-02T15:04:05",
        }
        for _, format := range formats {
            if t, err := time.Parse(format, v); err == nil {
                nt.Time = t
                return nil
            }
        }
        return fmt.Errorf("failed to parse: %s", v)
    default:
        return fmt.Errorf("unsupported type: %T", value)
    }
    return nil
}

---

Migrations

File Structure

migrations/
└── 001_create_users_table.sql

Examples

Migration Template

-- Migration: Brief description
-- Created: YYYY-MM-DD

CREATE TABLE IF NOT EXISTS table_name (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_table_name ON table_name(name);

Foreign Keys

-- SET NULL on delete
FOREIGN KEY (room_type_id) REFERENCES room_types(id) ON DELETE SET NULL

-- CASCADE delete
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE

Junction Table (Many-to-Many)

CREATE TABLE IF NOT EXISTS user_rooms (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    room_id INTEGER NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (room_id) REFERENCES rooms(id) ON DELETE CASCADE,
    UNIQUE(user_id, room_id)
);

CREATE INDEX IF NOT EXISTS idx_user_rooms_user_id ON user_rooms(user_id);
CREATE INDEX IF NOT EXISTS idx_user_rooms_room_id ON user_rooms(room_id);

Run Migrations

ctx := context.Background()
if err := db.MigrateFromFiles(ctx); err != nil {
    log.Fatal(err)
}

---

Best Practices Checklist and Tips

When writing repositories: - [ ] Scan integers as float64, convert to int64 - [ ] Use NullableTime for timestamps - [ ] Always defer rows.Close() - [ ] Check rows.Err() after iteration - [ ] Use COALESCE(NULLIF(?, ''), col) for partial updates - [ ] Set updated_at = NOW() on updates

When writing migrations: - [ ] Always use IF NOT EXISTS - [ ] Name files NNN_description.sql - [ ] Create indexes on foreign keys - [ ] Create indexes on WHERE/JOIN columns - [ ] Use UNIQUE constraints where appropriate