forked from mirrors/pkg-proxy
51 lines
2.2 KiB
Markdown
51 lines
2.2 KiB
Markdown
|
# Database Migrations
|
||
|
|
|
||
|
|
Schema changes are tracked in a `migrations` table. Each migration has a name and a function. On startup, `MigrateSchema()` loads the set of already-applied names in one query and runs anything new.
|
||
|
|
|
||
|
|
Fresh databases created via `Create()` get the full schema and all migrations are recorded as already applied.
|
||
|
|
|
||
|
|
## Adding a migration
|
||
|
|
|
||
|
|
In `internal/database/schema.go`:
|
||
|
|
|
||
|
|
1. Write a migration function:
|
||
|
|
|
||
|
|
```go
|
||
|
|
func migrateAddWidgetColumn(db *DB) error {
|
||
|
|
hasCol, err := db.HasColumn("packages", "widget")
|
||
|
|
if err != nil {
|
||
|
|
return fmt.Errorf("checking column widget: %w", err)
|
||
|
|
}
|
||
|
|
if !hasCol {
|
||
|
|
colType := "TEXT"
|
||
|
|
if db.dialect == DialectPostgres {
|
||
|
|
colType = "TEXT" // adjust if types differ
|
||
|
|
}
|
||
|
|
if _, err := db.Exec(fmt.Sprintf("ALTER TABLE packages ADD COLUMN widget %s", colType)); err != nil {
|
||
|
|
return fmt.Errorf("adding column widget: %w", err)
|
||
|
|
}
|
||
|
|
}
|
||
|
|
return nil
|
||
|
|
}
|
||
|
|
```
|
||
|
|
|
||
|
|
2. Append it to the `migrations` slice with the next sequential prefix:
|
||
|
|
|
||
|
|
```go
|
||
|
|
var migrations = []migration{
|
||
|
|
{"001_add_packages_enrichment_columns", migrateAddPackagesEnrichmentColumns},
|
||
|
|
{"002_add_versions_enrichment_columns", migrateAddVersionsEnrichmentColumns},
|
||
|
|
{"003_ensure_artifacts_table", migrateEnsureArtifactsTable},
|
||
|
|
{"004_ensure_vulnerabilities_table", migrateEnsureVulnerabilitiesTable},
|
||
|
|
{"005_add_widget_column", migrateAddWidgetColumn}, // new
|
||
|
|
}
|
||
|
|
```
|
||
|
|
|
||
|
|
3. Add the same column to both `schemaSQLite` and `schemaPostgres` at the top of the file so fresh databases start with the full schema.
|
||
|
|
|
||
|
|
## Rules
|
||
|
|
|
||
|
|
- Migration functions must be idempotent. Use `HasColumn`/`HasTable` checks or `IF NOT EXISTS` clauses so they're safe to run against a database that already has the change.
|
||
|
|
- Handle both SQLite and Postgres dialects. Common differences: `DATETIME` vs `TIMESTAMP`, `INTEGER DEFAULT 0` vs `BOOLEAN DEFAULT FALSE`, `INTEGER PRIMARY KEY` vs `SERIAL PRIMARY KEY`.
|
||
|
|
- Never reorder or rename existing entries. The name string is the migration's identity in the database.
|
||
|
|
- Never remove old migrations from the list. They won't run on already-migrated databases, but they need to exist for older databases upgrading for the first time.
|