[PR #230] [MERGED] fix: rewrite migration 0009 for SQLite compatibility #2107

New Issue

GiteaMirror · 2026-05-03T03:04:37-05:00

GiteaMirror commented

2026-05-03 03:04:37 -05:00

📋 Pull Request Information

Original PR: https://github.com/RayLabsHQ/gitea-mirror/pull/230
Author: @arunavo4
Created: 3/15/2026
Status: ✅ Merged
Merged: 3/15/2026
Merged by: @arunavo4

Base: main ← Head: fix/migration-0009-sqlite-compat

📝 Commits (1)

74f0abe fix: rewrite migration 0009 for SQLite compatibility and add migration validation

📊 Changes

6 files changed (+439 additions, -25 deletions)

View changed files

📝 .github/workflows/astro-build-test.yml (+6 -0)
📝 drizzle/0009_nervous_tyger_tiger.sql (+148 -23)
📝 package.json (+1 -0)
➕ scripts/validate-migrations.ts (+212 -0)
📝 src/lib/db/index.ts (+46 -2)
➕ src/lib/db/migrations.test.ts (+26 -0)

📄 Description

Summary

Rewrites migration 0009 using SQLite's table-recreation pattern (CREATE → INSERT SELECT → DROP → RENAME) instead of ALTER TABLE ADD COLUMN ... DEFAULT (unixepoch()), which SQLite rejects for expression defaults
Adds migration validation framework (scripts/validate-migrations.ts) with SQLite-specific lint rules and upgrade-path testing with seeded data
Adds runtime repair in src/lib/db/index.ts for users who may have a stale migration record from the broken release
Adds CI step that explicitly validates all migrations on every push/PR

Context

v3.13.0 shipped with a Drizzle-kit auto-generated migration that used ALTER TABLE repositories ADD imported_at integer DEFAULT (unixepoch()) NOT NULL. SQLite rejects this because expression defaults are not allowed in ALTER TABLE ADD COLUMN. This caused all upgrades from v3.12.x to fail at startup (#228, #229).

Prevention layers

Layer	What it catches	When
SQLite lint rules	Invalid SQL patterns (expression defaults, CURRENT_TIMESTAMP in ALTER TABLE)	Before SQL executes
From-scratch execution	SQL syntax errors in the full migration chain	Validates on :memory: DB
Upgrade path + fixtures	Data corruption, missing columns, broken backfills	Tests against real seeded data
Fixture requirement	Forces developer to write upgrade tests for each new migration	Fails if fixture is missing
CI explicit step	All of the above	Every push/PR
Runtime repair	Safety net for users with stale migration records	App startup (one-time, non-fatal)

Test plan

bun test:migrations passes (lint + from-scratch + upgrade path)
bun test src/lib/db/migrations.test.ts passes
Lint rules catch both DEFAULT (expr) and DEFAULT CURRENT_TIMESTAMP in ALTER TABLE
Lint rules don't false-positive on safe patterns (CREATE TABLE, RENAME)
CI workflow runs successfully
Verify upgrade from v3.12.x database works (Docker)

Fixes #228
Fixes #229

_{🔄 This issue represents a GitHub Pull Request. It cannot be merged through Gitea due to API limitations.}

## 📋 Pull Request Information **Original PR:** https://github.com/RayLabsHQ/gitea-mirror/pull/230 **Author:** [@arunavo4](https://github.com/arunavo4) **Created:** 3/15/2026 **Status:** ✅ Merged **Merged:** 3/15/2026 **Merged by:** [@arunavo4](https://github.com/arunavo4) **Base:** `main` ← **Head:** `fix/migration-0009-sqlite-compat` --- ### 📝 Commits (1) - [`74f0abe`](https://github.com/RayLabsHQ/gitea-mirror/commit/74f0abe1fb2c0912d717c95cc416c047c98bb94b) fix: rewrite migration 0009 for SQLite compatibility and add migration validation ### 📊 Changes **6 files changed** (+439 additions, -25 deletions) <details> <summary>View changed files</summary> 📝 `.github/workflows/astro-build-test.yml` (+6 -0) 📝 `drizzle/0009_nervous_tyger_tiger.sql` (+148 -23) 📝 `package.json` (+1 -0) ➕ `scripts/validate-migrations.ts` (+212 -0) 📝 `src/lib/db/index.ts` (+46 -2) ➕ `src/lib/db/migrations.test.ts` (+26 -0) </details> ### 📄 Description ## Summary - **Rewrites migration 0009** using SQLite's table-recreation pattern (CREATE → INSERT SELECT → DROP → RENAME) instead of `ALTER TABLE ADD COLUMN ... DEFAULT (unixepoch())`, which SQLite rejects for expression defaults - **Adds migration validation framework** (`scripts/validate-migrations.ts`) with SQLite-specific lint rules and upgrade-path testing with seeded data - **Adds runtime repair** in `src/lib/db/index.ts` for users who may have a stale migration record from the broken release - **Adds CI step** that explicitly validates all migrations on every push/PR ## Context v3.13.0 shipped with a Drizzle-kit auto-generated migration that used `ALTER TABLE repositories ADD imported_at integer DEFAULT (unixepoch()) NOT NULL`. SQLite rejects this because expression defaults are not allowed in `ALTER TABLE ADD COLUMN`. This caused all upgrades from v3.12.x to fail at startup (#228, #229). ## Prevention layers | Layer | What it catches | When | |-------|----------------|------| | SQLite lint rules | Invalid SQL patterns (expression defaults, CURRENT_TIMESTAMP in ALTER TABLE) | Before SQL executes | | From-scratch execution | SQL syntax errors in the full migration chain | Validates on :memory: DB | | Upgrade path + fixtures | Data corruption, missing columns, broken backfills | Tests against real seeded data | | Fixture requirement | Forces developer to write upgrade tests for each new migration | Fails if fixture is missing | | CI explicit step | All of the above | Every push/PR | | Runtime repair | Safety net for users with stale migration records | App startup (one-time, non-fatal) | ## Test plan - [x] `bun test:migrations` passes (lint + from-scratch + upgrade path) - [x] `bun test src/lib/db/migrations.test.ts` passes - [x] Lint rules catch both `DEFAULT (expr)` and `DEFAULT CURRENT_TIMESTAMP` in ALTER TABLE - [x] Lint rules don't false-positive on safe patterns (CREATE TABLE, RENAME) - [ ] CI workflow runs successfully - [ ] Verify upgrade from v3.12.x database works (Docker) Fixes #228 Fixes #229 --- <sub>🔄 This issue represents a GitHub Pull Request. It cannot be merged through Gitea due to API limitations.</sub>

GiteaMirror added the pull-request label 2026-05-03 03:04:37 -05:00

GiteaMirror closed this issue

2026-05-03 03:04:38 -05:00

Sign in to join this conversation.

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: github-starred/gitea-mirror#2107