> ## Documentation Index
> Fetch the complete documentation index at: https://sonamu.cartanova.ai/llms.txt
> Use this file to discover all available pages before exploring further.

# Migration Errors

> Troubleshooting migration issues

This covers common database migration-related problems in Sonamu and how to resolve them.

## Checksum File Parsing Error

### Symptoms

```bash theme={null}
pnpm sonamu sync

or

pnpm dev
```

Running these causes the following error:

```bash theme={null}
SyntaxError: Unexpected token } in JSON at position 1
    at JSON.parse (<anonymous>)
    at getPreviousChecksums (checksum.ts:109)
```

### Cause

The `sonamu.lock` file is corrupted. Common causes:

1. Sync interrupted (Ctrl+C) causing incomplete file save
2. File is empty or has invalid JSON format
3. Multiple processes modified the file simultaneously

### Solutions

#### 1. Force Re-sync (Recommended)

```bash theme={null}
pnpm sonamu sync --force
```

The `--force` flag removes the existing `sonamu.lock` and performs a full sync from scratch. This is safe and brings every tracked asset back into a consistent state.

#### 2. Check File Contents

```bash theme={null}
cat sonamu.lock
```

If the file is empty or contains only `{}`, delete and regenerate.

#### 3. Prevention

* Wait for sync to complete before interrupting
* Don't run `pnpm dev` in multiple terminals simultaneously

## Migration File Conflict

### Symptoms

```bash theme={null}
Error: Migration files conflict:
  - 20240115_create_users.ts
  - 20240115_add_email_column.ts
Both have timestamp: 20240115
```

### Cause

Multiple migrations created on the same day have duplicate timestamps.

### Solutions

#### 1. Manually Rename Files

```bash theme={null}
# In migrations directory
mv 20240115_add_email_column.ts 20240115_001_add_email_column.ts
mv 20240115_create_users.ts 20240115_002_create_users.ts
```

#### 2. Use Timestamp Format

Sonamu supports `YYYYMMDD_HHMMSS` format:

```bash theme={null}
pnpm sonamu migrate create add_email_column
# Creates: 20240115_143022_add_email_column.ts
```

## Generated Column Error

### Symptoms

```bash theme={null}
Error: column "full_name" cannot be cast automatically to type text
HINT: You might need to specify "USING full_name::text"
```

### Cause

This occurs when trying to modify a PostgreSQL Generated Column. Generated Columns are automatically computed from other column values and cannot be modified directly.

### Solutions

#### 1. Drop and Recreate Generated Column

```typescript theme={null}
// migrations/20240115_modify_generated_column.ts
import { Knex } from "knex";

export async function up(knex: Knex): Promise<void> {
  await knex.schema.alterTable("users", (table) => {
    // Drop Generated Column
    table.dropColumn("full_name");
  });

  await knex.schema.alterTable("users", (table) => {
    // Recreate with new definition
    table.specificType(
      "full_name",
      "TEXT GENERATED ALWAYS AS (first_name || ' ' || last_name) STORED",
    );
  });
}

export async function down(knex: Knex): Promise<void> {
  await knex.schema.alterTable("users", (table) => {
    table.dropColumn("full_name");
  });

  // Restore previous definition
  await knex.schema.alterTable("users", (table) => {
    table.specificType("full_name", "TEXT GENERATED ALWAYS AS (first_name || last_name) STORED");
  });
}
```

#### 2. Modify entity.json

```json theme={null}
{
  "columns": {
    "full_name": {
      "type": "string",
      "generated": "ALWAYS AS (first_name || ' ' || last_name) STORED",
      "nullable": true
    }
  }
}
```

## Timestamp Precision Error

### Symptoms

```bash theme={null}
column "created_at" is of type timestamp without time zone but default
expression is of type timestamp with time zone
```

### Cause

Precision for PostgreSQL `timestamp` type is not specified, causing mismatch with default value.

### Solutions

#### 1. Specify Precision in entity.json

```json theme={null}
{
  "columns": {
    "created_at": {
      "type": "datetime",
      "precision": 6,
      "nullable": false,
      "default": "CURRENT_TIMESTAMP(6)"
    },
    "updated_at": {
      "type": "datetime",
      "precision": 6,
      "nullable": false,
      "default": "CURRENT_TIMESTAMP(6)"
    }
  }
}
```

#### 2. Fix in Migration File

```typescript theme={null}
export async function up(knex: Knex): Promise<void> {
  await knex.schema.alterTable("users", (table) => {
    table
      .specificType("created_at", "TIMESTAMP(6)")
      .notNullable()
      .defaultTo(knex.raw("CURRENT_TIMESTAMP(6)"));
    table
      .specificType("updated_at", "TIMESTAMP(6)")
      .notNullable()
      .defaultTo(knex.raw("CURRENT_TIMESTAMP(6)"));
  });
}
```

## Foreign Key Constraint Error

