SyncRay SQL Database Sync

Expert guidance for working with SyncRay, a PowerShell-based SQL Server database synchronization tool that enables safe, flexible data migration with full INSERT, UPDATE, and DELETE support.

Overview

SyncRay is a transaction-safe database synchronization tool designed for SQL Server. It supports flexible record matching on any field combination, comprehensive validation, dry-run previews, and export filtering with WHERE clauses.

Project Structure

Development Instructions

1. PowerShell Compatibility Requirements

2. Error Handling Patterns

```powershell

try {

# Database operations

} catch {

Write-Host "Error: $($_.Exception.Message)" -ForegroundColor Red

throw

} finally {

# Always close connections

if ($connection) { $connection.Close() }

}

```

3. Validation Requirements

Before any database operation:

4. Safety Features to Maintain

5. Configuration Handling

**matchOn behavior:**

**Special column handling:**

6. Testing Workflow

```powershell

Test export with filtering

./src/sync-export.ps1 -From source -Tables TestTable

Test import (dry-run, default)

./src/sync-import.ps1 -To target -Tables TestTable

Execute after dry-run review

./src/sync-import.ps1 -To target -Tables TestTable -Execute

Use custom config

./src/sync-export.ps1 -From source -ConfigPath ./test-config.json

```

7. Adding New Features

When adding features, follow this order:

1. Update `sync-validation.ps1` if validation logic changes

2. Implement feature in `sync-export.ps1` or `sync-import.ps1`

3. Update `sync-config.example.json` if config schema changes

4. **Document in all four README files:**

- `README.md` (English)

- `README.de.md` (German/Deutsch)

- `README.fr.md` (French/Français)

- `README.es.md` (Spanish/Español)

5. Test with PowerShell 5.0

8. Commit Message Guidelines

**NEVER include:**

**DO include:**

**Example commit messages:**

```

Add WHERE clause filtering to export script

Allow selective data export by applying WHERE conditions

to source queries. Useful for partial migrations and testing.

```

```

Fix primary key detection for composite keys

Validation now correctly identifies multi-column primary keys

and displays them in error messages for better diagnostics.

```

9. Debugging Techniques

```powershell

Enable verbose output

$VerbosePreference = "Continue"

Check actual SQL queries in SQL Server Profiler

Use Write-Verbose for debug info (not Write-Host)

Write-Verbose "Executing query: $query"

```

10. Important Constraints

Common Scenarios

Scenario 1: Adding Export Filter Support

1. Add WHERE clause parameter to export script

2. Append WHERE condition to SELECT queries

3. Validate SQL syntax before execution

4. Update all four README files with examples

5. Test with complex WHERE clauses

Scenario 2: Improving Validation Errors

1. Modify `sync-validation.ps1` function

2. Add primary key lookup query

3. Format error message with key information

4. Test with invalid configurations

5. Update documentation if error format changes

Scenario 3: Adding New Configuration Option

1. Update example config JSON

2. Add validation logic for new option

3. Implement feature behavior

4. Update all README files

5. Test with both presence and absence of option

Key Reminders