1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
|
# SQL Migrations Implementation Plan
> **For agentic workers:** REQUIRED SUB-SKILL: Use superpowers:subagent-driven-development (recommended) or superpowers:executing-plans to implement this plan task-by-task. Steps use checkbox (`- [ ]`) syntax for tracking.
**Goal:** Replace the inline `CREATE TABLE IF NOT EXISTS` block in `internal/db/db.go` with versioned SQL files managed by `golang-migrate/migrate`, applied automatically at startup.
**Architecture:** Migration files live in `internal/db/migrations/` and are embedded into the binary via `embed.FS`. `db.Open` calls `migrate.Up()` after opening the database, ensuring the schema is always current before any caller uses `*DB`. golang-migrate tracks applied versions in a `schema_migrations` table it manages automatically.
**Tech Stack:** `github.com/golang-migrate/migrate/v4`, `database/sqlite` (modernc.org/sqlite adapter), `source/iofs` (embed.FS source)
---
## File Map
| File | Action | Purpose |
|---|---|---|
| `internal/db/migrations/000001_init.up.sql` | Create | Full current schema |
| `internal/db/migrations/000001_init.down.sql` | Create | Drop all tables in reverse order |
| `internal/db/db.go` | Modify | Add `//go:embed`, wire up migrate, remove inline `Exec` block |
| `go.mod` / `go.sum` | Modify | Add golang-migrate packages |
---
### Task 1: Add golang-migrate dependency
**Files:**
- Modify: `go.mod`, `go.sum`
- [ ] **Step 1: Fetch the packages**
```bash
go get github.com/golang-migrate/migrate/v4
go get github.com/golang-migrate/migrate/v4/database/sqlite
go get github.com/golang-migrate/migrate/v4/source/iofs
```
- [ ] **Step 2: Verify**
```bash
go list -m github.com/golang-migrate/migrate/v4
```
Expected output: `github.com/golang-migrate/migrate/v4 v4.x.x`
- [ ] **Step 3: Commit**
```bash
git add go.mod go.sum
git commit -m "chore: add golang-migrate dependency"
```
---
### Task 2: Create migration SQL files
**Files:**
- Create: `internal/db/migrations/000001_init.up.sql`
- Create: `internal/db/migrations/000001_init.down.sql`
- [ ] **Step 1: Create the up migration**
`internal/db/migrations/000001_init.up.sql`:
```sql
CREATE TABLE pages (
id INTEGER PRIMARY KEY AUTOINCREMENT,
path TEXT NOT NULL UNIQUE,
html_path TEXT NOT NULL,
title TEXT NOT NULL DEFAULT '',
date TEXT DEFAULT '',
tags TEXT DEFAULT '[]',
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_pages_path ON pages(path);
CREATE INDEX idx_pages_date ON pages(date);
CREATE TABLE posts (
id INTEGER PRIMARY KEY AUTOINCREMENT,
slug TEXT NOT NULL UNIQUE,
title TEXT NOT NULL DEFAULT '',
date TEXT DEFAULT '',
tags TEXT DEFAULT '[]',
draft INTEGER NOT NULL DEFAULT 0,
blocks TEXT NOT NULL DEFAULT '[]',
updated_at INTEGER NOT NULL DEFAULT (cast(strftime('%s','now') * 1000000 as integer))
);
CREATE INDEX idx_posts_slug ON posts(slug);
CREATE INDEX idx_posts_date ON posts(date);
CREATE TABLE redirects (
from_slug TEXT PRIMARY KEY,
to_slug TEXT NOT NULL
);
CREATE TABLE settings (
key TEXT PRIMARY KEY,
value TEXT NOT NULL DEFAULT ''
);
CREATE VIRTUAL TABLE pages_fts USING fts5(
path UNINDEXED,
title,
content,
tokenize = 'porter unicode61'
);
```
Note: `IF NOT EXISTS` is intentionally absent — golang-migrate guarantees each migration runs exactly once.
- [ ] **Step 2: Create the down migration**
`internal/db/migrations/000001_init.down.sql`:
```sql
DROP TABLE IF EXISTS pages_fts;
DROP TABLE IF EXISTS settings;
DROP TABLE IF EXISTS redirects;
DROP TABLE IF EXISTS posts;
DROP TABLE IF EXISTS pages;
```
- [ ] **Step 3: Commit**
```bash
git add internal/db/migrations/
git commit -m "feat: add initial schema migration files"
```
---
### Task 3: Wire golang-migrate into db.Open
**Files:**
- Modify: `internal/db/db.go`
- [ ] **Step 1: Run existing tests to confirm baseline**
```bash
go test ./internal/db/... -v
```
Expected: all tests pass.
- [ ] **Step 2: Replace db.go**
Full new content of `internal/db/db.go`:
```go
package db
import (
"database/sql"
"embed"
"github.com/golang-migrate/migrate/v4"
sqlitedriver "github.com/golang-migrate/migrate/v4/database/sqlite"
"github.com/golang-migrate/migrate/v4/source/iofs"
_ "modernc.org/sqlite"
)
//go:embed migrations
var migrationsFS embed.FS
type DB struct {
db *sql.DB
}
func Open(path string) (*DB, error) {
sqldb, err := sql.Open("sqlite", path)
if err != nil {
return nil, err
}
// SQLite works best with a single writer; also ensures in-memory DBs
// (used in tests) share one connection across the migrate driver and
// all application queries.
sqldb.SetMaxOpenConns(1)
if err := runMigrations(sqldb); err != nil {
sqldb.Close()
return nil, err
}
return &DB{db: sqldb}, nil
}
func runMigrations(sqldb *sql.DB) error {
src, err := iofs.New(migrationsFS, "migrations")
if err != nil {
return err
}
driver, err := sqlitedriver.WithInstance(sqldb, &sqlitedriver.Config{})
if err != nil {
return err
}
m, err := migrate.NewWithInstance("iofs", src, "sqlite", driver)
if err != nil {
return err
}
if err := m.Up(); err != nil && err != migrate.ErrNoChange {
return err
}
return nil
}
func (d *DB) Close() error { return d.db.Close() }
// RawDB returns the underlying *sql.DB for advanced queries.
func (d *DB) RawDB() *sql.DB { return d.db }
```
- [ ] **Step 3: Run tests**
```bash
go test ./internal/db/... -v
```
Expected: all tests pass (the `openTestDB` helper uses `:memory:`, `SetMaxOpenConns(1)` ensures the migrate driver and app queries share the same connection).
- [ ] **Step 4: Build to confirm no compile errors**
```bash
go build ./cmd/iblog
```
Expected: exits with code 0, no output.
- [ ] **Step 5: Commit**
```bash
git add internal/db/db.go
git commit -m "feat: wire golang-migrate into db.Open, remove inline schema"
```
|