### Symptoms

```bash theme={null}
Error: update or delete on table "users" violates foreign key constraint
"posts_user_id_foreign" on table "posts"
```

### Cause

Attempted to delete or modify a record that is being referenced.

### Solutions

#### 1. Use CASCADE Option

```typescript theme={null}
// entity.json
{
  "props": {
    "user_id": {
      "type": "id",
      "refer": "User",
      "onDelete": "CASCADE",  // Delete children when parent is deleted
      "onUpdate": "CASCADE"   // Update children when parent is updated
    }
  }
}
```

#### 2. Fix with Migration

```typescript theme={null}
export async function up(knex: Knex): Promise<void> {
  // Remove existing foreign key
  await knex.schema.alterTable("posts", (table) => {
    table.dropForeign(["user_id"]);
  });

  // Recreate with CASCADE option
  await knex.schema.alterTable("posts", (table) => {
    table
      .foreign("user_id")
      .references("id")
      .inTable("users")
      .onDelete("CASCADE")
      .onUpdate("CASCADE");
  });
}
```

## Migration Rollback Failure

### Symptoms

```bash theme={null}
pnpm sonamu migrate rollback

Error: Migration 20240115_create_users.ts has no down() function
```

### Cause

Migration file is missing or has improperly implemented `down()` function.

### Solutions

#### 1. Implement down() Function

```typescript theme={null}
export async function up(knex: Knex): Promise<void> {
  await knex.schema.createTable("users", (table) => {
    table.bigIncrements("id").primary();
    table.string("email").notNullable().unique();
    table.string("name").notNullable();
    table.timestamps(true, true);
  });
}

export async function down(knex: Knex): Promise<void> {
  await knex.schema.dropTableIfExists("users");
}
```

#### 2. Complex Migration Rollback

```typescript theme={null}
export async function up(knex: Knex): Promise<void> {
  // Add column
  await knex.schema.alterTable("users", (table) => {
    table.string("phone").nullable();
  });

  // Migrate data
  await knex.raw(`
    UPDATE users
    SET phone = contact_number
    WHERE contact_number IS NOT NULL
  `);

  // Drop old column
  await knex.schema.alterTable("users", (table) => {
    table.dropColumn("contact_number");
  });
}

export async function down(knex: Knex): Promise<void> {
  // Restore old column
  await knex.schema.alterTable("users", (table) => {
    table.string("contact_number").nullable();
  });

  // Restore data
  await knex.raw(`
    UPDATE users
    SET contact_number = phone
    WHERE phone IS NOT NULL
  `);

  // Drop new column
  await knex.schema.alterTable("users", (table) => {
    table.dropColumn("phone");
  });
}
```

## Migration State Mismatch

### Symptoms

```bash theme={null}
pnpm sonamu migrate status

Error: Migration table is corrupted
```

Or

```bash theme={null}
Migration 20240115_create_users.ts was applied but file is missing
```

### Causes

1. Migration file was deleted but record remains in DB
2. Migrations were applied differently across environments

### Solutions

#### 1. Check Migration Table

```sql theme={null}
SELECT * FROM knex_migrations ORDER BY migration_time;
```

#### 2. Remove Invalid Records

```sql theme={null}
-- Remove specific migration
DELETE FROM knex_migrations
WHERE name = '20240115_create_users.ts';

-- Or truncate table (caution!)
TRUNCATE knex_migrations;
```

#### 3. Re-apply Migrations

```bash theme={null}
# After truncating table
pnpm sonamu migrate latest
```

## Migrating from MySQL to PostgreSQL

### Symptoms

Various errors when converting existing MySQL-based projects to PostgreSQL

### Solutions

#### 1. Change Data Types

```typescript theme={null}
// MySQL
table.integer("status").unsigned();

// PostgreSQL
table.integer("status"); // PostgreSQL doesn't have unsigned
```

#### 2. Change Auto Increment

```typescript theme={null}
// MySQL
table.increments("id");

// PostgreSQL
table.bigIncrements("id"); // BIGSERIAL recommended
```

#### 3. String Types

```typescript theme={null}
// MySQL - TEXT type with size
table.text("content", "mediumtext");

// PostgreSQL - TEXT has no size limit
table.text("content");
```

#### 4. Boolean Type

```typescript theme={null}
// MySQL - TINYINT(1)
table.boolean("is_active");

// PostgreSQL - Actual BOOLEAN
table.boolean("is_active");
```

#### 5. JSON Type

```typescript theme={null}
// MySQL
table.json("metadata");

// PostgreSQL - JSONB recommended (indexable)
table.jsonb("metadata");
```

## Related Documentation

* [Database Connection](/en/troubleshooting/common-errors/database-connection)
* [Sonamu CLI: migrate](/en/tools-and-cli/sonamu-cli/migrate)
* [Database Configuration](/en/configuration/sonamu-config/database)