# Migrate Module

## Overview

The Migrate module handles database schema migrations for the dataflow-service project. It ensures that the database schema is kept up-to-date with the application's requirements. This module provides functionality for both initial schema creation and incremental schema changes.

## Directory Structure

```
migrate/
├── init_schema.go         # Initial schema creation
├── init_schema_test.go    # Tests for initial schema creation
├── migrate.go             # Core migration logic
├── record_202512241430_custom_storage.go  # Migration record for custom storage
├── record_202601041800_dm_premium.go      # Migration record for DM premium features
├── record_202601061205_biz_changefeed.go  # Migration record for biz changefeed
├── record_202601081524_replay.go           # Migration record for replay functionality
├── record_202601161041_biz_private_link_endpoint_unused_at.go  # Migration record for private link endpoint
├── record_202601161450_add_extra_info.go  # Migration record for adding extra info
└── record_202601291600_add_import_conn_method_and_endpoint_service_name.go  # Migration record for import connection method
```

## Core Components

### 1. init_schema.go

Contains the initial database schema definition. This file is used to create the database schema from scratch when setting up a new environment.

### 2. migrate.go

Contains the core migration logic. This file manages the migration process, including applying migration records in the correct order and tracking the current migration state.

### 3. Migration Records

Files prefixed with `record_` contain specific database schema changes. Each file represents a single migration operation and is named with a timestamp to ensure proper ordering.

## How to Use

### Running Migrations

Migrations can be run using the `dataflow migrate` command:

```bash
./dataflow migrate --config config.yaml
```

This command will apply all pending migrations to the database.

### Automatic Migration

In most cases, the worker will automatically run migrations on startup. This ensures that the database schema is always up-to-date when the service starts.

### Adding New Migrations

To add a new migration:

1. Create a new file in the `migrate/` directory with a name following the pattern `record_<timestamp>_<description>.go`
2. Implement the migration logic in the file
3. Register the migration in the `migrations` slice in `migrate.go`

### Example: Adding a New Migration

1. Create a new file `record_202602011200_add_new_column.go`:

```go
package migrate

import (
	"context"
	"database/sql"
	"fmt"
)

func init() {
	migrations = append(migrations, &migration{
		ID:        "202602011200",
		Desc:      "Add new column to table",
		MigrateFn: addNewColumn,
	})
}

func addNewColumn(ctx context.Context, db *sql.DB) error {
	// Migration logic here
	_, err := db.ExecContext(ctx, `ALTER TABLE my_table ADD COLUMN new_column VARCHAR(255)`)
	if err != nil {
		return fmt.Errorf("failed to add new column: %w", err)
	}
	return nil
}
```

2. Run the migration command to apply the change:

```bash
./dataflow migrate --config config.yaml
```

## Best Practices

### Migration Development

- **Keep migrations small**: Each migration should focus on a single change
- **Make migrations reversible**: If possible, include rollback logic
- **Test migrations**: Test migrations in a development environment before applying to production
- **Document migrations**: Add comments to explain the purpose of each migration

### Production Considerations

- **Backup before migrating**: Always back up the database before running migrations in production
- **Schedule migrations carefully**: Plan migrations during maintenance windows
- **Monitor migrations**: Monitor the migration process to ensure it completes successfully
- **Have a rollback plan**: Be prepared to roll back if a migration fails

## Common Issues

### Migration Failed

If a migration fails, check the following:

1. **Database connection**: Ensure the database is accessible
2. **Permissions**: Ensure the database user has sufficient permissions
3. **Schema state**: Check the current state of the database schema
4. **Migration logic**: Review the migration code for errors

### Database Locked

If the database is locked during migration, try:

1. **Waiting**: The lock may be released automatically
2. **Checking active connections**: Identify and terminate any long-running queries
3. **Restarting the service**: In some cases, a service restart may resolve the issue

## Notes

- Migrations are applied in chronological order based on their timestamp
- The migration process is idempotent - running it multiple times will only apply pending migrations
- Always test migrations in a staging environment before applying to production
- Keep a record of all migrations for auditing purposes