Claude Code skill for beads SQLite → Dolt migration (steveyegge/beads#2096)

README.md

beads-dolt-upgrade (Claude Code Skill)

A Claude Code skill that automates the beads SQLite → Dolt server migration for v0.56+. Handles Dolt server setup, per-repo migration, and JSONL import (working around the broken bd init --from-jsonl).

See steveyegge/beads#2096 for context.

Install

SKILL_DIR="${HOME}/.claude/skills/beads-dolt-upgrade"
mkdir -p "${SKILL_DIR}/scripts"
cd "${SKILL_DIR}"
GIST="https://gist.githubusercontent.com/jtsternberg/9bf7de05b421180a475063f7e90a4f5f/raw"
curl -fsSL "${GIST}/SKILL.md" -o SKILL.md
curl -fsSL "${GIST}/GOTCHAS.md" -o GOTCHAS.md
curl -fsSL "${GIST}/jsonl-to-dolt.py" -o scripts/jsonl-to-dolt.py
chmod +x scripts/jsonl-to-dolt.py
echo "Installed to ${SKILL_DIR}"

What's included

File	Purpose
SKILL.md	Main migration procedure (auto-triggered by Dolt errors)
GOTCHAS.md	Known pitfalls reference (loaded on demand)
scripts/jsonl-to-dolt.py	Batch import script for issues + dependencies

Standalone script

If you just need the import script without the full skill:

curl -fsSL https://gist.githubusercontent.com/jtsternberg/0d6ec3c0852c15c99b66a953045c099a/raw/jsonl-to-dolt.py -o jsonl-to-dolt.py
chmod +x jsonl-to-dolt.py
python3 jsonl-to-dolt.py <jsonl_path> <database_name> --port 3308

GOTCHAS.md

Migration Gotchas

Known pitfalls discovered during real migrations. Read this if any step fails unexpectedly.

bd init --from-jsonl is broken (v0.56.1)

Reports success but imports 0 issues. Filed as #2096. Use the bundled scripts/jsonl-to-dolt.py instead.

bd init refuses if beads.db exists

The old SQLite file triggers "This workspace is already initialized" even after removing .beads/dolt. You must rm -rf .beads entirely, not just the dolt subdirectory.

rm -rf .beads destroys the JSONL

The JSONL lives at .beads/issues.jsonl. If you remove .beads without backing up first, your issue data is gone. Always back up to /tmp/ before deleting.

bd migrate --to-dolt doesn't work (v0.56.1)

The --to-dolt flag references embedded Dolt mode, which was removed in v0.56.1. The command errors with "Dolt backend requires CGO" but this is a dead end — embedded mode no longer exists regardless of how the binary was built. v0.56.1 is server-only by design. Do not attempt to rebuild with CGO; use the server-based migration in SKILL.md instead.

Dolt issues table has NOT NULL fields without defaults

The design, acceptance_criteria, and notes columns are NOT NULL with no default value. JSONL data doesn't include these fields, so naive SQL INSERTs fail with:

Error 1105 (HY000): Field 'design' doesn't have a default value

The import script handles this by inserting empty strings for these columns.

dolt sql requires specific flags for non-interactive use

Three flags are needed to connect to the server non-interactively:

DOLT_CLI_PASSWORD="" dolt --host 127.0.0.1 --port 3308 --user root --no-tls sql -b < file.sql

• DOLT_CLI_PASSWORD="" — Without this, dolt prompts for password and hangs
• --no-tls — Without this, fails with "TLS requested but server does not support TLS"
• -b — Batch mode for multi-statement SQL files
• --host, --port, --user are global flags (before sql subcommand), not sql flags

bd sql only accepts single statements

Cannot pipe multi-statement SQL files through bd sql. For bulk imports, use dolt sql directly with the server connection flags above.

Socket warning during backup

cp -R .beads warns about bd.sock being a socket. This is harmless — the socket is for the daemon and gets recreated.

Database name format

The Dolt database name follows the pattern beads_<issue-prefix>. Find it with:

bd sql "SHOW DATABASES" 2>&1 | grep beads

Or check .beads/metadata.json for the dolt_database key.

Federation warnings are normal

After migration, bd doctor may show federation errors like:

Error 1045 (28000): Access denied for user 'root'@'192.168.65.1'

These point to Docker's internal IP, not the local server. They're non-critical and relate to remote sync configuration.

jsonl-to-dolt.py

#!/usr/bin/env python3
"""
Import beads issues from a JSONL file into a Dolt database via SQL.

Usage:
    python3 scripts/jsonl-to-dolt.py <jsonl_path> <database_name> [--host HOST] [--port PORT] [--user USER]

Requirements:
    - dolt CLI installed (brew install dolt)
    - Dolt server running on specified host:port

The script:
    1. Reads issues from the JSONL file
    2. Generates SQL INSERT statements with all required NOT NULL fields
    3. Imports dependencies from the same JSONL
    4. Pipes everything through `dolt sql` connected to the server
    5. Reports counts for verification
"""

import json
import subprocess
import sys
import tempfile
import os


def esc(v):
    """Escape a value for SQL insertion."""
    if v is None:
        return "NULL"
    return "'" + str(v).replace("\\", "\\\\").replace("'", "''") + "'"


def truncate_datetime(v):
    """Extract YYYY-MM-DDTHH:MM:SS from an ISO datetime string."""
    if not v:
        return None
    return v[:19]


def generate_sql(jsonl_path, database_name):
    """Generate SQL statements from a JSONL file. Returns (sql_string, issue_count, dep_count)."""
    with open(jsonl_path) as f:
        lines = f.readlines()

    statements = [f"USE `{database_name}`;"]
    issue_count = 0
    dep_count = 0

    for line in lines:
        line = line.strip()
        if not line:
            continue
        d = json.loads(line)

        # Insert issue with all NOT NULL fields defaulted
        sql = (
            f"INSERT IGNORE INTO issues "
            f"(id, title, description, design, acceptance_criteria, notes, "
            f"status, priority, issue_type, owner, created_at, created_by, "
            f"updated_at, closed_at, close_reason) VALUES ("
            f"{esc(d.get('id'))}, "
            f"{esc(d.get('title'))}, "
            f"{esc(d.get('description', ''))}, "
            f"'', '', '', "
            f"{esc(d.get('status'))}, "
            f"{d.get('priority', 2)}, "
            f"{esc(d.get('issue_type', 'task'))}, "
            f"{esc(d.get('owner', ''))}, "
            f"{esc(truncate_datetime(d.get('created_at', '')))}, "
            f"{esc(d.get('created_by', ''))}, "
            f"{esc(truncate_datetime(d.get('updated_at', '')))}, "
            f"{esc(truncate_datetime(d.get('closed_at')))}, "
            f"{esc(d.get('close_reason', ''))}"
            f");"
        )
        statements.append(sql)
        issue_count += 1

        # Insert dependencies
        for dep in d.get("dependencies", []):
            dep_sql = (
                f"INSERT IGNORE INTO dependencies "
                f"(issue_id, depends_on_id, type, created_at, created_by) VALUES ("
                f"{esc(dep.get('issue_id'))}, "
                f"{esc(dep.get('depends_on_id'))}, "
                f"{esc(dep.get('type', 'blocks'))}, "
                f"{esc(truncate_datetime(dep.get('created_at', '')))}, "
                f"{esc(dep.get('created_by', ''))}"
                f");"
            )
            statements.append(dep_sql)
            dep_count += 1

    return "\n".join(statements), issue_count, dep_count


def main():
    if len(sys.argv) < 3:
        print(f"Usage: {sys.argv[0]} <jsonl_path> <database_name> [--host HOST] [--port PORT] [--user USER]")
        print(f"Example: {sys.argv[0]} /tmp/backup/issues.jsonl beads_my-project --port 3308")
        sys.exit(1)

    jsonl_path = sys.argv[1]
    database_name = sys.argv[2]

    # Parse optional flags
    host = "127.0.0.1"
    port = "3308"
    user = "root"
    i = 3
    while i < len(sys.argv):
        if sys.argv[i] == "--host" and i + 1 < len(sys.argv):
            host = sys.argv[i + 1]
            i += 2
        elif sys.argv[i] == "--port" and i + 1 < len(sys.argv):
            port = sys.argv[i + 1]
            i += 2
        elif sys.argv[i] == "--user" and i + 1 < len(sys.argv):
            user = sys.argv[i + 1]
            i += 2
        else:
            print(f"Unknown argument: {sys.argv[i]}")
            sys.exit(1)

    if not os.path.exists(jsonl_path):
        print(f"Error: JSONL file not found: {jsonl_path}")
        sys.exit(1)

    print(f"Generating SQL from {jsonl_path}...")
    sql, issue_count, dep_count = generate_sql(jsonl_path, database_name)

    print(f"Generated {issue_count} issue INSERTs and {dep_count} dependency INSERTs")

    # Write SQL to temp file
    with tempfile.NamedTemporaryFile(mode="w", suffix=".sql", delete=False) as f:
        f.write(sql)
        sql_path = f.name

    try:
        print(f"Importing via dolt sql (server {host}:{port})...")
        env = os.environ.copy()
        env["DOLT_CLI_PASSWORD"] = ""
        result = subprocess.run(
            ["dolt", "--host", host, "--port", port, "--user", user, "--no-tls", "sql", "-b"],
            stdin=open(sql_path),
            capture_output=True,
            text=True,
            env=env,
        )

        if result.returncode != 0:
            print(f"Error during import:\n{result.stderr}")
            # Show the first error line for debugging
            for line in result.stderr.split("\n"):
                if "Error" in line:
                    print(f"  -> {line.strip()}")
                    break
            sys.exit(1)

        print(f"Import complete: {issue_count} issues, {dep_count} dependencies")
    finally:
        os.unlink(sql_path)


if __name__ == "__main__":
    main()

SKILL.md

name	description
beads-dolt-upgrade	MANDATORY: Invoke this skill IMMEDIATELY when ANY `bd` command fails with Dolt errors. Triggers: 'Dolt backend configured but database not found', 'Error 1045 (28000): Access denied', 'Unable to open database', 'Failed to open database', 'failed to create database', 'failed to connect to dolt server'. Do NOT attempt manual fixes.

Beads Dolt Upgrade

Beads v0.56+ uses Dolt (git-for-databases MySQL server) instead of SQLite. Server mode is mandatory — v0.56.1 intentionally removed embedded Dolt mode (see changelog: "remove embedded Dolt mode from init and main command paths", "drop dolthub/driver dependency — binary size 168MB → 41MB"). A running dolt sql-server is required; there is no embedded/CGO alternative.

Do NOT downgrade to v0.55.4 (Homebrew's current version) to get embedded mode back — beads is moving server-only.

Procedure

Phase 1: Dolt Server (One-Time)

Check if already running:

which dolt && lsof -i :3308

If both succeed, skip to Phase 2.

1.1 Install and Configure

brew install dolt
mkdir -p ~/dolt-data
lsof -i :3308  # Verify port is free; try 3309, 3310 if occupied

1.2 Create LaunchAgent

Write to ~/Library/LaunchAgents/com.dolt.sql-server.plist:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
<plist version="1.0">
<dict>
    <key>Label</key>
    <string>com.dolt.sql-server</string>
    <key>ProgramArguments</key>
    <array>
        <string>/opt/homebrew/bin/dolt</string>
        <string>sql-server</string>
        <string>--host</string>
        <string>127.0.0.1</string>
        <string>--port</string>
        <string>3308</string>
    </array>
    <key>WorkingDirectory</key>
    <string>/Users/JT/dolt-data</string>
    <key>RunAtLoad</key>
    <true/>
    <key>KeepAlive</key>
    <true/>
    <key>StandardOutPath</key>
    <string>/Users/JT/dolt-data/dolt-server.log</string>
    <key>StandardErrorPath</key>
    <string>/Users/JT/dolt-data/dolt-server.log</string>
</dict>
</plist>

Adjust /opt/homebrew/bin/dolt to /usr/local/bin/dolt on Intel Macs.

1.3 Start and Verify

launchctl load ~/Library/LaunchAgents/com.dolt.sql-server.plist
sleep 2
lsof -i :3308  # Should show dolt process

Phase 2: Per-Repo Migration

2.1 Back Up

BACKUP_DIR="/tmp/beads-backup-$(basename $(pwd))-$(date +%Y%m%d-%H%M%S)"
cp -R .beads "$BACKUP_DIR"
echo "Backup: $BACKUP_DIR"

Verify JSONL exists (source of truth for issues):

ls -la "$BACKUP_DIR"/issues.jsonl 2>/dev/null

Also back up worktree beads:

for wt in .git/beads-worktrees/*/; do
  [ -d "${wt}.beads" ] && cp -R "${wt}.beads" "${BACKUP_DIR}-wt-$(basename $wt)"
done

2.2 Fresh Init

Remove .beads entirely and reinitialize:

rm -rf .beads
bd init --server-host 127.0.0.1 --server-port 3308

This creates an empty Dolt database. bd doctor should show 0 errors in CORE SYSTEM.

2.3 Import Issues from JSONL

CRITICAL: bd init --from-jsonl is broken in v0.56.1 — it silently produces 0 issues (#2096). Use the bundled import script instead.

Run the import script with the backup JSONL:

python3 <SKILL_DIR>/scripts/jsonl-to-dolt.py "$BACKUP_DIR/issues.jsonl" "beads_$(basename $(pwd))" --port 3308

Where <SKILL_DIR> is the path to this skill's directory. The database name follows the pattern beads_<prefix> — check with:

bd sql "SHOW DATABASES" 2>&1 | grep beads

Commit the import to Dolt history:

bd dolt commit -m "Import issues from JSONL backup"

2.4 Verify

bd doctor    # Expect 0 errors in CORE SYSTEM
bd stats     # Issue counts should match backup

Compare counts:

BACKUP_COUNT=$(grep -c '"id"' "$BACKUP_DIR/issues.jsonl" 2>/dev/null || echo "unknown")
echo "Backup: $BACKUP_COUNT issues | Migrated: $(bd count 2>/dev/null || echo 'check bd stats')"

If counts don't match:

# Restore and retry: rm -rf .beads && cp -R "$BACKUP_DIR" .beads

2.5 Cleanup

Do NOT auto-delete backups. Report location to user:

echo "Backup at: $BACKUP_DIR — remove when confident: rm -rf $BACKUP_DIR"

Troubleshooting

Port Conflict

lsof -i :3308

Pick a different port and update the LaunchAgent plist.

"Access denied" After Migration

bd doctor --verbose 2>&1 | grep -i "server"

Ensure connection shows 127.0.0.1:3308, not Docker host IP.

LaunchAgent Not Starting After Reboot

launchctl list | grep dolt
launchctl load ~/Library/LaunchAgents/com.dolt.sql-server.plist

Federation Warnings

Federation errors pointing to 192.168.65.x are non-critical — they relate to remote sync, not local database.

Connecting to Dolt Directly

Use global flags (before subcommand) with empty password:

DOLT_CLI_PASSWORD="" dolt --host 127.0.0.1 --port 3308 --user root --no-tls sql -q "SHOW DATABASES;"

Something else failing?

See GOTCHAS.md for a comprehensive list of pitfalls: broken --from-jsonl flag, NOT NULL column defaults, bd migrate --to-dolt requiring CGO, dolt CLI password prompts, and more.