Adding a single column to SQLite forced me to rewrite every method

Introduction

Previously, I wrote about design decisions when using SQLite for personal development.

In that article, I claimed that "migrations are easy with AddColumnIfNotExists."

That was a lie. Adding just one column required modifications in 10 different places.

Furthermore, this wasn't because "the implementation was sloppy."

It is structurally designed that way.

---

What I Wanted to Do

I wanted to add an automatic archiving feature to the task management tool "tsk."

Complete a task → Automatically archive after 3 days

That's it. Simple.

---

The Problem: Not Knowing "When It Was Completed"

The existing table only had IsCompleted (0 or 1).

I know "whether it is completed," but I don't know "when it was completed."

To determine "3 days after completion," I need a completion date/time.

→ Add a CompletedAt TEXT column.

*Note: It is a TEXT column because storing it in yyyy-MM-dd HH:mm:ss format allows for chronological sorting via string comparison.

---

Step 1: Migration (This part is easy)

private void EnsureCompletedAtColumn(SqliteConnection conn)
{
    var cmd = conn.CreateCommand();
    cmd.CommandText = "PRAGMA table_info(Tasks);";

    bool exists = false;
    using var reader = cmd.ExecuteReader();
    while (reader.Read())
    {
        if (reader.GetString(1) == "CompletedAt")
        {
            exists = true;
            break;
        }
    }

    if (!exists)
    {
        var alter = conn.CreateCommand();
        alter.CommandText = "ALTER TABLE Tasks ADD COLUMN CompletedAt TEXT;";
        alter.ExecuteNonQuery();
    }
}

Call this from InitializeDatabase().

EnsureViewIdColumn(conn);
FixViewIdData(conn);
EnsureCompletedAtColumn(conn);

That took 5 minutes. The problem starts here.

---

Step 2: Modifying ToggleCompleted

When a task is marked as completed, record the CompletedAt time, and clear it when marked as incomplete.

The impact of adding the column starts to spread here.

// Get current state
var getCmd = conn.CreateCommand();
getCmd.CommandText = "SELECT IsCompleted FROM Tasks WHERE Id = $id";
getCmd.Parameters.AddWithValue("$id", id);
bool isCompleted = Convert.ToInt32(getCmd.ExecuteScalar()) == 1;

var cmd = conn.CreateCommand();
if (!isCompleted)
{
    cmd.CommandText = @"
        UPDATE Tasks SET IsCompleted = 1, CompletedAt = $now
        WHERE Id = $id";
    cmd.Parameters.AddWithValue("$now",
        DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
}
else
{
    cmd.CommandText = @"
        UPDATE Tasks SET IsCompleted = 0, CompletedAt = NULL
        WHERE Id = $id";
}
cmd.Parameters.AddWithValue("$id", id);
cmd.ExecuteNonQuery();

It can no longer be done with a single UPDATE statement.

---

Step 3: The Descent into Hell

When you add a column, every piece of code that references the table is affected.

Method	Modification
GetAllTasks	Modify SELECT statement
GetArchivedTasks	Modify SELECT statement
GetAllTasksAllView	Modify SELECT statement
ReadTask	Add reading logic
BindTaskParams	Add parameters
Insert	Modify SQL
Update	Modify SQL
ReInsert	Modify SQL
TaskItem	Add property
Clone	Add copy logic

Result: 1 column added → 10 locations to modify

---

Step 4: Correcting SELECT statements

The most important thing here is to "keep the order identical for all."

SELECT Id, Title, Category, DueDate, IsCompleted, CreatedAt,
       SortOrder, RepeatRule, Note, Tag, ViewId, CompletedAt
FROM Tasks

If even one is different, it will break.

---

Step 5: The Index Landmine

CompletedAt = reader.IsDBNull(11) ? null : reader.GetString(11)

Index dependency.

• Compiles successfully
• Breaks at runtime
• Hard to notice

A bug that cannot be statically detected.

---

Step 6: Why does this happen?

The cause is simple.

Because the DB schema and the code are tightly coupled.

• Reading DataReader by index
• Writing raw SQL in various places

→ Changes become scattered.

---

Step 7: How to avoid this (Briefly)

For example:

• Use column names to read (GetOrdinal)
• Consolidate Mappers into one place
• Use libraries like Dapper

Doing this will reduce the impact scope.

However, in exchange, the cost of the abstraction layer increases.

I deliberately didn't do this here.

---

Finally, AutoArchive

public int AutoArchive(int days)
{
    if (days <= 0) return 0;

    DateTime cutoff = DateTime.Today.AddDays(-days);
    string cutoffStr = cutoff.ToString("yyyy-MM-dd HH:mm:ss");

    using var conn = new SqliteConnection(_connectionString);
    conn.Open();

    var cmd = conn.CreateCommand();
    cmd.CommandText = @"
        UPDATE Tasks SET IsArchived = 1
        WHERE IsCompleted = 1
          AND IsArchived = 0
          AND CompletedAt IS NOT NULL
          AND CompletedAt <= $cutoff";
    cmd.Parameters.AddWithValue("$cutoff", cutoffStr);

    return cmd.ExecuteNonQuery();
}

Finally, the main topic.

---

Lessons Learned

① ALTER TABLE is just the starting point

② Standardize your SELECT statements

③ Index dependencies are dangerous

④ Always consider NULLs

⑤ Don't forget to update Clone()

---

Summary

Adding 1 column → Ripple effects across the whole application

This isn't a problem with SQLite.

It is a problem of "what structure the code was written in."

---

Download

GitHub

https://github.com/moritan777/tsk-releases/releases/latest

Download and extract tsk_v111.zip → Start with tsk.exe

---

Author: mitsukida
Contact: [e-mail]

🔗 Related Articles

• 🧩 I made a tool for task management with a black background and green text for a hacker feel
• 🧲 Snapping windows to screen edges in WPF (Magnet Snap)
• 📅 I created a parser that interprets natural Japanese input for dates like "Fri Review" for this Friday
• 🧩 Terminal-style task management tool, View feature added (v1.1.1)
• 🖥 Techniques for seriously creating a terminal-like UI with black background and green text in WPF
• 🌀 A story about forgetting to call RenderDisplay and breaking everything (WPF)