Sqlite Patterns

migrations := []string{"001_initial", "002_add_labels"}

Query methods

All queries live on the Queries struct. Follow the verb convention:

Not found

Return nil, nil — not found is not an error:

if err == sql.ErrNoRows {
    return nil, nil
}

Transactions

tx, err := q.db.Begin()
if err != nil {
    return fmt.Errorf("begin tx: %w", err)
}
defer tx.Rollback()

// ... operations on tx ...

return tx.Commit()

Dynamic partial updates

sets := []string{}
args := []any{}

if req.Name != nil {
    sets = append(sets, "name = ?")
    args = append(args, *req.Name)
}

if len(sets) == 0 {
    return q.GetThing(id) // no changes
}

sets = append(sets, "updated_at = ?")
args = append(args, time.Now().UTC())
args = append(args, id)

query := fmt.Sprintf("UPDATE things SET %s WHERE id = ?", strings.Join(sets, ", "))

FTS5 search

When adding a searchable entity, create an FTS5 virtual table with triggers:

CREATE VIRTUAL TABLE IF NOT EXISTS things_fts USING fts5(
    name, description,
    content='things',
    content_rowid='id'
);

-- Keep FTS in sync
CREATE TRIGGER things_ai AFTER INSERT ON things BEGIN
    INSERT INTO things_fts(rowid, name, description) VALUES (new.id, new.name, new.description);
END;

CREATE TRIGGER things_ad AFTER DELETE ON things BEGIN
    INSERT INTO things_fts(things_fts, rowid, name, description) VALUES ('delete', old.id, old.name, old.description);
END;

CREATE TRIGGER things_au AFTER UPDATE ON things BEGIN
    INSERT INTO things_fts(things_fts, rowid, name, description) VALUES ('delete', old.id, old.name, old.description);
    INSERT INTO things_fts(rowid, name, description) VALUES (new.id, new.name, new.description);
END;

Row scanning

Always defer rows.Close() immediately. Check rows.Err() after the loop:

rows, err := q.db.Query(query, args...)
if err != nil {
    return nil, fmt.Errorf("list things: %w", err)
}
defer rows.Close()

var things []models.Thing
for rows.Next() {
    var t models.Thing
    if err := rows.Scan(&t.ID, &t.Name, ...); err != nil {
        return nil, fmt.Errorf("scan thing: %w", err)
    }
    things = append(things, t)
}
return things, rows.Err()

Don'ts

• No ORM — raw SQL only
• No * in SELECT — always list columns explicitly
• No interface{} for args — use []any
• Never ignore rows.Err()